Ado|datagrid
This is a article about using reusable code to bind ADO data to controls.
Introduced
ADO is a very powerful technique for reading data from a database, but it can be confusing, and connecting data to a DataGrid or other control requires some skill and connectivity. The approach I use is to develop standardized reusable code to access the database and display the data. I've written a lot of asp.net pages that show results in the DataGrid through SQL statements.
This article will describe how I used reusable code to connect to ADO data and display the results in the DataGrid and other controls. I'll also tell you how to develop your own code for a similar task.
Background
This article assumes that you already have knowledge of c#,sql,ado and. NET Controls.
I use the Northwind database in the demo code, but you can use any database.
Using Code Web.config
I use the <appSettings> in Web.config to save the strings that are used in the program. If you have not done so, then you should try. I typically use Web.config to save database connection information because it makes it more portable.
<appSettings>
<add key= "Dsn_sql"
Value= "Server=localhost;uid=myuser;password=pass;database=northwind;" />
</appSettings>
DataGrid.aspx.cs
Here's the complete code for the Datagrid.aspx page. The role of the Bindgrid () function in this program enables you to connect to the database and display 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 connection string from Web.config
Public String Strconnectsql =
(configurationsettings.appsettings["Dsn_sql"]);
private void Page_Load (object sender, System.EventArgs e)
{
Constructing SQL strings
String SqlString = "SELECT * from Employee";
Call and construct the Bindgrid
Bindgrid (Strconnectsql, SqlString, DATAGRID1);
}
private void Bindgrid (String dbconnectstring, String SqlCommand,
System.Web.UI.WebControls.DataGrid Dgrid)
Loading the initialization page from the database
Bind to DataGrid
{
To create a data connection
SqlConnection conn = new SqlConnection (dbconnectstring);
Calling SQL statements
SqlCommand command = new SqlCommand (SqlCommand, conn);
Create data adapter
SqlDataAdapter adapter = new SqlDataAdapter (command);
Create and populate a dataset
DataSet ds = new DataSet ();
Adapter. Fill (DS);
Fill and bind to the DataGrid
Dgrid.datasource = ds;
Dgrid.databind ();
Close connection
Conn. Close ();
}
#region Web Form Designer generated code
Override protected void OnInit (EventArgs e)
{
//
Codegen:this the call are required by the ASP.net Web Form Designer.
//
InitializeComponent ();
Base. OnInit (e);
}
private void InitializeComponent ()
{
This. Load + = new System.EventHandler (this. Page_Load);
}
#endregion
}
}
Get SQL string from Web.config
Is it flexible to allow you to take out the strings you need from web.config? In this way, I specify a connection to the database, a report server, a default URL string for the home page, and some other global strings.
Using System.Configuration;
Get connection string from Web.config
Public String Strconnectsql = (configurationsettings.appsettings["Dsn_sql"]);
private void Bindgrid ()
This is the last thing the project does. I put the code on any page and I want to get the data from my own database and display it in a DataGrid. I don't have to write complex C # or ADO code. Random access to it, through the database, SQL, DataGrid parameters, just for me to get the data.
Bindgrid () How to work
You pass to Bindgrid () a database connection, a SQL string and a DataGrid identifier, and then it connects to the database, runs the SQL command, displays the data in the DataGrid, and exits the function.
Bindgrid (db, SQL, DataGrid)
Bindgrid ("Tell Me What Database", "Tell me what SQL statement you want to run", "Tell me which DataGrid you want to display data in")
Bindgrid input
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 input for this function in C #. All you have to do is specify which DataGrid you want to use this function for.
private void Bindgrid (String dbconnectstring,
String SqlCommand, System.Web.UI.WebControls.DataGrid Dgrid)
Loading the initialization page from the database
Bind to DataGrid
{
To create a data connection
SqlConnection conn = new SqlConnection (dbconnectstring);
Calling SQL statements
SqlCommand command = new SqlCommand (SqlCommand, conn);
Create data adapter
SqlDataAdapter adapter = new SqlDataAdapter (command);
Create and populate a dataset
DataSet ds = new DataSet ();
Adapter. Fill (DS);
Fill and bind to the DataGrid
Dgrid.datasource = ds;
Dgrid.databind ();
Close connection
Conn. Close ();
}
Call Bindgrid ()
Detailed description of function Bindgrid ():
Database connection string: specified in Web.config
SQL string: Any SQL string, or even a stored procedure
identifier of the Datagrid:datagrid
private void Page_Load (object sender, System.EventArgs e)
{
Constructing SQL strings
String SqlString = "SELECT * from Employee";
Call and construct the Bindgrid
Bindgrid (Strconnectsql, SqlString, DATAGRID1);
}
Using multiple DataGrids
With different SQL commands, place three DataGrid on the page. As shown below, you can just call Bindgrid () with different SQL commands three times. So now you can use the same code using multiple DataGrid.
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);
Using Bindlist ()
All right. Now we'll move from using Bindgrid () to using Bindlist (), which can use the Drop-down list in asp.net.
The code is a little bit hard to understand because DropDownList has two more properties:
DataTextField: The Drop-down list shows what the user sees.
DataValueField: Determine the value of the user's selection.
These values are added to the input parameters of bindlist (), so you can run it 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 connection string from Web.config
Public String Strconnectsql =
(configurationsettings.appsettings["Dsn_sql"]);
private void Page_Load (object sender, System.EventArgs e)
{
Creating SQL Strings
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 the call are required by the ASP.net Web Form Designer.
//
InitializeComponent ();
Base. OnInit (e);
}
<summary>
Required to Designer support-do not modify
The contents is with the Code Editor.
</summary>
private void InitializeComponent ()
{
This. Load + = new System.EventHandler (this. Page_Load);
}
#endregion
}
}
Interesting place.
One of the benefits of doing this is that you can specify a Web control as an input parameter to a function in asp.net. This really changed my coding habits and I'm now developing more generic reusable code.
Why use this Code
It's very simple. Once you're coding for a particular control, you don't have to write it again. You can use the same code again and again.
History
November 2004 V1.1
