Interacting with Databases Using ASP.NET 2.0 with the Microsoft Data Access Application Block
(Page 1 of 6 )
This is the third article in a series focusing on developing applications using ASP.NET 2.0 and the Data Access Application Block available in the Microsoft Enterprise Application Block Library for .NET 2.0. In this article, I shall go through the most used routines in almost every .NET application with several examples.
A downloadable file for this article is available
here.
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()
Next: Retrieving a table of rows in the form of a data table using the Data Access Application Block >>
More ASP.NET Articles
More By Jagadish Chaterjee