Example : How to bind GridView in asp.net

In this article I will illustrate how to bind GridView from SQL DataSource in asp.net.

Open Visual Studio. 
From File menu option , Select NewWebsite .
From the templates , select Asp.NetWebsite and name it.

default.aspx is the default file created when new website is created.

Double click on "default.aspx" page , and drag and drop Gridview Control on to the design view of aspx page. (Rename gridview control if u want)

Now open default.aspx.cs file either by double click on that file or right click in the aspx page and select ViewCode.

Write the following code and execute..


using System;
/*USE THESE NAMESPACES FOR CREATING DATABASE CONNECTION*/
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
/*USE THESE NAMESPACES IF CONNECTION STRING IS DEFINED IN WEB.CONFIG FILE*/
using System.Configuration;

public partial class UrWebsiteName : System.Web.UI.Page

{
    /*DECLARE CONNECTION VARIABLES*/
    SqlConnection Con;
    SqlCommand Cmd;
    SqlDataAdapter Adp;
    DataTable Dt;


 protected void Page_Load(object sender, EventArgs e)
    {
        /*Open database Connection*/
        Con = new SqlConnection("Data Source=172.162.35.36;Initial Catalog=MyDb;User  ID=abc;Password=133");

        Con.Open();

        /*Write Sql Command*/
        Cmd = new SqlCommand("Select * from temp", Con);

        /* SqlDataAdapter to fill datatable with result set.*/

        Adp = new SqlDataAdapter(Cmd);
        Cmd.ExecuteNonQuery();
        Dt = new DataTable();
        Adp.Fill(Dt);

        /*Bind the grid with the datatable*/
        GvTable.DataSource = Dt;
        GvTable.DataBind();
    }
}

Reading and Writing Connection String in Web.Config file using asp.net (for database connection)

In this article I will explain how to write Connection String in Web.Config file and Read the same Configuration String in .cs file to establish connection with SQL Server database.

Why should we write Connection String in Web.Config file?

                If database connection is required in a single page of website , then we write the connection string and open database connection in the same page it self.
If this database connection is required in many pages , then we have to write the same set of code in all these pages.
   
               Suppose at later stage , if database connection changes (either database or login creadentials) then we need to update it in all the pages which is time taking and error prone .So it would be better if there is a place where we can define the connection string once and reuse it in all the required pages of that website.
   
              This common place is Web.Config file.
   
Open Web.Config file and in the Configuration node add ConnectionStrings node like this

   

    <connectionStrings >
        <add name="MyConString" connectionString="Data Source=173.125.26.32;Initial Catalog=MyDb;User ID=Abc;Password=123" providerName="System.data.SqlClient"/>
    </connectionStrings>

Read Connection String in .cs file

For this we need to use the namespace
  ' using System.Configuration  '

This namespace imports all the configuration details from Web.Config file.

copy paste the following code in .cs file


  protected void Page_Load(object sender, EventArgs e)
    {
        /*Use the Connection String defined in  Web.Config file*/
        Con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConString"].ToString());
        Con.Open();


        /*CREATE COMMAND*/
        Cmd = new SqlCommand("Select * From ms_prao_mst", Con);

        /*EXECUTE IT USING EXECUTEREADER IF U ARE USING SQLREADER ELSE USE EXECUTECOMMAND*/
        Rdr = Cmd.ExecuteReader();
        try
        {
            /*LOOP TILL READ FAILS AND EXTRACT THE DATA WHICH U NEED*/
            while (Rdr.Read())
            {
                /*HERE prao_cd IS THE COLUMN NAME IN THE RESULT SET*/
                if (Rdr["prao_cd"] != DBNull.Value)
                {
                    lblText.Text = lblText.Text + " -  - " + Rdr["prao_cd"].ToString();
                }
            }
        }
        finally
        {
            if (Cmd != null)
                Cmd.Dispose();
            if (Con != null)
                Con.Close();
            if (Rdr != null)
                Rdr.Dispose();
        }
       
    }

Database Connection with asp.net using C# and retrieving data using Sql DataReader

In the .cs file write the following code to make a database connection :

/*IMPORT THESE NAMESPACES FOR CREATING DATABASE CONNECTION*/

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;


  /*DECLARE CONNECTION VARIABLES*/

    SqlConnection Con;
    SqlCommand Cmd;
    SqlDataReader Rdr;

Copy paste this Page_Load function in your .cs file


protected void Page_Load(object sender, EventArgs e)
    {
        /*WRITE CONNECTION STRING AND OPEN CONNECTION*/
       
 Con = new SqlConnection("Data Source=172.168.15.26;Initial Catalog=MyDatabase;User ID=MydatabaseId;Password=123");
        Con.Open();

        /*CREATE COMMAND*/

        Cmd = new SqlCommand("Select * From ms_prao_mst", Con);
       
/*EXECUTE IT USING EXECUTEREADER IF U ARE USING SQLREADER ELSE USE EXECUTECOMMAND*/

        Rdr = Cmd.ExecuteReader();
        try
        {
            /*LOOP TILL READ FAILS AND EXTRACT THE DATA WHICH U NEED*/
            while (Rdr.Read())
            {
                /*HERE prao_cd IS THE COLUMN NAME IN THE RESULT SET*/

                if (Rdr["prao_cd"] != DBNull.Value)
                {
                     /*lblText -  ID of label control in aspx page */
                    lblText.Text = lblText.Text + " -  - " + Rdr["prao_cd"].ToString();
                }
            }
        }
        finally
        {
            if (Cmd != null)
                Cmd.Dispose();
            if (Con != null)
                Con.Close();
            if (Rdr != null)
                Rdr.Dispose();
        }
       
    }