C#调用存储过程

原创

Ctansuozhe 2012-02-08 00:27:33 博主文章分类：数据库 ©著作权

文章标签 休闲存储过程调用输出输出参数输出参数存储过程 procedure应用示例 文章分类 .Net 后端开发

©著作权归作者所有：来自51CTO博客作者Ctansuozhe的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、调用没有输入输出参数的存储过程

存储过程：

CREATE PROC GETAllBooks
AS
BEGIN
SELECT * FROM Books
END

调用示例：

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StrConn"].ConnectionString);
conn.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetAllBooks";
        cmd.Connection = conn;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();

2、调用有输入参数的存储过程

存储过程：

CREATE PROC ProInput
@bookname varchar(20)
AS
BEGIN
INSERT INTO Books Values('ddd777',@bookname,2)
END

调用示例：

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StrConn"].ConnectionString);
        conn.Open();
                  SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ProInput";
        cmd.Connection = conn;

SqlParameter[] param = {
 new SqlParameter("@bookname",SqlDbType.VarChar,20)
 };
 param[0].Value = "测试程序";
 cmd.Parameters.Add(param[0]);
 Response.Write(cmd.ExecuteNonQuery().ToString() + " ");

3、调用有输出参数的存储过程

存储过程：

CREATE PROC ProOutput
@id int output
AS
BEGIN
INSERT INTO Books Values('ddd777','output parameter',2)
set @id=@@identity
END

调用示例：

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StrConn"].ConnectionString);
conn.Open();

SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ProOutput";
        cmd.Connection = conn;

SqlParameter[] param = {
 new SqlParameter("@id",SqlDbType.Int,4),

 };
 param[0].Direction = ParameterDirection.Output;
 cmd.Parameters.Add(param[0]);
 Response.Write(cmd.ExecuteNonQuery().ToString() + " ");
 Response.Write(param[0].Value.ToString());

4、调用有输入输出参数的存储过程

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StrConn"].ConnectionString);
conn.Open();

   SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ProInputOutput";
        cmd.Connection = conn;

        SqlParameter[] param = {
                               new SqlParameter("@id",SqlDbType.Int,4),
                               new SqlParameter("@bookname",SqlDbType.VarChar,20)
                               };
        param[0].Direction = ParameterDirection.Output;
        param[1].Value = "测试程序";
        cmd.Parameters.Add(param[0]);
        cmd.Parameters.Add(param[1]);

Response.Write(cmd.ExecuteNonQuery().ToString() + " ");
Response.Write(param[0].Value.ToString());

5、调用有输入返回值参数的存储过程

存储过程：

Create ProcProInputReturn
@bookname varchar(20)
AS
BEGIN
INSERT INTO Books Values('www1010',@bookname,2)
return @@identity
END

调用示例：

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StrConn"].ConnectionString);
conn.Open();

SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ProInputReturn";
        cmd.Connection = conn;

SqlParameter[] param = {
 new SqlParameter("@bookname",SqlDbType.VarChar,20),
 new SqlParameter("@val",SqlDbType.Int,4)
 };
 param[0].Value = "书的名称";
 param[1].Direction = ParameterDirection.ReturnValue;
 cmd.Parameters.Add(param[0]);
 cmd.Parameters.Add(param[1]);
 Response.Write(cmd.ExecuteNonQuery().ToString() + " ");
 Response.Write(param[1].Value.ToString());

6、调用有输入输出返回值的存储过程

存储过程：

create proc ProInputoutputReturn
@id int output,
@bookname varchar(20)
AS
begin

INSERT INTO Books Values('www10414',@bookname,2)
set @id=@@identity

return @@rowcount
end

调用示例：

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StrConn"].ConnectionString);
conn.Open();

SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ProInputoutputReturn";
        cmd.Connection = conn;

SqlParameter[] param = {
 new SqlParameter("@id",SqlDbType.Int,4),
 new SqlParameter("@bookname",SqlDbType.VarChar,20),
 new SqlParameter("@val",SqlDbType.VarChar,20)
 };
 param[0].Direction = ParameterDirection.Output;
 param[1].Value = "书的名称";
 param[2].Direction = ParameterDirection.ReturnValue;
 cmd.Parameters.Add(param[0]);
 cmd.Parameters.Add(param[1]);
 cmd.Parameters.Add(param[2]);
 Response.Write(cmd.ExecuteNonQuery().ToString() + " ");
 Response.Write(param[0].Value.ToString()+" ");
 Response.Write(param[2].Value.ToString());

7、具有返回值与记录集的存储过程

存储过程：

create proc prooutputresult
@id int output,
@bookname varchar(20)
as
begin
INSERT INTO Books Values('www10414',@bookname,2)
set @id=@@identity
select * from books
return @@rowcount
end

调用示例：

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StrConn"].ConnectionString);
conn.Open();

SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "prooutputresult";
        cmd.Connection = conn;

SqlParameter[] param = {
 new SqlParameter("@id",SqlDbType.Int,4),
 new SqlParameter("@bookname",SqlDbType.VarChar,20),
 new SqlParameter("@val",SqlDbType.VarChar,20)
 };
 param[0].Direction = ParameterDirection.Output;
 param[1].Value = "书的名称";
 param[2].Direction = ParameterDirection.ReturnValue;
 cmd.Parameters.Add(param[0]);
 cmd.Parameters.Add(param[1]);
 cmd.Parameters.Add(param[2]);
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 DataTable dt = new DataTable();
 da.Fill(dt);
 GridView1.DataSource = dt;
 GridView1.DataBind();
 Response.Write(param[0].Value.ToString()+" ");
 Response.Write(param[2].Value.ToString());

8、返回多个记录集的存储过程

存储过程：

Create proc getmultiLog
as
begin
select * from books
select * from admin
end

调用过程：

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StrConn"].ConnectionString);
conn.Open();

SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "getmultiLog";
        cmd.Connection = conn;

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();

GridView2.DataSource = ds.Tables[1];
GridView2.DataBind();