Connecting to Different Databases Using ASP.NET 2.0

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.

Database classes included in Microsoft Enterprise Library 2.0

The database classes are the main classes used to work with databases.  The following are the classes included in Microsoft Enterprise Library 2.0.

• Database
• SqlDatabase
• OracleDatabase
• GenericDatabase

Let us start with the “Database” class.  It is basically an abstract class. It cannot be directly instantiated. The following is the declaration for the database class.

Public MustInherit Class Database

Inherits System.Object

The above class is defined in the “Microsoft.Practices.EnterpriseLibrary.Data” namespace.  You can also observe the keyword “MustInherit,” which confirms that it is an abstract class.

You can create new database classes by inheriting from the database class. That will give the entire functionality of the Database class to your customized classes and you can further enhance them according to your requirements. This is generally used when you want to deal with any other database not included in “Microsoft Enterprise Library 2.0” (ex: Sybase). If you wanted to use the Database class directly to work with your existing databases, you need to use another class, “DatabaseFactory,” to instantiate a “Database” object.

Let us deal with another declaration:

Public Class SqlDatabase

Inherits Microsoft.Practices.EnterpriseLibrary.Data.Database

You can observe that the class SqlDatabase gets inherited from the previous class Database.  It is available in the “Microsoft.Practices.EntepriseLibrary.Data.Sql” namespace.  It has the total functionality of the Database class and has been optimized (and enhanced) to work with Microsoft SQL Server databases.  This class internally uses the Microsoft SQL Server .NET managed provider available through System.data.SqlClient.

Similarly, we have another database class, “OracleDatabase,” defined similarly to the above class. It is mainly used to optimize applications with only Oracle databases.  It is placed in the “Microsoft.Practices.EnterpriseLibrary.Data.Oracle” namespace.

Finally, we have another database class, “GenericDatabase,” which again gets inherited from “database” class. This is generally used when you do not know to which database you are connecting to. It is placed in the “Microsoft.Practices.EnterpriseLibrary.Data” namespace.

Connecting to Microsoft SQL Server database using the database class: source code

The previous section explained in detail every database class existing in “Microsoft Enterprise Library 2.0.” In this section, I shall introduce you to connecting to Microsoft SQL Server database using the database class.

To start with, modify your web.config class to look something like the following:

<configuration>

      <appSettings/>

      <connectionStrings>

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

      </connectionStrings>

      <system.web>

The following code uses the database class to connect to a Microsoft SQL Server database.

ImportsSystem.Data

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 db As Database

        db = DatabaseFactory.CreateDatabase("AdventureWorks")

        Me.lblMsg.Text = db.ConnectionStringWithoutCredentials

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

        Me.GridView1.DataSource = dt

        Me.GridView1.DataBind()

    End Sub

EndClass

The next section will explain the above code.

Explaining the source code

This section explains the code listed in the previous section.  Let us go part by part.  Consider the following:

ImportsSystem.Data

ImportsMicrosoft.Practices.EnterpriseLibrary.Data

To work with datatable or dataset objects, we need to import the “System.Data” namespace.  To deal with the database class (of Enterprise Library), we need to import the “Microsoft.Practices.EnterpriseLibrary.Data” namespace.

Further proceeding we have the following:

        Dim db As Database

        db = DatabaseFactory.CreateDatabase("AdventureWorks")

You can observe that I am using the DatabaseFactory class to instantiate a database object.  We cannot directly create an object of class database using the “new” operator as it is an abstract class. The DatabaseFactory class has a “static” (or “shared”) method named “CreateDatabase,” which is mainly used to create database objects.

Once a database object is instantiated, we can get or view the connection string information using the following statement:

        Me.lblMsg.Text = db.ConnectionStringWithoutCredentials

Further proceeding, we have the following:

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

The above executes a “SELECT” statement and finally returns a data table, which contains all the rows retrieved by that statement.  Finally, I show all those rows on a “GridView” using the following two statements:

        Me.GridView1.DataSource = dt

        Me.GridView1.DataBind()

That ends the explanation for the code given in the previous section.

Connecting to Microsoft SQL Server database using the SqlDatabase class

The previous two sections concentrated on the database class to connect and work with Microsoft SQL Server.  In this section, I shall introduce you to connecting to a Microsoft SQL Server database using the SqlDatabase class.

The following code uses the SqlDatabase class to connect to a Microsoft SQL Server database.

ImportsSystem.Data

ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql

PartialClass _Default

    Inherits System.Web.UI.Page

    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal
e As System.EventArgs) Handles btnConnect.Click

        Dim db As New SqlDatabase
(ConfigurationManager.ConnectionStrings
("AdventureWorks").ConnectionString)

        Me.lblMsg.Text = db.ConnectionStringWithoutCredentials

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

        Me.GridView1.DataSource = dt

        Me.GridView1.DataBind()

    End Sub

EndClass

There exists not much difference between the code in the previous section and this section, except that I used the SqlDatabase class available in the “Microsoft.Practices.EnterpriseLibrary.Data.Sql” namespace.  You can also observe that I am using “ConfigurationManager” to retrieve connection string from the “web.config” file. 

If you wanted to use the DatabaseFactory class to instantiate a SqlDatabase object, you can do it as follows:

ImportsSystem.Data

ImportsMicrosoft.Practices.EnterpriseLibrary.Data

ImportsMicrosoft.Practices.EnterpriseLibrary.Data.Sql

PartialClass _Default

    Inherits System.Web.UI.Page

    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal
e As System.EventArgs) Handles btnConnect.Click

        Dim db As SqlDatabase = DirectCast
(DatabaseFactory.CreateDatabase("AdventureWorks"), SqlDatabase)

        Me.lblMsg.Text = db.ConnectionStringWithoutCredentials

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

        Me.GridView1.DataSource = dt

        Me.GridView1.DataBind()

    End Sub

EndClass

Connecting to Microsoft SQL Server database using the GenericDatabase class

The previous sections concentrated on database and SqlDatabase classes to connect and work with Microsoft SQL Server.  In this section, I shall introduce you to connecting to Microsoft SQL Server database using the GenericDatabase class.

The following code uses the GenericDatabase class to connect to a Microsoft SQL Server database.

ImportsSystem.Data

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 db As New GenericDatabase
(ConfigurationManager.ConnectionStrings
("AdventureWorks").ConnectionString,
System.Data.SqlClient.SqlClientFactory.Instance)

        Me.lblMsg.Text = db.ConnectionStringWithoutCredentials

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

        Me.GridView1.DataSource = dt

        Me.GridView1.DataBind()

    End Sub

EndClass

There is not much difference between the code in the previous sections and this section, except that I used the GenericDatabase class available in the “Microsoft.Practices.EnterpriseLibrary.Data” namespace. You can also observe that I am using “ConfigurationManager” to retrieve connection string from the “web.config” file. 

Connecting to OLEDB data sources using the GenericDatabase class

The previous section concentrated on using the GenericDatabase class to connect and work with Microsoft SQL Server.  In this section, I shall continue with the same class and work with OLEDB data sources.

Before you begin, modify your web.config as follows:

<configuration>

      <appSettings/>

      <connectionStrings>

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

            <add name="AdventureWorks" connectionString="Provider=
sqloledb;Data Source=.sql2k5;Initial
Catalog=AdventureWorks;Integrated Security=SSPI;"/>

      </connectionStrings>

The following code uses the GenericDatabase class to connect to Microsoft SQL Server database using an OLEDB connection string.

ImportsSystem.Data

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 db As New GenericDatabase
(ConfigurationManager.ConnectionStrings
("AdventureWorks").ConnectionString,
System.Data.OleDb.OleDbFactory.Instance)

        Me.lblMsg.Text = db.ConnectionStringWithoutCredentials

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

        Me.GridView1.DataSource = dt

        Me.GridView1.DataBind()

    End Sub

EndClass

Connecting to an Oracle database using the GenericDatabase class

The previous section concentrated on using the GenericDatabase class to connect and work with Microsoft SQL Server.  In this section, I shall continue with the same class and work with Oracle databases.

Before you begin, modify your web.config as following:

<configuration>

      <appSettings/>

      <connectionStrings>

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

            <add name="MyOracleDB" connectionString= "Provider=msdaora;Data
Source=MyOracleDB;User Id=UserName;Password=asdasd;"  />

      </connectionStrings>

The following code uses the GenericDatabase class to connect to a Microsoft SQL Server database using OLEDB connection string.

ImportsSystem.Data

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 db As New GenericDatabase
(ConfigurationManager.ConnectionStrings
("MyOracleDB").ConnectionString,
System.Data.OleDb.OleDbFactory.Instance)

        Me.lblMsg.Text = db.ConnectionStringWithoutCredentials

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

        Me.GridView1.DataSource = dt

        Me.GridView1.DataBind()

    End Sub

EndClass

Even though I used the GenericDatabase class to connect to Oracle, you can also connect using the OracleDatabase as follows:

        Dim db As OracleDatabase = DirectCast
(DatabaseFactory.CreateDatabase("MyOracleDb"), OracleDatabase)

It all depends on your needs.  In my upcoming contributions, we shall look into the most used 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. 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.