访问数据库：
View Code 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> ///DataBase 的摘要说明 /// </summary> public class DataBase { //私有变量，数据库连接 protected SqlConnection Connection; protected string ConnectionString; //构造函数 public DataBase() { ConnectionString = ConfigurationSettings.AppSettings["strCon"]; } //保护方法,打开数据库连接 private void Open() { //判断数据库是否连接 if(Connection == null) { //不存在，新建并打开 Connection = new SqlConnection(ConnectionString); Connection.Open(); } else { //存在，判断是否处于关闭状态 if(Connection.State.Equals(ConnectionState.Closed)) Connection.Open();//连接处于关闭状态，重新打开 } } //公有方法，关闭数据库连接 public void Close() { if (Connection.State.Equals(ConnectionState.Open)) { Connection.Close();//连接处于打开状态，关闭连接 } } //公有方法，释放资源 public void Dispose() { if (Connection != null) { Connection.Dispose(); Connection = null; } } //私有方法，获得一个用来调用存储过程的SqlCommand //输入： // ProcName - 存储过程名 // Params - 用来调用存储过程的参数表 public SqlCommand CreatCommand(string ProcName, SqlParameter[] Prams) { //打开数据库连接 Open(); //创建一个命令对象 SqlCommand Cmd = new SqlCommand(ProcName, Connection); //指定命令对象的类型为存储过程 Cmd.CommandType = CommandType.StoredProcedure; // 依次把参数传入命令文本 if (Prams != null) { foreach (SqlParameter Parameter in Prams) Cmd.Parameters.Add(Parameter); } return Cmd; } /// <summary> /// 初始化参数值 /// </summary> /// <param name="ParamName">存储过程名称或命令文本</param> /// <param name="DbType">参数类型</param> /// <param name="Size">参数大小</param> /// <param name="Direction">参数方向</param> /// <param name="Value">参数值</param> /// <returns>新的 parameter 对象</returns> public SqlParameter MakeParam(string ParamName,SqlDbType DbType, Int32 Size,ParameterDirection Direction,object Value) { SqlParameter Param; if(Size>0) Param = new SqlParameter(ParamName,DbType,Size); else Param = new SqlParameter(ParamName,DbType); Param.Direction = Direction; if(!(Direction == ParameterDirection.Output && Value == null)) Param.Value = Value; return Param; } //公有方法，实例化一个用于调用存储过程的输入参数 //输入： // ParamName - 参数名称 // DbType - 参数类型 // Size - 参数大小 // Value - 值 public SqlParameter MakeInParam(string ParamName, SqlDbType DbTpye, int Size, object Value) { return MakeParam(ParamName, DbTpye, Size, ParameterDirection.Input, Value); } //公有方法，调用存储过程(带参数) //输入： // ProcName - 存储过程名 // Params - 用来调用存储过程的参数表 //输出： // 对Update、Insert、Delete操作返回影响到的行数，其他情况为-1 public int RunProc(string ProcName, SqlParameter[] Params) { int Count = -1; SqlCommand Cmd = CreatCommand(ProcName, Params); Count = Cmd.ExecuteNonQuery(); Close(); return Count; } //公有方法，调用存储过程(不带参数) //输入： // ProcName存储过程名 //输出： // 将执行结果以DataSet返回 public DataSet GetDataSet(string ProcName) { Open(); SqlDataAdapter da = new SqlDataAdapter(ProcName, Connection); DataSet ds = new DataSet(); da.Fill(ds); Close(); return ds; } //公有方法，调用存储过程(带参数) //输入： // ProcName - 存储过程名 // Params - 存储过程需要的参数 //输出： // 将执行结果以SqlDataReader返回 //注意：使用后主意调用SqlDataReader.Close()方法 public SqlDataReader RunProcGetReader(string ProcName, SqlParameter[] Params) { SqlCommand Cmd = CreatCommand(ProcName, Params); return Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } }
实体类以及增删改查方法：
View Code 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> ///Users 的摘要说明 /// </summary> public class Users { #region 私有成员 private string _userID; private string _userPwd; private string _userName; #endregion 私有成员 #region 属性 public string UserID { set { this._userID = value; } get { return this._userID; } } public string UserPwd { set { this._userPwd = value; } get { return this._userPwd; } } public string UserName { set { this._userName = value; } get { return this._userName; } } #endregion 属性 #region 方法 //向Users表中添加用户信息(采用存储过程) //输出： // 插入成功：返回True； // 插入失败：返回False； public bool InsertByProc() { SqlParameter[] Params = new SqlParameter[3]; DataBase mydb = new DataBase(); //用户编号 Params[0] = mydb.MakeInParam("@UserID", SqlDbType.VarChar, 50, UserID); //用户姓名 Params[1] = mydb.MakeInParam("@UserName", SqlDbType.VarChar, 50, UserName); //用户密码 Params[2] = mydb.MakeInParam("@UserPwd", SqlDbType.VarChar, 64, UserPwd); int Count = -1; Count = mydb.RunProc("Proc_UsersAdd", Params); if (Count > 0) return true; else return false; } //更新用户 //输入： // XUserID - 用户编号； //输出： // 更新成功：返回True； // 更新失败：返回False； public bool UpdateByProc(string XUserID) { SqlParameter[] Params = new SqlParameter[2]; DataBase mydb = new DataBase(); Params[0] = mydb.MakeInParam("@UserID", SqlDbType.VarChar, 50, XUserID); //用户编号 Params[1] = mydb.MakeInParam("@UserName", SqlDbType.VarChar, 50, UserName); int Count = -1; Count = mydb.RunProc("Proc_UsersModify", Params); if (Count > 0) return true; else return false;//用户姓名 } //删除用户 //输入： // XUserID - 用户编号； //输出： // 删除成功：返回True； // 删除失败：返回False； public bool DeleteByProc(string XUserID) { //定义参数类型 SqlParameter[] Params = new SqlParameter[1]; DataBase mydb = new DataBase(); //用户编号 Params[0] = mydb.MakeInParam("@UserID", SqlDbType.VarChar, 50, XUserID); int Count = -1; Count = mydb.RunProc("Proc_UsersDelete", Params); if (Count > 0) return true; else return false; } //查询用户 //查询所用用户 //不需要参数 public DataSet QueryUsers() { DataBase mydb = new DataBase(); return mydb.GetDataSet("Proc_UsersList"); } //根据UserID判断该用户是否存在 //输入： // XUserID - 用户编号； //输出： // 用户存在：返回True； // 用户不在：返回False； public bool CheckUser(string XUserID) { SqlParameter[] Params = new SqlParameter[1]; DataBase DB = new DataBase(); Params[0] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 50, XUserID); SqlDataReader DR = DB.RunProcGetReader("Proc_UsersDetail", Params); if (!DR.Read()) { return false; } else { return true; } } #endregion 方法 }
前台页面设计：
View Code <%@ 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> <script type="text/javascript"></script> <style type="text/css"> .style1 { font-size: small; } </style> </head> <body> <form id="form1" runat="server"> <div> <table border="0" cellpadding="0" cellspacing="0" height="100%" width="100%"> <tr> <td style="height:4px;" colspan="2"> </td> </tr> <tr> <td style=" 4px; background: url(../Images/line.gif) repeat-y;"> </td> <td valign="top" align="left" width="960px"> <h4>>>用户管理</h4> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="True" OnPageIndexChanging="GridView1_PageIndexChanging" PageSize="8" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" DataKeyNames="UserID" Font-Size="13px" Width="58%" SkinID="gvSkin" CellPadding="4" ForeColor="#333333" GridLines="None"> <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#FFFBD6" ForeColor="#333333" /> <Columns> <asp:TemplateField> <ItemTemplate> <asp:CheckBox ID="chkSelected" runat="server" Checked="False" Visible="True" /></ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="ID"> <ItemTemplate> <%# Container.DataItemIndex+1 %> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="编号"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("ID") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="姓名"> <EditItemTemplate> <asp:TextBox ID="tbxName" Width="70px" runat="server" Text='<%# Eval("userName") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server"><%# Eval("userName") %></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:CommandField ShowEditButton="True" HeaderText="编辑" /> <asp:CommandField ShowDeleteButton="True" HeaderText="删除" /> </Columns> <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" /> <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> <asp:Label ID="LabelPageInfo" runat="server" style="font-size: small"></asp:Label> <br /><asp:CheckBox ID="chkSelectAll" runat="server" AutoPostBack="true" Text="全选" Width="54px" OnCheckedChanged="chkSelectAll_CheckedChanged" style="font-size: small" /> <asp:ImageButton ID="ImageButtonDelete" runat="server" ImageUrl="~/Images/删除}.gif" OnClick="ImageButtonDelete_Click"></asp:ImageButton> <br /> </td> </tr> <tr> <td style=" 4px; background: url(../Images/line.gif) repeat-y;"> </td> <td valign="top" align="left" width="960px"> <table cellpadding="0" cellspacing="0" border="1" bordercolor="#cccccc" style="border-collapse:collapse; 44%;" frame="below"> <tr> <td bgcolor="#f6dda4" style="text-align:right;100%;" colspan="2"> <div class="title" align="left"> <h4> 添加用户</h4> </div> </td> </tr> <tr> <td bgcolor="#f6dda4" style="text-align:right;"> <span class="style1">编号</span>：</td> <td > <div align="left"> <asp:TextBox id="txtUserID" runat="server" MaxLength="20"></asp:TextBox> </div> </td> </tr> <tr> <td bgcolor="#f6dda4" style="text-align:right;"> <span class="style1">姓名</span>：</td> <td > <div align="left"> <asp:TextBox id="txtUserName" runat="server" MaxLength="20"></asp:TextBox> </div> </td> </tr> <tr> <td bgcolor="#f6dda4" style="text-align:right;"> <span class="style1">密码</span>：</td> <td > <div align="left"> <asp:TextBox id="txtUserPwd" runat="server" MaxLength="20" TextMode="Password" Width="128px"></asp:TextBox> </div> </td> </tr> <tr> <td> </td> <td valign="top" > <asp:ImageButton ID="imgBtnSave" runat="server" ImageUrl="~/Images/Save.GIF" OnClick="imgBtnSave_Click" /> <asp:ImageButton ID="imgBtnReturn" runat="server" CausesValidation="false" ImageUrl="~/Images/Return.GIF" onclick="imgBtnReturn_Click" /> <asp:Label ID="lblMessage" runat="server" ForeColor=red></asp:Label> </td> </tr> </table> </td> </tr> </table> </div> </form> </body> </html>
后台代码：
View Code 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 { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { GridViewBind(); } } public void GridViewBind() { //创建Users类对象user Users user = new Users(); //使用使用Users类QueryUsers方法查询所有用户信息 DataSet myds = user.QueryUsers(); //为GridView控件指定数据源 GridView1.DataSource = myds; //设置控件的主键字段值 GridView1.DataKeyNames = new string[] { "ID" }; //从数据库中绑定数据 GridView1.DataBind(); LabelPageInfo.Text = "你所在的位置：当前（第" + (GridView1.PageIndex + 1).ToString() + "页 共" + GridView1.PageCount.ToString() + "页）"; } //GridView控件RowDeleting事件 protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { //取出要删除记录的主键值 string userID = GridView1.DataKeys[e.RowIndex].Values[0].ToString(); //创建Users类的对象user Users user = new Users(); //根据主键使用DeleteByProc方法删除用户 if (user.DeleteByProc(userID)) { Response.Write("<script>alert('删除成功！');location='Default.aspx'</script>"); } else { Response.Write("<script>alert('删除失败！');location='Default.aspx'</script>"); } GridViewBind();//重新绑定数据 } protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { //GridView编辑项索引等于单击行的索引 GridView1.EditIndex = e.NewEditIndex; GridViewBind(); } //GridView控件RowUpdating事件 protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { //取出记录的主键值 string userID = GridView1.DataKeys[e.RowIndex].Values[0].ToString(); //创建Users类对象user Users user = new Users(); user.UserName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("tbxName")).Text; //使用Users类UpdateByProc方法修改用户信息,该方法中主要是调用Proc_UsersAdd存储过程 if (user.UpdateByProc(userID)) { //绑定数据库中数据 GridViewBind(); //修改成功给予提示 Response.Write("<script>alert('修改成功！');location='Default.aspx'</script>"); } else { Response.Write("<script>alert('修改失败！');location='Default.aspx'</script>"); } //取消编辑状态 GridView1.EditIndex = -1; //重新绑定下数据 GridViewBind(); } protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { GridView1.EditIndex = -1; GridViewBind(); } protected void ImageButtonDelete_Click(object sender, ImageClickEventArgs e) { for (int i = 0; i < this.GridView1.Rows.Count-1; i++) { bool isChecked = ((CheckBox)GridView1.Rows[i].FindControl("chkSelected")).Checked; if (isChecked) { string userID = ((Label)GridView1.Rows[i].FindControl("Label1")).Text; //创建Users类对象user Users user = new Users(); //根据主键使用DeleteByProc方法删除用户 if (user.DeleteByProc(userID)) { Response.Write("<script language=javascript>alert('删除成功!');location='Default.aspx'</script>"); } else { Response.Write("<script language=javascript>alert('" + userID + "删除失败!');location='Default.aspx'</script>"); } } } GridViewBind(); } protected void chkSelectAll_CheckedChanged(object sender, EventArgs e) { for (int i = 0; i < GridView1.Rows.Count; i++) { ((CheckBox)GridView1.Rows[i].FindControl("chkSelected")).Checked = this.chkSelectAll.Checked; } } protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; GridViewBind(); } protected void imgBtnSave_Click(object sender, ImageClickEventArgs e) { if (Page.IsValid) { Users user = new Users(); user.UserID = txtUserID.Text.Trim(); if (!user.CheckUser(user.UserID)) { user.UserName = txtUserName.Text; string PwdMD5 = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(txtUserPwd.Text.Trim(), "MD5").ToString(); user.UserPwd = PwdMD5; if (user.InsertByProc()) { lblMessage.Text = "成功插入该用户信息！"; Server.Transfer("Default.aspx"); } else { lblMessage.Text = "添加用户失败！"; } } else { lblMessage.Text = "数据库中存在具有该编号的用户，请重新输入！"; } } } protected void imgBtnReturn_Click(object sender, ImageClickEventArgs e) { Server.Transfer("Default.aspx"); } }
存储过程：
View Code CREATE PROCEDURE [dbo].[Proc_UsersAdd] (@UserID [varchar](50), @UserName [varchar](50), @UserPwd [varchar](64) ) AS INSERT INTO [db2].[dbo].[tb_user] (ID, [userName], [userPwd]) VALUES ( @UserID, @UserName, @UserPwd) CREATE PROCEDURE [dbo].[Proc_UsersDelete] (@UserID [varchar](50)) AS DELETE [db2].[dbo].[tb_user] WHERE ( [ID] = @UserID) CREATE PROCEDURE [dbo].[Proc_UsersList] AS SELECT [db2].[dbo].[tb_user].[ID], [db2].[dbo].[tb_user].[userName] FROM [db2].[dbo].[tb_user] ALTER PROCEDURE [dbo].[Proc_UsersModify] (@UserID [varchar](50), @UserName [varchar](50)) AS UPDATE [db2].[dbo].[tb_user] SET [userName] = @UserName WHERE ( [ID] = @UserID)