Ryan Smith

Simplify your life, abstract wherever possible.

I don't know about you but when I'm coding with I frequently find myself doing the same things over and over.  One of those things that used to drive me crazy with .Net was retrieving a table from the database and doing something with it.  I come from the classic asp world where it 'seemed' much more straight forward than it is in .Net...seems like the code count is higher...anyway it bugged me.  I heard about Enterprise Library's Data Access Application Block and decided to check it out. Better...code count got cut by a couple lines, but still not idiot proof (a design consideration, since I'll be it's only user). So I scoured the web, learned a lot from great programmers like Jean Paul Boodhoo (who's DNRTv series about Patterns inspired the following methods...inspired is a weak word...I blatantly pinched most of this from him but did tweak it a little (especially the SqlParameters part)).  Now when I want a DataTable I can call it as easily as this:

If I have a need to use tSQL I can do this:

DataTable MyDataTable = gateway.ExecuteSqlStatementDataTable("select * from Product_Categories");

Or if I have a need to use a stored procedure I can do this: 

DataTable MyDataTable = gateway.ExecuteSqlSP("dbo.usp_GetProductCategories");

That's it...one line blammo.  If I have a stored procedure with parameters it gets a little more codey but still pretty easy I just have to create an array of sql parameters:

SqlParameter[] sqp = new SqlParameter[2]; // the number here represents the number of parameters.
sqp[0] = new SqlParameter("@param1name",SqlDbType.Int);
sqp[0].Value = 35;
sqp[1] = new SqlParameter("@param2name",SqlDbType.VarChar);
sqp[1].Value = "some text";
DataTable MyDataTable = gateway.ExecuteSqlSP(sqp, "dbo.usp_SomeStoredProcedure");

 This is accomplished with the use of the Enterprise Library DAAB and the gateway pattern. Here's how to get this up and running:

<configuration>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

<dataConfiguration defaultDatabase="Connection String" />

<add name="Connection String" connectionString="Data Source=myserver;Initial Catalog=mydatabase;Persist Security Info=True;User ID=dbusername;Password=xxxxxx"

</configuration>

Second: Make sure your project has the following references (for the DAAB part of this solution):
Microsoft.Practices.EnterpriseLibrary.Common
Microsoft.Practices.EnterpriseLibrary.Data

Third: Make your gateway class.  Here's mine in it's entirety:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace ApplicationLibrary
{
    public class DbGateway

    {

        public DataTable ExecuteSqlSP(string spname)
        {
          
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetStoredProcCommand(spname))
            {
                using (DataSet myds = db.ExecuteDataSet(dbCommand))
                {
                    return myds.Tables[0].Copy();
                }
            }
           

        }

        public DataTable ExecuteSqlSP(SqlParameter[] parms, string spname)
        {
          
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetStoredProcCommand(spname))
            {
                foreach (SqlParameter sqp in parms)
                {
                    dbCommand.Parameters.Add(sqp);
                }

                using (DataSet myds = db.ExecuteDataSet(dbCommand))
                {
                 return myds.Tables[0].Copy();
                }

            }
           
           

        }

        public DataTable ExecuteSqlStatementDataTable(string sqlstatementstring)
        {
          
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(sqlstatementstring))
            {
             using (DataSet myds = db.ExecuteDataSet(dbCommand))
                {
                 return  myds.Tables[0].Copy();
                }
            }
                     
           

        }
    }
}

Fourth: Implement and use:

What I like to do is make a private insance in the class I'm using so right after my class declaration I'd do:

private DbGateway gateway = new DbGateway();

Now in the methods of that class I can use gateway over and over like you saw in the examples at the top.

Where to go from here:
One of the first things I can see people wanting to do is return the DataSet and not just the table (in the event there are many tables in the set).  I think you can see where that'd be pretty easy.

I hope you found this article helpful.  If you have any suggestions on changes or tweaks please feel free to leave a comment.

Special Thanks:
Jean Paul Boodhoo, Svante (from the asp.net forums) and Dot Net Rocks and DnrTV. (If you don't listen to either of those casts you are missing out my friend).