proceduretest.cs

一个能自己提交存储过程类型和参数名的类.可以方便写存储过程传参.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ConTest
{

    class procedureTest
    {
        public static int i = 0;
        public static SqlParameter parameter;


        #region 传参查结果
        public static void GetProcedurePatameter(SqlCommand cmd, SqlConnection myConnection, string procedureName, object[] name)
        {
            SqlCommand myCommand2 = new SqlCommand();

            myCommand2.Connection = myConnection;
            myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + procedureName + "' order by ORDINAL_POSITION";

            SqlDataReader reader = myCommand2.ExecuteReader();
            while (reader.Read())
            {
                parameter = new SqlParameter();
                parameter.ParameterName = reader["PARAMETER_NAME"].ToString();
                parameter.Direction = reader["PARAMETER_MODE"].ToString() == "IN" ? ParameterDirection.Input : ParameterDirection.Output;
                parameter.SqlDbType = SqlDbType.VarChar;
                SqlDbType type = (SqlDbType)Enum.Parse(typeof(SqlDbType), reader["DATA_TYPE"].ToString(), true);
                if (parameter.Direction == ParameterDirection.Input)
                    parameter.Value = name[i];
                parameter.SqlDbType = type;             
                cmd.Parameters.Add(parameter);
                i++;
            }
            reader.Close();
            reader.Dispose();
            myCommand2.Dispose();
        }
        #endregion

        public static void GetprocedureResolt(SqlCommand cmd, SqlConnection con, string procedureName)
        {
            SqlCommand command = new SqlCommand();
            SqlConnection connection = con;
            command.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + procedureName + "' order by ORDINAL_POSITION";
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                parameter = new SqlParameter();
                parameter.ParameterName = reader["PARAMETER_NAME"].ToString();
                parameter.Direction = reader["PARAMETER_MODE"].ToString() == "IN" ? ParameterDirection.Input : ParameterDirection.Output;
                parameter.SqlDbType = SqlDbType.VarChar;
                SqlDbType type = (SqlDbType)Enum.Parse(typeof(SqlDbType), reader["DATA_TYPE"].ToString(), true);
                cmd.Parameters.Add(parameter);
                reader.Close();
                reader.Dispose();
                command.Dispose();
            }
        }
    }
}