访问数据库：
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操作返回影响到的行数，其他情况为-1public 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="width: 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="width: 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; width: 44%;" frame="below"><tr><td bgcolor="#f6dda4" style="text-align:right;width: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" οnclick="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类对象userUsers 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类的对象userUsers 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类对象userUsers 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类对象userUsers 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] = @UserNameWHERE ( [ID] = @UserID)
