Ado.NET SQLHelper

using System;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;

namespace RaywindStudio.DAL {
    /// <summary>
    /// MSSQL数据库操作类
    /// </summary>
    public static class SqlHelper {
        /// <summary>
        /// 是否写调试信息
        /// 执行成功写到C:\DebugSQL.txt；
        /// 执行失败写到C:\DebugTxt.txt；
        /// </summary>
        public static bool debug = false;


        #region Select

        /// <summary>
        /// Select查表
        /// </summary>
        /// <param name="Columns">一条完整、直接执行的select语句</param>
        /// <param name="TableName">一条完整、直接执行的select语句</param>
        /// <param name="WP">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>DataTable</returns>
        public static DataTable SelectTable(string Columns, string TableName,
             WPrm[] WP, SqlConnection sqlconn) {
            return SelectTable(Columns, TableName, WP, "", sqlconn);
        }

        /// <summary>
        /// Select查表
        /// </summary>
        /// <param name="Columns">一条完整、直接执行的select语句</param>
        /// <param name="TableName">一条完整、直接执行的select语句</param>
        /// <param name="WP">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>DataTable</returns>
        public static DataTable SelectTable(string Columns, string TableName,
             WPrm WP, SqlConnection sqlconn) {
            return SelectTable(Columns, TableName, new WPrm[] { WP }, "", sqlconn);
        }

        /// <summary>
        /// Select查表
        /// </summary>
        /// <param name="Columns">一条完整、直接执行的select语句</param>
        /// <param name="TableName">一条完整、直接执行的select语句</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>DataTable</returns>
        public static DataTable SelectTable(string Columns, string TableName, SqlConnection sqlconn) {
            return SelectTable(Columns, TableName, new WPrm(), sqlconn);
        }

        /// <summary>
        /// Select查表
        /// </summary>
        /// <param name="Columns">一条完整、直接执行的select语句</param>
        /// <param name="TableName">一条完整、直接执行的select语句</param>
        /// <param name="ColumnsOrderByWithMode">排序列名加上ASC or DESC</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>DataTable</returns>
        public static DataTable SelectTable(string Columns, string TableName, string ColumnsOrderByWithMode, SqlConnection sqlconn) {
            return SelectTable(Columns, TableName, new WPrm(), ColumnsOrderByWithMode, sqlconn);
        }

        /// <summary>
        /// Select查表
        /// </summary>
        /// <param name="Columns">一条完整、直接执行的select语句</param>
        /// <param name="TableName">一条完整、直接执行的select语句</param>
        /// <param name="ColumnsOrderByWithMode">排序列名加上ASC or DESC</param>
        /// <param name="WP">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>DataTable</returns>
        public static DataTable SelectTable(string Columns, string TableName, WPrm WP,
            string ColumnsOrderByWithMode, SqlConnection sqlconn) {
            return SelectTable(Columns, TableName, new WPrm[] { WP },
                ColumnsOrderByWithMode, sqlconn);
        }

        /// <summary>
        /// Select查表
        /// </summary>
        /// <param name="Columns">一条完整、直接执行的select语句</param>
        /// <param name="TableName">一条完整、直接执行的select语句</param>
        /// <param name="ColumnsOrderByWithMode">排序列名加上ASC or DESC</param>
        /// <param name="WPS">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>DataTable</returns>
        public static DataTable SelectTable(string Columns, string TableName, WPrm[] WPS,
            string ColumnsOrderByWithMode, SqlConnection sqlconn) {
            string sql = "Select " + Columns + " From " + TableName + " Where 1=1 ";
            SqlParameter[] pr = new SqlParameter[WPS.Length];
            if (WPS[0].SqlParam.ParameterName.Length > 0) sql += SqlWhereBuild(WPS, ref pr);
            if (ColumnsOrderByWithMode.Length > 0) sql += " Order by " + ColumnsOrderByWithMode;
            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            if (WPS[0].SqlParam.ParameterName.Length > 0) cmd.Parameters.AddRange(pr);
            try {
                if (sqlconn.State != ConnectionState.Open) sqlconn.Open();
                using (DataTable dt = new DataTable()) {
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd)) {
                        try {
                            debugSQL(cmd.CommandText);
                            da.Fill(dt);
                            return dt;
                        } catch (Exception ex) {
                            debugTxt(cmd.CommandText, ex);
                            throw new Exception("SelectTable:
" + ex.Message);
                        }
                    }
                }
            } catch (Exception ex) {
                debugTxt(cmd.CommandText, ex);
                throw new Exception("SelectTable:
" + ex.Message);
            }
        }

        /// <summary>
        /// Select查值
        /// </summary>
        /// <param name="Columns">一条完整、直接执行的select语句</param>
        /// <param name="TableName">一条完整、直接执行的select语句</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <param name="WPS">SqlParameter</param>
        /// <returns>DataTable</returns>
        public static object SelectValue(string Columns, string TableName, WPrm[] WPS, SqlConnection sqlconn) {
            string sql = "Select " + Columns + " From " + TableName + " Where 1=1 ";
            SqlParameter[] pr = new SqlParameter[WPS.Length];
            if (WPS[0].SqlParam.ParameterName.Length > 0) sql += SqlWhereBuild(WPS, ref pr);
            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            if (WPS[0].SqlParam.ParameterName.Length > 0) cmd.Parameters.AddRange(pr);
            try {
                if (sqlconn.State != ConnectionState.Open)
                    sqlconn.Open();
                debugSQL(cmd.CommandText);
                return cmd.ExecuteScalar();
            } catch (Exception ex) {
                debugTxt(cmd.CommandText, ex);
                throw new Exception("SelectValue:
" + ex.Message);
            }
        }

        /// <summary>
        /// Select查值
        /// </summary>
        /// <param name="Columns">一条完整、直接执行的select语句</param>
        /// <param name="TableName">一条完整、直接执行的select语句</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <param name="WP">SqlParameter</param>
        /// <returns>DataTable</returns>
        public static object SelectValue(string Columns, string TableName,
            WPrm WP, SqlConnection sqlconn) {
            return SelectValue(Columns, TableName, new WPrm[] { WP }, sqlconn);
        }

        #endregion


        #region Insert
        /// <summary>
        /// 执行MSSQL插入表操作,默认列名：ColumnName=ParameterName.Replace("@","")
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="SP">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static int Insert(string TableName, SqlParameter[] SP, SqlConnection sqlconn) {
            string sql = "Insert into " + TableName + "(";
            for (int i = 0; i < SP.Length; i++)
                sql += SP[i].ParameterName.Replace("@", "") + ",";
            sql = sql.Substring(0, sql.Length - 1) + ") Values(";
            for (int j = 0; j < SP.Length; j++)
                sql += SP[j].ParameterName + ",";
            sql = sql.Substring(0, sql.Length - 1) + ")";

            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            cmd.Parameters.AddRange(SP);
            try {
                if (sqlconn.State != ConnectionState.Open)
                    sqlconn.Open();
                debugSQL(cmd.CommandText);
                return cmd.ExecuteNonQuery();
            } catch (Exception ex) {
                debugTxt(cmd.CommandText, ex);
                throw new Exception("InsertCMD:ExecuteNonQuery
" + ex.Message);
            }
        }


        /// <summary>
        /// 执行MSSQL插入表操作,默认列名：ColumnName=ParameterName.Replace("@","")
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="SP">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static int Insert(string TableName, SqlParameter SP, SqlConnection sqlconn) {
            return Insert(TableName, new SqlParameter[] { SP }, sqlconn);
        }


        /// <summary>
        /// 执行MSSQL插入表操作,默认列名：ColumnName=ParameterName.Replace("@","")
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="SPS">SqlParameter</param>
        /// <param name="ColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。
        /// 前后分别为Column和Value，添加在insert语句的column和value部分</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static int Insert(string TableName, SqlParameter[] SPS,
            string[,] ColumnValues, SqlConnection sqlconn) {
            string sql = "Insert into " + TableName + "(";
            for (int i = 0; i < SPS.Length; i++)
                sql += SPS[i].ParameterName.Replace("@", "") + ",";
            for (int ii = 0; ii < ColumnValues.GetLength(0); ii++)
                sql += ColumnValues[ii, 0] + ",";
            sql = sql.Substring(0, sql.Length - 1) + ") Values(";
            for (int j = 0; j < SPS.Length; j++)
                sql += SPS[j].ParameterName + ",";
            for (int jj = 0; jj < ColumnValues.GetLength(0); jj++)
                sql += ColumnValues[jj, 1] + ",";
            sql = sql.Substring(0, sql.Length - 1) + ")";
            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            cmd.Parameters.AddRange(SPS);
            try {
                if (sqlconn.State != ConnectionState.Open)
                    sqlconn.Open();
                debugSQL(cmd.CommandText);
                return cmd.ExecuteNonQuery();
            } catch (Exception ex) {
                debugTxt(cmd.CommandText, ex);
                throw new Exception("InsertCMD:ExecuteNonQuery
" + ex.Message);
            }
        }

        #endregion


        #region Update

        /// <summary>
        /// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","")
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="SPS">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <param name="WPS">查询条件 默认列名：ColumnName=ParameterName.Replace("@","")</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static int Update(string TableName, SqlParameter[] SPS, WPrm[] WPS, SqlConnection sqlconn) {
            string sql = "Update " + TableName + " Set ";
            for (int i = 0; i < SPS.Length; i++)
                sql += SPS[i].ParameterName.Replace("@", "")
                    + "=" + SPS[i].ParameterName + ",";
            sql = sql.Substring(0, sql.Length - 1)
               + " Where 1=1 ";
            SqlParameter[] pr = new SqlParameter[WPS.Length];
            if (WPS[0].SqlParam.ParameterName.Length > 0) sql += SqlWhereBuild(WPS, ref pr);
            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            cmd.Parameters.AddRange(SPS);
            if (WPS[0].SqlParam.ParameterName.Length > 0) cmd.Parameters.AddRange(pr);
            try {
                if (sqlconn.State != ConnectionState.Open)
                    sqlconn.Open();
                debugSQL(cmd.CommandText);
                return cmd.ExecuteNonQuery();
            } catch (Exception ex) {
                debugTxt(cmd.CommandText, ex);
                throw new Exception("Update:ExecuteNonQuery
" + ex.Message);
            }
        }

        /// <summary>
        /// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","")
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="SPS">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <param name="WPS">查询条件 默认列名：ColumnName=ParameterName.Replace("@","")</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static int Update(string TableName, SqlParameter SPS, WPrm WPS, SqlConnection sqlconn) {
            return Update(TableName, new SqlParameter[] { SPS }, new WPrm[] { WPS }, sqlconn);
        }

        /// <summary>
        /// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","")
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="SPS">SqlParameter</param>
        /// <param name="ColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。
        /// 前后分别为Column和Value，添加在insert语句的column和value部分</param>
        /// <param name="WPS">SqlParameter</param>
        /// <param name="ConditionsColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。
        /// 前后分别为Column和Value，添加在insert语句的column和value部分</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static int Update(string TableName, SqlParameter[] SPS, string[,] ColumnValues, WPrm[] WPS,
            string[,] ConditionsColumnValues, SqlConnection sqlconn) {
            string sql = "Update " + TableName + " Set ";
            for (int i = 0; i < SPS.Length; i++)
                sql += SPS[i].ParameterName.Replace("@", "")
                    + "=" + SPS[i].ParameterName + ",";
            for (int j = 0; j < ColumnValues.GetLength(0); j++)
                sql += ColumnValues[j, 0] + "=" + ColumnValues[j, 1] + ",";
            sql = sql.Substring(0, sql.Length - 1)
               + " Where 1=1 ";
            SqlParameter[] pr = new SqlParameter[WPS.Length];
            if (WPS[0].SqlParam.ParameterName.Length > 0) sql += SqlWhereBuild(WPS, ref pr);
            for (int j = 0; j < ConditionsColumnValues.GetLength(0); j++)
                sql += " and " + ConditionsColumnValues[j, 0] + "=" + ConditionsColumnValues[j, 1];

            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            cmd.Parameters.AddRange(SPS);
            if (WPS[0].SqlParam.ParameterName.Length > 0) cmd.Parameters.AddRange(pr);
            try {
                if (sqlconn.State != ConnectionState.Open)
                    sqlconn.Open();
                debugSQL(cmd.CommandText);
                return cmd.ExecuteNonQuery();
            } catch (Exception ex) {
                debugTxt(cmd.CommandText, ex);
                throw new Exception("Update:ExecuteNonQuery
" + ex.Message);
            }
        }

        /// <summary>
        /// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","")
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="SP">SqlParameter</param>
        /// <param name="ColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。
        /// 前后分别为Column和Value，添加在insert语句的column和value部分</param>
        /// <param name="WP">SqlParameter</param>
        /// <param name="ConditionsColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。
        /// 前后分别为Column和Value，添加在insert语句的column和value部分</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static int Update(string TableName, SqlParameter SP, string[,] ColumnValues, WPrm WP,
            string[,] ConditionsColumnValues, SqlConnection sqlconn) {
            return Update(TableName, new SqlParameter[] { SP }, ColumnValues,
                new WPrm[] { WP }, ConditionsColumnValues, sqlconn);
        }

        #endregion


        #region Delete

        /// <summary>
        /// 执行MSSQL删除表内数据操作
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <param name="WPS">SqlParameter</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static int Delete(string TableName, WPrm[] WPS, SqlConnection sqlconn) {
            string sql = "Delete From " + TableName + " Where 1=1 ";
            SqlParameter[] pr = new SqlParameter[WPS.Length];
            if (WPS[0].SqlParam.ParameterName.Length > 0) sql += SqlWhereBuild(WPS, ref pr);

            SqlCommand cmd = new SqlCommand(sql, sqlconn);
            if (WPS[0].SqlParam.ParameterName.Length > 0) cmd.Parameters.AddRange(pr);
            try {
                if (sqlconn.State != ConnectionState.Open)
                    sqlconn.Open();
                debugSQL(cmd.CommandText);
                return cmd.ExecuteNonQuery();
            } catch (Exception ex) {
                debugTxt(cmd.CommandText, ex);
                throw new Exception("Delete:ExecuteNonQuery
" + ex.Message);
            }
        }

        /// <summary>
        /// 执行MSSQL删除表内数据操作
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <param name="WPS">SqlParameter</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static int Delete(string TableName, WPrm WPS, SqlConnection sqlconn) {
            return Delete(TableName, new WPrm[] { WPS }, sqlconn);
        }

        #endregion


        #region Exec Proc

        /// <summary>
        /// 执行存储过程，无返回值
        /// </summary>
        /// <param name="procName">存储过程名称 </param>
        /// <param name="parameters">SqlParameters</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static void ExecProcNonReturn(string procName, SqlParameter[] parameters, SqlConnection sqlconn) {
            SqlCommand cmd = new SqlCommand(procName, sqlconn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(parameters);
            try {
                if (sqlconn.State != ConnectionState.Open)
                    sqlconn.Open();
                debugSQL(cmd.CommandText);
                cmd.ExecuteNonQuery();
            } catch (Exception ex) {
                debugTxt(cmd.CommandText, ex);
                throw new Exception("ExecProcNonReturn:ExecuteNonQuery
" + ex.Message);
            }
        }

        /// <summary>
        /// 执行存储过程，无返回值
        /// </summary>
        /// <param name="procName">存储过程名称 </param>
        /// <param name="parameters">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static void ExecProcNonReturn(string procName, SqlParameter parameters, SqlConnection sqlconn) {
            ExecProcNonReturn(procName, new SqlParameter[] { parameters }, sqlconn);
        }

        /// <summary>
        /// 执行存储过程，并直接返回执行的结果
        /// </summary>
        /// <param name="procName">存储过程名称 </param>
        /// <param name="parameters">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static object ExecProc(string procName, SqlParameter[] parameters, SqlConnection sqlconn) {
            SqlCommand cmd = new SqlCommand(procName, sqlconn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(parameters);
            try {
                if (sqlconn.State != ConnectionState.Open)
                    sqlconn.Open();
                debugSQL(cmd.CommandText);
                return cmd.ExecuteScalar();
            } catch (Exception ex) {
                debugTxt(cmd.CommandText, ex);
                throw new Exception("ExecProc:ExecuteScalar
" + ex.Message);
            }
        }

        /// <summary>
        /// 执行存储过程，并直接返回执行的结果
        /// </summary>
        /// <param name="procName">存储过程名称 </param>
        /// <param name="parameters">SqlParameter</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static object ExecProc(string procName, SqlParameter parameters, SqlConnection sqlconn) {
            return ExecProc(procName, new SqlParameter[] { parameters }, sqlconn);
        }

        /// <summary>
        /// 执行存储过程,带一个返回参数并返回此参数的执行结果
        /// <para>Example:</para>
        /// <para>SqlParameter[] sps = new SqlParameter[] {</para>
        /// <para>       new SqlParameter("@stageID", stgID), new SqlParameter("@sheepCode", sheepCode),</para>
        /// <para>   };</para>
        /// <para>SqlParameter spout = new SqlParameter() {</para>
        /// <para>   ParameterName = "@ret", Value = "", Direction = ParameterDirection.Output,</para>
        /// <para>   DbType = DbType.String, Size = 4000</para>
        /// <para>};</para>
        /// <para>object obj = SqlHelper.ExecProcWithOut("pGetPgkNO", sps, spout, CFG.sqlconn);</para>
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">SqlParameter</param>
        /// <param name="parameter_out">SqlParameter.Direction = ParameterDirection.Output;</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static object ExecProcWithOut(string procName, SqlParameter[] parameters,
            SqlParameter parameter_out, SqlConnection sqlconn) {
            SqlCommand cmd = new SqlCommand(procName, sqlconn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(parameters);
            cmd.Parameters.Add(parameter_out);
            try {
                if (sqlconn.State != ConnectionState.Open)
                    sqlconn.Open();
                cmd.ExecuteNonQuery();
                debugSQL(cmd.CommandText);
                return parameter_out.Value;
            } catch (Exception ex) {
                debugTxt(cmd.CommandText, ex);
                throw new Exception("ExecProc:ExecuteNonQuery
" + ex.Message);
            }
        }

        /// <summary>
        /// 执行存储过程,带一个返回参数并返回此参数的执行结果
        /// </summary>
        /// <param name="procName">存储过程名称 </param>
        /// <param name="parameters">SqlParameter</param>
        /// <param name="parameter_out">SqlParameter.Direction = ParameterDirection.Output;</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static object ExecProcWithOut(string procName, SqlParameter parameters,
            SqlParameter parameter_out, SqlConnection sqlconn) {
            return ExecProcWithOut(procName, new SqlParameter[] { parameters }, parameter_out, sqlconn);
        }

        /// <summary>
        /// 执行存储过程，无返回值
        /// </summary>
        /// <param name="procName">存储过程名称 </param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static void ExecProcNonReturn(string procName, SqlConnection sqlconn) {
            ExecProcNonReturn(procName, new SqlParameter[] { }, sqlconn);
        }

        /// <summary>
        /// 执行存储过程，并直接返回执行的结果
        /// </summary>
        /// <param name="procName">存储过程名称 </param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static object ExecProc(string procName, SqlConnection sqlconn) {
            return ExecProc(procName, new SqlParameter[] { }, sqlconn);
        }

        /// <summary>
        /// 执行存储过程,带一个返回参数并返回此参数的执行结果
        /// </summary>
        /// <param name="procName">存储过程名称 </param>
        /// <param name="parameter_out">SqlParameter.Direction = ParameterDirection.Output;</param>
        /// <param name="sqlconn">一个SQL连接</param>
        /// <returns>ExecuteNonQuery执行结果</returns>
        public static object ExecProcWithOut(string procName, SqlParameter parameter_out, SqlConnection sqlconn) {
            return ExecProcWithOut(procName, new SqlParameter[] { }, parameter_out, sqlconn);
        }

        #endregion


        #region Debug
        private static void debugSQL(string sql) {
            if (debug) {
                StreamWriter sw = new StreamWriter("C:\DebugSQL.txt", true);
                sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "	" + sql);
                sw.AutoFlush = true;
                sw.Close();
                sw.Dispose();
            }
        }

        private static void debugTxt(string sql, Exception ee) {
            if (debug) {
                StreamWriter sw = new StreamWriter("C:\DebugTxt.txt", true);
                sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "	" + sql + "
" + ee.ToString());
                sw.AutoFlush = true;
                sw.Close();
                sw.Dispose();
            }
        }

        #endregion


        #region Distinct
        /// <summary>
        /// 从DataRow集合中排除重复项，并返回一个DataTable
        /// </summary>
        /// <param name="drs">DataRow集合</param>
        /// <param name="ColumnName">筛选的条件列名</param>
        /// <param name="DataTableschemaClone">要将结果装入的空表的架构</param>
        /// <returns>DataTable</returns>
        public static DataTable DistInctTable(DataRow[] drs, string ColumnName, DataTable DataTableschemaClone) {
            try {
                DataTable dts = DataTableschemaClone.Clone();
                foreach (DataRow dr in drs)
                    dts.ImportRow(dr);
                for (int i = dts.Rows.Count - 1; i >= 0; i--)
                    if (dts.Select(ColumnName + "='" + dts.Rows[i][ColumnName].ToString() + "'").Length > 1)
                        dts.Rows.RemoveAt(i);
                dts.AcceptChanges();
                return dts;
            } catch (Exception ex) {
                throw new Exception("DistInctTable(From DataRow):
" + ex.Message);
            }
        }

        /// <summary>
        /// 从DataTable中排除重复行，并返回一个DataTable
        /// </summary>
        /// <param name="dt">源DataTable</param>
        /// <param name="ColumnName">筛选的条件列名</param>
        /// <returns>DataTable</returns>
        public static DataTable DistInctTable(DataTable dt, string ColumnName) {
            try {
                for (int i = dt.Rows.Count - 1; i >= 0; i--)
                    if (dt.Select(ColumnName + "='" + dt.Rows[i][ColumnName].ToString() + "'").Length > 1)
                        dt.Rows.RemoveAt(i);
                dt.AcceptChanges();
                return dt;
            } catch (Exception ex) {
                throw new Exception("DistInctTable(From DataTable):
" + ex.Message);
            }
        }

        #endregion

        private static string SqlWhereBuild(WPrm[] wps, ref SqlParameter[] sps) {
            if (wps[0].SqlParam.ParameterName.Length == 0) return "";
            string sql = "";
            for (int i = 0; i < wps.Length; i++) {
                WPrm wp = wps[i];
                sps[i] = wp.SqlParam;
                switch (wp.SqlOperator) {
                    case OP.Like:
                        sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + " like '%" + wp.SqlParam.ParameterName + "%'";
                        break;
                    case OP.Null:
                        sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + " is null ";
                        break;
                    case OP.NNul:
                        sql += " and not " + wp.SqlParam.ParameterName.Replace("@", "") + " is null ";
                        break;
                    case OP.In:
                        sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + " in (" + wp.SqlParam.ParameterName + ")";
                        break;
                    default:
                        sql += " and " + wp.SqlParam.ParameterName.Replace("@", "")
                            + WPrm.GetDesc(wp.SqlOperator) + wp.SqlParam.ParameterName;
                        break;
                        ////case OP.Equ:
                        ////    sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + "=" + wp.SqlParam.ParameterName;
                        ////    break;
                        ////case OP.UE:
                        ////    sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + "<>" + wp.SqlParam.ParameterName;
                        ////    break;
                        ////case OP.More:
                        ////    sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + ">" + wp.SqlParam.ParameterName;
                        ////    break;
                        ////case OP.MoE:
                        ////    sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + ">=" + wp.SqlParam.ParameterName;
                        ////    break;
                        ////case OP.Less:
                        ////    sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + "<" + wp.SqlParam.ParameterName;
                        ////    break;
                        ////case OP.LoE:
                        ////    sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + "<=" + wp.SqlParam.ParameterName;
                        ////    break;
                }
            }
            return sql;
        }
    }

    #region ConditionStringBuild
    public class WPrm {
        public SqlParameter SqlParam;
        public OP SqlOperator;
        public WPrm() {
            SqlParam = new SqlParameter();
            SqlOperator = OP.Equ;
        }
        /// <summary>
        /// WhereParam构造
        /// </summary>
        /// <param name="SqlParamName">必须以@符号开始，否则将自动在开头加@</param>
        /// <param name="SqlParamValue"></param>
        /// <param name="opt"></param>
        public WPrm(string SqlParamName, object SqlParamValue, OP opt = OP.Equ) {
            if (!SqlParamName.StartsWith("@"))
                SqlParamName = "@" + SqlParamName;
            SqlParam = new SqlParameter(SqlParamName, SqlParamValue);
            SqlOperator = opt;
        }

        /// <summary>
        /// 获取enum对象的Description属性
        /// </summary>
        /// <param name="pEnum">pEnum对象</param>
        /// <returns></returns>
        public static string GetDesc(Enum pEnum) {
            FieldInfo fi = pEnum.GetType().GetField(pEnum.ToString());
            DescriptionAttribute[] arrDesc = (DescriptionAttribute[])fi.GetCustomAttributes(typeof(DescriptionAttribute), false);
            return arrDesc[0].Description;
        }
    }
    /// <summary>
    /// SQL条件运算符
    /// </summary>
    public enum OP {
        /// <summary>
        /// 等于
        /// </summary>
        [Description("=")] Equ = 0,
        /// <summary>
        /// 大于
        /// </summary>
        [Description(">")] More = 1,
        /// <summary>
        /// 大于等于
        /// </summary>
        [Description(">=")] MoE = 2,
        /// <summary>
        /// 小于
        /// </summary>
        [Description("<")] Less = 3,
        /// <summary>
        /// 小于等于
        /// </summary>
        [Description("<=")] LoE = 4,
        /// <summary>
        /// 不等于
        /// </summary>
        [Description("<>")] UE = 5,
        /// <summary>
        /// like
        /// </summary>
        [Description("like")] Like = 6,
        /// <summary>
        /// is null
        /// </summary>
        [Description("is null")] Null = 7,
        /// <summary>
        /// not is null
        /// </summary>
        [Description("not is null")] NNul = 8,
        /// <summary>
        /// in
        /// </summary>
        [Description("in")] In = 9
    };
    #endregion
}