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 DataGrids.
This Article will describe how I use reusable code for connecting to ADO data and display the resulting data in DataGrids and other controls. I will also describe how to develop your own code for similar tasks.
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.
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>
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;
// Get the SQL Connection string from the web.config.
public String strConnectSQL =
(ConfigurationSettings.AppSettings["dsn_SQL"]);
private void Page_Load(object sender, System.EventArgs e)
{
// Build SQL String
string SQLstring = "Select * FROM Employee";
// Call and Build Grid
// BindGrid(SQLDBcontectString, SQLstring, DataGrid);
BindGrid(strConnectSQL, SQLstring, DataGrid1 );
}
private void BindGrid(string DBconnectString, string sqlCommand,
System.Web.UI.WebControls.DataGrid DGrid)
// Load intial page from database
// binds to datagrid
{
// create data connection
SqlConnection conn = new SqlConnection(DBconnectString);
// Call SQL from db
SqlCommand command = new SqlCommand(sqlCommand, conn);
// create data adapter
SqlDataAdapter adapter = new SqlDataAdapter(command);
// create and fill dataset
DataSet ds = new DataSet();
adapter.Fill(ds);
// fill and bind data to Datagrid
DGrid.DataSource = ds;
DGrid.DataBind();
// Close Connection
conn.Close();
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
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;// Get the SQL Connection string from the web.config.
public String strConnectSQL = (ConfigurationSettings.AppSettings["dsn_SQL"]);
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.
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") 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)
// Load intial page from database
// binds to datagrid
{
// create data connection
SqlConnection conn = new SqlConnection(DBconnectString);
// Call SQL from db
SqlCommand command = new SqlCommand(sqlCommand, conn);
// create data adapter
SqlDataAdapter adapter = new SqlDataAdapter(command);
// create and fill dataset
DataSet ds = new DataSet();
adapter.Fill(ds);
// fill and bind data to Datagrid
DGrid.DataSource = ds;
DGrid.DataBind();
// Close Connection
conn.Close();
}
The function BindGrid() defines:
DataGrid: The ID of the DataGrid. private void Page_Load(object sender, System.EventArgs e)
{
// Build SQL String
string SQLstring = "Select * FROM Employee";
// Call and Build Grid
// BindGrid(SQLDBcontectString, SQLstring, DataGrid);
BindGrid(strConnectSQL, SQLstring, DataGrid1 );
}
Say you wanted to populate three DataGrids 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 DataGrids.
// DataGrid 1
string SQLstring1 = "Select * FROM Employee";
BindGrid(strConnectSQL, SQLstring1, DataGrid1 );
// DateGrid 2
string SQLstring2 = "Select * FROM Customers";
BindGrid(strConnectSQL, SQLstring2, DataGrid2 );
//DataGrid3
string SQLstring3 = "Select * FROM Orsders";
BindGrid(strConnectSQL, SQLstring3, DataGrid3 );
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;
// Get the SQL Connection string from the web.config.
public String strConnectSQL =
(ConfigurationSettings.AppSettings["dsn_SQL"]);
private void Page_Load(object sender, System.EventArgs e)
{
// Build SQL String
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)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
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.
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.
V1.1 Nov 2004
| You must Sign In to use this message board. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
