Accessing SQL Server Database in Editable GridView

来源：互联网发布：爱家房产源码编辑：程序博客网时间：2024/05/18 01:10

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();    }}

0 0