SqlDb.cs
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
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
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
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
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
<%@ 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> </td> <td> <asp:Button ID="btnInsert" runat="server" οnclick="btnInsert_Click" Text="添加" Width="62px" /> </td> </tr> </table> </b></i> </div> </form> </body> </html>
Default.aspx.cs
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
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("添加失败!"); } } }
<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>