populate data in gridview tricks and tips and how stringbuilder is used to populate the data

--trick 1
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;


namespace sqlappend
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {

                string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;
                using (SqlConnection con = new SqlConnection(strcon))
                {
                 
                          string str = "select * from employee";
                    con.Open();
                    SqlCommand cmd = new SqlCommand(str, con);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    GridView1.DataSource = rdr;
                    GridView1.DataBind();
                    con.Close();

                }
         
         
            }

        }
    }
}

--web config

<connectionStrings>

<add name="ConnectionStringdb" connectionString="Data Source=ANURAG-PC\SQLEXPRESS;Initial Catalog=anurag;Integrated Security=True;" providerName="System.Data.SqlClient"/>

</connectionStrings>

---------------------trick 2-------------------------------------------------------------


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Text;





namespace sqlappend
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {

                string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;
                using (SqlConnection con = new SqlConnection(strcon))
                {
             
               StringBuilder str = new StringBuilder();
          str.Append("select * from employee");
          str.Append(" where Name='anurag'");
                    con.Open();
                    SqlCommand cmd = new SqlCommand(str.ToString(), con);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    GridView1.DataSource = rdr;
                    GridView1.DataBind();
                    con.Close();

                }
         
         
            }

        }
    }
}

----trick 3 using sqladapter


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Text;





namespace sqlappend
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {

                string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;
                using (SqlConnection con = new SqlConnection(strcon))
                {
                 
                    StringBuilder str = new StringBuilder();
                    str.Append("select * from employee");
                    str.Append(" where Name='anurag'");
                    con.Open();
                    SqlCommand cmd = new SqlCommand(str.ToString(), con);
                         SqlDataAdapter adp = new SqlDataAdapter();
                    DataSet ds = new DataSet();
                    adp.SelectCommand = cmd;
                    adp.Fill(ds);
                    GridView1.DataSource = ds;
                    GridView1.DataBind();
                    con.Close();

                }
         
         
            }

        }
    }
}

--trick 4 slight change


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Text;





namespace sqlappend
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {

                string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;
                using (SqlConnection con = new SqlConnection(strcon))
                {
                 
                    StringBuilder str = new StringBuilder();
                    str.Append("select * from employee");
                    str.Append(" where Name='anurag'");
                    con.Open();
                    SqlCommand cmd = new SqlCommand(str.ToString(), con);
                    SqlDataAdapter adp = new SqlDataAdapter();
                    DataSet ds = new DataSet();
                    adp.SelectCommand = cmd;
                    adp.Fill(ds);
                    DataTable dt = ds.Tables[0];
              GridView1.DataSource = dt;
                    GridView1.DataBind();
                    con.Close();

                }
         
         
            }

        }
    }
}

--trick 5--you can skip con.open and con.close when using sqladapter


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Text;





namespace sqlappend
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {

                string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;
                using (SqlConnection con = new SqlConnection(strcon))
                {
                 
                    StringBuilder str = new StringBuilder();
                    str.Append("select * from employee");
                    str.Append(" where Name='anurag'");
               
//no con open and close. Disconnected architecture.....

                    SqlCommand cmd = new SqlCommand(str.ToString(), con);
                    SqlDataAdapter adp = new SqlDataAdapter();
                    DataSet ds = new DataSet();
                    adp.SelectCommand = cmd;
                    adp.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                 

                }
         
         
            }

        }
    }
}

---trick 6 how to use listbox and display data in it

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.IO;

using System.Data;

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

{

    protected void Page_Load(object sender, EventArgs e)

    {

   

        datasetdemo();

       

    }

    

    private void datasetdemo()

    {

        string strsql = "select * from family where lastname like '%pat%'";

        try

        {

            using (SqlConnection cnn =

            new SqlConnection

            (@"Data Source=OMSHREERAM-PC\SQLEXPRESS;Initial Catalog=anurag;

                Integrated Security= True; "))

            {

                //SqlCommand cmd;

                using(SqlDataAdapter adp=new SqlDataAdapter(strsql,cnn))

                {

               

                DataSet ds= new DataSet();

                adp.Fill(ds,"anurag");

                foreach(DataRow dr in ds.Tables["anurag"].Rows)

                    {

                   

                    Lstbox.Items.Add(dr["lastname"].ToString());

                   

                    }

                    grd.DataSource = ds;

                    grd.DataBind();

               

                }

              

            }

        }

        catch(Exception ex)

        {

            errorlabel.Text = ex.Message;

       

        }

    }

---------------------------slight change------------------------

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.IO;

using System.Data;

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

{

    protected void Page_Load(object sender, EventArgs e)

    {

   

        datasetdemo();

       

    }

    

    private void datasetdemo()

    {

        string strsql = "select * from family where lastname like '%pat%'";

        try

        {

            using (SqlConnection cnn =

            new SqlConnection

            (@"Data Source=OMSHREERAM-PC\SQLEXPRESS;Initial Catalog=anurag;

                Integrated Security= True; "))

            {

                //SqlCommand cmd;

                using(SqlDataAdapter adp=new SqlDataAdapter(strsql,cnn))

                {

               

                DataSet ds= new DataSet();

                adp.Fill(ds,"anurag");

                    //foreach(DataRow dr in ds.Tables["anurag"].Rows)

                    //{

                   

                    //Lstbox.Items.Add(dr["lastname"].ToString());

                   

                    //}

                     Lstbox.DataTextField = "lastname";

                 Lstbox.DataValueField = "personid";

                 Lstbox.DataSource = ds.Tables["anurag"];

                 Lstbox.DataBind();

                    grd.DataSource = ds;

                    grd.DataBind();

               

                }

              

            }

        }

        catch(Exception ex)

        {

            errorlabel.Text = ex.Message;

       

        }

    }

}

--same result

--Data column concept and trick


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Text;





namespace sqlappend
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {

                string strcon = ConfigurationManager.ConnectionStrings["ConnectionStringdb"].ConnectionString;
                using (SqlConnection con = new SqlConnection(strcon))
                {
                    
                    StringBuilder str = new StringBuilder();
                    str.Append("select * from employee");
                    str.Append(" where Name='anurag'");
                  
                    SqlCommand cmd = new SqlCommand(str.ToString(), con);
                    SqlDataAdapter adp = new SqlDataAdapter();
                    DataSet ds = new DataSet();
                    adp.SelectCommand = cmd;
                    adp.Fill(ds,"anurag");


            foreach (DataColumn dc in ds.Tables["anurag"].Columns)
                    {

  ListBox1.Items.Add(string.Format("{0}:{1}", dc.ColumnName, dc.DataType));


                    }


                    DataTable dt = ds.Tables[0];
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                   

                }
            
            
            }

        }
    }
}