C# ParameterDirection

.net中有四个关于参数传入传出的类型 分别是：

System.Data.ParameterDirection.Input

System.Data.ParameterDirection.InputOutput

System.Data.ParameterDirection.Output

System.Data.ParameterDirection.ReturnValue

首先我把.Net中的参数定义为形式参数 而把存储过程的参数定义为实际参数

比如：cmd.Parameters.Add("@Input", System.Data.SqlDbType.Int);　 @Input为形式参数

而存储过程的@Input int,　 @Input为实际参数 

得到的结论如下：

数据库存储过程的实际参数如果没有默认值则形式参数必须传值给实际参数

但是如果形式参数的类型为ParameterDirection.Output 则传给实际参数的永远是空值

如果形式参数的类型为ParameterDirection.ReturnValue 则形式参数不会传值给实际参数 实际参数必须有默认值  否则代码会报错

如果形式参数类型为ParameterDirection.InputOutput 或者 ParameterDirection.Output 则实际参数必须有output 关键字

另外需要注意的是在.net中 System.DBNull.Value表示数据库参数为空值 而不是null

用于测试的存储过程如下：

/*

     function:测试C#中 ParameterDirection 枚举类的作用

     coder:benniuniu

     time:2007-11-25

*/

CREATE PROCEDURE proc_test_SQLParametersValue

@Input int,

@InputOutput int output,

@Output int output,

@ReturnValue int=1

AS  

     select @Input as Input,@InputOutput as InputOutput,@Output as [Output],@ReturnValue as ReturnValue

     return 2

GO

 

测试用的C# 代码类如下：

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Configuration;

using System.Data;

 

namespace CoderHelper.Test

{

    class SQLParametersValue

    {

        public SQLParametersValue()

        {

 

        }

 

        public int? Input=1;

        public int? InputOutput=2;

        public int? Output=3;

        public int? ReturnValue=4;

 

        public int? Input2;

        public int? InputOutput2;

        public int? Output2;

        public int? ReturnValue2;

 

        public void GetData(DataTable dt)

        {

            SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["LocalSqlConnectionString"]);

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;

            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.CommandText = "proc_test_SQLParametersValue";

 

            cmd.Parameters.Add("@Input", System.Data.SqlDbType.Int);

            cmd.Parameters["@Input"].Direction = System.Data.ParameterDirection.Input;

            if (Input == null)

            {

                cmd.Parameters["@Input"].Value = System.DBNull.Value;

            }

            else

            {

                cmd.Parameters["@Input"].Value = Input;

            }           

 

            cmd.Parameters.Add("@InputOutput", System.Data.SqlDbType.Int);

            cmd.Parameters["@InputOutput"].Direction = System.Data.ParameterDirection.InputOutput;

            if (InputOutput == null)

            {

                cmd.Parameters["@InputOutput"].Value = System.DBNull.Value;

            }

            else

            {

                cmd.Parameters["@InputOutput"].Value = InputOutput;

            }

 

            cmd.Parameters.Add("@Output", System.Data.SqlDbType.Int);

            cmd.Parameters["@Output"].Direction = System.Data.ParameterDirection.Output;

            if (Output == null)

            {

                cmd.Parameters["@Output"].Value = System.DBNull.Value;

            }

            else

            {

                cmd.Parameters["@Output"].Value = Output;

            }

 

            cmd.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);

            cmd.Parameters["@ReturnValue"].Direction = System.Data.ParameterDirection.ReturnValue;

            if (ReturnValue == null)

            {

                cmd.Parameters["@ReturnValue"].Value = System.DBNull.Value;

            }

            else

            {

                cmd.Parameters["@ReturnValue"].Value = ReturnValue;

            }

 

            SqlDataAdapter sa = new SqlDataAdapter(cmd);

            try

            {

                if (conn.State == System.Data.ConnectionState.Closed)

                {

                    conn.Open();

                }

 

                sa.Fill(dt);

 

                if (cmd.Parameters["@Input"].Value != System.DBNull.Value)

                    Input2 = Convert.ToInt32(cmd.Parameters["@Input"].Value);

 

                if (cmd.Parameters["@InputOutput"].Value != System.DBNull.Value)

                    InputOutput2 = Convert.ToInt32(cmd.Parameters["@InputOutput"].Value);

 

                if (cmd.Parameters["@Output"].Value != System.DBNull.Value)

                    Output2 = Convert.ToInt32(cmd.Parameters["@Output"].Value);

 

                if (cmd.Parameters["@ReturnValue"].Value != System.DBNull.Value)

                    ReturnValue2 = Convert.ToInt32(cmd.Parameters["@ReturnValue"].Value);

 

            }

            catch (Exception ex)

            {

 

            }

            finally

            {

                conn.Close();

            }

 

           

        }

 

    }

}

 

如上代码

public int? Output=3; 但是实际传给存储过程的值还是空值

public int? ReturnValue=4; 但是实际没有传值给存储过程

ReturnValue2 = Convert.ToInt32(cmd.Parameters["@ReturnValue"].Value); 取的是存储过程return的返回值此例存储过程代码是 return 2 所以取得值是2