通用的增删改操作

       通用的增删改操作

1. /// <summary>
2.         /// 增删改操作函数，返回值为受影响的行数
3.         /// </summary>
4.         /// <param name="procName">存储过程名称</param>
5.         /// <param name="para">参数</param>
6.         /// <returns></returns>
7.         public int RunSql(string procName, SqlParameter[] para)
8.         {
9.             int count = 0;
10.             try
11.             {
12.                 //得到SqlCommand对象
13.                 cmd = CreateCmd(procName, para);
14.                 conn.Open();
15.                 count = cmd.ExecuteNonQuery();
16.             }
17.             catch (SqlException ex)
18.             {
19.                 throw ex;
20.             }
21.             finally
22.             {
23.                 conn.Close();
24.             }
25.             return count;
26.         }
27. /// <summary>
28.         /// 创建SqlCommand来执行存储过程(带参数)
29.         /// </summary>
30.         /// <param name="procName"></param>
31.         /// <param name="para"></param>
32.         /// <returns></returns>
33.         public SqlCommand CreateCmd(string procName, SqlParameter[] para)
34.         {
35.             try
36.             {
37.                 conn = CreateConn(); //得到数据库连接
38.                 cmd = new SqlCommand(); //设置Command对象
39.                 cmd.CommandType = CommandType.StoredProcedure; //创建存储过程
40.                 cmd.CommandText = procName; //调用存储过程名称
41.                 cmd.Connection = conn; //创建数据库连接对象
42.                 if (para != null)
43.                 {
44.                     //添加存储过程参数
45.                     foreach (SqlParameter sp in para)
46.                     {
47.                         cmd.Parameters.Add(sp);
48.                     }
49.                 }
50.             }
51.             catch (SqlException ex)
52.             {
53.                 throw ex;
54.             }
55.             return cmd;
56.         }
57. /// <summary>
58.         /// 创建数据库连接
59.         /// </summary>
60. public SqlConnection CreateConn()
61.          {
62.             SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlConn"]);
63.             return conn;
64.       }
65. //举例说明，Adduser()增加用户
66. private void Adduser()
67.     {
68.         string CompaneyName = Request.Form["CompaneyName"];
69.         string TelPhone = Request.Form["TelPhone"];
70.         string FaxID = Request.Form["FaxID"];
71.         string CustName = Request.Form["CustName"];
72.         string MobileNo = Request.Form["MobileNo"];
73.         string zip = Request.Form["zip"];
74.         string provine = Request.Form["provine"];
75.         string city = Request.Form["city"];
76.         string Address = Request.Form["Address"];
77.         string sex = Request.Form["sex"];
78.         string Email = Request.Form["Email"];
79.         string remark = Request.Form["remark"];
80.         int x=0;
81.         try
82.             {
83.                 SqlParameter[] sp ={
84.                     para = new SqlParameter("@CompaneyName",CompaneyName),
85.                     para = new SqlParameter("@TelPhone",TelPhone),
86.                     para = new SqlParameter("@FaxID",FaxID),
87.                     para = new SqlParameter("@CustName",CustName),
88.                     para = new SqlParameter("@MobileNo",MobileNo),
89.                     para = new SqlParameter("@zip",zip),
90.                     para = new SqlParameter("@provine",provine),
91.                     para = new SqlParameter("@city",city),
92.                     para = new SqlParameter("@Address",Address),
93.                     para = new SqlParameter("@sex",sex),
94.                     para = new SqlParameter("@Email",Email),
95.                     para=new SqlParameter("@remark",remark)
96.                 };
97.                 x = sq.RunSql("E_adduser", sp);//E_adduser存储过程
98.                 if (x > 0)
99.                 {
100.                    Response.Write("<script>alert('增加成功') </script>");
101.                 }
102.                 else
103.                 {
104.                     Response.Write("<script>alert('失败') </script>");
105.                 }
106.             }
107.             catch (Exception ex)
108.             {
109.                 Response.Write(ex.Message);
110.             }
111.     }
112. //删除用户
113. public void deleteuser()
114.         {
115.             int custID =Convert.ToInt32(Request["custID"].ToString());
116.             int count = 0;
117.             try
118.             {
119.                 SqlParameter[] sp ={
120.                     para = new SqlParameter("@custID",custID)
121.                 };
122.                 count = sh.RunSql("E_Deluser", sp);
123.                 if (x > 0)
124.                 {
125.                    Response.Write("<script>alert('删除成功') </script>");
126.                 }
127.                 else
128.                 {
129.                     Response.Write("<script>alert('失败') </script>");
130.                 }
131.             }
132.             catch (Exception ex)
133.             {
134.                 throw ex;
135.             }
136.     }
137. //存储过程
138. //执行增加操作的存储过程
139. create proc E_adduser
140. (
141.       @CompaneyName varchar(40),
142.       @TelPhone varchar(30),
143.       @FaxID  varchar(30),
144.       @CustName varchar(30),
145.       @MobileNo varchar(20),
146.       @zip  varchar(15),
147.       @provine varchar(15),
148.       @city varchar(15),
149.       @Address varchar(100),
150.       @sex varchar(10),
151.       @Email varchar(50),
152.       @remark varchar(255)
153. )
154. as  
155. insert into Customers(CompanyName, PhoneNo,FaxNo,CustName,MobileNo,Zip,Province,City,Address,Gender,Email,Remark,LastCallTime) values(@CompaneyName,@TelPhone,@FaxID,@CustName,@MobileNo,@zip,@provine,@city,@Address,@sex,@Email,@remark,GetDate())
156. GO
157. //执行删除操作的存储过程
158. create proc E_Deluser(@custID int)
159. as
160.     delete from Customers where CustId = @custID
161. GO