1 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
