DataAccessLayer

/************************
 * 金色海洋工作室 —— 数据访问层
 *
 * 数据访问层(SQL Server 版)
 *
 * 最后修改时间：2006年8月15日
 ************************/

using System;
using System.Data;
using System.Data.SqlClient;
using JYK.DataStruct;            //结构，用于“实体类”
//using System.Security.Principal;

namespace JYK
{
    /// <summary>
    /// 存储过程的参数的类型，在输出型的参数里使用。
    /// </summary>
    public enum ParameterKind
    {
        Int,
        Double,
        Decimal,
        NVarChar,
        Bit
    }

    /// <summary>
    /// 这是一个通用的数据访问层接口。对ADO.NET的封装。功能类似于 SQLHelper ，但是比SQLHelper要简洁和高效。
    /// </summary>
    public sealed class DataAccessLayer
    {
        #region 属性
        private static string errorMsg;                    //出错信息
        private static bool isShowErrorSQL;            //是否显示出错的查询语句（包括存储过程名程）
        private int executeRowCount;                    //获取执行SQL查询语句后影响的行数
        private SqlCommand cm;                            //建立Command对象
        private SqlTransaction sqlTrans;                //用于事务处理
        private static bool isUseTrans;                    //是否启用了 .net 的事务处理

        /// <summary>
        /// 读取出错信息
        /// </summary>
        public string ErrorMsg
        {
            get { return errorMsg; }
        }

        /// <summary>
        /// 修改连接字符串，在同时访问两个或两个以上的数据库的时候使用
        /// </summary>
        public string cnString
        {
            set { cm.Connection.ConnectionString = value; }
            get { return cm.Connection.ConnectionString; }
        }

        /// <summary>
        /// 获取执行SQL查询语句后影响的行数
        /// </summary>
        public int ExecuteRowCount
        {
            get { return executeRowCount; }
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (isUseTrans)
                sqlTrans.Dispose();

            errorMsg = null;
            cm.Parameters.Clear();
            cm.Connection.Close();
            cm.Dispose();
        }
        #endregion

        public DataAccessLayer()    //构造函数
        {
            //默认不使用事务
            isUseTrans = false;
            //获取连接字符串
            cm = new SqlCommand();
            cm.Connection = new SqlConnection("获取连接字符串");
            //JYK.Connection.ConnectionString
            //初始化错误信息
            errorMsg = "0";
            isShowErrorSQL = true;  //本地运行，显示出错的查询语句（包括存储过程名程）
            //isShowErrorSQL = false; //服务器运行，不显示出错的查询语句（包括存储过程名程）
        }

        #region 内部函数

        //设置初始值
        /// <summary>
        /// 设置 errorMsg = "0" ；cm.CommandText 和 cm.CommandType
        /// </summary>
        /// <param name="commandText">查询语句或者存储过程</param>
        /// <param name="commandType">1：存储过程；2：查询语句</param>
        private void SetCommand(string commandText, int commandType)
        {
            errorMsg = "0";                            //清空错误信息
            executeRowCount = 0;
            cm.CommandText = commandText;
            if (commandType == 1)
                cm.CommandType = CommandType.Text;
            else
                cm.CommandType = CommandType.StoredProcedure;
        }

        //设置出错信息
        /// <summary>
        /// 当发生异常时，所作的处理
        /// </summary>
        /// <param name="FunctionName">函数名称</param>
        /// <param name="commandText">查询语句或者存储过程</param>
        /// <param name="message">错误信息</param>
        private void SetErrorMsg(string FunctionName, string commandText, string message)
        {
            //设置返回到上一层的错误信息
            errorMsg = FunctionName + "函数出现错误。<BR>错误信息：" + message;
            if (isShowErrorSQL)
                errorMsg += "<BR>查询语句：" + commandText;
            if (isUseTrans)
                this.TranRollBack();            //事务模式下：回滚事务

            cm.Connection.Close();                //关闭连接
            addLogErr(commandText, errorMsg);    //记录到错误日志
        }

        #endregion

        #region 记录错误日志
        //如果要使用的话，根据你的需要进行修改。
        public void addLogErr(string SPName, string ErrDescribe)
        {
            //记录到错误日志
            string FilePath = System.Web.HttpContext.Current.Server.MapPath("/log/" + DateTime.Now.ToString("yyyyMMdd") + ".txt");
            System.Text.StringBuilder str = new System.Text.StringBuilder();
            str.Append(DateTime.Now.ToString());
            str.Append(" ");
            str.Append(SPName);
            str.Append(" ");
            str.Append(ErrDescribe.Replace("<BR>", ""));
            str.Append(" ");

            System.IO.StreamWriter sw = null;
            try
            {
                sw = new System.IO.StreamWriter(FilePath, true, System.Text.Encoding.Unicode);
                sw.Write(str.ToString());
            }
            catch (Exception ex)
            {
                System.Web.HttpContext.Current.Response.Write("没有访问日志文件的权限！或日志文件只读!");
            }
            finally
            {
                if (sw != null)
                    sw.Close();
            }
        }
        #endregion

        //事务日志
        #region 事务处理部分。并没有做太多的测试，有不合理的地方请多指教
        /// <summary>
        /// 打开连接，并且开始事务。
        /// </summary>
        public void TranBegin()
        {
            cm.Connection.Open();
            sqlTrans = cm.Connection.BeginTransaction();
            cm.Transaction = sqlTrans;
            isUseTrans = true;
        }
        /// <summary>
        /// 提交事务，并关闭连接
        /// </summary>
        public void TranCommit()
        {
            sqlTrans.Commit();
            cm.Connection.Close();
        }
        /// <summary>
        /// 回滚事务，并关闭连接。在程序出错的时候，自动调用。
        /// </summary>
        public void TranRollBack()
        {
            sqlTrans.Rollback();
            cm.Connection.Close();
        }

        #endregion
        //查询语句部分
        #region 运行查询语句返回记录（DataSet、DataTable、DataRow、String[]、String）

        /// <summary>
        /// 运行SQL查询语句 返回DataSet。可以传入多条查询语句，返回的DataSet里会有多个DataTable
        /// </summary>
        /// <param name="SQL">查询语句。比如select * from tableName</param>
        /// <returns>返回DataSet</returns>
        #region 函数实现 — — RunSqlDataSet
        public DataSet RunSqlDataSet(string SQL)
        {
            SetCommand(SQL, 1);        //设置command
            SqlDataAdapter da = new SqlDataAdapter(cm);
            try
            {
                DataSet DS = new DataSet();
                da.Fill(DS);
                return DS;
            }
            catch (Exception ex)
            {
                SetErrorMsg("RunSqlDataSet", SQL, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                //自动关闭了，不用手动关闭。
                da.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 运行SQL查询语句 返回DataTable。
        /// </summary>
        /// <param name="SQL">查询语句。比如select * from tableName</param>
        /// <returns>返回DataTable</returns>
        #region 函数实现 — — RunSqlDataTable
        public DataTable RunSqlDataTable(string SQL)
        {
            SetCommand(SQL, 1);        //设置command
            SqlDataAdapter da = new SqlDataAdapter(cm);
            try
            {
                DataTable DT = new DataTable();
                da.Fill(DT);
                return DT;
            }
            catch (Exception ex)
            {
                SetErrorMsg("RunSqlDataTable", SQL, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                //自动关闭了，不用手动关闭。
                da.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 运行SQl语句返回第一条记录。返回DataRow
        /// </summary>
        /// <param name="SQL">查询语句。比如select * from tableName</param>
        /// <returns></returns>
        #region 函数实现 — — RunSqlDataRow
        public DataRow RunSqlDataRow(string SQL)
        {
            SetCommand(SQL, 1);        //设置command
            SqlDataAdapter da = new SqlDataAdapter(cm);
            try
            {
                DataTable DT = new DataTable();
                da.Fill(DT);
                if (DT.Rows.Count > 0)
                    return DT.Rows[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                SetErrorMsg("RunSqlDataRow", SQL, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                //自动关闭了，不用手动关闭。
                da.Dispose();
            }

        }
        #endregion

        /// <summary>
        /// 运行SQl语句返回第一条记录的数组。返回字符串数组
        /// </summary>
        /// <param name="SQL">查询语句。比如select top 1 * from tableName</param>
        /// <returns></returns>
        #region 函数实现 — — RunSqlStrings
        public string[] RunSqlStrings(string SQL)
        {
            //返回ID 传入查询语句，返回第一条记录的第一的字段的值
            SetCommand(SQL, 1);        //设置command
            try
            {
                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
                    cm.Connection.Open();

                SqlDataReader r = cm.ExecuteReader(CommandBehavior.SingleRow);
                if (r.Read())
                {
                    int ArrLength = r.FieldCount;

                    string[] strValue = new string[ArrLength];
                    for (int i = 0; i < ArrLength; i++)
                        strValue[i] = r.GetValue(i).ToString();
                    return strValue;
                }
                else
                {
                    r.Close();
                    return null;
                }
            }
            catch (Exception ex)
            {
                SetErrorMsg("RunSqlStrs", SQL, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                if (!isUseTrans)
                    cm.Connection.Close();

            }
        }
        #endregion

        /// <summary>
        /// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组
        /// </summary>
        /// <param name="SQL">查询语句。比如select myName from tableName</param>
        /// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>
        /// <returns></returns>
        #region 函数实现 — — RunSqlStringsByRow
        public string[] RunSqlStringsByRow(string SQL, string SQLForCount)
        {
            //先获取记录数
            int RowCount = 0;
            if (JYK.Functions.IsInt(SQLForCount))
            {
                RowCount = Int32.Parse(SQLForCount);
            }
            else
            {
                string strRowCount = RunSqlGetID(SQLForCount);
                if (strRowCount == null)
                    return null;

                RowCount = Int32.Parse(strRowCount);
            }
            if (RowCount < 1)
                return null;

            //返回ID 传入查询语句，返回第一条记录的第一的字段的值
            SetCommand(SQL, 1);        //设置command
            try
            {
                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
                    cm.Connection.Open();

                string[] strValue = new string[RowCount];
                SqlDataReader r = cm.ExecuteReader();
                int i = 0;
                while (r.Read())
                {
                    strValue[i] = r[0].ToString();
                    i++;
                }
                return strValue;

            }
            catch (Exception ex)
            {
                SetErrorMsg("RunSqlStrs", SQL, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                if (!isUseTrans)
                    cm.Connection.Close();

            }
        }
        #endregion

        /// <summary>
        /// 运行SQl语句返回第一条记录的第一列的值。
        /// </summary>
        /// <param name="SQL">查询语句。比如select top 1 ID from tableName where userName='aa'。会返回ID的内容</param>
        /// <returns></returns>
        #region 函数实现 — — RunSqlGetID
        public string RunSqlGetID(string SQL)
        {
            SetCommand(SQL, 1);        //设置command
            try
            {
                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
                    cm.Connection.Open();

                SqlDataReader r = cm.ExecuteReader(CommandBehavior.SingleRow);
                if (r.Read())
                    return r.GetValue(0).ToString();
                else
                    return null;
            }
            catch (Exception ex)
            {
                SetErrorMsg("RunSqlGetID", SQL, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                if (!isUseTrans)
                    cm.Connection.Close();
            }
        }
        #endregion

        #endregion

        #region 运行查询语句不返回记录集（无返回记录、检查持否存在指定的记录）
        /// <summary>
        /// 运行SQL查询语句，不返回记录集。用于添加、修改、删除等操作
        /// </summary>
        /// <param name="SQL">查询语句。比如insert into tableName 、update tableName</param>
        /// <returns></returns>
        #region 函数实现 — — RunSql
        public void RunSql(string SQL)
        {
            SetCommand(SQL, 1);        //设置command
            try
            {
                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
                    cm.Connection.Open();
                executeRowCount = cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SetErrorMsg("RunSql", SQL, ex.Message);    //处理错误
            }
            finally
            {
                if (!isUseTrans)
                    cm.Connection.Close();
            }
        }
        #endregion

        /// <summary>
        /// 执行一条SQL语句，看是否能查到记录 有：返回true；没有返回false，用于判断是否重名
        /// </summary>
        /// <param name="SQL">查询语句。比如select ID from tableName where userName='aa'</param>
        /// <returns></returns>
        #region 函数实现 — — RunSqlExists
        public bool RunSqlExists(string SQL)
        {
            SetCommand(SQL, 1);        //设置command
            try
            {
                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
                    cm.Connection.Open();

                SqlDataReader r = cm.ExecuteReader(CommandBehavior.CloseConnection);
                if (r.HasRows)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                errorMsg = "运行RunSqlExists函数时出现错误。<BR>错误信息：" + ex.Message;
                SetErrorMsg("RunSqlDataSet", SQL, ex.Message);    //处理错误
                return true;
            }
            finally
            {
                if (!isUseTrans)
                    cm.Connection.Close();
            }
        }
        #endregion

        #endregion
        //新增加的部分，返回结构数组用于绑定控件

        #region 函数实现 — — RunSqlStructBaseTitle
        /// <summary>
        /// 运行SQl语句返回结构数组BaseTitle
        /// </summary>
        /// <param name="SQL">查询语句。比如select myName from tableName</param>
        /// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>
        /// <param name="TitleCount">标题的字符数，一个汉字按照两个字符计算。传入“0”则表示不截取标题。</param>
        /// <param name="DateFormat">时间格式。比如“yyyy-MM-dd HH:mm:ss dddd”</param>
        /// <returns>返回BaseTitle结构的数组。URL，标题，时间，人气</returns>
        public BaseTitle[] RunSqlStructBaseTitle(string SQL, string SQLForCount, int TitleCount, string DateFormat, int IntroCount)
        {
            string strRowCount = RunSqlGetID(SQLForCount);
            if (strRowCount == null)
                return null;

            int DataCount = Int32.Parse(strRowCount);
            if (DataCount < 1)
                return null;

            return RunSqlStructBT(SQL, DataCount, TitleCount, DateFormat, IntroCount);
        }
        #endregion

        #region 函数实现 — — RunSqlStructBaseTitle
        /// <summary>
        /// 运行SQl语句返回结构数组BaseTitle
        /// </summary>
        /// <param name="SQL">查询语句。比如select myName from tableName</param>
        /// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>
        /// <param name="TitleCount">标题的字符数，一个汉字按照两个字符计算。传入“0”则表示不截取标题。</param>
        /// <param name="DateFormat">时间格式。比如“yyyy-MM-dd HH:mm:ss dddd”</param>
        /// <returns>返回BaseTitle结构的数组。URL，标题，时间，人气</returns>
        public BaseTitle[] RunSqlStructBaseTitle(string SQL, int DataCount, int TitleCount, string DateFormat, int IntroCount)
        {
            if (DataCount < 1)
                return null;

            return RunSqlStructBT(SQL, DataCount, TitleCount, DateFormat, IntroCount);
        }
        #endregion

        #region 函数实现 — — RunSqlStructBaseTitle
        /// <summary>
        /// 运行SQl语句返回结构数组BaseTitle
        /// </summary>
        /// <param name="SQL">查询语句。比如select myName from tableName</param>
        /// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>
        /// <param name="TitleCount">标题的字符数，一个汉字按照两个字符计算。传入“0”则表示不截取标题。</param>
        /// <param name="DateFormat">时间格式。比如“yyyy-MM-dd HH:mm:ss dddd”</param>
        /// <returns>返回BaseTitle结构的数组。URL，标题，时间，人气</returns>
        private BaseTitle[] RunSqlStructBT(string SQL, int DataCount, int TitleCount, string DateFormat, int IntroCount)
        {
            //返回ID 传入查询语句，返回第一条记录的第一的字段的值
            SetCommand(SQL, 1);        //设置command
            try
            {
                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
                    cm.Connection.Open();

                BaseTitle[] strValue = new BaseTitle[DataCount];
                SqlDataReader r = cm.ExecuteReader();
                int i = 0;
                while (r.Read())
                {
                    //主键
                    strValue[i].ID = r[0].ToString();
                    //网址
                    strValue[i].URL = r[1].ToString();
                    //判断截取字符数
                    if (TitleCount == 0)
                    {
                        strValue[i].AllTitle = r[2].ToString();
                        strValue[i].Title = r[2].ToString();
                    }
                    else
                    {
                        strValue[i].AllTitle = r[2].ToString();
                        strValue[i].Title = JYK.Functions.strCal(r[2].ToString(), TitleCount);
                    }

                    //判断时间
                    if (DateFormat.Length == 0)
                        strValue[i].AddedDate = "";
                    else
                        strValue[i].AddedDate = DateTime.Parse(r[3].ToString()).ToString(DateFormat);

                    //人气
                    strValue[i].Hits = r[4].ToString();
                    //图片路径
                    strValue[i].ImagePath = r[5].ToString();
                    //内容简介
                    if (IntroCount == 0)
                        strValue[i].Introduction = r[6].ToString();
                    else
                        strValue[i].Introduction = JYK.Functions.strCal(r[6].ToString(), IntroCount);

                    i++;
                }

                //                if (i == 0)
                //                {
                //                    //没有数据，返回空
                //                    return null;
                //                }
                //                else if (i < DataCount )
                //                {
                //                    //记录数不够用，修改数组大小
                //                    BaseTitle[] tmp = new BaseTitle[i];
                //                    int j = 0;
                //                    foreach(BaseTitle tt in strValue)
                //                    {
                //                        tmp[j].Hits         = tt.Hits ;
                //                        tmp[j].ID            = tt.ID  ;
                //                        tmp[j].ImagePath    = tt.ImagePath  ;
                //                        tmp[j].Introduction = tt.Introduction  ;
                //                        tmp[j].Title        = tt.Title  ;
                //                        tmp[j].URL            = tt.URL  ;
                //                        j++;
                //                        if (j == i )
                //                            break;
                //                    }
                //                    return tmp;
                //                }
                //                else
                //                {
                //                    return strValue;
                //                }
                return strValue;
            }
            catch (Exception ex)
            {
                SetErrorMsg("RunSqlStructBT", SQL, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                if (!isUseTrans)
                    cm.Connection.Close();

            }
        }
        #endregion

        //=====================================================================

        #region 函数实现 — — RunSqlStructCusTitle
        /// <summary>
        /// 运行SQl语句返回结构数组 CusTitle
        /// </summary>
        /// <param name="SQL">查询语句。比如select myName from tableName</param>
        /// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>
        /// <param name="TitleCount">标题的字符数，一个汉字按照两个字符计算。传入“0”则表示不截取标题。</param>
        /// <param name="DateFormat">时间格式。比如“yyyy-MM-dd HH:mm:ss dddd”</param>
        /// <returns>返回BaseTitle结构的数组。URL，标题，时间，人气</returns>
        public CusTitle[] RunSqlStructCusTitle(string SQL, string SQLForCount)
        {
            string strRowCount = RunSqlGetID(SQLForCount);
            if (strRowCount == null)
                return null;

            int DataCount = Int32.Parse(strRowCount);
            if (DataCount < 1)
                return null;

            return RunSqlStructCT(SQL, DataCount);
        }
        #endregion

        #region 函数实现 — — RunSqlStructCusTitle
        /// <summary>
        /// 运行SQl语句返回结构数组 CusTitle
        /// </summary>
        /// <param name="SQL">查询语句。比如select myName from tableName</param>
        /// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>
        /// <param name="TitleCount">标题的字符数，一个汉字按照两个字符计算。传入“0”则表示不截取标题。</param>
        /// <param name="DateFormat">时间格式。比如“yyyy-MM-dd HH:mm:ss dddd”</param>
        /// <returns>返回BaseTitle结构的数组。URL，标题，时间，人气</returns>
        public CusTitle[] RunSqlStructCusTitle(string SQL, int DataCount)
        {
            if (DataCount < 1)
                return null;

            return RunSqlStructCT(SQL, DataCount);
        }
        #endregion

        #region 函数实现 — — RunSqlStructCT
        /// <summary>
        /// 运行SQl语句返回结构数组 CusTitle
        /// </summary>
        /// <param name="SQL">查询语句。比如select myName from tableName</param>
        /// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>
        /// <param name="TitleCount">标题的字符数，一个汉字按照两个字符计算。传入“0”则表示不截取标题。</param>
        /// <param name="DateFormat">时间格式。比如“yyyy-MM-dd HH:mm:ss dddd”</param>
        /// <returns>返回BaseTitle结构的数组。URL，标题，时间，人气</returns>
        private CusTitle[] RunSqlStructCT(string SQL, int DataCount)
        {
            //返回ID 传入查询语句，返回第一条记录的第一的字段的值
            SetCommand(SQL, 1);        //设置command
            try
            {
                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
                    cm.Connection.Open();

                CusTitle[] strValue = new CusTitle[DataCount];
                SqlDataReader r = cm.ExecuteReader();
                int i = 0;
                int ArrLength = r.FieldCount - 2;
                while (r.Read())
                {
                    //ID
                    strValue[i].ID = r[0].ToString();
                    //标题
                    strValue[i].Title = r[1].ToString();

                    //其他
                    strValue[i].str = new string[ArrLength];
                    for (int j = 0; j < ArrLength; j++)
                        strValue[i].str[j] = r.GetValue(j + 2).ToString();

                    i++;
                }
                return strValue;

            }
            catch (Exception ex)
            {
                SetErrorMsg("RunSqlStrs", SQL, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                if (!isUseTrans)
                    cm.Connection.Close();

            }
        }
        #endregion

        //===========================end==============================
        //存储过程的参数部分
        #region 存储过程的参数部分——清除和添加参数

        #region 清除参数
        /// <summary>
        /// 清除SqlCommand的存储过程的参数。
        /// </summary>
        public void ClearParameter()
        { cm.Parameters.Clear(); }
        #endregion

        //int：        tinyint、smallint
        //bigint：
        //bool：    bit
        //double：    float、real
        //string：    char、nchar、varchar、nvarchar、uniqueidentifier、smalldatetime、datetime
        //string：    ntext、text

        //decimal：从 -10^38 +1 到 10^38 –1 的固定精度和小数位的数字数据。
        //numeric：功能上等同于 decimal。
        //decimal：    smallmoney、money

        //二进制
        //            binary、varbinary、image

        #region 输入型的参数 int 、double、decimal、nvarChar、、、
        #region int
        /// <summary>
        /// 添加int型的参数。
        /// </summary>
        /// <param name="ParameterName">参数名称。比如 @UserName</param>
        /// <param name="ParameterValue">参数值</param>
        public void addNewParameter(string ParameterName, int ParameterValue)
        {
            cm.Parameters.Add(ParameterName, SqlDbType.Int, 4);            //添加存储过程的参数
            cm.Parameters[ParameterName].Value = ParameterValue;            //负值
            //设置方向取默认值——输入
        }
        #endregion

        #region double
        /// <summary>
        /// 添加小数参数，double
        /// </summary>
        /// <param name="ParameterName">参数名称。比如 @UserName</param>
        /// <param name="ParameterValue">参数值</param>
        public void addNewParameter(string ParameterName, double ParameterValue)
        {
            cm.Parameters.Add(ParameterName, SqlDbType.Float);            //添加存储过程的参数
            cm.Parameters[ParameterName].Value = ParameterValue;            //负值
        }
        #endregion

        #region decimal
        /// <summary>
        /// 添加金额参数，方向是输入（input）。decimal
        /// </summary>
        /// <param name="ParameterName">参数名称。比如 @UserName</param>
        /// <param name="ParameterValue">参数值</param>
        public void addNewParameter(string ParameterName, decimal ParameterValue)
        {
            cm.Parameters.Add(ParameterName, SqlDbType.Decimal);            //添加存储过程的参数
            cm.Parameters[ParameterName].Value = ParameterValue;            //负值
        }
        #endregion

        #region nvarChar
        /// <summary>
        /// 添加nvarChar型的参数。方向是输入（input）
        /// </summary>
        /// <param name="ParameterName">参数名称。比如 @UserName</param>
        /// <param name="ParameterValue">参数值</param>
        /// <param name="size">参数大小</param>
        public void addNewParameter(string ParameterName, string ParameterValue, int size)
        {
            cm.Parameters.Add(ParameterName, SqlDbType.NVarChar, size);            //添加存储过程的参数
            cm.Parameters[ParameterName].Value = ParameterValue;            //负值
        }
        #endregion

        #region nText
        /// <summary>
        /// 添加nText型的参数。方向是输入（input）
        /// </summary>
        /// <param name="ParameterName">参数名称。比如 @UserName</param>
        /// <param name="ParameterValue">参数值</param>
        public void addNewParameter(string ParameterName, string ParameterValue)
        {
            cm.Parameters.Add(ParameterName, SqlDbType.NText);            //添加存储过程的参数
            cm.Parameters[ParameterName].Value = ParameterValue;            //负值
        }

        #endregion

        #region bit
        /// <summary>
        /// 添加bit型的参数。方向是输入（input）
        /// </summary>
        /// <param name="ParameterName">参数名称。比如 @UserName</param>
        /// <param name="ParameterValue">参数值</param>
        public void addNewParameter(string ParameterName, bool ParameterValue)
        {
            cm.Parameters.Add(ParameterName, SqlDbType.Bit);            //添加存储过程的参数
            cm.Parameters[ParameterName].Value = ParameterValue;            //负值
        }
        #endregion

        #endregion

        #region 输出型的参数

        /// <summary>
        /// 添加输出型的参数。只支持常用的几个参数类型，如果需要可以扩充。
        /// </summary>
        /// <param name="ParameterName">参数名称。比如 @UserName</param>
        /// <param name="ParameterValue">参数的类型</param>
        public void addNewParameter(string ParameterName, JYK.ParameterKind kind)
        {
            switch (kind)
            {
                case ParameterKind.Int:
                    cm.Parameters.Add(ParameterName, SqlDbType.Int);            //添加存储过程的参数
                    break;
                case ParameterKind.Double:
                    cm.Parameters.Add(ParameterName, SqlDbType.Float);            //添加存储过程的参数
                    break;
                case ParameterKind.Decimal:
                    cm.Parameters.Add(ParameterName, SqlDbType.Decimal);            //添加存储过程的参数
                    break;
                case ParameterKind.NVarChar:
                    cm.Parameters.Add(ParameterName, SqlDbType.NVarChar, 4000);            //添加存储过程的参数
                    break;
                case ParameterKind.Bit:
                    cm.Parameters.Add(ParameterName, SqlDbType.Bit);            //添加存储过程的参数
                    break;
            }
            cm.Parameters[ParameterName].Direction = ParameterDirection.Output;        //设置方向
        }

        #endregion

        #endregion

        #region 存储过程的参数部分——取参数的返回值

        /// <summary>
        /// 按序号返回参数值，一般在执行完存储过程后使用
        /// </summary>
        /// <param name="ParameterIndex">序号</param>
        /// <returns>返回参数的内容</returns>
        public string getParameter(int ParameterIndex)
        {
            return cm.Parameters[ParameterIndex].Value.ToString();
        }

        /// <summary>
        /// 按名称返回参数值，一般在执行完存储过程后使用
        /// </summary>
        /// <param name="ParameterName">参数名称。比如 @UserName</param>
        /// <returns>返回参数的内容</returns>
        public string getParameter(string ParameterName)
        {
            return cm.Parameters[ParameterName].Value.ToString();
        }
        #endregion

        #region 存储过程的参数部分——修改参数值
        /// <summary>
        /// 按序号修改参数值，一般在一次添加多条记录时用。
        /// </summary>
        /// <param name="ParameterIndex">序号</param>
        public void setParameter(int ParameterIndex, string parameterValue)
        { cm.Parameters[ParameterIndex].Value = parameterValue; }

        /// <summary>
        /// 按名称修改参数值，一般在一次添加多条记录时用
        /// </summary>
        /// <param name="ParameterName">参数名称。比如 @UserName</param>
        public void setParameter(string ParameterName, string parameterValue)
        { cm.Parameters[ParameterName].Value = parameterValue; }
        #endregion

        //存储过程部分
        #region 运行存储过程返回记录（DataSet、DataTable、不返回记录集）

        /// <summary>
        /// 运行存储过程返回DataSet。DataSet里面可以是多个表(DateTable)
        /// </summary>
        /// <param name="StoredProcedureName">存储过程名称</param>
        /// <returns>返回DataSet</returns>
        #region 函数实现 — — RunStoreDataSet
        public DataSet RunStoreDataSet(string StoredProcedureName)
        {
            SetCommand(StoredProcedureName, 2);        //设置command
            SqlDataAdapter da = new SqlDataAdapter(cm);
            try
            {
                DataSet DS = new DataSet();
                da.Fill(DS);
                return DS;
            }
            catch (Exception ex)
            {
                SetErrorMsg("RunStoreDataSet", StoredProcedureName, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                da.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 运行存储过程返回DataTable。
        /// </summary>
        /// <param name="StoredProcedureName">存储过程名称</param>
        /// <returns>返回DataTable</returns>
        #region 函数实现 — — RunStoreDataTable
        public DataTable RunStoreDataTable(string StoredProcedureName)
        {
            SetCommand(StoredProcedureName, 2);        //设置command
            SqlDataAdapter da = new SqlDataAdapter(cm);
            try
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                SetErrorMsg("RunStoreDataTable", StoredProcedureName, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                da.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 运行存储过程 不返回记录集，用于添加、修改、删除等操作
        /// </summary>
        /// <param name="ParameterName">存储过程名称</param>
        /// <returns></returns>
        public void RunStore(string StoredProcedureName)
        {
            SetCommand(StoredProcedureName, 2);        //设置command
            try
            {
                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
                    cm.Connection.Open();
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                SetErrorMsg("RunStore", StoredProcedureName, ex.Message);    //处理错误
            }
            finally
            {
                if (!isUseTrans)
                    cm.Connection.Close();
            }

        }

        #endregion

    }
}