.net调用存储过程详解

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

连接字符串

View Code

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

confige文件

View Code

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

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

View Code

 1 SqlConnection sqlconn = new SqlConnection(conn);
 2         SqlCommand cmd = new SqlCommand();
 3         // 设置sql连接
 4         cmd.Connection = sqlconn;
 5         // 如果执行语句
 6         cmd.CommandText = "Categoriestest1";
 7         // 指定执行语句为存储过程
 8         cmd.CommandType = CommandType.StoredProcedure;
 9 
10         SqlDataAdapter dp = new SqlDataAdapter(cmd);
11         DataSet ds = new DataSet();
12         // 填充dataset
13         dp.Fill(ds);
14         // 以下是显示效果
15         GridView1.DataSource = ds;
16         GridView1.DataBind();

存储过程Categoriestest1

View Code

1 CREATE PROCEDURE Categoriestest1 
2 AS
3 select * 
4 from  Categories
5 GO

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

c#代码部分

View Code

 1  SqlConnection sqlconn = new SqlConnection(conn);
 2         SqlCommand cmd = new SqlCommand();
 3 
 4         cmd.Connection = sqlconn;
 5         cmd.CommandText = "Categoriestest2";
 6         cmd.CommandType = CommandType.StoredProcedure;
 7         sqlconn.Open();
 8         // 执行并显示影响行数
 9         Label1.Text = cmd.ExecuteNonQuery().ToString();
10         sqlconn.Close();

存储过程Categoriestest2

View Code

1 CREATE PROCEDURE Categoriestest2  AS
2 insert into dbo.Categories 
3 (CategoryName,[Description],[Picture])
4 values ('test1','test1',null)
5 GO

3. 有返回值的存储过程

c#代码部分

View Code

 1 SqlConnection sqlconn = new SqlConnection(conn);
 2         SqlCommand cmd = new SqlCommand();
 3         cmd.Connection = sqlconn;
 4         cmd.CommandText = "Categoriestest3";
 5         cmd.CommandType = CommandType.StoredProcedure;
 6         // 创建参数
 7         IDataParameter[] parameters = {
 8                 new SqlParameter("rval", SqlDbType.Int,4)
 9             };
10         // 将参数类型设置为 返回值类型
11         parameters[0].Direction = ParameterDirection.ReturnValue;
12         // 添加参数
13         cmd.Parameters.Add(parameters[0]);
14 
15         sqlconn.Open();
16         // 执行存储过程并返回影响的行数
17         Label1.Text = cmd.ExecuteNonQuery().ToString();
18         sqlconn.Close();
19         // 显示影响的行数和返回值
20         Label1.Text += "-" + parameters[0].Value.ToString() ;

View Code

1 CREATE PROCEDURE Categoriestest3
2 AS
3 insert into dbo.Categories 
4 (CategoryName,[Description],[Picture])
5 values ('test1','test1',null)
6 return @@rowcount
7 GO

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

c#代码部分

View Code

 1 SqlConnection sqlconn = new SqlConnection(conn);
 2         SqlCommand cmd = new SqlCommand();
 3         cmd.Connection = sqlconn;
 4         cmd.CommandText = "Categoriestest4";
 5         cmd.CommandType = CommandType.StoredProcedure;
 6         // 创建参数
 7         IDataParameter[] parameters = {
 8                 new SqlParameter("@Id", SqlDbType.Int,4) ,
 9                 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) ,
10             };
11         // 设置参数类型
12         parameters[0].Direction = ParameterDirection.Output;  // 设置为输出参数
13         parameters[1].Value = "testCategoryName";
14         // 添加参数
15         cmd.Parameters.Add(parameters[0]);
16         cmd.Parameters.Add(parameters[1]);
17 
18         sqlconn.Open();
19         // 执行存储过程并返回影响的行数
20         Label1.Text = cmd.ExecuteNonQuery().ToString();
21         sqlconn.Close();
22         // 显示影响的行数和输出参数
23         Label1.Text += "-" + parameters[0].Value.ToString() ;

存储过程Categoriestest4

View Code

1 CREATE PROCEDURE Categoriestest4
2 @id int output,
3 @CategoryName nvarchar(15)
4 AS
5 insert into dbo.Categories 
6 (CategoryName,[Description],[Picture])
7 values (@CategoryName,'test1',null)
8 set  @id = @@IDENTITY
9 GO

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

c#代码部分

View Code

 1 SqlConnection sqlconn = new SqlConnection(conn);
 2         SqlCommand cmd = new SqlCommand();
 3         cmd.Connection = sqlconn;
 4         cmd.CommandText = "Categoriestest5";
 5         cmd.CommandType = CommandType.StoredProcedure;
 6         // 创建参数
 7         IDataParameter[] parameters = {
 8                 new SqlParameter("@Id", SqlDbType.Int,4) ,
 9                 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) ,
10                 new SqlParameter("rval", SqlDbType.Int,4)
11             };
12         // 设置参数类型
13         parameters[0].Direction = ParameterDirection.Output;       // 设置为输出参数
14         parameters[1].Value = "testCategoryName";                  // 给输入参数赋值
15         parameters[2].Direction = ParameterDirection.ReturnValue;  // 设置为返回值
16         // 添加参数
17         cmd.Parameters.Add(parameters[0]);
18         cmd.Parameters.Add(parameters[1]);
19         cmd.Parameters.Add(parameters[2]);
20 
21         sqlconn.Open();
22         // 执行存储过程并返回影响的行数
23         Label1.Text = cmd.ExecuteNonQuery().ToString();
24         sqlconn.Close();
25         // 显示影响的行数，输出参数和返回值
26         Label1.Text += "-" + parameters[0].Value.ToString() + "-" + parameters[2].Value.ToString();

存储过程Categoriestest5

View Code

 1 CREATE PROCEDURE Categoriestest5
 2 @id int output,
 3 @CategoryName nvarchar(15)
 4 AS
 5 insert into dbo.Categories 
 6 (CategoryName,[Description],[Picture])
 7 values (@CategoryName,'test1',null)
 8 set  @id = @@IDENTITY
 9 return @@rowcount
10 GO

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

c#代码部分

View Code

 1  SqlConnection sqlconn = new SqlConnection(conn);
 2         SqlCommand cmd = new SqlCommand();
 3         cmd.Connection = sqlconn;
 4         cmd.CommandText = "Categoriestest6";
 5         cmd.CommandType = CommandType.StoredProcedure;
 6         // 创建参数
 7         IDataParameter[] parameters = {
 8                 new SqlParameter("@Id", SqlDbType.Int,4) ,
 9                 new SqlParameter("@CategoryName", SqlDbType.NVarChar,15) ,
10                 new SqlParameter("rval", SqlDbType.Int,4)                   // 返回值
11             };
12         // 设置参数类型
13         parameters[0].Direction = ParameterDirection.Output;        // 设置为输出参数
14         parameters[1].Value = "testCategoryName";                   // 给输入参数赋值
15         parameters[2].Direction = ParameterDirection.ReturnValue;   // 设置为返回值
16         // 添加参数
17         cmd.Parameters.Add(parameters[0]);
18         cmd.Parameters.Add(parameters[1]);
19         cmd.Parameters.Add(parameters[2]);
20 
21         SqlDataAdapter dp = new SqlDataAdapter(cmd);
22         DataSet ds = new DataSet();
23         // 填充dataset
24         dp.Fill(ds);
25         // 显示结果集
26         GridView1.DataSource = ds.Tables[0];
27         GridView1.DataBind();
28 
29         Label1.Text = "";
30         // 显示输出参数和返回值
31         Label1.Text +=  parameters[0].Value.ToString() + "-" + parameters[2].Value.ToString();

存储过程Categoriestest6

View Code

 1 CREATE PROCEDURE Categoriestest6
 2 @id int output,
 3 @CategoryName nvarchar(15)
 4 AS
 5 insert into dbo.Categories 
 6 (CategoryName,[Description],[Picture])
 7 values (@CategoryName,'test1',null)
 8 set  @id = @@IDENTITY
 9 select * from Categories
10 return @@rowcount
11 GO

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

c#代码部分

View Code

 1  SqlConnection sqlconn = new SqlConnection(conn);
 2         SqlCommand cmd = new SqlCommand();
 3         
 4         cmd.Connection = sqlconn;
 5         cmd.CommandText = "Categoriestest7";
 6         cmd.CommandType = CommandType.StoredProcedure;
 7 
 8         SqlDataAdapter dp = new SqlDataAdapter(cmd);
 9         DataSet ds = new DataSet();
10         // 填充dataset
11         dp.Fill(ds);
12         // 显示结果集1
13         GridView1.DataSource = ds.Tables[0];
14         GridView1.DataBind();
15         // 显示结果集2
16         GridView2.DataSource = ds.Tables[1];
17         GridView2.DataBind();

存储过程Categoriestest7

View Code

1 CREATE PROCEDURE Categoriestest7
2 AS
3 select * from Categories
4 select * from Categories
5 GO