2012年4月9日 星期一

GridView 新增、刪除、修改以及排序

在 Asp.net 中 GridView 也可以做很多的事情,不只有顯示資料而已。

最近研究了在 GridView 中也能新增、刪除、修改以及排序,參考了兩篇文章

GridView 排序
GridView 新刪修

經過稍加修改後,使用微軟範例資料庫 NorthWind ( 北風 ) 做測試,終於完成,以下為 *.aspx 程式碼:
<asp:GridView ID="gv" runat="server" AutoGenerateColumns="false" 
    OnRowEditing="gv_RowEditing" OnRowCancelingEdit="gv_RowCancelingEdit"
    OnRowUpdating="gv_RowUpdating" BackColor="#DDDDDD" BorderStyle="None"
    BorderWidth="1px" CellPadding="5" CellSpacing="1" GridLines="None"
    Style="line-height: 22px; width: 100%;" onrowdeleting="gv_RowDeleting" 
    AllowPaging="True" onpageindexchanging="gv_PageIndexChanging" 
    PageSize="10" AllowSorting="True" onsorting="gv_Sorting">
    <RowStyle BackColor="#ffffff" ForeColor="Black" />
    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
    <PagerStyle BackColor="#ffffff" HorizontalAlign="left" />
    <HeaderStyle BackColor="#efefef" Font-Bold="True" />
    <AlternatingRowStyle BackColor="#f7fafe" />
    <EmptyDataTemplate>
        Sorry, No any data.
    </EmptyDataTemplate>
    <Columns>
        <asp:TemplateField>
            <HeaderTemplate>
                <asp:LinkButton ID="lbInsert" runat="server" Width="70px" 
                onclick="lbInsert_Click">新增</asp:LinkButton>
            </HeaderTemplate>
            <ItemTemplate>
                <asp:LinkButton ID="lbEdit" runat="server" 
                CommandName="Edit">編輯</asp:LinkButton> 
                |
                <asp:LinkButton ID="lbDelete" runat="server" 
                OnClientClick="javascript:return confirm('確定刪除?')" 
                CommandName="Delete">刪除</asp:LinkButton>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:LinkButton ID="lbUpdate" runat="server" 
                CommandName="Update">更新</asp:LinkButton>
                |
                <asp:LinkButton ID="lbCancelUpdate" runat="server" 
                CommandName="Cancel">取消</asp:LinkButton>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:LinkButton ID="lbSave" runat="server" 
                onclick="lbSave_Click">儲存</asp:LinkButton>
                |
                <asp:LinkButton ID="lbCancelSave" runat="server" 
                onclick="lbCancelSave_Click">取消</asp:LinkButton>
            
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="客戶編號" SortExpression="CustomerID">
            <ItemTemplate>
                <asp:Label ID="lblCustomerID" runat="server" 
                Text='<%# Eval("CustomerID") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:Label ID="lblCustomerIDEdit" runat="server" 
                Text='<%# Eval("CustomerID") %>'></asp:Label>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbCustomerIDFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="公司名稱" SortExpression="CompanyName">
            <ItemTemplate>
                <asp:Label ID="lblCompanyName" runat="server" 
                Text='<%# Eval("CompanyName") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="tbCompanyNameEdit" runat="server" 
                Text='<%# Eval("CompanyName") %>' ></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbCompanyNameFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="聯絡人姓名" SortExpression="ContactName">
            <ItemTemplate>
                <asp:Label ID="lblContactName" runat="server" 
                Text='<%# Eval("ContactName") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="tbContactNameEdit" runat="server" 
                Text='<%# Eval("ContactName") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbContactNameFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="聯絡人職稱" SortExpression="ContactTitle">
            <ItemTemplate>
                <asp:Label ID="lblContactTitle" runat="server" 
                Text='<%# Eval("ContactTitle") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="tbContactTitleEdit" runat="server" 
                Text='<%# Eval("ContactTitle") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbContactTitleFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="地址" SortExpression="Address">
            <ItemTemplate>
                <asp:Label ID="lblAddress" runat="server" 
                Text='<%# Eval("Address") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="tbAddressEdit" runat="server" 
                Text='<%# Eval("Address") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbAddressFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="城市" SortExpression="City">
            <ItemTemplate>
                <asp:Label ID="lblCity" runat="server" 
                Text='<%# Eval("City") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="tbCityEdit" runat="server" 
                Text='<%# Eval("City") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbCityFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="地區" SortExpression="Region">
            <ItemTemplate>
                <asp:Label ID="lblRegion" runat="server" 
                Text='<%# Eval("Region") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="tbRegionEdit" runat="server" 
                Text='<%# Eval("Region") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbRegionFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="郵遞區號" SortExpression="PostalCode">
            <ItemTemplate>
                <asp:Label ID="lblPostalCode" runat="server" 
                Text='<%# Eval("PostalCode") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="tbPostalCodeEdit" runat="server" 
                Text='<%# Eval("PostalCode") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbPostalCodeFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="國家" SortExpression="Country">
            <ItemTemplate>
                <asp:Label ID="lblCountry" runat="server" 
                Text='<%# Eval("Country") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="tbCountryEdit" runat="server" 
                Text='<%# Eval("Country") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbCountryFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="電話" SortExpression="Phone">
            <ItemTemplate>
                <asp:Label ID="lblPhone" runat="server" 
                Text='<%# Eval("Phone") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="tbPhoneEdit" runat="server" 
                Text='<%# Eval("Phone") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbPhoneFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="傳真" SortExpression="Fax">
            <ItemTemplate>
                <asp:Label ID="lblFax" runat="server" 
                Text='<%# Eval("Fax") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:TextBox ID="tbFaxEdit" runat="server" 
                Text='<%# Eval("Fax") %>'></asp:TextBox>
            </EditItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="tbFaxFooter" runat="server" 
                Text=""></asp:TextBox>
            </FooterTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

而 *.cs 檔要引用三個參考,
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

程式碼:
private SqlConnection connection;
private SqlCommand command;
private SqlDataAdapter adapter;
private DataTable dt;

private string strConnection;
private string strConnectionName = "NorthwindConnectionString";

protected void Page_Load(object sender, EventArgs e)
{
    /* 建立資料庫連線 */
    strConnection = ConfigurationManager.
        ConnectionStrings[strConnectionName].ConnectionString;
    connection = new SqlConnection(strConnection);
    command = new SqlCommand();
    command.Connection = connection;

    if (!IsPostBack)
        GVGetData();
}

/// <summary>
/// 取得資料
/// </summary>
private DataTable GetData()
{
    dt = new DataTable();
    command.CommandText = "SELECT * FROM Customers ";
    adapter = new SqlDataAdapter(command);

    adapter.Fill(dt);

    return dt;
}

/// <summary>
/// 取得資料
/// </summary>
private void GVGetData()
{
    DataTable _dt;

    if (ViewState["se"] == null)
    {
        _dt = GetData();
        gv.DataSource = _dt;
        gv.DataBind();
    }
    else
    {
        string se = Convert.ToString(ViewState["se"]);
        SortDirection sd = (SortDirection)ViewState["sd"];
        this.GVGetData(sd, se);
    }
}

/// <summary>
/// 取得排序資料
/// </summary>
private void GVGetData(SortDirection pSortDirection, 
    string pSortExpression)
{
    DataTable _dt = GetData();

    string sSort = string.Empty;
    if (pSortDirection == SortDirection.Ascending)
    {
        sSort = pSortExpression;
    }
    else
    {
        sSort = string.Format("{0} {1}", pSortExpression, "DESC"); 
    }

    DataView dv = _dt.DefaultView;
    dv.Sort = sSort;

    gv.DataSource = dv;
    gv.DataBind();

}

/// <summary>
/// 編輯資料
/// </summary>
protected void gv_RowEditing(object sender, GridViewEditEventArgs e)
{
    gv.EditIndex = e.NewEditIndex;

    GVGetData();
}

/// <summary>
/// 取消編輯
/// </summary>
protected void gv_RowCancelingEdit(object sender, 
    GridViewCancelEditEventArgs e)
{
    gv.EditIndex = -1;

    GVGetData();
}

/// <summary>
/// 更新資料
/// </summary>
protected void gv_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    string strCustomerID, strCompanyName, strContactName, strContactTitle, strAddress, 
           strCity, strRegion, strPostalCode, strCountry, strPhone, strFax;

    strCustomerID = ((Label)gv.Rows[e.RowIndex].Cells[0].
        FindControl("lblCustomerIDEdit")).Text;
    strCompanyName = ((TextBox)gv.Rows[e.RowIndex].Cells[0].
        FindControl("tbCompanyNameEdit")).Text;
    strContactName = ((TextBox)gv.Rows[e.RowIndex].Cells[0].
        FindControl("tbContactNameEdit")).Text;
    strContactTitle = ((TextBox)gv.Rows[e.RowIndex].Cells[0].
        FindControl("tbContactTitleEdit")).Text;
    strAddress = ((TextBox)gv.Rows[e.RowIndex].Cells[0].
        FindControl("tbAddressEdit")).Text;
    strCity = ((TextBox)gv.Rows[e.RowIndex].Cells[0].
        FindControl("tbCityEdit")).Text;
    strRegion = ((TextBox)gv.Rows[e.RowIndex].Cells[0].
        FindControl("tbRegionEdit")).Text;
    strPostalCode = ((TextBox)gv.Rows[e.RowIndex].Cells[0].
        FindControl("tbPostalCodeEdit")).Text; ;
    strCountry = ((TextBox)gv.Rows[e.RowIndex].Cells[0].
        FindControl("tbCountryEdit")).Text;
    strPhone = ((TextBox)gv.Rows[e.RowIndex].Cells[0].
        FindControl("tbPhoneEdit")).Text;
    strFax = ((TextBox)gv.Rows[e.RowIndex].Cells[0].
        FindControl("tbFaxEdit")).Text;

    /* 更新資料驗證作業 
       ...
       ...
       ...
    */

    /* 更新資料 */
    command.Parameters.Clear();
    command.Parameters.AddWithValue("customerID", strCustomerID);
    command.Parameters.AddWithValue("companyName", strCompanyName);
    command.Parameters.AddWithValue("contactName", strContactName);
    command.Parameters.AddWithValue("contactTitle", strContactTitle);
    command.Parameters.AddWithValue("address", strAddress);
    command.Parameters.AddWithValue("city", strCity);
    command.Parameters.AddWithValue("region", strRegion);
    command.Parameters.AddWithValue("postalCode", strPostalCode);
    command.Parameters.AddWithValue("country", strCountry);
    command.Parameters.AddWithValue("phone", strPhone);
    command.Parameters.AddWithValue("fax", strFax);

    command.CommandText = 
        @"UPDATE Customers SET CompanyName=@companyName, " +
        @"ContactName=@contactName, ContactTitle=@contactTitle, " +
        @"Address=@address, City=@city, Region=@region, " +
        @"PostalCode=@postalCode, Country=@country, " +
        @"Phone=@phone, Fax=@fax " +
        @"WHERE CustomerID=@customerID ";

    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();

    gv.EditIndex = -1;

    GVGetData();
}

/// <summary>
/// 刪除資料
/// </summary>
protected void gv_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    string strCustomerID;

    strCustomerID = ((Label)gv.Rows[e.RowIndex].Cells[0].
        FindControl("lblCustomerID")).Text;

    /* 刪除資料 */
    command.Parameters.Clear();
    command.Parameters.AddWithValue("customerID", strCustomerID);

    command.CommandText = 
        "DELETE FROM Customers WHERE CustomerID=@customerID ";

    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();

    GVGetData();
}

protected void lbInsert_Click(object sender, EventArgs e)
{
    gv.FooterRow.Visible = true;
}

protected void lbCancelSave_Click(object sender, EventArgs e)
{
    gv.FooterRow.Visible = false;
}

/// <summary>
/// 儲存資料
/// </summary>
protected void lbSave_Click(object sender, EventArgs e)
{
    string strCustomerID, strCompanyName, strContactName, strContactTitle, strAddress,
           strCity, strRegion, strPostalCode, strCountry, strPhone, strFax;

    strCustomerID = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbCustomerIDFooter")).Text;
    strCompanyName = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbCompanyNameFooter")).Text;
    strContactName = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbContactNameFooter")).Text;
    strContactTitle = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbContactTitleFooter")).Text;
    strAddress = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbAddressFooter")).Text;
    strCity = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbCityFooter")).Text;
    strRegion = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbRegionFooter")).Text;
    strPostalCode = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbPostalCodeFooter")).Text; ;
    strCountry = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbCountryFooter")).Text;
    strPhone = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbPhoneFooter")).Text;
    strFax = ((TextBox)gv.FooterRow.Cells[0].
        FindControl("tbFaxFooter")).Text;

    /* 新增資料驗證作業 
       ...
       ...
       ...
    */

    /* 更新資料 */
    command.Parameters.Clear();
    command.Parameters.AddWithValue("customerID", strCustomerID);
    command.Parameters.AddWithValue("companyName", strCompanyName);
    command.Parameters.AddWithValue("contactName", strContactName);
    command.Parameters.AddWithValue("contactTitle", strContactTitle);
    command.Parameters.AddWithValue("address", strAddress);
    command.Parameters.AddWithValue("city", strCity);
    command.Parameters.AddWithValue("region", strRegion);
    command.Parameters.AddWithValue("postalCode", strPostalCode);
    command.Parameters.AddWithValue("country", strCountry);
    command.Parameters.AddWithValue("phone", strPhone);
    command.Parameters.AddWithValue("fax", strFax);

    command.CommandText = 
        @"INSERT INTO Customers (CustomerID, CompanyName, " + 
        @"ContactName, ContactTitle, Address, City, Region, " +
        @"PostalCode, Country, Phone, Fax) VALUES (@customerID, " +
        @"@companyName, @contactName, @contactTitle, @address, " +
        @"@city, @region, @postalCode, @country, @phone, @fax )";

    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();

    GVGetData();
}

/// <summary>
/// 換頁
/// </summary>
protected void gv_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gv.PageIndex = e.NewPageIndex;
    GVGetData();
}

/// <summary>
/// 排序
/// </summary>
protected void gv_Sorting(object sender, GridViewSortEventArgs e)
{
    string se = ViewState["se"] != null ? 
        Convert.ToString(ViewState["se"]) : string.Empty;
    SortDirection sd = ViewState["sd"] != null ? 
        (SortDirection)ViewState["sd"] : SortDirection.Ascending;

    if (string.IsNullOrEmpty(se))
    {
        se = e.SortExpression;
        sd = SortDirection.Ascending;
    }

    // 如果欄位與本來不同
    if (se != e.SortExpression)
    {
        // 切換為目前所指定欄位
        se = e.SortExpression;

        // 指定排列方式為升冪
        sd = SortDirection.Ascending;
    }
    // 如果欄位與本來相同
    else
    {
        // 切換升冪為降冪,降冪為升冪
        if (sd == SortDirection.Ascending)
            sd = SortDirection.Descending;
        else
            sd = SortDirection.Ascending;
    }

    // 紀錄欄位與排列方式 ( 升冪或降冪 )
    ViewState["se"] = se;
    ViewState["sd"] = sd;

    GVGetData(sd, se);
}

顯示:
新增:
刪除:
修改:
排序:

回aspnet目錄
回首頁



10 則留言 :

  1. protected void lbSave_Click(object sender, EventArgs e)
    {
    string strTxt_TeacherName, strTxt_NewTeacher, strTxt_School, strTxt_Field, strTxt_Office, strTxt_Phone, strTxt_Mail;

    strTxt_TeacherName = ((TextBox)GridView1.FooterRow.Cells[0].FindControl("老師姓名")).Text;
    strTxt_NewTeacher = ((TextBox)GridView1.FooterRow.Cells[0].FindControl("老師職位")).Text;
    strTxt_School = ((TextBox)GridView1.FooterRow.Cells[0].FindControl("學歷")).Text;
    strTxt_Field = ((TextBox)GridView1.FooterRow.Cells[0].FindControl("研究領域")).Text;
    strTxt_Office = ((TextBox)GridView1.FooterRow.Cells[0].FindControl("辦公室")).Text;
    strTxt_Phone = ((TextBox)GridView1.FooterRow.Cells[0].FindControl("連絡電話")).Text;
    strTxt_Mail = ((TextBox)GridView1.FooterRow.Cells[0].FindControl("信箱")).Text;

    command.Parameters.Clear();

    command.Parameters.AddWithValue("老師姓名", strTxt_TeacherName);
    command.Parameters.AddWithValue("老師職位", strTxt_NewTeacher);
    command.Parameters.AddWithValue("學歷", strTxt_School);
    command.Parameters.AddWithValue("研究領域", strTxt_Field);
    command.Parameters.AddWithValue("辦公室", strTxt_Office);
    command.Parameters.AddWithValue("連絡電話", strTxt_Phone);
    command.Parameters.AddWithValue("信箱", strTxt_Mail);

    command.CommandText =
    @"INSERT INTO 師資介紹 (老師姓名, 老師職位, 學歷, 研究領域, 辦公室, 連絡電話, 信箱)
    VALUES (@老師姓名, @老師職位, @學歷, @研究領域, @辦公室, @連絡電話, @信箱)";
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
    GridView1.DataBind();
    }
    我參考你的程式碼修改了 可是新增一筆資料無法儲存 他上面顯示說NullReferenException

    回覆刪除
    回覆
    1. 我想了解一下狀況,我的 SKYPE:david06172002

      刪除
  2. 這個範例很棒, 但有個問題, 如果一開始沒有資料的話, "新增"這個按鈕就不會出現了, 也就無法新增資料了

    回覆刪除
    回覆
    1. 謝謝你的建議,當初沒有考慮到。或許可以把新增按鈕獨立出來,不過我想應該不難解決,就有勞各位讀者做延伸了。

      刪除
  3. 似乎排版會有問題...這是前端

    //
    //
    //
    //刪除
    //
    //
    //
    //
    //
    //

    回覆刪除
    回覆
    1. 你好我使用您範例的程式碼~新增與更新功能沒有問題,但是刪除就會有問題,感覺按下刪除按鈕卻沒有進入
      protected void GridView1_RowDeleting
      (object sender, GridViewDeleteEventArgs e){
      //code
      }
      的方法內,code在這邊上傳會很亂如果不介意可以寄信給您,幫助我解決問題嗎~謝謝!!

      刪除
    2. 無法輸入程式碼,所以在下面重新發問了~謝謝!!

      刪除
  4. 想請問一下 如果套進去Calendar_SelectionChanged事件裡 我要如何取出當天日期的資料!

    回覆刪除
  5. 請問,如果刪除時,JS跳出CONFIRM的詢問畫面,若點選NO拒絕的話,在程式哪段有做判斷呢??~~~!!!

    回覆刪除
    回覆
    1. 您可以單獨拉出一個 javascript function 出來做判斷,我認為,當按下取消時,其實不需要做任何動作

      刪除

Related Posts Plugin for WordPress, Blogger...