C#调用存储过程带输出参数或返回值
分类：MSSQL-05-26 14:02330人阅读评论(0)收藏举报
[csharp]view plaincopyprint?CREATEPROCEDURE[dbo].[GetNameById] @studentidvarchar(8), @studentnamenvarchar(50)OUTPUT AS BEGIN SELECT@studentname=studentnameFROMstudent WHEREstudentid=@studentid if@@Error<>0 RETURN-1 else RETURN0 END using(SqlConnectionconn=newSqlConnection(connStr)) { try { SqlCommandcmd=newSqlCommand("GetNameById",conn); mandType=CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@studentid","09888888");//给输入参数赋值 SqlParameterparOutput=cmd.Parameters.Add("@studentname",SqlDbType.NVarChar,50);//定义输出参数 parOutput.Direction=ParameterDirection.Output;//参数类型为Output SqlParameterparReturn=newSqlParameter("@return",SqlDbType.Int); parReturn.Direction=ParameterDirection.ReturnValue;//参数类型为ReturnValue cmd.Parameters.Add(parReturn); conn.Open(); cmd.ExecuteNonQuery(); MessageBox.Show(parOutput.Value.ToString());//显示输出参数的值 MessageBox.Show(parReturn.Value.ToString());//显示返回值 } catch(System.Exceptionex) { MessageBox.Show(ex.Message); } } CreatePROCEDUREAddOrderTran @countrynvarchar(100), @addsnvarchar(100), @ynamesnvarchar(100), @pidsnvarchar(100), @cellpnvarchar(100), @cphonenvarchar(100), @amountsnvarchar(100), @cartnumbernvarchar(100) as Declare@idint BEGINTRANSACTION insertintoOrders(Order_Country,Order_Adress,Order_UserName,Order_PostID,Cells,Order_Phone,Total_pay,CartNumber,IsPay) values(@country,@adds,@ynames,@pids,@cellp,@cphone,@amounts,@cartnumber,'0') Select@id=@@identity insertintoOrders_Item(OrderNumber,ProductsID,Products_Color,Products_Price,Order_Qty,Item_Total) select@id,Carts_Item.ProductsID,Carts_Item.Products_Color,Carts_Item.Products_Price,Carts_Item.Item_Qty,Carts_Item.Total_Pay fromCarts_ItemwhereCarts_Item.CartNumber=@cartnumber deleteCarts_ItemwhereCartNumber=@cartnumber IF@@error<>0--发生错误 BEGIN ROLLBACKTRANSACTION RETURN0 END ELSE BEGIN COMMITTRANSACTION RETURN@id--执行成功 END #region执行存储过程 SqlParameter[]param=newSqlParameter[] { newSqlParameter("@country",country), newSqlParameter("@adds",adds), newSqlParameter("@ynames",ynames), newSqlParameter("@pids",pids), newSqlParameter("@cellp",cellp), newSqlParameter("@cphone",cphone), newSqlParameter("@amounts",amounts), newSqlParameter("@cartnumber",cartnumber), newSqlParameter("@return",SqlDbType.Int) }; param[8].Direction=ParameterDirection.ReturnValue; MSCL.SqlHelper.RunProcedure("AddOrderTran",param); objectobj=param[8].Value;//接受返回值 //stringconnStr=System.Configuration.ConfigurationManager.AppSettings["ConStr"].ToString(); //using(SqlConnectionconn=newSqlConnection(connStr)) //{//conn.Open(); //SqlCommandcmd=newSqlCommand("AddOrderTran",conn); //mandType=CommandType.StoredProcedure; //SqlParameterpara1=newSqlParameter("@country",country); //para1.Direction=ParameterDirection.Input;//参数方向为输入参数 //cmd.Parameters.Add(para1); //SqlParameterpara2=newSqlParameter("@adds",adds); //para2.Direction=ParameterDirection.Input; //cmd.Parameters.Add(para2); //SqlParameterpara3=newSqlParameter("@ynames",ynames); //para3.Direction=ParameterDirection.Input; //cmd.Parameters.Add(para3); //SqlParameterpara4=newSqlParameter("@pids",pids); //para4.Direction=ParameterDirection.Input; //cmd.Parameters.Add(para4); //SqlParameterpara5=newSqlParameter("@cellp",cellp); //para5.Direction=ParameterDirection.Input; //cmd.Parameters.Add(para5); //SqlParameterpara6=newSqlParameter("@cphone",cphone); //para6.Direction=ParameterDirection.Input; //cmd.Parameters.Add(para6); //SqlParameterpara7=newSqlParameter("@amounts",amounts); //para7.Direction=ParameterDirection.Input; //cmd.Parameters.Add(para7); //SqlParameterpara8=newSqlParameter("@cartnumber",cartnumber); //para8.Direction=ParameterDirection.Input; //cmd.Parameters.Add(para8); //SqlParameterparaReturn=newSqlParameter("@return",SqlDbType.Int); //paraReturn.Direction=ParameterDirection.ReturnValue;//参数方向为返回参数 //cmd.Parameters.Add(paraReturn); //cmd.ExecuteNonQuery(); //objectobj=paraReturn; //if(obj.ToString()=="0") //{////存储过程执行失败 //} //else //{////成功 //} //} //#endregion 本文的数据库用的是sqlserver自带数据Northwind 1.只返回单一记录集的存储过程 SqlConnectionsqlconn=newSqlConnection(conn); SqlCommandcmd=newSqlCommand(); //设置sql连接 cmd.Connection=sqlconn; //如果执行语句 mandText="Categoriestest1"; //指定执行语句为存储过程 mandType=CommandType.StoredProcedure; SqlDataAdapterdp=newSqlDataAdapter(cmd); DataSetds=newDataSet(); //填充dataset dp.Fill(ds); //以下是显示效果 GridView1.DataSource=ds; GridView1.DataBind(); 存储过程Categoriestest1 CREATEPROCEDURECategoriestest1 AS select* fromCategories GO 2.没有输入输出的存储过程 SqlConnectionsqlconn=newSqlConnection(conn); SqlCommandcmd=newSqlCommand(); cmd.Connection=sqlconn; mandText="Categoriestest2"; mandType=CommandType.StoredProcedure; sqlconn.Open(); //执行并显示影响行数 Label1.Text=cmd.ExecuteNonQuery().ToString(); sqlconn.Close(); 存储过程Categoriestest2 CREATEPROCEDURECategoriestest2AS insertintodbo.Categories (CategoryName,[Description],[Picture]) values('test1','test1',null) GO 3.有返回值的存储过程 SqlConnectionsqlconn=newSqlConnection(conn); SqlCommandcmd=newSqlCommand(); cmd.Connection=sqlconn; mandText="Categoriestest3"; mandType=CommandType.StoredProcedure; //创建参数 IDataParameter[]parameters={ newSqlParameter("rval",SqlDbType.Int,4) }; //将参数类型设置为返回值类型 parameters[0].Direction=ParameterDirection.ReturnValue; //添加参数 cmd.Parameters.Add(parameters[0]); sqlconn.Open(); //执行存储过程并返回影响的行数 Label1.Text=cmd.ExecuteNonQuery().ToString(); sqlconn.Close(); //显示影响的行数和返回值 Label1.Text+="-"+parameters[0].Value.ToString(); 存储过程Categoriestest3 CREATEPROCEDURECategoriestest3 AS insertintodbo.Categories (CategoryName,[Description],[Picture]) values('test1','test1',null) return@@rowcount GO 4.有输入参数和输出参数的存储过程 SqlConnectionsqlconn=newSqlConnection(conn); SqlCommandcmd=newSqlCommand(); cmd.Connection=sqlconn; mandText="Categoriestest4"; mandType=CommandType.StoredProcedure; //创建参数 IDataParameter[]parameters={ newSqlParameter("@Id",SqlDbType.Int,4), newSqlParameter("@CategoryName",SqlDbType.NVarChar,15), }; //设置参数类型 parameters[0].Direction=ParameterDirection.Output;//设置为输出参数 parameters[1].Value="testCategoryName"; //添加参数 cmd.Parameters.Add(parameters[0]); cmd.Parameters.Add(parameters[1]); sqlconn.Open(); //执行存储过程并返回影响的行数 Label1.Text=cmd.ExecuteNonQuery().ToString(); sqlconn.Close(); //显示影响的行数和输出参数 Label1.Text+="-"+parameters[0].Value.ToString(); 存储过程Categoriestest4 CREATEPROCEDURECategoriestest4 @idintoutput, @CategoryNamenvarchar(15) AS insertintodbo.Categories (CategoryName,[Description],[Picture]) values(@CategoryName,'test1',null) set@id=@@IDENTITY GO 5.同时具有返回值、输入参数、输出参数的存储过程 SqlConnectionsqlconn=newSqlConnection(conn); SqlCommandcmd=newSqlCommand(); cmd.Connection=sqlconn; mandText="Categoriestest5"; mandType=CommandType.StoredProcedure; //创建参数 IDataParameter[]parameters={ newSqlParameter("@Id",SqlDbType.Int,4), newSqlParameter("@CategoryName",SqlDbType.NVarChar,15), newSqlParameter("rval",SqlDbType.Int,4) }; //设置参数类型 parameters[0].Direction=ParameterDirection.Output;//设置为输出参数 parameters[1].Value="testCategoryName";//给输入参数赋值 parameters[2].Direction=ParameterDirection.ReturnValue;//设置为返回值 //添加参数 cmd.Parameters.Add(parameters[0]); cmd.Parameters.Add(parameters[1]); cmd.Parameters.Add(parameters[2]); sqlconn.Open(); //执行存储过程并返回影响的行数 Label1.Text=cmd.ExecuteNonQuery().ToString(); sqlconn.Close(); //显示影响的行数，输出参数和返回值 Label1.Text+="-"+parameters[0].Value.ToString()+"-"+parameters[2].Value.ToString(); 存储过程Categoriestest5 CREATEPROCEDURECategoriestest5 @idintoutput, @CategoryNamenvarchar(15) AS insertintodbo.Categories (CategoryName,[Description],[Picture]) values(@CategoryName,'test1',null) set@id=@@IDENTITY return@@rowcount GO 6.同时返回参数和记录集的存储过程 SqlConnectionsqlconn=newSqlConnection(conn); SqlCommandcmd=newSqlCommand(); cmd.Connection=sqlconn; mandText="Categoriestest6"; mandType=CommandType.StoredProcedure; //创建参数 IDataParameter[]parameters={ newSqlParameter("@Id",SqlDbType.Int,4), newSqlParameter("@CategoryName",SqlDbType.NVarChar,15), newSqlParameter("rval",SqlDbType.Int,4)//返回值 }; //设置参数类型 parameters[0].Direction=ParameterDirection.Output;//设置为输出参数 parameters[1].Value="testCategoryName";//给输入参数赋值 parameters[2].Direction=ParameterDirection.ReturnValue;//设置为返回值 //添加参数 cmd.Parameters.Add(parameters[0]); cmd.Parameters.Add(parameters[1]); cmd.Parameters.Add(parameters[2]); SqlDataAdapterdp=newSqlDataAdapter(cmd); DataSetds=newDataSet(); //填充dataset dp.Fill(ds); //显示结果集 GridView1.DataSource=ds.Tables[0]; GridView1.DataBind(); Label1.Text=""; //显示输出参数和返回值 Label1.Text+=parameters[0].Value.ToString()+"-"+parameters[2].Value.ToString(); 存储过程Categoriestest6 CREATEPROCEDURECategoriestest6 @idintoutput, @CategoryNamenvarchar(15) AS insertintodbo.Categories (CategoryName,[Description],[Picture]) values(@CategoryName,'test1',null) set@id=@@IDENTITY select*fromCategories return@@rowcount GO 7.返回多个记录集的存储过程 SqlConnectionsqlconn=newSqlConnection(conn); SqlCommandcmd=newSqlCommand(); cmd.Connection=sqlconn; mandText="Categoriestest7"; mandType=CommandType.StoredProcedure; SqlDataAdapterdp=newSqlDataAdapter(cmd); DataSetds=newDataSet(); //填充dataset dp.Fill(ds); //显示结果集1 GridView1.DataSource=ds.Tables[0]; GridView1.DataBind(); //显示结果集2 GridView2.DataSource=ds.Tables[1]; GridView2.DataBind(); 存储过程Categoriestest7 CREATEPROCEDURECategoriestest7 AS select*fromCategories select*fromCategories GO
