USE [master] GO /****** 对象: Database [Test] 脚本日期: 11/10/2010 11:37:09 ******/ IF EXISTS(SELECT 1 FROM sys.databases WHERE [Name] = 'Test' ) DROP DATABASE Test GO CREATE DATABASE Test ON PRIMARY ( NAME = N'Test_dat',FILENAME = N'D:/Test_dat.mdf') LOG ON ( NAME = N'Test_Log',FILENAME = N'D:/Test_Log.ldf ') GO /****** 对象: Table UserInfo 脚本日期: 11/10/2010 11:37:09 ******/ USE Test GO IF EXISTS(SELECT 1 FROM sysobjects WHERE [type]='u' AND [Name]='UserInfo') DROP TABLE dbo.UserInfo GO CREATE TABLE dbo.UserInfo ( UserID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, UserName VARCHAR(50) NOT NULL, UserSex VARCHAR(50) NOT NULL, UserDesc VARCHAR(500) NULL ) GO INSERT INTO dbo.UserInfo ( UserName, UserSex, UserDesc ) (SELECT 'AAA','男','AAAA' UNION SELECT 'BBB','男','BBBB' UNION SELECT 'CCC','男','CCCC' UNION SELECT 'DDD','男','DDDD' UNION SELECT 'EEE','男','EEEE' UNION SELECT 'FFF','男','FFFF' ) GO /****** 对象: PROCEDURE 脚本日期: 11/10/2010 11:37:09 ******/ --创建查询存储过程 IF EXISTS(SELECT 1 FROM sys.objects WHERE [name]='PROC_Select') DROP PROCEDURE PROC_Select GO CREATE PROCEDURE PROC_Select AS SELECT * FROM dbo.UserInfo GO --创建修改存储过程 IF EXISTS(SELECT 1 FROM sys.objects WHERE [name]='PROC_Update') DROP PROCEDURE PROC_Update GO CREATE PROCEDURE PROC_Update @UserID INT, @UserName VARCHAR(50), @UserSex VARCHAR(50), @UserDesc VARCHAR(500) AS UPDATE dbo.UserInfo SET UserName=@UserName,UserSex=@UserSex,UserDesc=@UserDesc WHERE UserID=@UserID GO --创建删除存储过程 IF EXISTS(SELECT 1 FROM sys.objects WHERE [name]='PROC_Delete') DROP PROCEDURE PROC_Delete GO CREATE PROCEDURE PROC_Delete @UserID INT AS DELETE FROM dbo.UserInfo WHERE UserID=@UserID GO --创建添加存储过程 IF EXISTS(SELECT 1 FROM sys.objects WHERE [name]='PROC_Insert') DROP PROCEDURE PROC_Insert GO CREATE PROCEDURE PROC_Insert @UserName VARCHAR(50), @UserSex VARCHAR(50), @UserDesc VARCHAR(500) AS INSERT INTO dbo.UserInfo ( UserName, UserSex, UserDesc ) VALUES ( @UserName, -- UserName - varchar(50) @UserSex, -- UserSex - varchar(50) @UserDesc -- UserDesc - varchar(500) ) GO SQLHepler.cs using System; using System.Collections.Generic; using System.Linq; using System.Data; using System.Data.SqlClient; using System.Text; using System.Configuration; namespace DAL { public class SQLHelper { private static SqlConnection connection; private static SqlCommand cmd; public static SqlConnection Connection { get { string constr = ConfigurationManager.ConnectionStrings["OtherDB"].ToString(); if (connection == null) { connection = new SqlConnection(constr); connection.Open(); } else if (connection.State == System.Data.ConnectionState.Closed) connection.Open(); else if (connection.State == System.Data.ConnectionState.Broken) { connection.Close(); connection.Open(); } return connection; } } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { cmd.Connection = conn; cmd.CommandText = cmdText; //判断是否需要事物处理 if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); int retval = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return retval; } private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { cmd = new SqlCommand(storedProcName, connection); cmd.CommandType = CommandType.StoredProcedure; if (parameters != null) { foreach (SqlParameter parameter in parameters) { if (parameter.SqlDbType == SqlDbType.DateTime) { if ((DateTime)parameter.Value == DateTime.MinValue) parameter.Value = System.DBNull.Value; } cmd.Parameters.Add(parameter); } } return cmd; } public static DataSet ExecuteDataset(SqlConnection connection, string name, SqlParameter[] paraValues) { DataSet dataSet = new DataSet(); SqlDataAdapter sqlDA = new SqlDataAdapter(); cmd = BuildQueryCommand(connection, name, paraValues); sqlDA.SelectCommand = cmd; sqlDA.Fill(dataSet, "Table"); connection.Close(); return dataSet; } } } UserService.cs using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using Model; namespace DAL { public class UserService { string msg = string.Empty; public DataSet ExecteSelect() { DataSet ds = new DataSet(); try { ds = SQLHelper.ExecuteDataset(SQLHelper.Connection, "PROC_Select", new SqlParameter[] { }); } catch (Exception e) { msg = e.Message; } return ds; } public int ExecteModity(UserInfo user) { int res = 0; try { SqlParameter[] Params = new SqlParameter[]{ new SqlParameter("@UserID", SqlDbType.Int), new SqlParameter("@UserName", SqlDbType.VarChar,50), new SqlParameter("@UserSex", SqlDbType.VarChar,50), new SqlParameter("@UserDesc", SqlDbType.VarChar,500), }; Params[0].Value = user.UserID; Params[1].Value = user.UserName; Params[2].Value = user.UserSex; Params[3].Value = user.UserDesc; res = SQLHelper.ExecuteNonQuery(SQLHelper.Connection, CommandType.StoredProcedure, "PROC_Update", Params); } catch (Exception e) { msg = e.Message; } return res; } public int ExecteDelete(UserInfo user) { int res = 0; try { SqlParameter[] Params = new SqlParameter[]{ new SqlParameter("@UserID", SqlDbType.Int), }; Params[0].Value = user.UserID; res = SQLHelper.ExecuteNonQuery(SQLHelper.Connection, CommandType.StoredProcedure, "PROC_Delete", Params); } catch (Exception e) { msg = e.Message; } return res; } public int ExecteInsert(UserInfo user) { int res = 0; try { SqlParameter[] Params = new SqlParameter[]{ new SqlParameter("@UserName", SqlDbType.VarChar,50), new SqlParameter("@UserSex", SqlDbType.VarChar,50), new SqlParameter("@UserDesc", SqlDbType.VarChar,500), }; Params[0].Value = user.UserName; Params[1].Value = user.UserSex; Params[2].Value = user.UserDesc; res = SQLHelper.ExecuteNonQuery(SQLHelper.Connection, CommandType.StoredProcedure, "PROC_Insert", Params); } catch (Exception e) { msg = e.Message; } return res; } } } Default.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebUI._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> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="Button1" runat="server" Text="添加用户" OnClick="Button1_Click" /> <asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1" AutoGenerateColumns="False" DataKeyNames="UserID"> <Columns> <asp:TemplateField HeaderText="用户姓名"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("UserName") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="LabelName" runat="server" Text='<%# Eval("UserName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="用户性别"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("UserSex") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="LabelSex" runat="server" Text='<%# Eval("UserSex") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="用户描述"> <EditItemTemplate> <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("UserDesc") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="LabelDesc" runat="server" Text='<%# Eval("UserDesc") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:CommandField HeaderText="操作" ShowDeleteButton="True" ShowEditButton="True" /> </Columns> </asp:GridView> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DataObjectTypeName="Model.UserInfo" DeleteMethod="ExecteDelete" InsertMethod="ExecteInsert" SelectMethod="ExecteSelect" TypeName="BLL.UserManage" UpdateMethod="ExecteModity"> <DeleteParameters> <asp:Parameter Name="UserID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="UserID" Type="Int32" /> </UpdateParameters> </asp:ObjectDataSource> </div> </form> </body> </html> WinAddUser.aspx.cs protected void Button1_Click(object sender, EventArgs e) { UserInfo user = new UserInfo(); user.UserName = TextBox1.Text.Trim(); user.UserSex = RadioButton1.Checked ? "男" : "女"; user.UserDesc = TextBox2.Text.Trim(); int res = userMng.ExecteInsert(user); if (res>0) Response.Redirect("Default.aspx"); else Response.Redirect("WinAddUser.aspx"); }