Search This Blog

Tuesday, July 22, 2014

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:

eidfnamelnameagesalarydeptdoj
1rajeevsukla2312000.net23-Oct-11 12:00:00 AM
2sowmyakumari2319000db13-Nov-10 12:00:00 AM
3kishorekumar2736000android16-Oct-11 12:00:00 AM
4abimanyubiswal22nullandroid20-Feb-10 12:00:00 AM
5sonikumar2421800.net21-Jun-09 12:00:00 AM
6anu_singh2212000db23-Oct-10 12:00:00 AM
7_dineshmoh%anty2315000.net26-Aug-09 12:00:00 AM
8nishala_kumari2218000db19-Jul-08 12:00:00 AM
1rajeevsukla2312000.net23-Oct-11 12:00:00 AM

Posted by at No comments:

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 :

eidfnamelnameagesalarydeptdoj
1rajeevsukla2312000.net23-Oct-11 12:00:00 AM
2sowmyakumari2319000db13-Nov-10 12:00:00 AM
3kishorekumar2736000android16-Oct-11 12:00:00 AM
4abimanyubiswal22nullandroid20-Feb-10 12:00:00 AM
5sonikumar2421800.net21-Jun-09 12:00:00 AM
6anu_singh2212000db23-Oct-10 12:00:00 AM
7_dineshmoh%anty2315000.net26-Aug-09 12:00:00 AM
8nishala_kumari2218000db19-Jul-08 12:00:00 AM
1rajeevsukla2312000.net23-Oct-11 12:00:00 AM

Posted by at No comments:

Monday, July 21, 2014

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.
Posted by at No comments:
Subscribe to: Posts (Atom)