Sunday, February 17, 2013

Using Microsoft Enterprise Library Database Connection

One of the advantages of using the Microsoft Enterprise Library for website database access is that you can treat data queried as an array.  Here's the download page: Microsoft Enterprise Library.

You'll need 3 references in your project to make this example work:

Microsoft.Practices.EnterpriseLibrary.Common
Microsoft.Practices.EnterpriseLibrary.Data
Microsoft.Practices.ObjectBuilder

You'll also need these three usings:


using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;


I'm going to setup a generic database class that you can copy into your source and change the names to fit your application:


namespace MyDatabaseNS
{
    Database cpDatabase;
    DbCommand cpCommand;

    public class MyDatabase
    {
        public MyDatabase()
        {
            cpDatabase = DatabaseFactory.CreateDatabase("MYDB");
        }

        public DataSet SelectQuery(string psQuery)
        {
             cpCommand = cpDatabase.GetSqlStringCommand(psQuery);
            return cpDatabase.ExecuteDataSet(cpCommand);
        }

        public int ExecuteNonQuery(string psQuery)
        {
            cpCommand = cpDatabase.GetSqlStringCommand(psQuery);
            return cpDatabase.ExecuteNonQuery(cpCommand);
        }
    }
}


In addition to this code, you'll need to modify your web.config file (I'm assuming you are using an MS SQL server database):


<connectionStrings>

<add name="MYDB" connectionString="Data Source=SQLDATABASENAME;Persist Security Info=True;Max Pool Size=500;Connect Timeout=600;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>

</connectionStrings>


OK, now, how do you use it?  First you will need to include System.Data:

using System.Data;

Then you can instantiate a new object and use the select method to read a DataSet:


MyDatabase lMyDatabase = new MyDatabase();

string lsQuery = "SELECT * FROM sys.all_columns";
DataSet laData = lMyDatabase.SelectQuery(lsQuery);


Now you can spin through your data and perform some function (like printing it to a web page):


foreach (DataRow lpRow in laData.tables[0].rows)
{
    HttpContext.Current.Response.Write(lpRow["name"].ToString());
}

Also, be aware that there is a timeout for the query.  If you're having issues with this, then put this into your SelectQuery and ExecuteNonQuery methods before calling the return command:


cpCommand.CommandTimeout = 0;


I hope this helps with your database coding.  Drop me a comment if something doesn't seem to work.  I've been using this code since 2008 and I've had my share of "issues" with database systems.