C#调用带参数的存储过程

时间：2011-03-28 来源：许明会

带参数的存储过程：默认参数、输出参数、返回值

        /*==============================================
        -- Author:        xuminghui
        -- Create date: 2011/03/28
        -- Description:    带默认参数，输出参数和返回值的存储过程
        -- =============================================
        CREATE PROCEDURE ProcedureTest
            @myName varchar(20) output,
            @myAge int = 33
        AS
        BEGIN
            SET NOCOUNT ON;

            set @myName='xuminghui'
            return 1978+@myAge
        END
        GO
        */
        private static void SqlCommandWithComplexParameters()
        {
            string connect = "Data Source=.\\sqlexpress; Initial Catalog=NorthWind ; Integrated Security = true;";
            //准备Connection对象和Command对象：设置Command对象调用存储过程
            SqlConnection cn = new SqlConnection(connect);
            SqlCommand cmd = new SqlCommand("ProcedureTest", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            //设定参数：输出参数-参数名称必须和存储过程输出参数名一致；
            //返回值参数-参数名称任意、修改默认参数
            SqlParameter paramMyName = cmd.Parameters.Add("@myname", SqlDbType.VarChar, 20);
            paramMyName.Direction = ParameterDirection.Output;
            SqlParameter paramReturnValue = cmd.Parameters.Add("Return", SqlDbType.Int);
            paramReturnValue.Direction = ParameterDirection.ReturnValue;
            //cmd.Parameters.AddWithValue("@myAge", 34);
            //执行查询，获取存储过程执行后的输出参数和返回参数值

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
            //输出结果：MyName=xuminghui         ReturnValue=2011
            Console.WriteLine("MyName={0}\t ReturnValue={1}\t \n", paramMyName.Value, paramReturnValue.Value);
        }