Returning Multiple Resultsets in VB.NET in ASP.NET 4.0
In this tutorial, we will be looking at how we can create a class similar to the one in the Data Access Component article that will use just one database query to return two resultsets from a SQL database. This increases performance by limiting connections to the database, also improving on the time it takes to retrieve data.
Getting Started
What we need to do first is create our SQL database that we will be working with. Open up Visual Studio.NET 2010 or 2008 and create a new web project in VB then right-click the App_Data folder in Solution Explorer and choose Add New Item.. SQL Server Database. Once it has been created, goto Server Explorer and right-click the Tables folder and choose Add New Table. This will open the table designer in VS. For this example, we will create two columns – ID and City. The data type of ID should be int, and City should be varchar(50). Now make sure that the ID column is Primary Key and the Identity Specification is set to Yes.
Yes, it is possible to find a good web host. Sometimes it takes a while to find one you are comfortable with. After trying several, we went with Server Intellect and have been very happy thus far. They are by far the most professional, customer service friendly and technically knowledgeable host we’ve found so far.
Once we’re done with that, we can save and close. Name the table tblUScities. Then add another table the same way (right-click Tables folder in Server Explorer). This table we’re going to make exactly the same, except we’re going to call it tblUKcities.
Once we have our two tables, we are going to want to add some records. Right-click each table in the Server Explorer, and then choose Show Table Data. Add some cities to each table.
Now that our database is ready, we can begin to develop our web application. Let’s go ahead and create a new class; right-click on our project in Solution Explorer and choose Add ASP.NET Folder > App_Code. Then right-click on the App_Code folder and choose Add New Item.. Class. Give it the name Cities.vb.
We should now be presented with the default code of the new class. Within this class, we will create two classes to represent our two tables. But first, we need to define our connection string. The connection string is defined in Web.config and is referenced using the WebConfigurationManager of the System.Web.Configuration namespace. In our Web.config file, we enter the connection string just before system.web like so:
<connectionStrings> <add name="ConnectionString" connectionString="Data Source=.\SQLEXP RESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClie nt"/> </connectionStrings>
Imports Microsoft.VisualBasic
Imports System.Web.Configuration
Public Class Cities
Private Shared ReadOnly _connectionString As String
Shared Sub New()
_connectionString = WebConfigurationManager.ConnectionStrings ("connectionString").ConnectionString
End Sub
End Class
Now that we are able to connect to our database, we will create the classes to represent our tables. We will name the class the same as the table name, and then define variables relating to the columns. Then we create properties within each class to represent the columns, so that we can retrieve the data successfully. Because our tables are identical, the classes that represent them will also be identical:
Public Class tblUScities
Private _id As Integer
Private _city As String
Public Property Id() As Integer
Get
Return _id
End Get
Set(ByVal value As Integer)
_id = value
End Set
End Property
Public Property City() As String
Get
Return _city
End Get
Set(ByVal value As String)
_city = value
End Set
End Property
End Class
Public Class tblUKcities
Private _id As Integer
Private _city As String
Public Property Id() As Integer
Get
Return _id
End Get
Set(ByVal value As Integer)
_id = value
End Set
End Property
Public Property City() As String
Get
Return _city
End Get
Set(ByVal value As String)
_city = value
End Set
End Property
End Class
Need help with cloud hosting? Try Server Intellect. We used them for our cloud hosting services and we are very happy with the results!
Next on the agenda is to create the method that will retrieve the data from the database. Remember, we are wanting to retrieve data from both database tables in one call or connection. To do this, we can use the SqlCommand using multiple queries. When we use multiple queries, they must be separated with a semicolon. In this method we will make use of the System.Data.SqlClient namespace, so we will need to make reference to it. We can use the same SqlDataReader for both tables, as we are using just one SqlCommand. As we begin writing the method, we are helped by the IntelliSense, which provides us with properties and methods of the classes we created:
Public Shared Sub GetData(ByVal tblUScities As List(Of Cities.tblUScities), ByVal tblUKcities As List(Of Cities.tblUKcities))
Dim commandText As String = "SELECT ID,City FROM tblUScities;SELECT ID,City FROM tblUKcities"
Dim con As New SqlConnection(_connectionString)
Dim cmd As New SqlCommand(commandText, con)
Using con
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
Do While dr.Read()
Dim usCity As New Cities.tblUScities()
usCity.Id = CInt(Fix(dr("ID")))
usCity.City = CStr(dr("City"))
tblUScities.Add(usCity)
Loop
dr.NextResult()
Do While dr.Read()
Dim ukCity As New Cities.tblUKcities()
ukCity.Id = CInt(Fix(dr("ID")))
ukCity.City = CStr(dr("City"))
tblUKcities.Add(ukCity)
Loop
End Using
End Sub
<form id="form1" runat="server">
<asp:Button ID="butGetNames" runat="server" Text="Get Data" /> <br /><br />
<strong>US Cities:</strong><br />
<asp:GridView ID="grdMale" runat="server" Width="225px" />
<br /><br />
<strong>UK Cities:</strong><br />
<asp:GridView ID="grdFemale" runat="server" Width="225px" />
</form>
Yes, it is possible to find a good web host. Sometimes it takes a while to find one you are comfortable with. After trying several, we went with Server Intellect and have been very happy thus far. They are by far the most professional, customer service friendly and technically knowledgeable host we’ve found so far.
Then the final thing is to create the button event handler. Double-click on the button in design view to create the onclick handler, then we use the following code to reference our class to retrieve the data to bind to the GridView controls:Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub butGetNames_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles butGetNames.Click
Dim usCities As List(Of Cities.tblUScities) = New List(Of Cities.tblUScities)()
Dim ukCities As List(Of Cities.tblUKcities) = New List(Of Cities.tblUKcities)()
Cities.GetData(usCities, ukCities)
grdMale.DataSource = usCities
grdMale.DataBind()
grdFemale.DataSource = ukCities
grdFemale.DataBind()
End Sub
End Class
Congratulations! We have learned how to create our own Data Access Component to connect to and retrieve multiple resultsets from a SQL Server Database. For the full source file please download below, have a good one!
MultipleRulesets-vb
