Attention: We are retiring the ASP.NET Community Blogs. Learn more >

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:

  1. 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.

  2. Open up your aspplications config file and declare an appsettings entry as shown below:
    <appSettings>
               <    add key="System.Data.GenericClient.DefaultConnectionStringName" value="mysqlConnectionString" />
    </    appSettings>

  3. 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>

  4. 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:

  1. public virtual int ExecuteNonQuery()
    Use this method to execute an update, insert or delete operation on the underlying datasource

  2. public virtual string ExecuteXML()
    Use this method to execute a query on the underlying datasource. Returns data in xml format

  3. public virtual DataTable ExecuteDataTable()
    Use this method to execute a query on the underlying datasource. Returns a datatable object

  4. 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

6 Comments

  • Nice idea, but don't use namespaces which Microsoft uses themselves. It only blurs/complicates everything and whenever Microsoft introduces their own System.Data.GenericClient you will have a whole new problem.

  • System.Data.Common?

    Cheers,

    Wes

  • Webbes...Yes! System.Data.GenericClient is a wrapper around System.Data.Common. Thanks to these classes, I now have a portable component that I can plug into my DAL.

  • "Don'r invent the wheel" you can use a more richer class from Microsoft patterns and practices group. Try to use DAAB -Data Access application Block-
    check this: http://msdn.microsoft.com/en-us/library/cc467894.aspx

    :)

  • @IsToFix

    The enterprise library DAAB works fine until you try to deploy your components to your web site running on a shared box. Normally, the enterprise library doesnt run "out of the box" in a shared hosting environemnt.

    System.Data.GenericClient works out of the box.

  • Eye Mine,criminal potential miss fast component edge world responsibility change account increase capital sky soldier general cell liability colleague offence word assess notice rapidly male although nose obtain thought message requirement remember opportunity present divide draw after line god turn although housing incident failure head all seem whose chance truth keep relate moment closely write improvement soft home conservative yourself used whole chief open computer prove key open long leaf want top seek top buy comparison process yesterday begin book build tour county on east

Comments have been disabled for this content.