This tutorial will teach you how to use Data Access Components to retrieve data from more than one table in one go, which can save on server resources. VB version.
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!
Looking for the C# version? Click here!
Looking for more .NET DB Tutorials? Click Here!
Sometimes, making queries against a database can be hardware-intensive, especially if you're running a very large data-based site. Often, returning more than one datasets or resultsets from multiple tables in a database can be useful.
Using Data Access Components, we're able to customize the way we interact with a database. In this tutorial, we will demonstrate how to write a class that will connect to a SQL database and retrieve more than one resultset. We do this by calling more than one SQL statement.
In this example, we will be using one database with two tables; tblMale, which will hold male names; and tblFemale, which will hold female names. Each table will have an id column and a name column.
Once we have our database set up, we can start building our class. Right-click your project in Solution Explorer and click Add New Item.. Class.
We will start by defining our properties for the columns:
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Web.Configuration
Public Class People
Private Shared ReadOnly _connectionString As String Public Class peopleMale
Private _id As Integer Private _name 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 name() As String
Get
Return _name End Get Set(ByVal value As String)
_name = value End Set End Property End Class Public Class peopleFemale
Private _id As Integer Private _name 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 Name() As String
Get
Return _name End Get Set(ByVal value As String)
_name = value End Set End Property End Class |
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
We define a class for each of our tables, and then we define properties for each of our columns within each table.
Next, we want to create the method for retrieving the data from both tables. We do not need two methods for this, as we're going to retrieve them at the same time. The method will look something like this:
Public Shared Sub GetData(ByVal tblMale As List(Of People.peopleMale), ByVal tblFemale As List(Of People.peopleFemale))
Dim commandText As String = "SELECT id,name FROM tblMale;SELECT id,name FROM tblFemale" 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 newMale As New People.peopleMale()
newMale.Id = CInt(Fix(dr("Id")))
newMale.name = CStr(dr("Name"))
tblMale.Add(newMale) Loop dr.NextResult() Do While dr.Read()
Dim newFemale As New People.peopleFemale()
newFemale.Id = CInt(Fix(dr("Id")))
newFemale.Name = CStr(dr("Name"))
tblFemale.Add(newFemale) Loop End Using
Shared Sub New()
_connectionString = WebConfigurationManager.ConnectionStrings("connectionString").ConnectionString End Sub |
Notice we use two SQL statements in one string for the SQL Command. We use this method to retrieve all the data from both tables in the database.
Finally we define our connection string, which is stored in the Web.config:
| Shared Sub New()
_connectionString = WebConfigurationManager.ConnectionStrings("connectionString").ConnectionString End Sub |
There's one more block of code we need to add. We will add the following to the Page_Load event of the ASPX page:
| Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim theMale As List(Of People.peopleMale) = New List(Of People.peopleMale)()
Dim theFemale As List(Of People.peopleFemale) = New List(Of People.peopleFemale)()
People.GetData(theMale, theFemale)
grdMale.DataSource = theMale
grdMale.DataBind()
grdFemale.DataSource = theFemale
grdFemale.DataBind() End Sub |
This block of code sets the GridView's datasources after it gets the data from the database, using the GetData method in the class we created.
The following is what the ASPX will look like:
<form id="form1" runat="server">
<strong>Male Names:</strong><br /> <asp:GridView ID="grdMale" runat="server" Width="225px" /> <br /><br /> <strong>Female Names:</strong><br /> <asp:GridView ID="grdFemale" runat="server" Width="225px" /> |
Now if we run this web application, we should be presented with two separate tables of data - one filled with male names from the database, and the other filled with female names from the database.
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.
Looking for the C# version? Click here!
Looking for more .NET DB Tutorials? Click Here!
|