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 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;
  connection.Open( );
  IDataReader reader = command.ExecuteReader( );
  DataTable table = new DataTable( );
  table.Load( reader );
  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.

IDbCommand

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.

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( );
  connection.Open( );
  command.CommandText = query;
  command.ExecuteNonQuery( );

DataTable

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( );
  table.Load( reader );

IDataReader

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";            
  }

IDataParameter

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 );

DbProviderFactories

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.

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

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)

comments powered by Disqus