Interacting with Databases Using ASP.NET 2.0 with the Microsoft Data Access Application Block

If you are new to Microsoft application blocks, I strongly suggest you refer to my first article in this series titled Developing ASP.NET 2.0 Applications with Microsoft Data Access Application Block.  It gives a solid foundation for beginners. 

If you are unsure of how to select and use the proper database class, I suggest that you go through my second article in this series titled Connecting to Different Databases using ASP.NET 2.0.  In this article, I focus on working with Microsoft SQL Server.

Retrieving a table of rows in the form of a dataset using the Data Access Application Block

In this section, we shall retrieve a dataset by passing a SELECT statement to the Data Access Application Block.  Before we begin, modify your web.config in such a way that it looks something like the following:

<configuration>
      <appSettings/>
      <connectionStrings>            <add name="AdventureWorks" connectionString=
"Database=AdventureWorks;
Server=(local)SQL2k5;Integrated
Security=SSPI;" providerName="System.Data.SqlClient"/> 
      </connectionStrings>

The following is the complete code needed to retrieve a dataset by passing a SELECT statement to the Data Access Application Block.

ImportsSystem.Data
ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql
ImportsMicrosoft.Practices.EnterpriseLibrary.Data
PartialClass _Default
    Inherits System.Web.UI.Page
    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal
e As System.EventArgs) Handles btnConnect.Click
        Me.GridView1.DataSource = getDataset("select * from
HumanResources.department").Tables(0)
        Me.GridView1.DataBind()
    End Sub
    Private Function getDataset(ByVal SQL As String) As DataSet
        Dim db As SqlDatabase = DirectCast
(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
        Dim ds As DataSet = db.ExecuteDataSet(CommandType.Text,
SQL)
        Return ds
    End Function   

EndClass

You can observe that in the above code, I defined my own function “getDataset,” which accepts a SELECT statement as a parameter and returns a “Dataset” object. This lets us reuse the function any number of times within the same form very easily.

You can also observe that I am assigning only the data table as the data source to the GridView control (as shown in the following):

Me.GridView1.DataSource = getDataset("select * from
HumanResources.department").Tables(0)
        Me.GridView1.DataBind()

Retrieving a table of rows in the form of a data table using the Data Access Application Block

In previous section, we worked with a dataset. In this section, we shall retrieve a data table by passing a SELECT statement to the Data Access Application Block. 

The following is the complete code needed to retrieve a data table by passing a SELECT statement to the Data Access Application Block.

ImportsSystem.Data
ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql
ImportsMicrosoft.Practices.EnterpriseLibrary.Data
PartialClass _Default
    Inherits System.Web.UI.Page
    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal
e As System.EventArgs) Handles btnConnect.Click
        Me.GridView1.DataSource = getDataTable("select * from
HumanResources.department")
        Me.GridView1.DataBind()
    End Sub
    Private Function getDataTable(ByVal SQL As String) As
DataTable
        Dim db As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
        Dim dt As DataTable = db.ExecuteDataSet(CommandType.Text,
SQL).Tables(0)
        Return dt
    End Function
EndClass

You can observe that in the above code, I defined my own function, “getDatatable,” which accepts a SELECT statement as a parameter and returns a “Datatable” object.  This lets us reuse the function any number of times within the same form very easily.

You can also observe that I am returning only the data table from within the function “getDataTable,” by selecting only the first table in the dataset as follows:

        Dim dt As DataTable = db.ExecuteDataSet(CommandType.Text,
"select * from HumanResources.department").Tables(0)
        Return dt

Retrieving a single row in the form of a data row using the Data Access Application Block

In previous sections, we worked with datasets and data tables.  In this section, we shall retrieve a single row in the form of a data row, by passing a SELECT statement to the Data Access Application Block. 

The following is the complete code needed to retrieve a data row by passing a SELECT statement to the Data Access Application Block.

ImportsSystem.Data
ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql
ImportsMicrosoft.Practices.EnterpriseLibrary.Data
PartialClass _Default
    Inherits System.Web.UI.Page
    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal
e As System.EventArgs) Handles btnConnect.Click
        Dim dr As DataRow = getDataRow("select * from
HumanResources.department where departmentid=2")
        Me.lblMsg.Text = dr("Name") & ", " & dr("GroupName")
    End Sub
    Private Function getDataRow(ByVal SQl As String) As DataRow
        Dim db As SqlDatabase = DirectCast
(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
        Dim dt As DataTable = db.ExecuteDataSet(CommandType.Text,
SQl).Tables(0)
        Dim dr As DataRow = dt.Rows(0)
        Return dr
    End Function
EndClass

You can observe that in the above code, I defined my own function, “getDataRow,” which accepts a SELECT statement as a parameter and returns a “Datarow” object. This lets us reuse the function any number of times within the same form very easily.

Let me explain it a bit.  First, I retrieve a data table using the following code:

        Dim dt As DataTable = db.ExecuteDataSet(CommandType.Text,
"select * from HumanResources.department").Tables(0)

Once the data table is filled, we can select only the first row in that table and return to the calling method using the following:

        Dim dr As DataRow = dt.Rows(0)
        Return dr

Retrieving a single value using the Data Access Application Block

In this section, we shall retrieve a single value onto the form by passing a SELECT statement to the Data Access Application Block. 

The following is the complete code:

ImportsSystem.Data
ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql
ImportsMicrosoft.Practices.EnterpriseLibrary.Data
PartialClass _Default
    Inherits System.Web.UI.Page
    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal
e As System.EventArgs) Handles btnConnect.Click
        Me.lblMsg.Text = getSingleValue("select count(*) from
HumanResources.department")
    End Sub
    Private Function getSingleValue(ByVal SQl As String) As
String
        Dim db As SqlDatabase = DirectCast
(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
        Dim dt As DataTable = db.ExecuteDataSet(CommandType.Text,
SQl).Tables(0)
        Return dt.Rows(0)(0) & ""
    End Function
EndClass

In the above code, there is nothing much to explain. Up to now, I didn’t implement exception handling in any of the above code. Just to help you understand, we can even handle errors efficiently by rewriting the above program as follows:

ImportsSystem.Data
ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql
ImportsMicrosoft.Practices.EnterpriseLibrary.Data
PartialClass _Default
    Inherits System.Web.UI.Page
    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal
e As System.EventArgs) Handles btnConnect.Click
        Try
            Me.lblMsg.Text = getSingleValue("select count(*) from
HumanResources.department")
        Catch ex As Exception
            Me.lblMsg.Text = ex.Message
        End Try
    End Sub
    Private Function getSingleValue(ByVal SQl As String) As String
        Try
            Dim db As SqlDatabase = DirectCast
(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
            Dim dt As DataTable = db.ExecuteDataSet
(CommandType.Text, SQl).Tables(0)
            Return dt.Rows(0)(0) & ""
        Catch ex As Exception
            Throw New Exception("Unable to retrieve value:" &
ex.Message)
        End Try
    End Function
EndClass

Executing a DML statement using the Data Access Application Block

In all of the previous sections, we only retrieved information from a SQL Server database using the Data Access Application Block. Now, we shall work on executing any DML statement using the Data Access Application Block.

The following is the complete code needed to execute any DML statement using the Data Access Application Block.

ImportsSystem.Data
ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql
ImportsMicrosoft.Practices.EnterpriseLibrary.Data
PartialClass _Default
    Inherits System.Web.UI.Page
    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal
e As System.EventArgs) Handles btnConnect.Click
        Try
            Me.lblMsg.Text = SQLExecute("delete from
HumanResources.department") & " rows deleted"
        Catch ex As Exception
            Me.lblMsg.Text = ex.Message
        End Try
    End Sub
    Private Function SQLExecute(ByVal SQL As String) As String
        Dim RowsAffected As Integer
        Try
            Dim db As SqlDatabase = DirectCast
(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)
            RowsAffected = db.ExecuteNonQuery(CommandType.Text,
SQL)
            Return RowsAffected
        Catch ex As Exception
            Throw New Exception("Unable to execute DML:" &
ex.Message)
        End Try
    End Function
EndClass

In the above code, you can understand that I am executing a DML statement using the “ExecuteNonQuery” method available in the class “SqlDatabase.”  As we are executing only DML and not any stored procedure, we need to specify that the “CommandType” is “Text.” The “ExecuteNonQuery” also returns the number of rows affected, once a DML command is executed. The same is being returned from the “SQLExecute” method.

Summary

I just wanted to let you know that the code in this article is neither suitable for all scenarios nor is it the best implementation for every application.  For example, you can develop another wrapper class by extending the existing Data Access Application Block to make your code reusable in several other applications. 

If you wanted to use it throughout the machine, you can make it strongly signed and place in GAC as well. Further, you can integrate with COM+, .NET Remoting, Web services and so on, by properly implementing serialization for your wrapper.  It all depends on the factors you would like to consider while developing an application.

You can also observe that I didn’t even use/execute stored procedures in any of the methods. In my upcoming contributions, we shall look into the most used advanced routines in the Data Access Application Block (along with accessing stored procedures).  Don’t forget to check back or sign up for a newsletter to notify you!

The entire source code for this article is available in the form of a downloadable zip file. The solution was developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows Server 2003 Enterprise Edition together with Microsoft SQL Server 2005 Developer Edition and Microsoft Enterprise Library for .NET Framework 2.0 (January 2006 version).  I didn’t really test the solution with any other/previous editions.  If you have any problems in executing the solution, please post in the discussion area.

Any feedback, suggestions, bugs, errors, improvements etc., are highly appreciated at jag_chat@yahoo.com.