c# 调用带有返回值的存储过程

先在SQL下建立一个存储过程：

create procedure procedure_bbb
@name char(10),
@address char(50) output
as
select @address=address from bbb where name=@name
if @@error<>0
   return -1
else
   return 0
go

下面是C#代码：

using System;
using System.Threading;
using System.Data;
using System.Data.SqlClient;

namespace program
{
    class wangjun
    {
        static void Main(string[] args)
        {
            //建立连接数据库对象sc
            SqlConnection sc = new SqlConnection();
            //设置连接字符串
            sc.ConnectionString = "server=.;database=wangjun;uid=sa;pwd=sqlserver";
            try
            {
                //使用连接对象连接到数据库
                sc.Open();
                //使用连接对象建立命令对象cmd
                SqlCommand cmd = sc.CreateCommand();
                //设置命令对象的字符串值
                cmd.CommandText = "procedure_bbb";
                //设置命令对象的类型为存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                //设置一个输入参数值为"大彪"
                cmd.Parameters.Add("@name", SqlDbType.Char, 10).Value = "大彪";
                //新建一个参数对象sp，它由cmd.parameters.add("@address",SqlDbType.Char,50)的返回值来得到实例
                SqlParameter sp = cmd.Parameters.Add("@address",SqlDbType.Char,50);
                //设置参数对象sp的范围是输出参数 output
                sp.Direction = ParameterDirection.Output;
                //新建一个参数对象sp2，它由cmd.Parameters.Add("@return",SqlDbType.Int)的返回值来得到实例
                SqlParameter sp2 = cmd.Parameters.Add("@return",SqlDbType.Int);
                //设置参数对象sp2的范围是得到返回值 returnvalue
                sp2.Direction = ParameterDirection.ReturnValue;
                //执行命令对象
                cmd.ExecuteNonQuery();
                //从返回值参数中得到返回数值 output参数的值
                string s = (string)sp.Value;
                //从返回值参数中得到返回数值 returnvalue参数的值
                int n = (int)sp2.Value;
                //打印这两个参数
                Console.WriteLine("{0}：{1}", s, n.ToString());
                //Console.WriteLine(s);
            }
            catch (SqlException e)
            {
                Console.WriteLine("出现错误{0}", e.Message);
            }
            finally
            {
                //关闭数据库
                sc.Close();
            }
        }
    }
}

 

使用SqlCommandBuilder静态方法填充命令对象参数

using System;
using System.Data;
using System.Data.SqlClient;

namespace program
{
    class wangjun
    {
        static void Main(string[] args)
        {
            //建立连接数据库对象
            SqlConnection sc = new SqlConnection("server=.;database=wangjun;uid=sa;pwd=sqlserver");
            try
            {
                //打开数据库
                sc.Open();
                //使用连接对象建立数据库命令对象
                SqlCommand cmd = sc.CreateCommand();
                //为数据库命令对象添加命令语句
                cmd.CommandText = "procedure_bbb";
                //设置数据库命令类型为commandtype.storedprocedure 存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                //使用静态方法sqlcommandbuilder的driveparameter方法填充cmd参数
                SqlCommandBuilder.DeriveParameters(cmd);
                //设置输入参数的值
                cmd.Parameters["@name"].Value = "大彪";
                cmd.Parameters["@address"].Value = "";
                //开始运行存储过程
                cmd.ExecuteNonQuery();
                //得到存储过程结果
                string s = (string)cmd.Parameters["@address"].Value;
                //打印结果
                Console.WriteLine(s);
            }
            catch (SqlException e)
            {
                Console.WriteLine("出现错误{0}", e.Message);
            }
            finally
            {
                //关闭连接
                sc.Close();
            }
        }
    }

}