C#调用SQL Server存储过程工具类

执行存储过程

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for BaseDB
/// </summary>
public class BaseDB
{
    #region 相关数据库访问成员变量定义
    /// <summary>
    /// 数据库连接对象
    /// </summary>
    public SqlConnection conn = null;
    /// <summary>
    /// 命令操作
    /// </summary>
    private SqlCommand m_Command = null;
    /// <summary>
    /// 异常错误标示
    /// 0 无异常  1 严重异常  2 一般性异常，可以继续执行
    /// </summary>
    public byte m_nExpErrorFlag = 0;
    /// <summary>
    /// 内部错误
    /// </summary>
    public string m_InnerError = "";
    /// <summary>
    /// 外部错误
    /// </summary>
    public string m_OutError = "对不起，数据库操作异常，请将错误即时报告给我们。";
    #endregion

    #region BaseDB
    /// <summary>
    /// 构造数据库连接
    /// </summary>
    /// <param name="strConnectString">数据库连接语句</param>
    public BaseDB(string strConnectString)
    {
        if (conn == null)
            conn = new SqlConnection(strConnectString);
        if (conn.State != ConnectionState.Open)
        {
            int nCount = 0;
            while (nCount < 5)
            {
                try
                {
                    nCount++;
                    conn.Open();
                    if (conn.State == ConnectionState.Open) break;
                }
                catch (Exception exp)
                {
                    nCount++;
                    m_nExpErrorFlag = 1;
                    m_InnerError = "错误对象名：" + exp.Source + " 异常消息：" + exp.Message;
                    SystemError.CreateErrorLog(m_InnerError);
                }
            }
        }
    }
    public BaseDB()
    {

    }
    #endregion

    #region 相关方法定义

    #region CreateCommand
    /// <summary>
    /// 创建使用存储过程的操作命令对象
    /// </summary>
    /// <param name="procName">过程名</param>
    /// <param name="prams">参数集</param>
    /// <returns>返回值</returns>
    private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
    {
        //创建数据库操作
        if (m_Command == null)
            m_Command = new SqlCommand(procName, conn);
        else
            m_Command.CommandText = procName;

        m_Command.CommandType = CommandType.StoredProcedure;

        m_Command.Parameters.Clear();//清除参数
        // add proc parameters
        if (prams != null)
        {
            foreach (SqlParameter parameter in prams)
                m_Command.Parameters.Add(parameter);
        }

        // return param 此处暂时可不要
        /*m_Command.Parameters.Add(
            new SqlParameter("ReturnValue", SqlDbType.Int, 4,
            ParameterDirection.ReturnValue, false, 0, 0,
            string.Empty, DataRowVersion.Default, null));
        */

        return m_Command;
    }
    #endregion

    #region RunProc
    /// <summary>
    /// 运行存储过程
    /// </summary>
    /// <param name="procName">过程名</param>
    /// <param name="prams">参数集</param>
    /// <returns>返回过程的返回值</returns>
    public int RunProc(string strProcName, SqlParameter[] prams)
    {
        m_nExpErrorFlag = 0;
        SqlCommand myCommand;
        try
        {
            myCommand = CreateCommand(strProcName, prams);
            myCommand.ExecuteNonQuery();
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal)
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction;
                    }
                    else
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=\"" + parameter.Value + "\",Direction." + parameter.Direction;
                    }
                }
            }

            SystemError.CreateErrorLog(m_InnerError);

            return -9;
        }

        conn.Close();
        return (int)myCommand.Parameters["@ReturnValue"].Value;
    }
    #endregion

    #region RunProcDT

    /// <summary>
    /// 运行存储过程,返回DataTable，用于查询类存储过程的操作
    /// </summary>
    /// <param name="procName">过程名</param>
    public DataTable RunProcDT(string strProcName)
    {
        m_nExpErrorFlag = 0;
        SqlCommand myCommand;
        SqlDataAdapter dataAdapter;
        DataTable dtResult;
        try
        {
            myCommand = CreateCommand(strProcName, null);
            dataAdapter = new SqlDataAdapter();

            dataAdapter.SelectCommand = myCommand;
            dtResult = new DataTable();

            dataAdapter.Fill(dtResult);
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message;
            SystemError.CreateErrorLog(m_InnerError);
            return null;
        }

        conn.Close();
        return dtResult;
    }

    /// <summary>
    /// 运行存储过程,返回DataTable
    /// </summary>
    /// <param name="procName">过程名</param>
    /// <param name="prams">参数集</param>
    /// <param name="strDataTableName">表别名</param>
    public DataTable RunProcDT(string strProcName, SqlParameter[] prams)
    {
        m_nExpErrorFlag = 0;
        SqlCommand myCommand;
        SqlDataAdapter dataAdapter;
        DataTable dtResult;
        try
        {
            myCommand = CreateCommand(strProcName, prams);
            dataAdapter = new SqlDataAdapter();

            dataAdapter.SelectCommand = myCommand;
            dtResult = new DataTable();

            dataAdapter.Fill(dtResult);
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal)
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction;
                    }
                    else
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=\"" + parameter.Value + "\",Direction." + parameter.Direction;
                    }
                }
            }

            SystemError.CreateErrorLog(m_InnerError);
            return null;
        }

        conn.Close();
        return dtResult;
    }

    /// <summary>
    /// 运行存储过程,返回DataTable，用于查询类存储过程的操作
    /// </summary>
    /// <param name="procName">过程名</param>
    /// <param name="strDataTableName">表别名</param>
    public DataTable RunProcDT(string strProcName, string strDataTableName)
    {
        m_nExpErrorFlag = 0;
        SqlCommand myCommand;
        SqlDataAdapter dataAdapter;
        DataTable dtResult;
        try
        {
            myCommand = CreateCommand(strProcName, null);
            dataAdapter = new SqlDataAdapter();

            dataAdapter.SelectCommand = myCommand;
            dtResult = new DataTable();
            dtResult.TableName = strDataTableName;

            dataAdapter.Fill(dtResult);
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message;
            SystemError.CreateErrorLog(m_InnerError);
            return null;
        }

        conn.Close();
        return dtResult;
    }

    /// <summary>
    /// 运行存储过程,返回DataTable
    /// </summary>
    /// <param name="procName">过程名</param>
    /// <param name="prams">参数集</param>
    /// <param name="strDataTableName">表别名</param>
    public DataTable RunProcDT(string strProcName, SqlParameter[] prams, string strDataTableName)
    {
        m_nExpErrorFlag = 0;
        SqlCommand myCommand;
        SqlDataAdapter dataAdapter;
        DataTable dtResult;
        try
        {
            myCommand = CreateCommand(strProcName, prams);
            dataAdapter = new SqlDataAdapter();

            dataAdapter.SelectCommand = myCommand;
            dtResult = new DataTable();
            dtResult.TableName = strDataTableName;

            dataAdapter.Fill(dtResult);
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal)
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction;
                    }
                    else
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=\"" + parameter.Value + "\",Direction." + parameter.Direction;
                    }
                }
            }

            SystemError.CreateErrorLog(m_InnerError);
            return null;
        }

        conn.Close();
        return dtResult;
    }

    #endregion

    #region RunProcScalar

    /// <summary>
    /// 运行存储过程,返回第一行第一列，用于查询类存储过程的操作
    /// </summary>
    /// <param name="procName">过程名</param>
    public object RunProcScalar(string strProcName)
    {
        m_nExpErrorFlag = 0;
        SqlCommand myCommand;
        object o;
        try
        {
            myCommand = CreateCommand(strProcName, null);
            o = myCommand.ExecuteScalar();
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message;
            SystemError.CreateErrorLog(m_InnerError);
            return null;
        }

        conn.Close();
        return o;
    }

    /// <summary>
    /// 运行存储过程,返回第一行第一列，用于查询类存储过程的操作
    /// </summary>
    /// <param name="procName">过程名</param>
    public object RunProcScalar(string strProcName, SqlParameter[] prams)
    {
        m_nExpErrorFlag = 0;
        SqlCommand myCommand;
        object o;
        try
        {
            myCommand = CreateCommand(strProcName, prams);
            o = myCommand.ExecuteScalar();
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal)
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction;
                    }
                    else
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=\"" + parameter.Value + "\",Direction." + parameter.Direction;
                    }
                }
            }

            SystemError.CreateErrorLog(m_InnerError);
            return null;
        }
        conn.Close();
        return o;
    }
    #endregion

    #region RunProcDS
    /// <summary>
    /// 运行存储过程,返回DataSet，用于查询类存储过程的操作
    /// </summary>
    /// <param name="procName">过程名</param>
    public DataSet RunProcDS(string strProcName)
    {
        m_nExpErrorFlag = 0;
        SqlCommand myCommand;
        SqlDataAdapter dataAdapter;
        DataSet dsResult;
        try
        {
            myCommand = CreateCommand(strProcName, null);
            dataAdapter = new SqlDataAdapter();

            dataAdapter.SelectCommand = myCommand;
            dsResult = new DataSet();

            dataAdapter.Fill(dsResult);
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message;
            SystemError.CreateErrorLog(m_InnerError);
            return null;
        }

        conn.Close();
        return dsResult;
    }

    /// <summary>
    /// 运行存储过程,返回DataSet
    /// </summary>
    /// <param name="procName">过程名</param>
    /// <param name="prams">参数集</param>
    /// <param name="strDataTableName">表别名</param>
    public DataSet RunProcDS(string strProcName, SqlParameter[] prams)
    {
        m_nExpErrorFlag = 0;
        SqlCommand myCommand;
        SqlDataAdapter dataAdapter;
        DataSet dsResult;
        try
        {
            myCommand = CreateCommand(strProcName, prams);
            dataAdapter = new SqlDataAdapter();

            dataAdapter.SelectCommand = myCommand;
            dsResult = new DataSet();

            dataAdapter.Fill(dsResult);
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal)
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction;
                    }
                    else
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=\"" + parameter.Value + "\",Direction." + parameter.Direction;
                    }
                }
            }

            SystemError.CreateErrorLog(m_InnerError);
            return null;
        }

        conn.Close();
        return dsResult;
    }
    #endregion

    #region RunProcReader
    /// <summary>
    /// 运行存储过程，返回SqlDataReader（使用完须关闭SqlDataReader,释放使用SqlDataReader资源）
    /// </summary>
    /// <param name="strProcName">过程名</param>
    /// <returns></returns>
    public SqlDataReader RunProcReader(string strProcName)
    {
        m_nExpErrorFlag = 0;
        SqlDataReader myReader;
        SqlCommand myCommand;
        try
        {
            myCommand = CreateCommand(strProcName, null);
            myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + "异常消息：" + exp.Message;
            SystemError.CreateErrorLog(m_InnerError);
            return null;
        }
        return myReader;
    }

    /// <summary>
    /// 运行存储过程，返回SqlDataReader（使用完须关闭SqlDataReader,释放使用SqlDataReader资源）
    /// </summary>
    /// <param name="strProcName">过程名</param>
    /// <param name="prams">参数集</param>
    /// <returns></returns>
    public SqlDataReader RunProcReader(string strProcName, SqlParameter[] prams)
    {
        m_nExpErrorFlag = 0;
        SqlDataReader myReader;
        SqlCommand myCommand;
        try
        {
            myCommand = CreateCommand(strProcName, prams);
            myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }
        catch (Exception exp)
        {
            m_nExpErrorFlag = 1;
            m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + "异常消息：" + exp.Message;

            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal)
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction;
                    }
                    else
                    {
                        m_InnerError = m_InnerError + "\r\n" + parameter.ParameterName + "=\"" + parameter.Value + "\",Direction." + parameter.Direction;
                    }
                }
            }

            SystemError.CreateErrorLog(m_InnerError);
            return null;
        }
        return myReader;
    }
    #endregion

    #region GetDataSet
    public DataSet GetDataSet(string sql)
    {
        SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        if (conn.State != ConnectionState.Closed)
            conn.Close();
        return ds;
    }
    #endregion

    #region 针对SqlParameters的一些构造模块

    /// <summary>
    /// 构建存储过程调用参数
    /// </summary>
    /// <param name="ParamName">存储过程名称</param>
    /// <param name="Direction">传入还是传出</param>
    /// <param name="Value">参数值</param>
    /// <returns>返回SqlParameter对象</returns>
    private SqlParameter MakeParam(string ParamName, ParameterDirection Direction, object Value)
    {
        SqlParameter param = new SqlParameter();

        param.ParameterName = ParamName;
        param.Direction = Direction;
        if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value;
        return param;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="paramName"></param>
    /// <param name="Direction"></param>
    /// <param name="Type"></param>
    /// <param name="Size"></param>
    /// <param name="Value"></param>
    /// <returns></returns>
    private SqlParameter MakeParam(string paramName, ParameterDirection Direction, SqlDbType Type, int Size, object Value)
    {
        SqlParameter param = new SqlParameter(paramName, Type, Size);
        param.Direction = Direction;
        if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value;
        return param;
    }

    /// <summary>
    /// 构建存储过程传入参数
    /// </summary>
    /// <param name="ParamName">存储过程名称</param>
    /// <param name="Value">参数值</param>
    /// <returns>返回SqlParameter对象</returns>
    public SqlParameter MakeInParam(string ParamName, object Value)
    {
        return MakeParam(ParamName, ParameterDirection.Input, Value);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="ParamName"></param>
    /// <param name="Type"></param>
    /// <param name="Size"></param>
    /// <param name="Value"></param>
    /// <returns></returns>
    public SqlParameter MakeInParam(string ParamName, SqlDbType Type, int Size, object Value)
    {
        return MakeParam(ParamName, ParameterDirection.Input, Type, Size, Value);
    }

    /// <summary>
    /// 构建存储过程
    /// </summary>
    /// <param name="ParamName">存储过程名称</param>
    /// <param name="Value">参数值</param>
    /// <returns>返回SqlParameter对象</returns>
    public SqlParameter MakeReturnParam(string ParamName)
    {
        return MakeParam(ParamName, ParameterDirection.ReturnValue, null);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="ParamName"></param>
    /// <param name="Type"></param>
    /// <param name="Size"></param>
    /// <param name="Value"></param>
    /// <returns></returns>
    public SqlParameter MakeReturnParam(string ParamName, SqlDbType Type, int Size)
    {
        return MakeParam(ParamName, ParameterDirection.ReturnValue, Type, Size, null);
    }

    /// <summary>
    /// 构建存储过程传出参数
    /// </summary>
    /// <param name="ParamName">存储过程名称</param>
    /// <returns>返回SqlParameter对象</returns>
    public SqlParameter MakeOutParam(string ParamName)
    {
        return MakeParam(ParamName, ParameterDirection.Output, null);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="ParamName"></param>
    /// <param name="Type"></param>
    /// <param name="Size"></param>
    /// <returns></returns>
    public SqlParameter MakeOutParam(string ParamName, SqlDbType Type, int Size)
    {
        return MakeParam(ParamName, ParameterDirection.Output, Type, Size, null);
    }

    #endregion

    #endregion
}

快捷生成SqlParameter数组

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

namespace DataAccess
{
    /// <summary>
    /// 存储过程相关工具类
    /// </summary>
    public class ProcHelper
    {
        #region 快捷生成一个 SqlParameter 数组

        /// <summary>
        /// 快捷生成一个 SqlParameter 数组
        /// </summary>
        /// <param name="paramArr">参数信息
        /// 格式要求：参数名 类型(长度) 参数类型（in/out/inout/return）, [如果前一个参数是输入参数那么这个参数是前一个参数的值],参数名 类型(长度) 参数类型（in/out/inout/return）,...
        /// 例："id int(4) in", 1, "name varchar(20) out"
        ///     上述参数表示将创建两个 SqlParameter 对象 name 分别为 @id 和 @name，@ 可省略不写
        ///     1 为名为 @id 的输入参数即 SqlParameter 对象的 Value 属性值
        /// </param>
        /// <returns>返回生成的参数列表</returns>
        public static SqlParameter[] GenParams(params object[] paramArr)
        {
            var sqlParameters = new List<SqlParameter>();
            bool isInDirection = false; // 记录上一个循环元素是否是输入参数
            int currentLoopParamIndex = -1; // 记录上一个参数的索引
            foreach (var param in paramArr)
            {
                // 如果上一个元素是输入参数，那么将后面一个元素赋值给上一个参数的 Value 属性
                if (isInDirection)
                {
                    sqlParameters.ToArray()[currentLoopParamIndex].Value = param;
                    isInDirection = false;
                    continue;
                }

                bool isParamInfo = param.GetType().Name.ToLower() == "string";
                if (isParamInfo && !isInDirection)
                {
                    try
                    {
                        var strs = param.ToString().Split(' ');
                        string paramName = strs[0].Contains("@") ? strs[0] : "@" + strs[0];
                        string[] dataTypeArr = strs[1].Split('(');
                        string dataTypeStr = dataTypeArr[0];
                        int dataLength = Convert.ToInt32(dataTypeArr[1].Replace(")", ""));
                        string directionStr = strs[2];
                        SqlParameter sqlParameter = new SqlParameter(paramName, GetDataType(dataTypeStr), dataLength);
                        sqlParameter.Direction = GetDirection(directionStr);
                        isInDirection = sqlParameter.Direction == ParameterDirection.Input;
                        sqlParameters.Add(sqlParameter);
                        currentLoopParamIndex++;
                    }
                    catch (Exception e)
                    {
                        throw new Exception("[" + param + "]参数格式不正确");
                    }
                }
            }

            return sqlParameters.ToArray();
        }

        #endregion

        #region 通过字符串获取参数对应数据类型的枚举

        private static SqlDbType GetDataType(string dataTypeStr)
        {
            dataTypeStr = dataTypeStr.ToLower();
            int typeCode = -1;
            switch (dataTypeStr)
            {
                case "bigint":
                    typeCode = 0;
                    break;
                case "binary":
                    typeCode = 1;
                    break;
                case "bit":
                    typeCode = 2;
                    break;
                case "char":
                    typeCode = 3;
                    break;
                case "datatime":
                    typeCode = 4;
                    break;
                case "decimal":
                    typeCode = 5;
                    break;
                case "float":
                    typeCode = 6;
                    break;
                case "image":
                    typeCode = 7;
                    break;
                case "int":
                    typeCode = 8;
                    break;
                case "money":
                    typeCode = 9;
                    break;
                case "nchar":
                    typeCode = 10;
                    break;
                case "ntext":
                    typeCode = 11;
                    break;
                case "nvarchar":
                    typeCode = 12;
                    break;
                case "real":
                    typeCode = 13;
                    break;
                case "uniqueidentifier":
                    typeCode = 14;
                    break;
                case "smalldatatime":
                    typeCode = 15;
                    break;
                case "smallint":
                    typeCode = 16;
                    break;
                case "smallmoney":
                    typeCode = 17;
                    break;
                case "text":
                    typeCode = 18;
                    break;
                case "timestamp":
                    typeCode = 19;
                    break;
                case "tinyint":
                    typeCode = 20;
                    break;
                case "varbinary":
                    typeCode = 21;
                    break;
                case "varchar":
                    typeCode = 22;
                    break;
                case "variant":
                    typeCode = 23;
                    break;
                case "xml":
                    typeCode = 25;
                    break;
                case "udt":
                    typeCode = 29;
                    break;
                case "structured":
                    typeCode = 30;
                    break;
                case "date":
                    typeCode = 31;
                    break;
                case "time":
                    typeCode = 32;
                    break;
                case "datatime2":
                    typeCode = 33;
                    break;
                case "datetimeoffset":
                    typeCode = 34;
                    break;
            }

            if (typeCode == -1) throw new Exception("无此数据类型[" + dataTypeStr + "]");
            return (SqlDbType) typeCode;
        }

        #endregion

        #region 通过字符串获取参数输出类型

        private static ParameterDirection GetDirection(string directionStr)
        {
            int directionCode = -1;
            switch (directionStr)
            {
                case "in":
                    directionCode = 1;
                    break;
                case "out":
                    directionCode = 2;
                    break;
                case "inout":
                    directionCode = 3;
                    break;
                case "return":
                    directionCode = 6;
                    break;
            }

            if (directionCode == -1) throw new Exception("无此输出类型[" + directionStr + "]");
            return (ParameterDirection) directionCode;
        }

        #endregion
    }
}