If you are new to .NET development, or especially Mono development you are probably wondering how to translate all of those MSDN SqlClient examples to the database that you use. Maybe you are just testing things out and using Access files, or maybe you are an SQL Lite junking on Linux and dabbling with Mono, or maybe you are a PostgreSQL fan and want to access it from Windows and Linux. For all of these cases, the rules are the same. IT DOES NOT MATTER.
I suppose I have a big gripe with the MSDN Documentation Library and the .NET Framework SDK Documentation, and even the documentation in Monodoc to a lesser extent (Lesser because there is rarely any examples there). These documentation sources don’t have good examples on how to use the Framework Data classes they way they were intended.
For Example…
public void ReadMyData(string myConnString)
{
string mySelectQuery = "SELECT OrderID, Customer FROM Orders";
SqlConnection myConnection = new SqlConnection(myConnString);
SqlCommand myCommand = new SqlCommand(mySelectQuery,myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader();
try
{
while (myReader.Read())
{
Console.WriteLine(myReader.GetInt32(0) +
", " + myReader.GetString(1));
}
}
finally
{
// always call Close when done reading.
myReader.Close();
// always call Close when done reading.
myConnection.Close();
}
}
Here, each instance of a type is tied directly to the library which talks to the database, but glancing over the Documentation it is easy to notice that SqlConnection is implemented from a more general interface called IDbConnection. SImilarly SqlCommand from IDbCommand and SqlDataReader from IDataReader.
Using these Interfaces, the instantiation looks more like this:
IDbConnection myConnection = new SqlConnection(myConnString);
IDbCommand myCommand =
new SqlCommand(mySelectQuery,myConnection);
myConnection.Open();
IDataReader myReader = myCommand.ExecuteReader();
So far we have done nothing but lower the number of points which we would have to change if we decided to change from using System.Data.SqlClient to System.Data.Odbc or another provider. A savvy wielder of regular expressions would point out that if I needed such a change, even across a large number of files, a regular expression could be formulated to make the change for me. I’ll concede to that. (I’ll also not elaborate that such a regex would probably be difficult to construct and those who would try, like me, are probably begging for trouble.) I’ll counter with one big difference.
The types used to instantiate these objects and indeed even the assembly used to instantiate these objects can easily be changed at runtime. You could easily use app.config, or another mechanism of your choice to move from SQL Server to PostgreSQL at runtime, without compiling, or even without stopping your running application.
Programmers familiar with Microsoft’s Data Access Application Blocks may be thinking this is obvious, and of course DAAB does this, but I’ll be quick to counter than DAAB as shipped in the latest Patterns and Practices Enterprise Application Blocks doesn’t include OleDb, ODBC, or any drivers other than SQL Server, Oracle, and DB2. DB2 needs a separate download to work. Also limiting is the license surrounding DAAB. DAAB is open source with a specific line item in the license not allowing applications which use DAAB to require derivative works to be open source. In other words, no GPL allowed. That drawback and the overall size and complexity(admittedly it isn’t THAT big or bad) lean me to steer away from using DAAB.
However, there is a far smaller, simpler, and more powerful-for-the-size library included in Mono! The Mono.Data assembly contains a Mono.Data namespace (surprise!). Within the namespace is 6 classes. One of them is a Enumerator. One is a configuration section handler. That leaves four classes to investigate. DataTools is a class with static members. In 2.0 I think this would be a static class. It has two function which have two and three overloads respectively. AddParameter adds a Parameter to an IDbCommmand. FillDataSet does… well… I’ll let you guess. The remaining classes are where the meat is. They are Provider, ProviderCollection, and ProviderFactory. As the names suggest they all revolve around Provider.
It is probably easiest to show some code:
IDbConnection conn = ProviderFactory.CreateConnectionFromConfig("CmDbStr");
conn.Open();
IDbCommand idc = conn.CreateCommand();
idc.CommandText = mySelectQuery;
IDataReader reader = idc.ExecuteReader();
As you can see, the only class that is actually called is a Factory Method from ProviderFactory. This returns a Provider instance. This instance then does the rest of the work. So what about which classes and assemblies are utlimately used? Well this is handled (in this case) in the app.config file. The CreateConnectionFromConfig Method reads settings from app.config which look like this:
< ?xml version="1.0" encoding="utf-8" ? >
< configuration >
< configsections >
< sectiongroup name="mono.data" >
< section name="providers" type="Mono.Data.ProviderSectionHandler,Mono.Data" / >
< /sectiongroup >
< /configsections >
< appsettings >
< add key="CmDbStr" value="factory=System.Data.SqlClient;server=MySqlServer;database=MyDataBase;uid=MyDBUser;pwd=1234" / >
< /appsettings >
< mono .data >
< providers >
< provider name="System.Data.SqlClient" connection="System.Data.SqlClient.SqlConnection"
adapter="System.Data.SqlClient.SqlDataAdapter" command="System.Data.SqlClient.SqlCommand"
description="Microsoft Sql Server Provider"
assembly="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/ >
< provider name="System.Data.OleDb" connection="System.Data.OleDb.OleDbConnection"
adapter="System.Data.OleDb.OleDbDataAdapter" command="System.Data.OleDb.OleDbCommand"
assembly="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
description="OLEDB" / >
< provider name="System.Data.Odbc" connection="System.Data.Odbc.OdbcConnection"
adapter="System.Data.OleDb.OdbcDataAdapter" command="System.Data.Odbc.OdbcCommand"
assembly="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
description="ODBC"/ >
< provider name="Mono.Data.TdsClient" connection="Mono.Data.TdsClient.TdsConnection"
adapter="Mono.Data.TdsClient.TdsDataAdapter" command="Mono.Data.TdsClient.Command"
assembly="Mono.Data.TdsClient" / >
< provider name="Mono.Data.MySql" connection="Mono.Data.MySql.MySqlConnection"
adapter="Mono.Data.MySql.MySqlDataAdapter" command="Mono.Data.MySql.MySqlCommand"
assembly="Mono.Data.MySql" / >
< provider name="Mono.Data.PostgreSqlClient"
connection="Mono.Data.PostgreSqlClient.PgSqlConnection"
adapter="Mono.Data.PostgreSqlClient.PgSqlDataAdapter" command="Modo.Data.PostgreSqlClient.PgSqlCommand"
assembly="Mono.Data.PostgreSqlClient" / >
< provider name="Mono.Data.SqliteClient"
connection="Mono.Data.SqliteClient.SqliteConnection"
adapter="Mono.Data.SqliteClient.SqliteDataAdapter" command="Mono.Data.SqliteClient.SqliteCommand"
assembly="Mono.Data.SqliteClient" / >
< provider name="Mono.Data.SybaseClient"
connection="Mono.Data.SybaseClient.SybaseConnection" command="Mono.Data.SybaseClient.Sybase.Command"
adapter="Mono.Data.SybaseClient.SybaseDataAdapter"
assembly="Mono.Data.SybaseClient" / >
< /providers >
< /mono >
< /configuration >
The Provider class actually instantiates these using Type.GetType() with a string composited with type names and assembly names from the providers section. This means the limits of Type.GetType() apply here. The assembly must either be registered in the GAC, or be in the applications working directory. (More correctly the BaseDirectory of the current AppDomain)
Remember all that junk about DAAB licensing? Well, Mono.Data is part of Mono. Mono is licensed under the… hahaha… you thought I was going to type GPL didn’t you? It is not. It turns out Mono is licensed under the MIT license. Miguel explains why in a recent interview. But this license means you can pretty much do whatever you want with it.
One of the biggest benefits I found in using Mono.Data over DAAB is that the Providers expose the DataAdapter Interface in a bit nicer way. The one common option which isn’t handled by Mono.Data, because it isn’t a required part of an ADO.NET provider is the commonly used Command Builder. Unfortunately SqlCommandBuilder and ODBCCommandBuilder are specific helper classes and not part of a standard “command builder” interface or required implementation for an ADO.NET Provider. Fortunately building commands ones self is straightforward, and since best practices say that most of the commands should be stored procedures anyway, the Command Builder isn’t useful except for those quick and dirty programs.
For an example of another Data access layer check out this code project article http://www.codeproject.com/vb/net/data_access_layer.asp.
To see just how simple Mono.Data is, check out the source in viewcvs. http://svn.myrealbox.com/viewcvs/trunk/mcs/class/Mono.Data/
Just remember that the Data Access layer of your applications can either bind you to a particular database, or it can free you from the limits of one particular database. I recommend choosing freedom whenever possible.
