An idea the team an I had today, was to build a more fluent interface for creating dynamic SQL queries. Here's what I mean:

[TestFixture] public
class when_creating_an_insert_query_for_two_or_more_columns {
[Test] public void should_return_the_correct_sql()
{ var query = Insert.Into<CustomersTable>()
.ValueOf("mo").ForColumn(c => c.FirstName())
.And() .ValueOf("khan").ForColumn(c
=> c.LastName()) .End(); var expected = "INSERT
INTO Customers ( FirstName, LastName ) VALUES ( @FirstName, @LastName );";
query.ToSql().ShouldBeEqualTo(expected); } }

It's the responsibility of the query object to prepare the command with the command parameter names and values, so in this test I'm just focused on the raw sql. One of the benefits of this API, is that it's strongly typed, so you can't stick a string in a column represented by a long.

For example, Imagine a customers table that looks like this:

          public class 
          CustomersTable : IDatabaseTable { public
string Name() { return "Customers";
} public IDatabaseColumn<long>
Id() { return new DatabaseColumn<long>("Id");
} public IDatabaseColumn<string>
FirstName() { return new DatabaseColumn<string>("FirstName");
} public IDatabaseColumn<string>
LastName() { return new DatabaseColumn<string>("LastName");
} }

Here's what we've got so far for contracts...

          public class 
          Insert { public
static ITableSelector<Table> Into<Table>() where Table
: IDatabaseTable { return
new TableSelector<Table>(); } } public
interface ITableSelector<Table> { IColumnSelector<Table,
ColumnType> ValueOf<ColumnType>(ColumnType value); } public
interface IColumnSelector<Table, ColumnType>
{ IChainedSelector<Table> ForColumn<TColumn>(Func<Table,
TColumn> columnSelection) where TColumn : IDatabaseColumn<ColumnType>;
} public interface IChainedSelector<Table>
{ ITableSelector<Table> And(); IQuery End();
}

And here's as far as we got with the implementation...

          public class 
          TableSelector<Table>
: ITableSelector<Table> where Table
: IDatabaseTable { public IColumnSelector<Table,
T> ValueOf<T>(T value) { return new ColumnSelector<Table,
T>(value); } } public class ColumnSelector<Table,
T> : IColumnSelector<Table, T> where Table
: IDatabaseTable { private
readonly T value; public ColumnSelector(T
value) { this.value = value; } public IChainedSelector<Table>
ForColumn<TColumn>(Func<Table, TColumn>
columnSelection) where TColumn : IDatabaseColumn<T>
{ var table = Activator.CreateInstance<Table>(); return
new ChainedSelector<Table, T, TColumn>(
table, value, columnSelection(table) ); } } public class ChainedSelector<Table,
Value, Column> : IChainedSelector<Table> where Table
: IDatabaseTable where Column
: IDatabaseColumn<Value> { private
readonly Table table; private readonly Value
value; private readonly Column column; public ChainedSelector(Table
table, Value value, Column column) { this.table =
table; this.value = value; this.column
= column; } public ITableSelector<Table>
And() { throw new NotImplementedException();
} public IQuery End()
{ var builder = new InsertStatementBuilder(table.Name());
builder.Add(column, value); return builder.EndQuery();
} }

The most important piece is still missing, and that's implementing the "And()" method on ChainedSelector... and finishing off the End method. I'm drawing a blank.. Thoughts are appreciated!

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.

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
= newDataTable(
); 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.

• 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( ); 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
= newDataTable(
); 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; } publicIDbConnection 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!