Connecting to Different Databases Using ASP.NET 2.0 - Connecting to Microsoft SQL Server database using the database class: source code
(Page 2 of 5 )
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.
Next: Connecting to Microsoft SQL Server database using the SqlDatabase class >>
More ASP.NET Articles
More By Jagadish Chaterjee
