Gridview with Hyperlink sample code

In .aspx file:

<asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="false">
    <AlternatingRowStyle BackColor="green" />
    <Columns>
      <asp:HyperLinkField HeaderText="Name" DataTextField="PName"
         DataNavigateUrlFields="pname"
       DataNavigateUrlFormatString="Sales.aspx?pname={0}" />
      <asp:BoundField HeaderText="MinQty" DataField="MinOrdQty" />
      <asp:BoundField HeaderText="MaxQty" DataField="MaxOrdQty" />
      <asp:BoundField HeaderText="UnitPrice" DataField="UnitPrice" />
    </Columns>
    </asp:GridView>

In .aspx.cs file:

protected void Page_Load(object sender, EventArgs e)
    {
        string strcn = "Data source=name-of-sql-server;initial catalog=database-name;integrated security=sspi";
        SqlConnection cn = new SqlConnection(strcn);
        SqlCommand cmd = new SqlCommand("select pname,minordqty,MaxOrdQty,UnitPrice from dbo.products", cn);
        try
        {
            cn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter DA = new SqlDataAdapter(cmd);
            DA.Fill(ds);
            DataTable dt = ds.Tables[0];
            //Session["ds"] = ds;
            gvProducts.DataSource = dt;
            gvProducts.DataBind();

        }
        catch (SqlException ex)
        {

        }
        finally
        {
            cn.Close();
        }
    }
In Details page (Sales.aspx) :
<asp:GridView ID="gvSales" runat="server"></asp:GridView>

In Sales.aspx.cs:

protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["pname"] != null)
        {
            string productName = Request.QueryString["pname"].ToString();
            if (productName != null && productName != string.Empty)
                DisplaySalesDetails(productName);
        }
    }
    private void DisplaySalesDetails(string pname)
    {
        string strcn = "Data source=ADMIN-PC\\SQLEXPRESS;initial catalog=adomaterial;integrated security=sspi";
        SqlConnection cn = new SqlConnection(strcn);
        string qry=@"select * from dbo.sales where pid in(select pid from products
        where pname='";
        qry+=pname+"');";
        SqlCommand cmd = new SqlCommand(qry,cn);
        try
        {
            cn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter DA = new SqlDataAdapter(cmd);
            DA.Fill(ds);
            Session["ds"] = ds;
            gvSales.DataSource = ds;
            gvSales.DataBind();

        }
        catch (SqlException ex)
        {

        }
        finally
        {
            cn.Close();
        }
    }
Table structure:
Products table:

Pid	PName	Mid	MinOrdQty	MaxOrdQty	Active	UnitPrice
1	colgate	1	50	500		65
2	DoveSoap	1	100	1000		25
3	DoveShampoo	1	50	500		150
4	ParachuteOil	2	20	50		80
5	RinSoap	2	50	500		15
6	SurfExcel	1	50	500		55
7	VIM	2	10	100		15
8	EXO	3	10	150		35
9	Medimix	3	25	150		25
10	Pears	4	10	200		26
11	NoteBook	4	51	270		48
12	Cinthol		20	300		23
13	fiama	4	20	200		67

Sales table:

Sid	Qty	OriginalPrice	TotalPrice	Discount	Active	SaleDate	Pid
1	10	1350	1200	150		23-Dec-11 12:00:00 AM	3
2	25	2000	1800	200		21-Aug-11 12:00:00 AM	4
3	20	1300	1000	300		13-Jun-10 12:00:00 AM	1
4	150	5500	4900	600		23-Dec-09 12:00:00 AM	6
5	50	1300	1100	200		02-Jun-08 12:00:00 AM	10
6	15	375	350	25		01-Feb-12 12:00:00 AM	2
7	25	3375	3100	275		02-Feb-11 12:00:00 AM	3
8	200	3000	2500	500		07-Oct-10 12:00:00 AM	5
9	150	3300	3200	100		29-Jul-11 12:00:00 AM	9
10	100	3500	3200	300		15-Nov-10 12:00:00 AM	8
11	10	550	500	50		02-Feb-11 12:00:00 AM	6
12	50	1100	1000	100		11-Aug-09 12:00:00 AM	9
13	80	1200	950	250		23-Dec-10 12:00:00 AM	7
14	100	2500	900	400		20-Oct-11 12:00:00 AM	9
15	120	1600	1500	100		03-Oct-12 12:00:00 AM	7

Paging in GridView

In .aspx file :

<asp:GridView ID="GridView1" runat="server" PageSize="5" AllowPaging="true"         onpageindexchanging="GridView1_PageIndexChanging">
<PagerSettings Mode="Numeric" />
</asp:GridView>

In .aspx.cs file :

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

{

    protected void Page_Load(object sender, EventArgs e)

    {

        //sspi=Security Support Provider Interface ~= trusted connection

        if(!IsPostBack)

            DisplayEmployees();

    }

    private void DisplayEmployees()

    {

        string strcn = "data source=ADMIN-PC\\SQLEXPRESS;initial catalog=db17;integrated security=sspi";

        SqlConnection cn = new SqlConnection(strcn);

        SqlCommand cmd = new SqlCommand("select * from employees", cn);

        try

        {

            cn.Open();

            DataSet ds = new DataSet();

            SqlDataAdapter DA = new SqlDataAdapter(cmd);

            DA.Fill(ds);

            Session["ds"] = ds;

            GridView1.DataSource = ds;

            GridView1.DataBind();

        }

        catch (SqlException ex)

        {

        }

        finally

        {

            cn.Close();

        }

    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

    {

        DataSet ds = (DataSet)Session["ds"];

        DataTable dt = ds.Tables[0];

        GridView1.DataSource = dt;

        GridView1.PageIndex = e.NewPageIndex;//this code must be written between Datasource and DataBind

        GridView1.DataBind();

    }

}

Table structure:

eid	fname	lname	age	salary	dept	doj
1	rajeev	sukla	23	12000	.net	23-Oct-11 12:00:00 AM
2	sowmya	kumari	23	19000	db	13-Nov-10 12:00:00 AM
3	kishore	kumar	27	36000	android	16-Oct-11 12:00:00 AM
4	abimanyu	biswal	22	null	android	20-Feb-10 12:00:00 AM
5	soni	kumar	24	21800	.net	21-Jun-09 12:00:00 AM
6	anu	_singh	22	12000	db	23-Oct-10 12:00:00 AM
7	_dinesh	moh%anty	23	15000	.net	26-Aug-09 12:00:00 AM
8	nishala	_kumari	22	18000	db	19-Jul-08 12:00:00 AM
1	rajeev	sukla	23	12000	.net	23-Oct-11 12:00:00 AM

Sorting in Gridview Control

Write the following code In .aspx page :

<asp:GridView ID="gvstudents" runat="server" AllowSorting="true" 

    OnSorting="gvstudents_sorting">

    </asp:GridView>

Code in .aspx.cs:

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

{

    

    protected void Page_Load(object sender, EventArgs e)

    {

        if(!IsPostBack)

        {

            string strcon = "data source=name of sql server ;initial catalog=database name;integrated security=sspi or user id=sqlserver userid ; password=somepassword";

            string sConnection = strcon;

            DataSet ds = new DataSet();

            SqlConnection cn = new SqlConnection(sConnection);

            using (cn)

            {

                SqlCommand cmd = new SqlCommand("select * from employees", cn);

                using (cmd)

                {

                    SqlDataAdapter da = new SqlDataAdapter();

                    da.SelectCommand = cmd;

                    da.Fill(ds);

                    gvstudents.DataSource = ds;

                    gvstudents.DataBind();

                    Session["ds"] = ds;

                    ViewState["sortcolumn"] = string.Empty;

                    ViewState["sortdirection"] = string.Empty;

                }

            }

        }

    }

    protected void gvstudents_sorting(object sender, GridViewSortEventArgs e)

    {

        if (Session["ds"] != null)

        {

           DataSet ds = (DataSet)Session["ds"];

           DataTable dt = ds.Tables[0];

           DataView dv = dt.DefaultView;//DataView ado.net view in t-sql 

            if (ViewState["sortcolumn"].ToString() == e.SortExpression.ToString())

            {

                if ("asc" == ViewState["sortdirection"].ToString())

                {

                    ViewState["sortdirection"] = "DESC";

                }

                else

                {

                    ViewState["sortdirection"] = "asc";

                }

            }

            else

            {

                ViewState["sortcolumn"] = e.SortExpression.ToString();

                ViewState["sortdirection"] = "asc";

            }

            if (dv != null)

            {

                dv.Sort = e.SortExpression + " " + ViewState["sortdirection"].ToString();

                //dv.Sort= "ename asc";

                gvstudents.DataSource = dv;

                gvstudents.DataBind();

            }

        }

    }

}

Table structure :

eid	fname	lname	age	salary	dept	doj
1	rajeev	sukla	23	12000	.net	23-Oct-11 12:00:00 AM
2	sowmya	kumari	23	19000	db	13-Nov-10 12:00:00 AM
3	kishore	kumar	27	36000	android	16-Oct-11 12:00:00 AM
4	abimanyu	biswal	22	null	android	20-Feb-10 12:00:00 AM
5	soni	kumar	24	21800	.net	21-Jun-09 12:00:00 AM
6	anu	_singh	22	12000	db	23-Oct-10 12:00:00 AM
7	_dinesh	moh%anty	23	15000	.net	26-Aug-09 12:00:00 AM
8	nishala	_kumari	22	18000	db	19-Jul-08 12:00:00 AM
1	rajeev	sukla	23	12000	.net	23-Oct-11 12:00:00 AM

Asp.net Gridview edit update and delete code

In GridViewEditUpdateDelete.aspx add the following code ( in the form tag )

<asp:GridView ID="gvPatients" AutoGenerateColumns="false" runat="server"
    AutoGenerateEditButton="true" AutoGenerateDeleteButton="true"
    DataKeyNames="PatientID"
        onrowediting="gvPatients_RowEditing"
        OnRowCancelingEdit="gvPatients_RowCancel" OnRowUpdating="gvPatients_RowUpdating"
        OnRowDeleting="gvPatients_RowDeleting"
        onselectedindexchanged="gvPatients_SelectedIndexChanged">
    <HeaderStyle BackColor="AliceBlue" ForeColor="Red"/>
    <Columns>
    <asp:TemplateField HeaderText="PatientID">
    <ItemTemplate>
    <asp:Label ID="lblPatientID" runat="server" Text='<%#Eval("PatientID") %>'/>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="PatientName">
    <EditItemTemplate>
    <asp:TextBox ID="tbName" runat="server" Text='<%#Eval("PatientName")%>' />
    </EditItemTemplate>
    <ItemTemplate>
    <asp:Label ID="lblName" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"PatientName")%>' />
    </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="AGE">
    <EditItemTemplate>
    <asp:TextBox ID="tbAge" runat="server" Text='<%#Eval("age")%>' />
    </EditItemTemplate>
    <ItemTemplate>
    <asp:Label ID="lblAge" runat="server" Text='<%#Eval("age")%>' />
    </ItemTemplate>
    </asp:TemplateField>  
    </Columns>
    </asp:GridView>

In .aspx.cs page add the following code:

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

{

    DataSet ds = new DataSet();

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            FillPatientInfo();

        }

    }

    private void FillPatientInfo()

    {

        //bad approach for getting connection string.

        string strcon = "data source=your sql server name ;initial catalog=your database name ;integrated security=sspi";

        SqlConnection cn = new SqlConnection(strcon);

        SqlCommand cmd = new SqlCommand("select * from patient", cn);

        SqlDataAdapter da = new SqlDataAdapter();

        da.SelectCommand = cmd;

        da.Fill(ds);

        gvPatients.DataSource = ds;

        gvPatients.DataBind();

        Session["dsPatients"] = ds;

        //GridView1.DataSource = ds;

        //GridView1.DataBind();

    }

    protected void gvPatients_RowEditing(object sender, GridViewEditEventArgs e)

    {

        gvPatients.EditIndex = e.NewEditIndex;

        gvPatients.DataSource = (DataSet)Session["dsPatients"];

        gvPatients.DataBind();

    }

    protected void gvPatients_RowDeleting(object sender, GridViewDeleteEventArgs e)

    {

        Label lblPatientID = (Label)gvPatients.Rows[e.RowIndex].FindControl("lblPatientID");

        SqlConnection cn = new SqlConnection("data source=ADMIN-PC\\SQLEXPRESS;initial catalog=palletechnologies;integrated security=sspi");

        SqlCommand cmd = new SqlCommand();

        StringBuilder sb = new StringBuilder();

        sb.Append("delete patient where patientid='" + lblPatientID.Text + "'");

        cmd.CommandText = sb.ToString();

        cmd.Connection = cn;

        cn.Open();

        cmd.ExecuteNonQuery();

        cn.Close();

        FillPatientInfo();

    }

    protected void gvPatients_RowUpdating(object sender, GridViewUpdateEventArgs e)

    {

        Label lblPatientID = (Label)gvPatients.Rows[e.RowIndex].FindControl("lblPatientID");

        TextBox tbAge = (TextBox)gvPatients.Rows[e.RowIndex].FindControl("tbAge");

        TextBox tbName = (TextBox)gvPatients.Rows[e.RowIndex].FindControl("tbName");

        SqlConnection cn = new SqlConnection("data source=ADMIN-PC\\SQLEXPRESS;initial catalog=palletechnologies;integrated security=sspi");

        SqlCommand cmd = new SqlCommand();

        StringBuilder sb = new StringBuilder();

        sb.Append("update patient set patientname='" + tbName .Text+ "',");

        sb.Append("age='"+tbAge.Text+"' where patientid='"+lblPatientID.Text+"'");

        cmd.CommandText = sb.ToString();

        cmd.Connection = cn;

        cn.Open();

        cmd.ExecuteNonQuery();

        cn.Close();

        gvPatients.EditIndex = -1;

        FillPatientInfo();

    }

    protected void gvPatients_SelectedIndexChanged(object sender, EventArgs e)

    {

        

    }

    protected void gvPatients_RowCancel(object sender, GridViewCancelEditEventArgs e)

    {

        gvPatients.EditIndex = -1;

        e.Cancel = true;

        gvPatients.DataSource = (DataSet)Session["dsPatients"];

        gvPatients.DataBind();

    }

}

Patient table structure: 

Create a patient table with PatientID,PatientName and Age columns.

How to set startup url in asp.net mvc

1.Go to MVC web application property page and set Start Action Specific Page value equal to ControllerName/ActionMethodName ( refer below given screen shot )