Easily use ADO in DataGrids, DropDown Lists

• Download source - 11.9 Kb

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 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.

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;
    // 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
  }
}

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;

// Get the SQL Connection string from the web.config.
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)
// 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();
}

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)
{
  // Build SQL String
  string SQLstring = "Select * FROM Employee";

  // Call and Build Grid
  // BindGrid(SQLDBcontectString, SQLstring, DataGrid);
  BindGrid(strConnectSQL, SQLstring, DataGrid1 );
}

Populating Multiple DataGrids

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 );

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;
    // 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
  }
}

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