When using the gridview control there are a few different ways that we can bind data to it. Here we will walk through the steps to databind a datatable to a gridview by populating a datatable with data from multiple tables within our database. To do this we will need to create a simple web site with a small database.
We chose
Server Intellect for its
cloud servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
Adding the Default.aspx PageFirst, we want to add a simple web form to the project. At this point, I have created a new ASP.NET Empty Web Site. Next, we need to do the following:
- Right click the project in your solution explorer.
- Select add new item…
- Select a web form.
- Name it ‘Default.aspx’.
- Click add.
- Open Default.aspx up to design mode.
- Drag and drop a gridview control onto the web form
Adding the DatabaseNext, we need to add a database with two tables in it that we will use to populate the gridview with data. To do this:
- Right click the project in your solution explorer.
- Select add ASP.NET folder.
- Select App_Data.
- Right click the App_Data folder in your solution explorer.
- Select add new item…
- Select a Sql Server Database.
- Click add.
Now that we have a database, we need to add two tables to it with some sample data. To do this:
- Expand the Database.mdf folder in your server/database explorer.
- Right click the Tables folder.
- Select add new table.
- Add the following columns with their respective types to the table:
| Column Name | Data Type |
| ID | int |
| Data | nvarchar(50) |
- Save the table as ‘Table1′.
- Right click the Tables folder.
- Select add new table.
- Add the following columns with their respective types to the table:
| Column Name | Data Type |
| ID | int |
| Data | nvarchar(50) |
- Save the table as ‘Table2′.
Next, we need to add some sample data to the tables. To Table1 add the following:
| ID | Data |
| 1 | T1 Temp data 1 |
| 2 | T1 Temp data 2 |
| 3 | T1 Temp data 3 |
To Table2 add the following:
| ID | Data |
| 1 | T2 Temp data 1 |
| 2 | T2 Temp data 2 |
| 3 | T2 Temp data 3 |
Adding the ConnectionStringNow that we have a database, we need to add a new connection string to it. To do this, open up the Web.Config file for editing and add in the following code between the <configuration> and <system.web> tags:
Code Block
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Adding Data to the GridViewNext, we need to databind our data from the tables to our gridview. To do this, we need to open up Default.aspx.cs for editing and add in the following using statements:
Code Block
The using statements we will need.
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
Next, we need to add the following code to the Page_Load event method:
Code Block
The Page_Load event method.
protected void Page_Load(object sender, EventArgs e)
{
//data reader we will use to read data from our tables
SqlDataReader rdr;
//data table we will format and databind to our gridview
DataTable dt = new DataTable();
//add the 4 columns we will use to our table
dt.Columns.Add("t1ID");
dt.Columns.Add("t1Data");
dt.Columns.Add("t2ID");
dt.Columns.Add("t2Data");
//datarow we will use to add new rows to our datatable
DataRow dr;
//connect to our db
SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
//send a sql command to select everything from the first table
SqlCommand cmd = new SqlCommand("SELECT * FROM Table1", conn);
cmd.CommandType = CommandType.Text;
using (conn)
{
//open connection
conn.Open();
//read data from the table to our data reader
rdr = cmd.ExecuteReader();
//loop through each row we have read
while (rdr.Read())
{
//create a new row in our datatable
dr = dt.NewRow();
//add data to our row
dr["t1ID"] = rdr["ID"].ToString();
dr["t1Data"] = rdr["Data"].ToString();
//add row to the table
dt.Rows.Add(dr);
//update datatable
dt.AcceptChanges();
}
}
//get data from second table
//connect to our db
conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
//send a sql command to select everything from the second table
cmd = new SqlCommand("SELECT * FROM Table2", conn);
cmd.CommandType = CommandType.Text;
//a counter to keep track of which row we are adding data to
int i = 0;
using (conn)
{
conn.Open();
rdr = cmd.ExecuteReader();
//loop through each row we have read
while (rdr.Read())
{
//add data to our row
dt.Rows[i]["t2ID"] = rdr["ID"].ToString();
dt.Rows[i]["t2Data"] = rdr["Data"].ToString();
//increment our counter
i++;
}
}
//databind our datatable to our gridview
GridView1.DataSource = dt;
GridView1.DataBind();
}
Server Intellect assists companies of all sizes with their
hosting needs by offering fully configured server solutions coupled with proactive server management services.
Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
This code will create a datatable object, populate it with the data from both of our tables, and then databind it to our gridview.
