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. C# version.
We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!
Looking for the VB 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:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Configuration;
public class People
{
private static readonly string _connectionString; public class peopleMale {
private int _id; private string _name; public int Id {
get {
return _id; } set {
_id = value; } } public string Name {
get {
return _name; } set {
_name = value; } } } public class peopleFemale {
private int _id; private string _name; public int Id {
get {
return _id; } set {
_id = value; } } public string Name {
get {
return _name; } set {
_name = value; } } } |
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.
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 static void GetData(List<People.peopleMale> tblMale, List<People.peopleFemale> tblFemale)
{
string commandText = "SELECT id,name FROM tblMale;SELECT id,name FROM tblFemale"; SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand(commandText, con); using (con) {
con.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) {
People.peopleMale newMale = new People.peopleMale();
newMale.Id = System.Convert.ToInt32((int)(dr["id"]));
newMale.Name = System.Convert.ToString(dr["name"]);
tblMale.Add(newMale); } dr.NextResult(); while (dr.Read()) {
People.peopleFemale newFemale = new People.peopleFemale();
newFemale.Id = System.Convert.ToInt32((int)(dr["id"]));
newFemale.Name = System.Convert.ToString(dr["name"]);
tblFemale.Add(newFemale); } } |
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:
static People()
{
_connectionString = WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString; } |
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 void Page_Load(object sender, EventArgs e)
{
List<People.peopleMale> theMale = new List<People.peopleMale>(); List<People.peopleFemale> theFemale = new List<People.peopleFemale>(); People.GetData(theMale, theFemale); grdMale.DataSource = theMale; grdMale.DataBind(); grdFemale.DataSource = theFemale; grdFemale.DataBind(); |
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.
Looking for the VB version? Click here!
Looking for more .NET DB Tutorials? Click Here!
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.
|