CREATE PROCEDURE [dbo].[GetNameById]
2 @studentid varchar(8),
3 @studentname nvarchar(50) OUTPUT
4 AS
5 BEGIN
6 SELECT @studentname=studentname FROM student
7 WHERE studentid=@studentid
8 if @@Error<>0
9 RETURN -1
10 else
11 RETURN 0
12 END
13
14
15 using (SqlConnection conn = new SqlConnection(connStr))
16 {
17 try
18 {
19 SqlCommand cmd = new SqlCommand("GetNameById", conn);
20 cmd.CommandType = CommandType.StoredProcedure;
21 cmd.Parameters.AddWithValue("@studentid", "09888888"); //给输入参数赋值
22 SqlParameter parOutput =cmd.Parameters.Add("@studentname", SqlDbType.NVarChar, 50); //定义输出参数
23 parOutput.Direction = ParameterDirection.Output; //参数类型为Output
24 SqlParameter parReturn = new SqlParameter("@return", SqlDbType.Int);
25 parReturn.Direction = ParameterDirection.ReturnValue; //参数类型为ReturnValue
26 cmd.Parameters.Add(parReturn);
27 conn.Open();
28 cmd.ExecuteNonQuery();
29 MessageBox.Show(parOutput.Value.ToString()); //显示输出参数的值
30 MessageBox.Show(parReturn.Value.ToString()); //显示返回值
31 }
32 catch (System.Exception ex)
33 {
34 MessageBox.Show(ex.Message);
35 }
36 }
37
38
39 Create PROCEDURE AddOrderTran
40 @country nvarchar(100),
41 @adds nvarchar(100),
42 @ynames nvarchar(100),
43 @pids nvarchar(100),
44 @cellp nvarchar(100),
45 @cphone nvarchar(100),
46 @amounts nvarchar(100),
47 @cartnumber nvarchar(100)
48 as
49 Declare @id int
50 BEGIN TRANSACTION
51 insert into Orders(Order_Country,Order_Adress,Order_UserName,Order_PostID,Cells,Order_Phone,Total_pay,CartNumber,IsPay)
52 values (@country,@adds,@ynames,@pids,@cellp,@cphone,@amounts,@cartnumber,'0')
53 Select @id=@@identity
54 insert into Orders_Item (OrderNumber,ProductsID,Products_Color,Products_Price,Order_Qty,Item_Total)
55 select @id,Carts_Item.ProductsID,Carts_Item.Products_Color,Carts_Item.Products_Price,Carts_Item.Item_Qty,Carts_Item.Total_Pay
56 from Carts_Item where Carts_Item.CartNumber=@cartnumber
57 delete Carts_Item where CartNumber=@cartnumber
58 IF @@error <> 0 --发生错误
59 BEGIN
60 ROLLBACK TRANSACTION
61 RETURN 0
62 END
63 ELSE
64 BEGIN
65 COMMIT TRANSACTION
66 RETURN @id --执行成功
67 END
68
69
70
71 #region 执行存储过程
72
73 SqlParameter[] param = new SqlParameter[]
74 {
75 new SqlParameter("@country",country),
76 new SqlParameter("@adds",adds),
77 new SqlParameter("@ynames",ynames),
78 new SqlParameter("@pids", pids),
79 new SqlParameter("@cellp",cellp),
80 new SqlParameter("@cphone", cphone),
81 new SqlParameter("@amounts",amounts),
82 new SqlParameter("@cartnumber",cartnumber),
83 new SqlParameter("@return",SqlDbType.Int)
84 };
85 param[8].Direction = ParameterDirection.ReturnValue;
86 MSCL.SqlHelper.RunProcedure("AddOrderTran", param);
87 object obj = param[8].Value; //接受返回值
88
89
90 //string connStr = System.Configuration.ConfigurationManager.AppSettings["ConStr"].ToString();
91 //using (SqlConnection conn = new SqlConnection(connStr))
92 //{
93 // conn.Open();
94 // SqlCommand cmd = new SqlCommand("AddOrderTran", conn);
95 // cmd.CommandType = CommandType.StoredProcedure;
96
97 // SqlParameter para1 = new SqlParameter("@country", country);
98 // para1.Direction = ParameterDirection.Input; //参数方向 为输入参数
99 // cmd.Parameters.Add(para1);
100
101 // SqlParameter para2 = new SqlParameter("@adds", adds);
102 // para2.Direction = ParameterDirection.Input;
103 // cmd.Parameters.Add(para2);
104
105 // SqlParameter para3 = new SqlParameter("@ynames", ynames);
106 // para3.Direction = ParameterDirection.Input;
107 // cmd.Parameters.Add(para3);
108
109 // SqlParameter para4 = new SqlParameter("@pids", pids);
110 // para4.Direction = ParameterDirection.Input;
111 // cmd.Parameters.Add(para4);
112
113 // SqlParameter para5 = new SqlParameter("@cellp", cellp);
114 // para5.Direction = ParameterDirection.Input;
115 // cmd.Parameters.Add(para5);
116
117 // SqlParameter para6 = new SqlParameter("@cphone", cphone);
118 // para6.Direction = ParameterDirection.Input;
119 // cmd.Parameters.Add(para6);
120
121 // SqlParameter para7 = new SqlParameter("@amounts", amounts);
122 // para7.Direction = ParameterDirection.Input;
123 // cmd.Parameters.Add(para7);
124
125 // SqlParameter para8 = new SqlParameter("@cartnumber", cartnumber);
126 // para8.Direction = ParameterDirection.Input;
127 // cmd.Parameters.Add(para8);
128
129 // SqlParameter paraReturn = new SqlParameter("@return", SqlDbType.Int);
130 // paraReturn.Direction = ParameterDirection.ReturnValue; //参数方向 为返回参数
131 // cmd.Parameters.Add(paraReturn);
132
133 // cmd.ExecuteNonQuery();
134
135 // object obj = paraReturn;
136 // if (obj.ToString() == "0")
137 // {
138 // //存储过程执行失败
139 // }
140 // else
141 // {
142 // //成功
143 // }
144 //}
145 //#endregion
146
147
148
149 本文的数据库用的是sql server自带数据Northwind
150
151 1.只返回单一记录集的存储过程
152
153 SqlConnection sqlconn = new SqlConnection(conn);
154 SqlCommand cmd = new SqlCommand();
155 // 设置sql连接
156 cmd.Connection = sqlconn;
157 // 如果执行语句
158 cmd.CommandText = "Categoriestest1";
159 // 指定执行语句为存储过程
160 cmd.CommandType = CommandType.StoredProcedure;
161
162 SqlDataAdapter dp = new SqlDataAdapter(cmd);
163 DataSet ds = new DataSet();
164 // 填充dataset
165 dp.Fill(ds);
166 // 以下是显示效果
167 GridView1.DataSource = ds;
168 GridView1.DataBind();
169
170
171
172 存储过程Categoriestest1
173 CREATE PROCEDURE Categoriestest1
174 AS
175 select *
176 from Categories
177 GO
178
179
180
181 2. 没有输入输出的存储过程
182 SqlConnection sqlconn = new SqlConnection(conn);
183 SqlCommand cmd = new SqlCommand();
184
185 cmd.Connection = sqlconn;
186 cmd.CommandText = "Categoriestest2";
187 cmd.CommandType = CommandType.StoredProcedure;
188 sqlconn.Open();
189 // 执行并显示影响行数
190 Label1.Text = cmd.ExecuteNonQuery().ToString();
191 sqlconn.Close();
192
193
194 存储过程Categoriestest2
195
196 CREATE PROCEDURE Categoriestest2 AS
197 insert into dbo.Categories
198 (CategoryName,[Description],[Picture])
199 values ('test1','test1',null)
200 GO
201
202
203
204 3. 有返回值的存储过程
205 SqlConnection sqlconn = new SqlConnection(conn);
206 SqlCommand cmd = new SqlCommand();
207 cmd.Connection = sqlconn;
208 cmd.CommandText = "Categoriestest3";
209 cmd.CommandType = CommandType.StoredProcedure;
210 // 创建参数
211 IDataParameter[] parameters = {
212 new SqlParameter("rval", SqlDbType.Int,4)
213 };
214 // 将参数类型设置为 返回值类型
215 parameters[0].Direction = ParameterDirection.ReturnValue;
216 // 添加参数
217 cmd.Parameters.Add(parameters[0]);
218
219 sqlconn.Open();
220 // 执行存储过程并返回影响的行数
221 Label1.Text = cmd.ExecuteNonQuery().ToString();
222 sqlconn.Close();
223 // 显示影响的行数和返回值
224 Label1.Text += "-" + parameters[0].Value.ToString() ;
225
226
227 存储过程Categoriestest3
228
229 CREATE PROCEDURE Categoriestest3
230 AS
231 insert into dbo.Categories
232 (CategoryName,[Description],[Picture])
233 values ('test1','test1',null)
234 return @@rowcount
235 GO
236
237 4. 有输入参数和输出参数的存储过程
238
239 SqlConnection sqlconn = new SqlConnection(conn);
240 SqlCommand cmd = new SqlCommand();
241 cmd.Connection = sqlconn;
242 cmd.CommandText = "Categoriestest4";
243 cmd.CommandType = CommandType.StoredProcedure;
244 // 创建参数
245 IDataParameter[] parameters = {
246 new SqlParameter("@Id", SqlDbType.Int,4) ,
247 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) ,
248 };
249 // 设置参数类型
250 parameters[0].Direction = ParameterDirection.Output; // 设置为输出参数
251 parameters[1].Value = "testCategoryName";
252 // 添加参数
253 cmd.Parameters.Add(parameters[0]);
254 cmd.Parameters.Add(parameters[1]);
255
256 sqlconn.Open();
257 // 执行存储过程并返回影响的行数
258 Label1.Text = cmd.ExecuteNonQuery().ToString();
259 sqlconn.Close();
260 // 显示影响的行数和输出参数
261 Label1.Text += "-" + parameters[0].Value.ToString() ;
262
263 存储过程Categoriestest4
264
265 CREATE PROCEDURE Categoriestest4
266 @id int output,
267 @CategoryName nvarchar(15)
268 AS
269 insert into dbo.Categories
270 (CategoryName,[Description],[Picture])
271 values (@CategoryName,'test1',null)
272 set @id = @@IDENTITY
273 GO
274
275
276
277 5. 同时具有返回值、输入参数、输出参数的存储过程
278
279 SqlConnection sqlconn = new SqlConnection(conn);
280 SqlCommand cmd = new SqlCommand();
281 cmd.Connection = sqlconn;
282 cmd.CommandText = "Categoriestest5";
283 cmd.CommandType = CommandType.StoredProcedure;
284 // 创建参数
285 IDataParameter[] parameters = {
286 new SqlParameter("@Id", SqlDbType.Int,4) ,
287 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) ,
288 new SqlParameter("rval", SqlDbType.Int,4)
289 };
290 // 设置参数类型
291 parameters[0].Direction = ParameterDirection.Output; // 设置为输出参数
292 parameters[1].Value = "testCategoryName"; // 给输入参数赋值
293 parameters[2].Direction = ParameterDirection.ReturnValue; // 设置为返回值
294 // 添加参数
295 cmd.Parameters.Add(parameters[0]);
296 cmd.Parameters.Add(parameters[1]);
297 cmd.Parameters.Add(parameters[2]);
298
299 sqlconn.Open();
300 // 执行存储过程并返回影响的行数
301 Label1.Text = cmd.ExecuteNonQuery().ToString();
302 sqlconn.Close();
303 // 显示影响的行数，输出参数和返回值
304 Label1.Text += "-" + parameters[0].Value.ToString() + "-" + parameters[2].Value.ToString();
305
306 存储过程Categoriestest5
307
308 CREATE PROCEDURE Categoriestest5
309 @id int output,
310 @CategoryName nvarchar(15)
311 AS
312 insert into dbo.Categories
313 (CategoryName,[Description],[Picture])
314 values (@CategoryName,'test1',null)
315 set @id = @@IDENTITY
316 return @@rowcount
317 GO
318
319
320 6. 同时返回参数和记录集的存储过程
321 SqlConnection sqlconn = new SqlConnection(conn);
322 SqlCommand cmd = new SqlCommand();
323 cmd.Connection = sqlconn;
324 cmd.CommandText = "Categoriestest6";
325 cmd.CommandType = CommandType.StoredProcedure;
326 // 创建参数
327 IDataParameter[] parameters = {
328 new SqlParameter("@Id", SqlDbType.Int,4) ,
329 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) ,
330 new SqlParameter("rval", SqlDbType.Int,4) // 返回值
331 };
332 // 设置参数类型
333 parameters[0].Direction = ParameterDirection.Output; // 设置为输出参数
334 parameters[1].Value = "testCategoryName"; // 给输入参数赋值
335 parameters[2].Direction = ParameterDirection.ReturnValue; // 设置为返回值
336 // 添加参数
337 cmd.Parameters.Add(parameters[0]);
338 cmd.Parameters.Add(parameters[1]);
339 cmd.Parameters.Add(parameters[2]);
340
341 SqlDataAdapter dp = new SqlDataAdapter(cmd);
342 DataSet ds = new DataSet();
343 // 填充dataset
344 dp.Fill(ds);
345 // 显示结果集
346 GridView1.DataSource = ds.Tables[0];
347 GridView1.DataBind();
348
349 Label1.Text = "";
350 // 显示输出参数和返回值
351 Label1.Text += parameters[0].Value.ToString() + "-" + parameters[2].Value.ToString();
352
353 存储过程Categoriestest6
354
355 CREATE PROCEDURE Categoriestest6
356 @id int output,
357 @CategoryName nvarchar(15)
358 AS
359 insert into dbo.Categories
360 (CategoryName,[Description],[Picture])
361 values (@CategoryName,'test1',null)
362 set @id = @@IDENTITY
363 select * from Categories
364 return @@rowcount
365 GO
366
367
368 7. 返回多个记录集的存储过程
369
370 SqlConnection sqlconn = new SqlConnection(conn);
371 SqlCommand cmd = new SqlCommand();
372
373 cmd.Connection = sqlconn;
374 cmd.CommandText = "Categoriestest7";
375 cmd.CommandType = CommandType.StoredProcedure;
376
377 SqlDataAdapter dp = new SqlDataAdapter(cmd);
378 DataSet ds = new DataSet();
379 // 填充dataset
380 dp.Fill(ds);
381 // 显示结果集1
382 GridView1.DataSource = ds.Tables[0];
383 GridView1.DataBind();
384 // 显示结果集2
385 GridView2.DataSource = ds.Tables[1];
386 GridView2.DataBind();
387
388
389 存储过程Categoriestest7
390
391 CREATE PROCEDURE Categoriestest7
392 AS
393 select * from Categories
394 select * from Categories
395 GO
浙公网安备 33010602011771号