C#中调用SQL存储过程(带输入输出参数的例子)

 

首先建立存储过程，默认为输入参数，输出参数后面要加output

    /**

        存储过程         

        create procedure queryStuNameById

        @stuId varchar(10),--输入参数

        @stuName varchar(10) output --输出参数

            as

        select @stuName=stuName from stuInfo where stuId=@stuId         

     */

Command对象上设定输出参数类型，大小后，还要指明参数为输出参数。

//注意输出参数要设置大小,否则size默认为0,

sqlComm.Parameters.Add("@stuName", SqlDbType.VarChar, 10);

//设置参数的类型为输出参数,默认情况下是输入,

sqlComm.Parameters["@stuName"].Direction = ParameterDirection.Output;

执行完命令后，还要通过Command对象获得输出参数的值。

name = sqlComm.Parameters["@stuName"].Value.ToString();

完整代码如下：

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

 

namespace StoreProduceTest

{

    public class Program

    {

        static void Main(string[] args)

        {

            Operater op = new Operater();

            string name = op.QueryStuNameById("1234");

 

            Console.WriteLine(string.Format("学号为1234的学生的姓名为{0}", name));

        }

 

 

    }

 

    public class Operater

    {

        private string ConStr = "server=.;database=User;uid=sa;pwd=1234";

        private SqlConnection sqlCon = null;

        private SqlCommand sqlComm = null;

        SqlDataReader dr = null;

 

        public string QueryStuNameById(string Id)

        {

 

            string name = "";

 

            try

            {

                using (sqlCon = new SqlConnection(ConStr))

                {

 

                    sqlCon.Open();

                    sqlComm = new SqlCommand("queryStuNameById", sqlCon);

                    //设置命令的类型为存储过程

                    sqlComm.CommandType = CommandType.StoredProcedure;

 

                    //设置参数

                    sqlComm.Parameters.Add("@stuId", SqlDbType.VarChar);

 

                    //注意输出参数要设置大小,否则size默认为0,

                    sqlComm.Parameters.Add("@stuName", SqlDbType.VarChar, 10);

                    //设置参数的类型为输出参数,默认情况下是输入,

                    sqlComm.Parameters["@stuName"].Direction = ParameterDirection.Output;

 

                    //为参数赋值

                    sqlComm.Parameters["@stuId"].Value = "1234";

                    //执行

                    sqlComm.ExecuteNonQuery();

 

                    //得到输出参数的值,把赋值给name,注意,这里得到的是object类型的,要进行相应的类型轮换

                    name = sqlComm.Parameters["@stuName"].Value.ToString();

 

                }

 

            }

            catch (Exception ex)

            {

 

                Console.WriteLine(ex.ToString());

            }

            return name;

        }

    }

}

 

 改自：http://blog.163.com/pengpeng_614/blog/#m=0&t=1&c=fks_087067093083088069093095086095085081086067083087080074