26 August 2007


by mo

Here it is my final assignment for CMPP298 - Database Programming ADO.NET. Assignment is as follows:

“Create a Windows Forms application to support data maintenance (CRUD functionality) of Invoices per Vendor in the Payables database.

Make use of the Connection, Transaction, Command, and DataReader objects of the ADO.NET SQLClient Provider, not DataSets, DataTables, DataAdapters or the Configuration Wizard.

The user has to select a Vendor and then view, add, edit or delete Invoice records for the selected vendor. Changes have to be saved before the user can select another vendor.

Feel free to make use of a combination of UI controls such as Grids, TextBoxes, ComboBoxes, etc. “

The focus of the assignment was on ADO.NET 2.0. So my implementation of validation, UI, and domain is rather weak. I wanted to focus my attention on learning the core of ADO.NET 2.0 by learning more about…

  • IDbConnection: The interface the the abstract DbConnection type implements that all connection sub classes inherit from for each ado.net provider. (OleDb, Odbc, Oracle, SQL Server, MySQL)
  • IDbCommand: The interface that the abstract DbCommand type implements that all command objects inherit from.
  • DataTable: An in memory type used to store data in a tabular format.
  • IDataReader: A forward only, read-only type that allows you to iterate through a result set.
  • IDataParameter: This type represents a parameter for a IDbCommand object.
  • DbProviderFactories: Provides a factory method to return a DbProviderFactory abstract factory with methods such as CreateConnection.


The IDbConnection interface demands a set of very simple methods and properties. I found CreateCommand() and Open() to be the most useful. Working against the interface as opposed to the concrete implementation simplified learning for myself. I was able to focus on the most important traits and behaviors that all Connection types share.

The CreateCommand() method returns a IDbCommand type that has it’s connection property set to the the IDbConnection type that created it. Further reducing the amount of code needed to be written.

In my assignment I created and ConnectionFactory that has a single factory method that returns IDbConnection types. This separates where the connection string settings are retrieved from, from where the connection object is actually used.

using (IDbConnection connection = _connectionFactory.Create()) 
  IDbCommand command = connection.CreateCommand();
  command.CommandText = sqlQuery;
  IDataReader reader = command.ExecuteReader();
  DataTable table = new DataTable();
  return table;

You might have also noticed that IDbConnection also implements the IDisposable interface which allows me to use the type in a using block, which implicitly calls the Dispose method on the type. The Dispose method will then close the connection and clean up any other unmanaged resources.


Types that implement the IDbCommand interface are used to execute “commands” against a database. This uses the Command pattern with 3 types of execute methods.

  • ExecuteReader: will execute a SQL command against the data source and return and IDataReader type that allows you to read through a result set.
  • ExecuteNonQuery: will execute a SQL command against the data source and returns the number of rows affected by the command.
  • ExecuteScalar: will execute a SQL command against the data source and returns the value in the first column of the first row.

The CommandText property is either the name of the stored procedure or the raw SQL to execute against the data source.

IDbCommand command = connection.CreateCommand();
command.CommandText = query;


A DataTable is an in memory container for tabular data. It has a method named Load() that takes in an IDataReader type and will load the data table with the entire result set from the IDataReader. The overloaded Load method looks like this in Lutz Reflector:

A client usage of the Load method looks like this:

IDataReader reader = command.ExecuteReader();
DataTable table = new DataTable();


Types that implement the IDataReader interface allow for forward-only reading through 1 or more result sets.

The IDataReader type also inherits from IDataRecord which exposes a set of get methods like GetDecimal(), GetBoolean(), GetOrdinal(), GetString() to read out the value from each column as you iterate through the result set. This can definitely lead to some sloppy code such as…

  private void SloppyReader(IDataReader reader) {
    string customerFirstName = !reader.IsDBNull( 1 ) ? reader.GetString( 1 ) : "Unknown";
    string customerLastName = !reader.IsDBNull( 2 ) ? reader.GetString( 2 ) : "Unknown";            


The IDataParameter is a parameter that is used by command objects. For example in the following SQL syntax @FirstName and @LastName are parameters.

INSERT INTO Customer (FirstName, LastName) VALUES (@FirstName, @LastName);

The following C# will insert the values “Mo” and “Khan” as the @FirstName, and @LastName parameters.

  IDbCommand command = connection.CreateCommand( );
  command.CommandText = "INSERT INTO Customer (FirstName, LastName) VALUES (@FirstName, @LastName);";

  IDataParameter commandParameter = command.CreateParameter( );
  commandParameter.ParameterName = "@FirstName";
  commandParameter.Value = "Mo";
  command.Parameters.Add( commandParameter );

  commandParameter = command.CreateParameter( );
  commandParameter.ParameterName = "@LastName";
  commandParameter.Value = "Khan";
  command.Parameters.Add( commandParameter );


This type has a factory method called GetFactory() which returns an abstract factory for creating connections, commands, adapters and parameters. To construct a DbProviderFactory you need to specify the ADO.NET provider to use. For example for the SQL Server Provider, you would use the invariant provider name of System.Data.SqlClient. This works great with the ConnectionStringSettings section of the *.config file. Using the ConnectionStringSettings type you can extract the connection string as well as the provider name and construct a DbProviderFactory without a re-compile. With a quick change to the *.config I can switch from a SQL Server provider to an Oracle provider or MySQL provider, or an OleDb provider or and Odbc provider.

  <add name="PayablesConnection" 
       connectionString="data source=(local);Integrated Security=SSPI;Initial Catalog=Payables;" 
       providerName="System.Data.SqlClient" />

The DbProviderFactory allows you to create database agnostic ADO.NET types through factory methods.

  public DatabaseConnectionFactory(ConnectionStringSettings connectionStringSettings) {
    _settings = connectionStringSettings;

  public IDbConnection Create() {
    IDbConnection connection = DbProviderFactories.GetFactory(_settings.ProviderName).CreateConnection();
    connection.ConnectionString = _settings.ConnectionString;
    return connection;

ADO.NET seems intimidating at first, but when you break it down piece by piece it’s not such a scary beast. Now none of the examples provided use stored procedures, but using similar techniques discussed you could transition to stored procedures with ease and confidence.

Now as for DataSets, DataAdapters, CodeGen and Wizards… that’s a whole other story!

Sait.Cmpp298.Assignment3.zip (2.35 MB)

csharp database