System.Data.GenericClient : A custom Data Access Component
Developers often find themselves having to connect to data in a variety of datasources ranging from MS Access to large scale relational databases such as Oracle, SQL Server, MySql etc. Each different datasource type typically requires importing a different .Net provider-specific namespace for working wth a specific database. For instance, to connect to an Oracle Database the consuming application needs to import the System.Data.OracleClient Namespace and untilize classes such as OracleCommand, OracleDateReader etcettera. To connect to SqlServer applications developers import the System.Data.SqlClient namespace into their applications and program against the classes provided by the namespace. The net effect of having multpile provider specific namespaces is that your DAL components are rarely ever portable, with each different database type normally requiring provider specific code.
System.Data.GenericClient is a simple but generic custom built data access component that solves some of the issues outlined above. System.Data.GenericClient provides a very simple but familiar API that can be configured declaratively in an application configuration file as well as programmatically in your DAL. Below I'll take a look at using System.Data.Generic to connect to various datasources using a consistent API.
Here's what you need to do to get started using System.Data.GenericClient in your applications:
- Download the zip file at the link provided below . Extract the contents, then add a reference in your application to the extracted System.Data.GenericClient.dll file.
- Open up your aspplications config file and declare an appsettings entry as shown below:
<appSettings>
<add key="System.Data.GenericClient.DefaultConnectionStringName" value="mysqlConnectionString" />
</appSettings> - Declare a connectionstring entry. Make sure the name of this connection string matches the appsettings entry value specified above. In this case, the connectionstring entry name should be specified as "mysqlConnectionString. Provide a valid MySql Connection string. See example below:
<connectionStrings>
<add name="mysqlConnectionString"
connectionString="Network Address=serveradress;
Initial Catalog='mydb';User Name='sa';Password='pwd'"
providerName="MySql.Data.MySqlClient" />
</connectionStrings> - Write data access logic to connect to the database. Below is an example of how to return a datatable object from the datasource
using (System.Data.GenericClient gClnt = new GenericClient())
{
gClnt.Command.CommandType = CommandType.Text;
gClnt.Command.CommandText = "select * from employees";
using (DataTable dt = gClnt.ExecuteDataTable())
{
this.dataGridView1.DataSource = dt;
}
}
The code snippet above shows how simple it is to use System.Data.GenericClient to access data from mySQL in a provider independent format. For each aditional datasource your application needs to connect to, specify a valid connection string in the applications configuration file as outlined above, then specify a valid command text or stored procedure on GenericClient's command object.
See below for a list of operations supported by the GenericClient object:
-
public virtual int ExecuteNonQuery()
Use this method to execute an update, insert or delete operation on the underlying datasource -
public virtual string ExecuteXML()
Use this method to execute a query on the underlying datasource. Returns data in xml format -
public virtual DataTable ExecuteDataTable()
Use this method to execute a query on the underlying datasource. Returns a datatable object -
public virtual object ExecuteScalar()
Use this method to execute a query on the underlying datasource. Returns a scalar object
System.Data.GenericClient remains a work in progress. In addition to the features highlighted above, you will find other features in the namespace. These and other features will be further refined and documented in future updates.
File Download: System.Data.GenericClient.zip
Want a copy of the source code? Send email to jaycentdrysdale@hotmail.com