Accessing SQL Server Database in Editable GridView

Introduction

In this article,we are going to discuss how to access the SQL Server Database using ADO.NET framework. Topics covered in this article include :

• Accessing Database using ADO.NET
• GridView data binding
• Using GridView template to implement update, delete, insert operation on database tables

1. Background Knowledge

ADO.NET Overview

Connection  

Establishes a connection to a specific data source. The base class for all Connection objects is the DbConnection class.

Command 

Executes a command against a data source. Exposes Parameters and can execute in the scope of a Transaction from a Connection. The base class for allCommand objects is the DbCommand class.

DataReader 

Reads a forward-only, read-only stream of data from a data source. The base class for all DataReader objects is the DbDataReader class.

DataAdapter

Populates a DataSet and resolves updates with the data source. The base class for all DataAdapter objects is the DbDataAdapter class.

DataSet 

The DataSet represents a complete set of data, including related tables, constraints, and relationships among the tables. An ADO.NET DataSet contains a collection of zero or more tables represented by DataTable objects. 

Accessing SQL Server Database

       Fetch Database Table using DataSet

public DataSet GetDataSet()
{
    string strCon = "Data Source =(local);Initial Catalog = StevensUniversity;
    IntegratedSecurity = True";
    string strQuery = "Select * from Student"
    SqlConnection con = new SqlConnection(strCon);
    con.Open();
    SqlCommand cmd = new SqlCommand(strQuery,con);
    DataSetds = new DataSet();
    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    adp.Fill(ds);
    con.Close();
    return ds;
}

Update Operation on Database 

public void RowDelete()
{     
    string strDelete = "Delete From Student where StudentID = 1000"    
    string strUpdate = "Update Student Set StudentName = 'Calvin Klein' Where StudentID = '1001'"    
    SqlConnection con = new SqlConnection(strCon);   
    con.Open();
    SqlCommand cmdDelete = new SqlCommand(strDelete, con);
    SqlCommand cmdUpdate = new SqlCommand(strUpdate, con);
    cmdDelete.ExecuteNonQuery();
    cmdUpdate.ExecuteNonQuery();
    con.Close();
}

2. Using GridView 

Webform Page

Step 1 -  Build an ASP.NET GridView with RowEditing, RowCancelingEdit, RowUpdating, Row Deleting , RowCommand and RowDataBound events

<asp:GridView ID="grdStudent" runat="server" AutoGenerateColumns="False" 
    DataKeyNames="StudentID" ShowFooter="True"
    OnRowEditing="grdStudent_RowEditing"
    OnRowCancelingEdit="grdStudent_RowCancelingEdit"
    OnRowUpdating="grdStudent_RowUpdating"
    OnRowDeleting="grdStudent_RowDeleting" 
    OnRowCommand="grdStudent_RowCommand"
    OnRowDataBound="grdStudent_RowBound" CellPadding="4" ForeColor="#333333" 
    GridLines="None" >
</asp:GridView>

Step 2 - Add CommandField and TemplateField

CommandField for delete command,  ItemTemplate for bindging and displaying data, EditItemTemplate for editing items, and Footer Template for inserting items

<Columns>                                                                                                                                       <asp:CommandField ShowEditButton="True"/>                                                                                          
    <asp:TemplateField ShowHeader="False">
        <ItemTemplate>
            <asp:LinkButton ID="lnkDelete" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"></asp:LinkButton>
        </ItemTemplate>
        <FooterTemplate>
            <asp:LinkButton ID="lnkInsert" runat="server" CausesValidation="False" CommandName="Insert" Text="Insert">
            </asp:LinkButton>   
       </FooterTemplate>
   </asp:TemplateField>
 
   <asp:TemplateField HeaderText="StudentID">                                                                                          
       <ItemTemplate>
           <asp:Label ID="lblStudentID" runat="server" Text='<%# Eval("StudentID")%>'></asp:Label>
       </ItemTemplate>                                                                                                                  
       <EditItemTemplate>
        <asp:TextBox runat="server" ID="txtStudentID" Text='<%# Eval("StudentID")%>'></asp:TextBox>
       </EditItemTemplate>                                                                                    
       <FooterTemplate>
            <asp:TextBox runat="server" ID="txtNewStudentID" Text='<%# Eval("StudentID") %>'></asp:TextBox>
       </FooterTemplate>                                                                                                                   
   </asp:TemplateField>                                                                                                                  
</Columns>

Code-Behind

Step 1 - Initialize GridView

public DataSet GetDataSet(string strQuery)
{
    SqlConnection con = new SqlConnection(strCon);
    con.Open();
    SqlCommand cmd = new SqlCommand(strQuery, con);
    DataSet ds = new DataSet();
    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    adp.Fill(ds);
    con.Close();
    return ds;
}
public void BindData()
{
    grdStudent.DataSource = GetDataSet("Select * from Student");
    grdStudent.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindData();
    }
}
<p>
	<span style="white-space:pre"></span><span style="white-space:pre"></span>Step 1 - Binding Data
</p>
<pre name="code" class="csharp">public void grdStudent_RowBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        DropDownList ddlGender = (DropDownList)e.Row.FindControl("ddlGender");
        Label lblGender = (Label)e.Row.FindControl("lblGender");
        //[Bug]Will get null reference exception if fail to examine the value of ddlGender
        if (ddlGender != null)
        {
            ddlGender.DataSource = GetDataSet("Select distinct Gender from Student");
            ddlGender.DataTextField = "Gender";
            ddlGender.DataValueField = "Gender";
            ddlGender.DataBind();
        }
    }
    if (e.Row.RowType == DataControlRowType.Footer)
    {
        //[Bug]Will get null reference exception if using following fetch control method
        //DropDownList ddlNewGender = (DropDownList)grdStudent.FooterRow.FindControl("ddlNewGender");
        DropDownList ddlNewGender = (DropDownList)e.Row.FindControl("ddlNewGender");
        if (ddlNewGender != null)
        {
            ddlNewGender.DataSource = GetDataSet("Select distinct Gender from Student");
            ddlNewGender.DataTextField = "Gender";
            ddlNewGender.DataValueField = "Gender";
            ddlNewGender.DataBind();
            ddlNewGender.Items.Insert(0, "--Select--");
        }
    }
}

Step 3 - Edit/Cancel Edit Command

public void grdStudent_RowEditing(object sender, GridViewEditEventArgs e)
{
    grdStudent.EditIndex = e.NewEditIndex; 
    BindData();
}
public void grdStudent_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    grdStudent.EditIndex = -1;
    BindData();
}

Step 4 - Update/Delete/Insert Data

public void grdStudent_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    string StudentID = grdStudent.DataKeys[e.RowIndex].Values[0].ToString();
    string NewStudentID = ((TextBox)grdStudent.Rows[e.RowIndex].FindControl("txtStudentID")).Text;
    string NewDepartment =((TextBox)grdStudent.Rows[e.RowIndex].FindControl("txtDepartment")).Text;
    string NewStudentName = ((TextBox)grdStudent.Rows[e.RowIndex].FindControl("txtStudentName")).Text;
    string NewGender = ((DropDownList)grdStudent.Rows[e.RowIndex].FindControl("ddlGender")).SelectedItem.ToString();
    string NewEnrollmentDate = ((TextBox)grdStudent.Rows[e.RowIndex].FindControl("txtEnrollmentDate")).Text;
    string strUpdate = "Update Student set StudentID = '" + NewStudentID + "', Department='" + NewDepartment + "', StudentName='" + NewStudentName + "', Gender='" +  NewGender  + "', EnrollmentDate='" + NewEnrollmentDate + "' where StudentID = " + StudentID;                                                      SqlConnection con = new SqlConnection(strCon);
    con.Open();
    SqlCommand cmd = new SqlCommand(strUpdate,con);
    cmd.ExecuteNonQuery();
    con.Close();
    grdStudent.EditIndex = -1;
    BindData();
}


public void grdStudent_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    //[Bug]Will get null reference exception if using following fetch control method
    //string StudentID= ((TextBox)grdStudent.Rows[e.RowIndex].FindControl("txtStudentID")).Text;
    string StudentID = grdStudent.DataKeys[e.RowIndex].Values[0].ToString();                                                                                                                string strDelete = "Delete From Student where StudentID = " + StudentID;         
    SqlConnection con = new SqlConnection(strCon);
    con.Open();
    SqlCommand cmd = new SqlCommand(strDelete, con);
    cmd.ExecuteNonQuery();
    con.Close();
    BindData();
}


public void grdStudent_RowCommand(object sender, GridViewCommandEventArgs e)
{
   if (e.CommandName.Equals("Insert"))
   {
        string NewStudentID = ((TextBox)grdStudent.FooterRow.FindControl("txtNewStudentID")).Text;
        string NewDepartment = ((TextBox)grdStudent.FooterRow.FindControl("txtNewDepartment")).Text;
        string NewStudentName = ((TextBox)grdStudent.FooterRow.FindControl("txtNewStudentName")).Text;
        string NewGender = (DropDownList)grdStudent.FooterRow.FindControl("ddlNewGender")).SelectedItem.ToString();
        string NewEnrollmentDate = ((TextBox)grdStudent.FooterRow.FindControl("txtNewEnrollmentDate")).Text;
        string strInsert = "Insert into Student (StudentID, Department, StudentName, Gender,EnrollmentDate)Values ('" + NewStudentID + "','"                                                                        +  NewDepartment + "','"+  NewStudentName + "','"+ NewGender+ "','" + NewEnrollmentDate + "')";
        SqlConnection con = new SqlConnection(strCon);
        con.Open();
        SqlCommand cmd = new SqlCommand(strInsert, con);
        cmd.ExecuteNonQuery();
        con.Close();
        BindData();
    }
}