C#调用存储过程详解

本文的数据库用的是sql server自带数据Northwind

连接字符串：

string conn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

 

confige文件：

<connectionStrings>

        <add name="NorthwindConnectionString" connectionString="Data Source=.;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>

 </connectionStrings>

 

1. 只返回单一记录集的存储过程

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

         // 设置sql连接 

         cmd.Connection = sqlconn; 

         // 如果执行语句 

         cmd.CommandText = "Categoriestest1"; 

         // 指定执行语句为存储过程 

         cmd.CommandType = CommandType.StoredProcedure; 

  

        SqlDataAdapter dp = new SqlDataAdapter(cmd); 

         DataSet ds = new DataSet(); 

         // 填充dataset 

         dp.Fill(ds); 

         // 以下是显示效果 

         GridView1.DataSource = ds; 

         GridView1.DataBind();

 

存储过程Categoriestest1

CREATE PROCEDURE Categoriestest1  

 AS

 select *  

 from  Categories 

 GO 

 

 

 

2. 没有输入输出的存储过程

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

  

        cmd.Connection = sqlconn; 

         cmd.CommandText = "Categoriestest2"; 

         cmd.CommandType = CommandType.StoredProcedure; 

         sqlconn.Open(); 

         // 执行并显示影响行数 

         Label1.Text = cmd.ExecuteNonQuery().ToString(); 

         sqlconn.Close();

存储过程Categoriestest2

CREATE PROCEDURE Categoriestest2  AS

 insert into dbo.Categories  

 (CategoryName,[Description],[Picture]) 

 values ('test1','test1',null) 

 GO

 

 

3. 有返回值的存储过程

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

         cmd.Connection = sqlconn; 

         cmd.CommandText = "Categoriestest3"; 

         cmd.CommandType = CommandType.StoredProcedure; 

         // 创建参数 

         IDataParameter[] parameters = { 

                 new SqlParameter("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

CREATE PROCEDURE Categoriestest3 

 AS

 insert into dbo.Categories  

 (CategoryName,[Description],[Picture]) 

 values ('test1','test1',null) 

 return @@rowcount 

 GO

 

 

4. 有输入参数和输出参数的存储过程

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

         cmd.Connection = sqlconn;  

         cmd.CommandText = "Categoriestest4"; 

         cmd.CommandType = CommandType.StoredProcedure; 

         // 创建参数 

         IDataParameter[] parameters = { 

                 new SqlParameter("@Id", SqlDbType.Int,4) , 

                 new SqlParameter("@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

CREATE PROCEDURE Categoriestest4 

 @id int output, 

 @CategoryName nvarchar(15) 

 AS

 insert into dbo.Categories  

 (CategoryName,[Description],[Picture]) 

 values (@CategoryName,'test1',null) 

 set  @id = @@IDENTITY 

 GO

 

 

5. 同时具有返回值、输入参数、输出参数的存储过程

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

         cmd.Connection = sqlconn; 

         cmd.CommandText = "Categoriestest5"; 

         cmd.CommandType = CommandType.StoredProcedure; 

         // 创建参数 

         IDataParameter[] parameters = { 

                 new SqlParameter("@Id", SqlDbType.Int,4) , 

                 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) , 

                 new SqlParameter("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

CREATE PROCEDURE Categoriestest5 

 @id int output, 

 @CategoryName nvarchar(15) 

 AS

 insert into dbo.Categories  

 (CategoryName,[Description],[Picture]) 

 values (@CategoryName,'test1',null) 

 set  @id = @@IDENTITY 

 return @@rowcount 

 GO

 

 

6. 同时返回参数和记录集的存储过程

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

         cmd.Connection = sqlconn; 

         cmd.CommandText = "Categoriestest6"; 

         cmd.CommandType = CommandType.StoredProcedure; 

         // 创建参数 

         IDataParameter[] parameters = { 

                 new SqlParameter("@Id", SqlDbType.Int,4) , 

                 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) , 

                 new SqlParameter("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]); 

  

        SqlDataAdapter dp = new SqlDataAdapter(cmd); 

         DataSet ds = new DataSet(); 

         // 填充dataset 

         dp.Fill(ds); 

         // 显示结果集 

         GridView1.DataSource = ds.Tables[0]; 

         GridView1.DataBind(); 

  

        Label1.Text = ""; 

         // 显示输出参数和返回值 

         Label1.Text +=  parameters[0].Value.ToString() + "-" + parameters[2].Value.ToString();

存储过程Categoriestest6

CREATE PROCEDURE Categoriestest6 

 @id int output, 

 @CategoryName nvarchar(15) 

 AS

 insert into dbo.Categories  

 (CategoryName,[Description],[Picture]) 

 values (@CategoryName,'test1',null) 

 set  @id = @@IDENTITY 

 select * from Categories 

 return @@rowcount 

 GO

 

 

7. 返回多个记录集的存储过程

SqlConnection sqlconn = new SqlConnection(conn); 

         SqlCommand cmd = new SqlCommand(); 

          

         cmd.Connection = sqlconn; 

         cmd.CommandText = "Categoriestest7";  

         cmd.CommandType = CommandType.StoredProcedure; 

  

        SqlDataAdapter dp = new SqlDataAdapter(cmd); 

         DataSet ds = new DataSet(); 

         // 填充dataset 

         dp.Fill(ds); 

         // 显示结果集1 

         GridView1.DataSource = ds.Tables[0]; 

         GridView1.DataBind(); 

         // 显示结果集2 

         GridView2.DataSource = ds.Tables[1]; 

         GridView2.DataBind();

存储过程Categoriestest7

CREATE PROCEDURE Categoriestest7 

 AS

 select * from Categories 

 select * from Categories 

 GO  

 

 

C#.net中使用存储过程
1.构造与Sql服务器连接对象
  SqlConnection MyCon = new SqlConnection()
2.构造SqlCommand对象
  SqlCommand MyCmd = new SqlCommand()
3.指定SqlCommand对象调用的存储过程
  MyCmd.CommandText = "ProcedureName"
  ProcedureName是Sql数据库中实际存在的存储过程名
4.声明SqlCommand对象的命令类型为存储过程
  MyCmd.CommandType = CommandType.StoredProcedure
5.构造参数对象
  SqlParameter MyParam = new SqlParameter("@ParamName",类型,宽度);
  @ParamName:是调用存储过程的参数,参数名必需和现有的存储过程一致
  类型:SqlDbType.Char字符型,C#中用SqlDbType存储了Sql数据类型
  宽度:类型字宽.如char(15)
6.赋值给参数对象
  MyParam.Value = Value
7.将参数添加到SqlCommand对象
  MyCmd.Parameters.Add(MyParam)
8.打开Sql连接对象
  MyCon.Open()
9.执行SqlCommand
  MyCmd.ExecuteNonQuery()
10.关闭Sql连接对象
   MyCon.Close()