Returning Multiple Resultsets in VB.NET in ASP.NET 3.5
Introduction
In this article, 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.
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
What we will learn in this article:
- How to build a Data Access Component to interact with a SQL Database;
- How to return multiple resultsets from a SQL database.
Getting Started
What we need to do first is create our SQL database that we will be working with. Open up Visual Studio.NET 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.
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.
We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
Once we have our two tables, we are going to want to add some records. Right-click each table in turn 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:
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!
Now to reference this connection string in our class, where we will be making the connection to the database, we need to first import System.Web.Configuration namespace, and define a variable to store it in, then assign the string to the variable we create:
Imports System.Web.Configuration
Public Class Cities
Shared Sub New()
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 our identical, the classes that represent them will also be identical:
Private _city As String
Public Property Id() As Integer
Set(ByVal value As Integer)
Public Property City() As String
Set(ByVal value As String)
Public Class tblUKcities
Private _city As String
Public Property Id() As Integer
Set(ByVal value As Integer)
Public Property City() As String
Set(ByVal value As String)
We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.
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:
[Click to enlarge]
Dim con As New SqlConnection(_connectionString)
Dim cmd As New SqlCommand(commandText, con)
Using con
Dim dr As SqlDataReader = cmd.ExecuteReader()
Do While dr.Read()
usCity.Id = CInt(Fix(dr("ID")))
usCity.City = CStr(dr("City"))
tblUScities.Add(usCity)
dr.NextResult()
Do While dr.Read()
ukCity.Id = CInt(Fix(dr("ID")))
ukCity.City = CStr(dr("City"))
tblUKcities.Add(ukCity)
The only thing left to do is incorporate this into our ASPX page. We will use two GridView controls, one for each database table, and also a button to bind the data to the GridViews. To do this, we simply add the controls to our ASPX page:
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
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:
Protected Sub butGetNames_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles butGetNames.Click
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()
What we have Learned
We have learned how to create our own Data Access Component to connect to and retrieve multiple resultsets from a SQL Server Database.
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
Attachments
|
|