More Proper Data Abstractions in .NET and Mono Applications

Hi Andy! 🙂 *waves*

In http://little.xmtp.net/blog/2005/10/21/proper-data-abstraction-in-net-and-mono-applications/, wow, almost a month ago, I talked about how to use Mono.Data.ProviderFactory to build data applications which are not tied to a particular ADO.NET data provider. I completely skipped using Parameters. I’d like to revisit it.

Remember that this is bad:

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connstring);
conn.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(cmdstring,conn);
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);


This is good:

IDbConnection conn = ProviderFactory.CreateConnectionFromConfig("CmDbStr");
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = cmdstring;
IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);


This is a nice, simple example of using the IDataReader. What about IDbDataAdapter? The .NET Framework SDK Documentation says to create new DataAdapter instances like this:

SqlConnection conn = new SqlConnection(connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(query, conn);
adapter.Fill(dataset);
return dataset;
...
OdbcConnection conn = new OdbcConnection(connection);
OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = new OdbcCommand(query, conn);
adapter.Fill(dataset);
return dataset;

To do so would lock me into using a particular Provider for my connections.

Using Mono.Data to create a DataAdapter is definitely different, but it works well.

IDbConnection conn = ProviderFactory.CreateConnectionFromConfig("CmDbStr");
conn.Open();
IDbCommand selectCommand = conn.CreateCommand();
selectCommand.CommandText = selectCommandString;
IDbDataAdapter da = ProviderFactory.CreateDataAdapter(selectCommand);


Once the IDbDataAdapter is created, you can set the other commands.

da.InsertCommand = conn.CreateCommand();
da.UpdateCommand = conn.CreateCommand();
da.DeleteCommand = conn.CreateCommand();
da.InsertCommand.CommandText = insertCommandString;
da.UpdateCommand.CommandText = updateCommandString;
da.DeleteCommand.CommandText = deleteCommandString;


If you like using CommandBuilders such as SqlCommandBuilder or OdbcCommandBuilder, you are stuck here. Well, the enterprise developer in me says you should be using stored procedures for your CRUD, but the Agile goofball in me says that quick and dirty wins the race. Mono.Data doesn’t have any helpers for use with Command Builders. The problem is that in ADO.NET 1.0, Command Builders are second class citizens. There are no interfaces for it. SqlCommandBuilder inherits from System.ComponentModel.Component. ADO.NET 2.0 changes this, but Mono hasn’t implemented any provider factories yet. The provider model in ADO.NET 2 may not require it. I’ve yet to dive into ADO.NET 2 enough to say for sure.

If you notice above we filled a DataSet using da.Fill(dataset). It turns out IDbDataAdapter inherits from IDataAdapter. The Fill method is from IDataAdapter, so we can fill datasets with our ProviderFactory provided adapter with no problems.

The last piece of ADO.NET which I’ll address is parameters.

From the SDK Documentation again:

myDataAdapter.SelectCommand.Parameters.Add("@CategoryName", SqlDbType.VarChar, 80).Value = "toasters";
myDataAdapter.SelectCommand.Parameters.Add("@SerialNum", SqlDbType.Int).Value = 239;
myDataAdapter.Fill(myDataSet);


Wow, it looks like that might work on our IDbCommands too! But it turns out that Parameters is an IDataParameterCollection which doesn’t have an Add method. The above Add method is a member of the SqlDataParameterCollection. The quick and easy workaround it to case and use this add method.

IDbConnection conn = ProviderFactory.CreateConnectionFromConfig("CmDbStr");
conn.Open();
IDbCommand selectCommand = conn.CreateCommand();
selectCommand.CommandText = selectCommandString;
((SqlDataParameterCollection)selectCommand.Parameters).Add("@Param",SqlDbType.VarChar,80).Value="param val";
IDbDataAdapter da = ProviderFactory.CreateDataAdapter(selectCommand);


This works, but it immediately ties us to a particular provider again.

As mentioned in the last Mono.Data post, the DataTools class has some helpers for that.

IDbCommand idcSelect = conn.CreateCommand();
idcSelect.CommandText = sSelect;
DataTools.AddParameter(idcSelect, "@Username", DbType.String);
DataTools.AddParameter(idcSelect, "@Email", DbType.String);
((IDataParameter)idpcSelect["@Username"]).Value = Username;
((IDataParameter)idpcSelect["@Email"]).Value = emailaddres;


There are many excellent helper methods in the static class Mono.Data.DataTools. I’ve found reading the source to be the most useful way to figure out how to use them.

If you do look at the source for the AddParameter Method, you will see that it is damn simple:

IDataParameter param=Cmd.CreateParameter();
Cmd.Parameters.Add(param);
param.ParameterName=ParameterName;
param.Direction=Direction;
param.DbType=DbType;
return param;


This is a direct example on how to add parameters to your IDbCommand if you wanted direct access to that parameter without using the cast in the prior example.

Did I say that Parameters was the last thing I wanted to mention? I can’t stop there. This one is really important and it is something which I have not seen anywhere in ADO.NET documentation both online or in the SDK. I don’t have much use for paper books, but I would hope it is there.

I wouldn’t even know about this, or its importance had it not been for prior experience with Perl. Yes, that is right, Perl DBI! The DBI(3) perldoc/manpage (type perldoc DBI at your friendly shell prompt) is one of the best documents for good database interaction. Specifically it points out dos and don’ts for working with databases. It stresses the use of DBI->prepare and DBI->execute for successive SQL executions. While I would hope that use of DataSets and DataAdapters would do this for you, sometimes you just have to use a Command directly. When using a Command directly, repeatedly, it is important to not “prepare” every time. This happens behind the scenes. ADO.NET doesn’t directly have a “prepare” method, so it is not as easy to see the need to solve a problem which you don’t know you have until your application slows to a snail pace.

Above we created parameters and set their values in different steps. Here is how it may look when implemented poorly.

foreach ( Username in Usernames ) {
   IDbCommand idcUpdate = conn.CreateCommand();
   idcUpdate.CommandText = sSelect;
   DataTools.AddParameter(idcUpdate, "@Username", DbType.String);
   DataTools.AddParameter(idcUpdate, "@LastDate", DbType.String);
   ((IDataParameter)idcUpdate["@Username"]).Value = Username;
   ((IDataParameter)idcUpdate["@LasteDate"]).Value = DateTime.Now;
   idcUpdate.ExecuteNonQuery();
}


That is about as bad as it gets. A new command is created for each cycle of the loop. New Parameters, new statement preparation, etc…

It turns out that it is pretty damn simple to fix.

IDbCommand idcUpdate = conn.CreateCommand();
idcUpdate.CommandText = sSelect;
DataTools.AddParameter(idcUpdate, "@Username", DbType.String);
DataTools.AddParameter(idcUpdate, "@LastDate", DbType.String);
foreach ( Username in Usernames ) {
   ((IDataParameter)idcUpdate["@Username"]).Value = Username;
   ((IDataParameter)idcUpdate["@LasteDate"]).Value = DateTime.Now;
   idcUpdate.ExecuteNonQuery();
}

Here the command is created once, outside the loop. The statement is prepared with its parameters. The loop simply changes the parameter values and executes the query again. This is the equivalent of a Perl DBI prepare outside a loop with an execute inside the loop.

I hope this helps. I’d love to see some excellent Web and Database apps from the Open Source community which don’t tie users to a particular database. *cough*wordpress*cough*

Bye Andy!