C# 无固定参数的存储过程(篇三)

转自:http://topic.csdn.net/u/20080502/20/21502bcf-0169-480e-a038-a523585cd1bc.html?305383154

这个比较复杂,但是使用很方便

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
using System.IO;
using System.Collections;

/// <summary>
/// 执行存储过程类
/// </summary>
public class Vip120_Procedure
{
    private string sp_name;
    private SqlConnection myConnection;
    private SqlCommand myCommand;
    private SqlParameter myParameter;
    public Vip120_Procedure()//
        // TODO: 在此处添加构造函数逻辑
        //
    }
    ///

    /// 存储过程的返回值纪录类

    /// DataTable : 表示返回的表

    /// Output : 存储过程的输出参数

    /// ReturnValue : 存储过程的返回值

    ///

    public class SqlResult
    {

        public int ReturnValue;

        public Hashtable Output;

        //public DataSet dataSet;
        public DataTable DataTable;
        public SqlResult()
        {

            ReturnValue = 0;

            Output = new Hashtable();

            //dataSet = new DataSet();
            DataTable = new DataTable();

        }

    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="spName">存储过程名称</param>
    /// <param name="parameters">传入的参数值数组</param>
    /// <returns></returns>
    public SqlResult sqlhelper(string spName, object[] parameters)
    {
        sp_name = spName;
        return Call(parameters);
    }
    private SqlResult Call(params object[] parameters)
    {

        SqlResult result = new SqlResult();

        myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["vip120ConnectionString"].ConnectionString);

        myCommand = new SqlCommand(this.sp_name, myConnection);

        myCommand.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

        try
        {

            myConnection.Open();

            GetProcedureParameter(parameters);

            //myAdapter.Fill(result.dataSet, "Table");
            myAdapter.Fill(result.DataTable);
            GetOutputValue(result);

        }

        catch (Exception e)
        {

            throw e;

        }

        finally
        {

            myAdapter.Dispose();

            myCommand.Dispose();

            myConnection.Close();

            myConnection.Dispose();

        }

        return result;

    }
    private void GetProcedureParameter(params object[] parameters)
    {

        SqlCommand myCommand2 = new SqlCommand();

        myCommand2.Connection = this.myConnection;

        myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + this.sp_name + "' order by ORDINAL_POSITION";

        SqlDataReader reader = null;

        try
        {

            reader = myCommand2.ExecuteReader();

            myParameter = new SqlParameter();

            myParameter.ParameterName = "@Value";

            myParameter.SqlDbType = SqlDbType.Int;

            myParameter.Direction = ParameterDirection.ReturnValue;

            myCommand.Parameters.Add(myParameter);

            int i = 0;

            while (reader.Read())
            {

                myParameter = new SqlParameter();

                myParameter.ParameterName = reader["PARAMETER_NAME"].ToString();

                myParameter.Direction = reader["PARAMETER_MODE"].ToString() == "IN" ? ParameterDirection.Input : ParameterDirection.Output;




 switch (reader["DATA_TYPE"].ToString())
                {

                    case "bit":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (bool)parameters[i];

                        myParameter.SqlDbType = SqlDbType.Bit;

                        break;

                    case "bigint":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (int)parameters[i];

                        myParameter.SqlDbType = SqlDbType.BigInt;

                        break;

                    case "int":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (int)parameters[i];

                        myParameter.SqlDbType = SqlDbType.Int;

                        break;

                    case "decimal":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (double)parameters[i];

                        myParameter.SqlDbType = SqlDbType.Decimal;

                        myParameter.Precision = (byte)reader["NUMERIC_PRECISION"];

                        myParameter.Scale = (byte)reader["NUMERIC_SCALE"];

                        break;

                    case "nvarchar":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (string)parameters[i];

                        myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];

                        myParameter.SqlDbType = SqlDbType.NVarChar;

                        break;

                    case "varchar":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (string)parameters[i];

                        myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];

                        myParameter.SqlDbType = SqlDbType.VarChar;

                        break;

                    case "nchar":
                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (string)parameters[i];

                        myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];

                        myParameter.SqlDbType = SqlDbType.NChar;

                        break;

                    case "char":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (string)parameters[i];

                        myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];

                        myParameter.SqlDbType = SqlDbType.Char;

                        break;

                    case "ntext":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (string)parameters[i];

                        myParameter.SqlDbType = SqlDbType.NText;

                        break;

                    case "text":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (string)parameters[i];

                        myParameter.SqlDbType = SqlDbType.Text;

                        break;

                    case "datetime":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (DateTime)parameters[i];

                        myParameter.SqlDbType = SqlDbType.DateTime;

                        break;

                    case "smalldatetime":

                        if (myParameter.Direction == ParameterDirection.Input)

                            myParameter.Value = (DateTime)parameters[i];

                        myParameter.SqlDbType = SqlDbType.DateTime;

                        break;

                    case "image":

                        if (myParameter.Direction == ParameterDirection.Input)
                        {

                            HttpPostedFile PostedFile = (HttpPostedFile)parameters[i];

                            Byte[] FileByteArray = new Byte[PostedFile.ContentLength];

                            Stream StreamObject = PostedFile.InputStream;

                            StreamObject.Read(FileByteArray, 0, PostedFile.ContentLength);

                            myParameter.Value = FileByteArray;

                        }

                        myParameter.SqlDbType = SqlDbType.Image;

                        break;

                    case "uniqueidentifier":

                        //myParameter.Value = (string)parameters[i];

                        myParameter.SqlDbType = SqlDbType.UniqueIdentifier;

                        break;

                    default: break;

                }

                i++;

                myCommand.Parameters.Add(myParameter);

            }

        }

        catch (Exception e)
        {

            throw e;

        }

        finally
        {

            if (reader != null) reader.Close();

            myCommand2.Dispose();

        }
    }
    private void GetOutputValue(SqlResult result)
    {

        result.ReturnValue = (int)myCommand.Parameters["@Value"].Value;

        foreach (SqlParameter parameter in myCommand.Parameters)
        {

            if (parameter.Direction == ParameterDirection.Output)
            {
                result.Output.Add(parameter.ParameterName, parameter.Value);
            }
        }
    }
}

{