Introduction
ADO is a very powerful way of getting data out of a database, but it's also very confusing, and there are numerous techniques and connection methods of getting your data onto a DataGrid
or other controls. The approach I took was to standardize and develop reusable compartmented code to access databases and display data. I've written ASP.NET pages that can access limitless SQL queries for displaying results in limitless DataGrid
s.
This Article will describe how I use reusable code for connecting to ADO data and display the resulting data in DataGrid
s and other controls. I will also describe how to develop your own code for similar tasks.
Background
This article assumes that you have knowledge of C#, SQL, ADO and .NET controls.
I use the NorthWind database in the demo code but it can be altered to use any database.
Using the code
Web.Config
I use the <appSettings>
in the web.config to store strings that are used in the application. If you've never used application settings in web.config then do try; I generally use web.config to store database connection information for the simple reason that it can be quickly and easily changed for the whole project and makes code / project a lot more portable.
<appSettings>
<add key="dsn_SQL"
value="SERVER=localhost;uid=myuser;password=pass;DATABASE=NorthWind;"/>
</appSettings>
DataGrid.aspx.cs
Below is the complete code for the DataGrid.aspx page. In a nutshell, the BindGrid()
function connects to the database and displays the resulting data in the DataGrid
.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Configuration;
namespace Easy_ADO_Binds
{
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
public String strConnectSQL =
(ConfigurationSettings.AppSettings["dsn_SQL"]);
private void Page_Load(object sender, System.EventArgs e)
{
string SQLstring = "Select * FROM Employee";
BindGrid(strConnectSQL, SQLstring, DataGrid1 );
}
private void BindGrid(string DBconnectString, string sqlCommand,
System.Web.UI.WebControls.DataGrid DGrid)
{
SqlConnection conn = new SqlConnection(DBconnectString);
SqlCommand command = new SqlCommand(sqlCommand, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
DGrid.DataSource = ds;
DGrid.DataBind();
conn.Close();
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
Get the SQL String From the Web.Config
This will allow you to pick your string up from the web.config, neat eh? I use this for specifying database connections, reporting servers, default URL string for home or linked projects, and any other global strings.
using System.Configuration;
public String strConnectSQL = (ConfigurationSettings.AppSettings["dsn_SQL"]);
private void BindGrid()
This is the business end of the project. I drop this code into any page I wish to fetch and display data from my database to my DataGrid
. I don't have to write complex C# or ADO code. Drop it in, pass DB, SQL, DataGrid
parameters, and it goes get the data for me.
How BindGrid() works
You pass BindGrid()
a database connection, SQL string, and the DataGrid
ID, and it goes off, makes the connection to your database, runs the SQL command, and displays the data in a DataGrid
.
BindGrid( db, SQL, DataGrid)
BindGrid( "Tell Me What Database", "Tell Me What SQL you want to run", "Tell Me What DataGrid you want to display the data in")
BindGrid Inputs
private void BindGrid(string DBconnectString,
string sqlCommand, System.Web.UI.WebControls.DataGrid DGrid)
string DBconnectString
: Database
string sqlCommand
: SQL
System.Web.UI.WebControls.DataGrid DGrid
: DataGrid
Note: you can specify a Web Control as an input for a function in C#. All you have to do is specify what DataGrid
ID you want the function to act on.
private void BindGrid(string DBconnectString,
string sqlCommand, System.Web.UI.WebControls.DataGrid DGrid)
{
SqlConnection conn = new SqlConnection(DBconnectString);
SqlCommand command = new SqlCommand(sqlCommand, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
DGrid.DataSource = ds;
DGrid.DataBind();
conn.Close();
}
Calling BindGrid()
The function BindGrid()
defines:
- Database connection string: as specified in the Web.Config
- SQL String: any SQL string, even stored procedures can be specified here.
DataGrid
: The ID of the DataGrid
.
private void Page_Load(object sender, System.EventArgs e)
{
string SQLstring = "Select * FROM Employee";
BindGrid(strConnectSQL, SQLstring, DataGrid1 );
}
Populating Multiple DataGrids
Say you wanted to populate three DataGrid
s supplied by different SQL Commands. Just call the BindGrid()
three times with different SQL commands as shown below. So you are now using the same code to populate multiple DataGrid
s.
string SQLstring1 = "Select * FROM Employee";
BindGrid(strConnectSQL, SQLstring1, DataGrid1 );
string SQLstring2 = "Select * FROM Customers";
BindGrid(strConnectSQL, SQLstring2, DataGrid2 );
string SQLstring3 = "Select * FROM Orsders";
BindGrid(strConnectSQL, SQLstring3, DataGrid3 );
Make a BindList()
OK. We are going to alter the BindGrid()
code to make a BindList()
that will populate a ASP.NET DropDownList
.
The code is a bit more complicated as the DropDownList
has two more properties you have to specify:
DataTextField
: What is displayed in the dropdown list and what the user sees.
DataValueField
: The value used by your code to determine the user choice.
These values are added to the input parameters of BindList()
, so running it goes like this:
BindList(db, SQL, Text, Value, DropDownList);
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Configuration;
namespace BindList
{
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DropDownList DropDownList1;
public String strConnectSQL =
(ConfigurationSettings.AppSettings["dsn_SQL"]);
private void Page_Load(object sender, System.EventArgs e)
{
string SQLstring = "Select EmployeeID, FirstName + ' ' + LastName" +
" as name FROM Employees";
string TextField = "name";
string ValueField = "EmployeeID";
BindList(strConnectSQL, SQLstring, TextField ,
ValueField, DropDownList1 );
}
private void BindList(string strConnectSQL, string SQLstring,
string TextField, string ValueField,
System.Web.UI.WebControls.DropDownList Dlist)
{
SqlConnection myConnection = new SqlConnection(strConnectSQL);
SqlCommand myCommand = new SqlCommand( SQLstring, myConnection );
myConnection.Open();
Dlist.DataSource = myCommand.ExecuteReader();
Dlist.DataTextField = TextField;
Dlist.DataValueField = ValueField;
Dlist.DataBind();
myConnection.Close();
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
Points of Interest
One of the best things I've learned doing this is that you can specify web controls as input parameters for functions in ASP.NET. This has certainly changed my coding habits and I'm now developing more generic reusable code.
Why Use this Code
It's really simple. Once written for a particular control, you never have to write it again. You can use the same code again and again.
History
V1.1 Nov 2004