Binding GridView with Data - The ADO.NET way

This example demonstrates how to populate GridView with data from the database using the ADO.NET way.
Before you proceed reading this example be sure that you know the basics of ADO.NET manipulation. If you are not familiar with ADO.NET then I would suggest you to refer at the following link below:

ADO.NET Tutorial

STEP 1: Setting up the Connection String

- Open your Web.config file and set up your connection string like below:

<connectionStrings> <add name="MyDBConnection" connectionString="Data Source=WPHVD185022-9O0; Initial Catalog=Northwind; Integrated Security=SSPI;" providerName="System.Data.SqlClient"/> </connectionStrings>

STEP 2: Create the GetConnectionString() method

- Create a method for accessing your connection string that was set up at the Web.config file

private string GetConnectionString(){ return ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString; }

Note: MyDBConnection is the name of the connectionstring that was set up in the webconfig.

STEP 3: Setting up the GridView in the mark up (ASPX)

- Grab a GridView from the Visual Studio ToolBox and then Set AutoGenerateColumns to False.
- Add BoundField Columns in GridView and set the DataField and the HeaderText accordingly. See below:

Populating GrindView Control

STEP 4: Create the BindGridView() method

- After setting up your GridView in the mark up then switch back to Code behind
- Declare the following NameSpace below so that we can use the SqlClient built-in libraries

using System.Data.SqlClient;

-Create the method for Binding the GridView

private void BindGridView() {

    DataTable dt = new DataTable();
    SqlConnection connection = new SqlConnection(GetConnectionString()); 

    try
    {
      connection.Open();
      string sqlStatement = "SELECT \* FROM Customers";
      SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
      SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
      sqlDa.Fill(dt);

          if (dt.Rows.Count > 0)
          {
            GridView1.DataSource = dt;
            GridView1.DataBind();
          }
    }
    catch (System.Data.SqlClient.SqlException ex)
    {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
    }
    finally
    {
        connection.Close();
    }

}

STEP 5: Calling the BindGridView() method on initial load of the page.

protected void Page_Load(object sender, EventArgs e){ if (!Page.IsPostBack){ BindGridView(); } }

STEP 6: Displaying the Output in the page

- Compile and run your page to see the output.. see below

That simple!