执行命令方法的封装（添加/修改/删除）

 

SqlDb.cs

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
///SqlDb 的摘要说明
/// </summary>
public class SqlDb
{
    public SqlConnection sqlcon;  //申明一个SqlConnection对象
    protected string ConnectionString;
    public SqlDb()
    {
        ConnectionString = ConfigurationSettings.AppSettings["conStr"];
    }
    //保护方法，打开数据库连接
    private void Open()
    {
        //判断数据库连接是否存在
        if (sqlcon == null)
        {
            //不存在，新建并打开
            sqlcon = new SqlConnection(ConnectionString);
            sqlcon.Open();
        }
        else
        {
            //存在，判断是否处于关闭状态
            if (sqlcon.State.Equals(ConnectionState.Closed))
                sqlcon.Open();    //连接处于关闭状态，重新打开
        }
    }

    //公有方法，关闭数据库连接
    public void Close()
    {
        if (sqlcon.State.Equals(ConnectionState.Open))
        {
            sqlcon.Close();     //连接处于打开状态，关闭连接
        }
    }
    /// <summary>
    /// 析构函数，释放非托管资源
    /// </summary>
    ~SqlDb()
    {
        try
        {
            if (sqlcon != null)
                sqlcon.Close();
        }
        catch { }
        try
        {
            Dispose();
        }
        catch { }
    }
    //公有方法，释放资源
    public void Dispose()
    {
        if (sqlcon != null)        // 确保连接被关闭
        {
            sqlcon.Dispose();
            sqlcon = null;
        }
    }
    /// <summary>
    /// 此方法用来执行SQL语句
    /// </summary>
    /// <param name="SqlCom">要执行的SQL语句</param>
    /// <returns>返回受影响的行数</returns>
    public bool ExceSQL(string strSqlCom)
    {
        Open();
        SqlCommand sqlcom = new SqlCommand(strSqlCom,sqlcon);
        SqlTransaction tran = sqlcon.BeginTransaction();//实例化事务对象
        sqlcom.Transaction = tran;//将事务对象与命令对象关联
        try
        {
            //执行SQL语句
            sqlcom.ExecuteNonQuery();
            tran.Commit();//提交事务
            //SQL语句执行成功，返回true值
            return true;
        }
        catch
        {
            tran.Rollback();//事务回滚
            Close();
            return false;//SQL语句执行失败，返回false值
        }
        finally
        {  
            Close();//关闭数据库连接
        }
    }
    /// <summary>
    /// 此方法返回一个DataSet类型
    /// </summary>
    /// <param name="SqlCom">要执行的SQL语句</param>
    /// <returns></returns>
    public DataSet ExceDS(string SqlCom)
    {
        try
        {
            Open();   //打开链接
            SqlCommand sqlcom = new SqlCommand(SqlCom, sqlcon);
            SqlDataAdapter sqldata = new SqlDataAdapter();
            sqldata.SelectCommand = sqlcom;
            DataSet ds = new DataSet();
            sqldata.Fill(ds);
            return ds;
        }
        finally
        {
            Close();
        }
    }
    /// <summary>
    /// 此方法实现数据绑定到GridView中
    /// </summary>
    /// <param name="dl">要绑定的控件</param>
    /// <param name="SqlCom">要执行的SQL语句</param>
    /// <returns></returns>
    public bool BindData(GridView dl, string SqlCom)
    {
        dl.DataSource = this.ExceDS(SqlCom);
        try
        {
            dl.DataBind();
            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            Close();
        }
    }
}

StrHelper.cs

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
//引入如下命名空间
using System.Text.RegularExpressions;
using System.Text;

/// <summary>
///StrHelper 的摘要说明
/// </summary>
public class StrHelper
{
    public StrHelper(){}
    /// <summary>
    /// 截取字符串函数
    /// </summary>
    /// <param name="str">所要截取的字符串</param>
    /// <param name="num">截取字符串的长度</param>
    /// <returns></returns>
    static public string GetSubString(string str, int num)
    {
        #region
        return (str.Length > num) ? str.Substring(0, num) + "..." : str;
        #endregion
    }
    /// <summary>
    /// 截取字符串优化版
    /// </summary>
    /// <param name="stringToSub">所要截取的字符串</param>
    /// <param name="length">截取字符串的长度</param>
    /// <returns></returns>
    public static string GetFirstString(string stringToSub, int length)
    {
        #region
        Regex regex = new Regex("[\u4e00-\u9fa5]+", RegexOptions.Compiled);
        char[] stringChar = stringToSub.ToCharArray();
        StringBuilder sb = new StringBuilder();
        int nLength = 0;
        bool isCut = false;
        for (int i = 0; i < stringChar.Length; i++)
        {
            if (regex.IsMatch((stringChar[i]).ToString()))//regex.IsMatch指示正则表达式在输入字符串中是否找到匹配项
            {
                sb.Append(stringChar[i]);//将信息追加到当前 StringBuilder 的结尾
                nLength += 2;
            }
            else
            {
                sb.Append(stringChar[i]);
                nLength = nLength + 1;
            }
            if (nLength > length)//替换字符串
            {
                isCut = true;
                break;
            }
        }
        if (isCut)
            return sb.ToString() + "...";
        else
            return sb.ToString();
        #endregion
    }
    /// 弹出JavaScript小窗口
    /// </summary>
    /// <param name="js">窗口信息</param>
    public static void Alert(string message)
    {
        #region
        string js = @"<Script language='JavaScript'>
                    alert('" + message + "');</Script>";
        HttpContext.Current.Response.Write(js);

        #endregion
    }
    public static void GridViewHeader(GridView gdv)//显示表头并显示没有数据的提示信息
    {
        //表头的设置
        GridViewRow row = new GridViewRow(-1, -1, DataControlRowType.EmptyDataRow, DataControlRowState.Normal);
        foreach (DataControlField field in gdv.Columns)
        {
            TableCell cell = new TableCell();
            cell.Text = field.HeaderText;
            cell.Width = field.HeaderStyle.Width;
            cell.Height = field.HeaderStyle.Height;
            cell.ForeColor = field.HeaderStyle.ForeColor;
            cell.Font.Size = field.HeaderStyle.Font.Size;
            cell.Font.Bold = field.HeaderStyle.Font.Bold;
            cell.Font.Name = field.HeaderStyle.Font.Name;
            cell.Font.Strikeout = field.HeaderStyle.Font.Strikeout;
            cell.Font.Underline = field.HeaderStyle.Font.Underline;
            cell.BackColor = field.HeaderStyle.BackColor;
            cell.VerticalAlign = field.HeaderStyle.VerticalAlign;
            cell.HorizontalAlign = field.HeaderStyle.HorizontalAlign;
            cell.CssClass = field.HeaderStyle.CssClass;
            cell.BorderColor = field.HeaderStyle.BorderColor;
            cell.BorderStyle = field.HeaderStyle.BorderStyle;
            cell.BorderWidth = field.HeaderStyle.BorderWidth;
            row.Cells.Add(cell);
        }
        TableItemStyle headStyle = gdv.HeaderStyle;
        TableItemStyle emptyStyle = gdv.EmptyDataRowStyle;
        emptyStyle.Width = headStyle.Width;
        emptyStyle.Height = headStyle.Height;
        emptyStyle.ForeColor = headStyle.ForeColor;
        emptyStyle.Font.Size = headStyle.Font.Size;
        emptyStyle.Font.Bold = headStyle.Font.Bold;
        emptyStyle.Font.Name = headStyle.Font.Name;
        emptyStyle.Font.Strikeout = headStyle.Font.Strikeout;
        emptyStyle.Font.Underline = headStyle.Font.Underline;
        emptyStyle.BackColor = headStyle.BackColor;
        emptyStyle.VerticalAlign = headStyle.VerticalAlign;
        emptyStyle.HorizontalAlign = headStyle.HorizontalAlign;
        emptyStyle.CssClass = headStyle.CssClass;
        emptyStyle.BorderColor = headStyle.BorderColor;
        emptyStyle.BorderStyle = headStyle.BorderStyle;
        emptyStyle.BorderWidth = headStyle.BorderWidth;
        //空白行的设置
        GridViewRow row1 = new GridViewRow(0, -1, DataControlRowType.EmptyDataRow, DataControlRowState.Normal);
        TableCell cell1 = new TableCell();
        cell1.Text = "没有相关数据可以显示！";
        cell1.BackColor = System.Drawing.Color.White;
        row1.Cells.Add(cell1);
        cell1.ColumnSpan = 6;//合并列
        if (gdv.Controls.Count == 0)
        {
            gdv.Page.Response.Write("<script language='javascript'>alert('必须在初始化表格类之前执行DataBind方法并设置EmptyDataText属性不为空!');</script>");
        }
        else
        {
            gdv.Controls[0].Controls.Clear();
            gdv.Controls[0].Controls.AddAt(0, row);
            gdv.Controls[0].Controls.AddAt(1, row1);
        }
    }
}

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>无标题页</title>
    <style type="text/css">
        .style1
        {
            width: 25%;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            BackColor="#DEBA84" BorderColor="#DEBA84" BorderWidth="1px" 
            CellPadding="3" 
            onrowcancelingedit="GridView1_RowCancelingEdit" 
            onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" 
            onrowupdating="GridView1_RowUpdating" PageSize="4" 
            style="font-size: small" AllowPaging="True" EmptyDataText="没有相关数据可以显示！" 
            onpageindexchanging="GridView1_PageIndexChanging" BorderStyle="None" 
            CellSpacing="2" DataKeyNames="BccdID">
            <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
            <Columns>
                <asp:BoundField DataField="BccdID" HeaderText="编号" ReadOnly="True" />
                <asp:BoundField DataField="BccdName" HeaderText="名称" />
                <asp:BoundField DataField="BccdPrice" HeaderText="价格" />
                <asp:BoundField DataField="BccdInStock" HeaderText="现有库存量" />
                <asp:BoundField DataField="BccdSaleDate" DataFormatString="{0:d}" 
                    HeaderText="发行日期" ReadOnly="True" />
                <asp:CommandField ButtonType="Image" CancelImageUrl="~/Images/BtnCancel.gif" 
                    EditImageUrl="~/Images/BtnUpdate.gif" HeaderText="编辑" ShowEditButton="True" 
                    UpdateImageUrl="~/Images/BtnSave.gif" />
                <asp:TemplateField HeaderText="删除" ShowHeader="False">
                    <ItemTemplate>
                        <asp:ImageButton ID="ImageButton1" runat="server" CommandName="Delete" 
                            ImageUrl="~/Images/BtnDelete.gif" 
                            onclientclick="return confirm('确定删除吗？');" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
            <PagerStyle ForeColor="#8C4510" 
                HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#738A9C" ForeColor="White" Font-Bold="True" />
            <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
        </asp:GridView>
        <i>
        <asp:Label ID="lblMessage" runat="server"></asp:Label>
    
        您当前正在查看的页码：<b><font color="#ff0000"><%=GridView1.PageIndex + 1%>
                        /
                        <%=GridView1.PageCount%>
                    <br />
                    </font>
        <table class="style1">
            <tr>
                <td align="right">
                    名称 ：</td>
                <td>
                    <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td align="right">
                    价格：</td>
                <td>
                    <asp:TextBox ID="txtPrice" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td align="right">
                    现有库存量：</td>
                <td>
                    <asp:TextBox ID="txtSum" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:Button ID="btnInsert" runat="server" onclick="btnInsert_Click" Text="添加" 
                        Width="62px" />
                </td>
            </tr>
        </table>
        </b></i>
    
    </div>
    </form>
</body>
</html>

Default.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
//引入命名空间
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    SqlDb db = new SqlDb();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {  
            BindData();//调用自定义方法绑定数据到控件
        }
    }
    public void BindData()
    {
        string sqlstr = "select * from bccd01 order by BccdID desc";//定义执行查询操作的SQL语句
        db.BindData(GridView1, sqlstr);
        if (GridView1.Rows.Count > 0)
        {
            return;//有数据，不要处理
        }
        else//显示表头并显示没有数据的提示信息
        {
            StrHelper.GridViewHeader(GridView1);
        }
    }
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        //定义一个整型变量count存储根据当前行索引获取到的指定单元格中的字段值
        int count = int.Parse(GridView1.Rows[e.RowIndex].Cells[3].Text.Trim());
        if (count > 0)
        {
            e.Cancel = true;//取消删除动作
            lblMessage.Text = "警告：库存量大于0的商品不得删除！";
            lblMessage.ForeColor = System.Drawing.Color.Red;
            return;
        }
        string delete_sql = "delete from bccd01 where BccdID='" + GridView1.DataKeys[e.RowIndex].Value.ToString() + "'";
        bool delete = db.ExceSQL(delete_sql);//调用ExceSQL执行删除操作
        if (delete)
        {
            StrHelper.Alert("删除成功！");
            BindData();//调用自定义方法重新绑定控件中数据
        }
        else
        {
            StrHelper.Alert("删除失败！");
        }
    }
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
         //取得编辑行的关键字段的值
        string bccdID = GridView1.DataKeys[e.RowIndex].Value.ToString();
        //取得文本框中输入的内容
        string bccdName=((TextBox)(GridView1.Rows[e.RowIndex].Cells[1].Controls[0])).Text.ToString().Trim();
        string bccdPrice=((TextBox)(GridView1.Rows[e.RowIndex].Cells[2].Controls[0])).Text.ToString().Trim();
        string bccdInStock=((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text.ToString().Trim();
        //定义更新操作的SQL语句
        string update_sql = "update bccd01 set BccdName='" + bccdName + "',BccdPrice='" + bccdPrice + "',BccdInStock='" + bccdInStock + "' where BccdID='" + bccdID + "'";
        bool update = db.ExceSQL(update_sql);//调用ExceSQL执行更新操作
        if (update)
        {
            StrHelper.Alert("修改成功！");
            //设置GridView控件的编辑项的索引为－1，即取消编辑
            GridView1.EditIndex = -1;
            BindData();
        }
        else
        {
            StrHelper.Alert("修改失败！");
        }
    }
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        //设置GridView控件的编辑项的索引为－1，即取消编辑
        GridView1.EditIndex = -1;
        BindData();//数据绑定
    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        BindData();//数据绑定
    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        BindData();//数据绑定
    }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        string InsertSql = "Insert Into bccd01(BccdName,BccdPrice,BccdInStock) values('" + txtName.Text + "','" + txtPrice.Text + "','" + txtSum.Text + "')";
        bool insert = db.ExceSQL(InsertSql);//调用公共类中的ExceSQL()执行添加操作
        if (insert)//如果添加成功
        {
            StrHelper.Alert("添加成功！");
            BindData();//重新绑定数据
        }
        else
        {
            StrHelper.Alert("添加失败！");
        }
    }
}