JLH/JLHHJSvr/DBA/DAL_SQL/SqlHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace LJLib.DAL.SQL
{
    internal static class SqlHelper
    {
        public static DateTime GetServerTime(SqlCommand cmd)
        {
            cmd.CommandText = "SELECT GETDATE()";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            return Convert.ToDateTime(cmd.ExecuteScalar());
        }

        public static bool DataBaseExists(string ConnectionString, string dbname)
        {
            SqlConnectionStringBuilder builer = new SqlConnectionStringBuilder(ConnectionString);
            builer.InitialCatalog = "master";
            using (SqlConnection con = new SqlConnection(builer.ConnectionString))
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT COUNT(0) from sysdatabases WHERE name = @name";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = dbname;
                int cnt = Convert.ToInt32(cmd.ExecuteScalar());
                return cnt > 0;
            }
        }

        public static void CreateDataBase(string ConnectionString, string dbname)
        {
            SqlConnectionStringBuilder builer = new SqlConnectionStringBuilder(ConnectionString);
            builer.InitialCatalog = "master";
            using (SqlConnection con = new SqlConnection(builer.ConnectionString))
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "CREATE DATABASE " + dbname;
                cmd.ExecuteNonQuery();
            }
        }

        public static int SelectCount(SqlCommand cmd, string table, string wherestr, IEnumerable<SqlParameter> sqlparams = null)
        {
            string strcmd = "SELECT COUNT(0) FROM " + table;
            if (!string.IsNullOrEmpty(wherestr))
            {
                wherestr = wherestr.Trim();
                strcmd += " WHERE " + wherestr;
            }
            cmd.CommandText = strcmd;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            if (sqlparams != null)
            {
                foreach (SqlParameter sqlparam in sqlparams)
                {
                    if (sqlparam.Value == null)
                    {
                        sqlparam.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(sqlparam);
                }
            }
            return Convert.ToInt32(cmd.ExecuteScalar());
        }

        public static int Select<T>(SqlCommand cmd, IList<T> modles, string wherestr, string fields = "*") where T : ModleBase, new()
        {
            return Select(cmd, modles, wherestr, null, null, fields, 0, 0);
        }

        public static int Select<T>(SqlCommand cmd, IList<T> modles, string wherestr, IEnumerable<SqlParameter> sqlparams, string fields = "*") where T : ModleBase, new()
        {
            return Select(cmd, modles, wherestr, sqlparams, null, fields, 0, 0);
        }

        public static int Select<T>(SqlCommand cmd, IList<T> modles, string whereStr, IEnumerable<SqlParameter> sqlparams, string orderByStr, string outputFields, int pageindex, int pagesize) where T : ModleBase, new()
        {
            if (modles.IsReadOnly)
            {
                throw new ArgumentException("参数modles不能为只读");
            }

            ModleInfo info = GetModleInfo(typeof(T));
            string strCmd = BuildSelectCmd(info);

            PrepareSelectCmd(cmd, strCmd, whereStr, sqlparams, ref outputFields, orderByStr, pageindex, pagesize);
            IList<FieldInfo> fields = GetFields(info, outputFields);

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    T modle = new T();
                    foreach (FieldInfo field in fields)
                    {
                        object value = null;
                        try
                        {
                            value = reader[field.FieldName];
                        }
                        catch (Exception)
                        {
                            continue;
                        }
                        if (value == null || value == DBNull.Value)
                        {
                            continue;
                        }
                        if (value is string)
                        {
                            value = value.ToString().Trim();
                        }
                        value = ConvertValue(field.SetMethod.GetParameters()[0].ParameterType, value);
                        field.SetMethod.Invoke(modle, new object[] { value });
                    }
                    modles.Add(modle);
                }

                return modles.Count;
            }
        }

        private static IList<FieldInfo> GetFields(ModleInfo info, string outputFields)
        {
            List<FieldInfo> rslt = new List<FieldInfo>();
            string[] fields = outputFields.Split(',');
            foreach (string field in fields)
            {
                string key = field.Trim().ToLower();
                if (info.Fields.ContainsKey(key))
                {
                    rslt.Add(info.Fields[key]);
                }
            }
            return rslt;
        }

        private static string BuildSelectCmd(ModleInfo info)
        {
            string fields = string.Empty;
            foreach (FieldInfo field in info.Fields.Values)
            {
                if (string.IsNullOrEmpty(fields))
                {
                    fields = field.FieldName;
                }
                else
                {
                    fields += "," + field.FieldName;
                }
            }
            return "SELECT " + fields + " FROM " + info.TableName;
        }

        public static int Select<T>(SqlCommand cmd, IList<T> modles, string wherestr, IEnumerable<SqlParameter> sqlparams, int pageindex, int pagesize, string orderstr, string fields = "*") where T : ModleBase, new()
        {
            return Select(cmd, modles, wherestr, sqlparams, orderstr, fields, pageindex, pagesize);
        }

        public static int SelectOne<T>(SqlCommand cmd, T modle, string fields = "*") where T : ModleBase
        {
            ModleInfo info = GetModleInfo(typeof(T));

            Dictionary<string, FieldInfo> allFields = new Dictionary<string, FieldInfo>();
            Dictionary<string, FieldInfo> dictNonPrimaryKeys = new Dictionary<string, FieldInfo>();
            info.GetFields(fields, allFields, dictNonPrimaryKeys);

            List<FieldInfo> nonPrimaryKeys = new List<FieldInfo>(dictNonPrimaryKeys.Values);

            SelectCommandText(cmd, info.TableName, info.PrimaryKeys, nonPrimaryKeys);
            AddParameters(cmd, modle, info.PrimaryKeys);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                {
                    return 0;
                }
                foreach (FieldInfo field in nonPrimaryKeys)
                {
                    object value = reader[field.FieldName];
                    if (value == DBNull.Value)
                    {
                        value = null;
                    }
                    if (value != null && value is string)
                    {
                        value = value.ToString().Trim();
                    }
                    if (value != null)
                    {
                        value = ConvertValue(field.SetMethod.GetParameters()[0].ParameterType, value);
                    }
                    field.SetMethod.Invoke(modle, new object[] { value });
                }
                return 1;
            }
        }

        public static int Delete<T>(SqlCommand cmd, T modle) where T : ModleBase
        {
            ModleInfo info = GetModleInfo(typeof(T));
            DeleteCommandText(cmd, info.TableName, info.PrimaryKeys);
            AddParameters(cmd, modle, info.PrimaryKeys);
            return cmd.ExecuteNonQuery();
        }

        private static void DeleteCommandText(SqlCommand cmd, string tablename, List<FieldInfo> list)
        {
            string strcmd = "DELETE FROM " + tablename + " WHERE " + list[0].FieldName + "=@" + list[0].FieldName;
            for (int i = 1; i < list.Count; i++)
            {
                strcmd += " AND " + list[i].FieldName + "=@" + list[i].FieldName;
            }
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strcmd;
        }

        public static void BulkInsert<T>(SqlCommand cmd, IEnumerable<T> modles, string fields = "*")
        {
            ModleInfo info = GetModleInfo(typeof(T));

            Dictionary<string, FieldInfo> allFields = new Dictionary<string, FieldInfo>();
            Dictionary<string, FieldInfo> dictNonPrimaryKeys = new Dictionary<string, FieldInfo>();
            info.GetFields(fields, allFields, dictNonPrimaryKeys);
            using (DataTable dt = new DataTable())
            {
                int cnt = 0;
                foreach (var field in info.PrimaryKeys)
                {
                    var col = new DataColumn(field.FieldName);
                    col.AllowDBNull = true;
                    var rtType = field.GetMethod.ReturnType;
                    if (rtType.IsGenericType)
                    {
                        Type GT = rtType.GetGenericTypeDefinition();
                        Type BT = rtType.GetGenericArguments()[0];
                        if (GT == typeof(Nullable<>))
                        {
                            rtType = BT;
                        }
                    }
                    col.DataType = rtType;
                    dt.Columns.Add(col);
                    cnt++;
                }
                foreach (var kvp in dictNonPrimaryKeys)
                {
                    var col = new DataColumn(kvp.Value.FieldName);
                    col.AllowDBNull = true;
                    var rtType = kvp.Value.GetMethod.ReturnType;
                    if (rtType.IsGenericType)
                    {
                        Type GT = rtType.GetGenericTypeDefinition();
                        Type BT = rtType.GetGenericArguments()[0];
                        if (GT == typeof(Nullable<>))
                        {
                            rtType = BT;
                        }
                    }
                    col.DataType = rtType;
                    dt.Columns.Add(col);
                    cnt++;
                }

                foreach (var model in modles)
                {
                    var values = new object[cnt];
                    int i = 0;
                    foreach (var field in info.PrimaryKeys)
                    {
                        values[i] = field.GetMethod.Invoke(model, null);
                        if (values[i] == null)
                        {
                            values[i] = DBNull.Value;
                        }
                        i++;
                    }
                    foreach (var kvp in dictNonPrimaryKeys)
                    {
                        values[i] = kvp.Value.GetMethod.Invoke(model, null);
                        if (values[i] == null)
                        {
                            values[i] = DBNull.Value;
                        }
                        i++;
                    }
                    dt.Rows.Add(values);
                }

                using (SqlBulkCopy sqlBC = new SqlBulkCopy(cmd.Connection))
                {
                    //一次批量的插入的数据量
                    sqlBC.BatchSize = 1000;
                    //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
                    sqlBC.BulkCopyTimeout = 60;

                    ////設定 NotifyAfter 属性，以便在每插入10000 条数据时，呼叫相应事件。 
                    //sqlBC.NotifyAfter = 10000;
                    //sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

                    //设置要批量写入的表
                    sqlBC.DestinationTableName = info.TableName;
                    //自定义的datatable和数据库的字段进行对应
                    foreach (var field in info.PrimaryKeys)
                    {
                        sqlBC.ColumnMappings.Add(field.FieldName, field.FieldName);
                    }
                    foreach (var kvp in dictNonPrimaryKeys)
                    {
                        sqlBC.ColumnMappings.Add(kvp.Value.FieldName, kvp.Value.FieldName);
                    }
                    //批量写入
                    sqlBC.WriteToServer(dt);
                }
            }
        }

        public static int InsertOrUpdate<T>(SqlCommand cmd, T modle, string fields = "*") where T : ModleBase
        {
            ModleInfo info = GetModleInfo(typeof(T));

            Dictionary<string, FieldInfo> allFields = new Dictionary<string, FieldInfo>();
            Dictionary<string, FieldInfo> dictNonPrimaryKeys = new Dictionary<string, FieldInfo>();
            info.GetFields(fields, allFields, dictNonPrimaryKeys);

            List<FieldInfo> nonPrimaryKeys = new List<FieldInfo>(dictNonPrimaryKeys.Values);

            try
            {
                if (nonPrimaryKeys.Count > 0 && info.PrimaryKeys.Count > 0)
                {
                    bool withnonkey = false;
                    if (info.PrimaryKeys.Count == 1)
                    {
                        withnonkey = info.PrimaryKeys[0].GetMethod.Invoke(modle, null) == null;
                    }

                    if (withnonkey)
                    {
                        InsertCommandText(cmd, info.TableName, nonPrimaryKeys);
                        AddParameters(cmd, modle, nonPrimaryKeys);
                        cmd.CommandText += ";select @@IDENTITY";
                        object id = cmd.ExecuteScalar();
                        if (info.PrimaryKeys[0].DbType == SqlDbType.BigInt)
                        {
                            info.PrimaryKeys[0].SetMethod.Invoke(modle, new object[] { Convert.ToInt64(id) });
                        }
                        else if (info.PrimaryKeys[0].DbType == SqlDbType.Int)
                        {
                            info.PrimaryKeys[0].SetMethod.Invoke(modle, new object[] { Convert.ToInt32(id) });
                        }
                        else
                        {
                            info.PrimaryKeys[0].SetMethod.Invoke(modle, new object[] { id });
                        }
                        return 1;
                    }
                    else
                    {
                        UpdateCommandText(cmd, info.TableName, info.PrimaryKeys, nonPrimaryKeys);
                        AddParameters(cmd, modle, info.PrimaryKeys, nonPrimaryKeys);
                        int cnt = cmd.ExecuteNonQuery();
                        if (cnt == 0)
                        {
                            InsertCommandText(cmd, info.TableName, info.PrimaryKeys, nonPrimaryKeys);
                            AddParameters(cmd, modle, info.PrimaryKeys, nonPrimaryKeys);
                            return cmd.ExecuteNonQuery();
                        }
                        else
                        {
                            return cnt;
                        }
                    }
                }
                else if (nonPrimaryKeys.Count > 0)
                {
                    InsertCommandText(cmd, info.TableName, nonPrimaryKeys);
                    AddParameters(cmd, modle, nonPrimaryKeys);
                    return cmd.ExecuteNonQuery();
                }
                else if (info.PrimaryKeys.Count > 0)
                {
                    InsertCommandText(cmd, info.TableName, info.PrimaryKeys);
                    AddParameters(cmd, modle, info.PrimaryKeys);
                    return cmd.ExecuteNonQuery();
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception)
            {
                Trace.Write(ModleToString(modle, info.PrimaryKeys, nonPrimaryKeys));
                throw;
            }
        }

        private static object ModleToString(object modle, List<FieldInfo> list, List<FieldInfo> nonPrimaryKeys)
        {
            StringBuilder rslt = new StringBuilder();
            rslt.Append(modle.GetType().ToString() + ":{");
            bool hasone = false;
            int i;
            if (list.Count > 0)
            {
                hasone = true;
                rslt.Append(string.Format("{0}:\"{1}\"", list[0].FieldName,
                    list[0].GetMethod.Invoke(modle, null) ?? string.Empty));
                for (i = 1; i < list.Count; i++)
                {
                    rslt.Append(string.Format(",{0}:\"{1}\"", list[i].FieldName,
    list[i].GetMethod.Invoke(modle, null) ?? string.Empty));
                }
            }
            i = 0;
            if (!hasone && nonPrimaryKeys.Count > 0)
            {
                rslt.Append(string.Format("{0}:\"{1}\"", nonPrimaryKeys[0].FieldName,
                    nonPrimaryKeys[0].GetMethod.Invoke(modle, null) ?? string.Empty));
                i = 1;
            }
            for (; i < nonPrimaryKeys.Count; i++)
            {
                rslt.Append(string.Format(",{0}:\"{1}\"", nonPrimaryKeys[i].FieldName,
                    nonPrimaryKeys[i].GetMethod.Invoke(modle, null) ?? string.Empty));
            }

            return rslt.ToString();
        }

        private static void InsertCommandText(SqlCommand cmd, string tablename, params List<FieldInfo>[] list)
        {
            string strfields = string.Empty;
            string strvalues = string.Empty;
            bool first = true;
            foreach (List<FieldInfo> fields in list)
            {
                foreach (FieldInfo field in fields)
                {
                    if (first)
                    {
                        strfields = field.FieldName;
                        strvalues = "@" + field.FieldName;
                        first = false;
                    }
                    else
                    {
                        strfields += "," + field.FieldName;
                        strvalues += ",@" + field.FieldName;
                    }
                }
            }
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO " + tablename + "(" + strfields + ") VALUES(" + strvalues + ")";
        }

        private static void UpdateCommandText(SqlCommand cmd, string tablename, List<FieldInfo> primaryKeys, List<FieldInfo> nonPrimaryKeys)
        {
            string strcmd = "UPDATE " + tablename + " SET " + nonPrimaryKeys[0].FieldName + "=@" + nonPrimaryKeys[0].FieldName;
            for (int i = 1; i < nonPrimaryKeys.Count; i++)
            {
                strcmd += "," + nonPrimaryKeys[i].FieldName + "=@" + nonPrimaryKeys[i].FieldName;
            }
            strcmd += " WHERE " + primaryKeys[0].FieldName + "=@" + primaryKeys[0].FieldName;
            for (int i = 1; i < primaryKeys.Count; i++)
            {
                strcmd += " AND " + primaryKeys[i].FieldName + "=@" + primaryKeys[i].FieldName;
            }
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strcmd;
        }

        private static void SelectCommandText(SqlCommand cmd, string tablename, List<FieldInfo> primaryKeys, List<FieldInfo> nonPrimaryKeys)
        {
            string strcmd = "SELECT " + nonPrimaryKeys[0].FieldName;
            for (int i = 1; i < nonPrimaryKeys.Count; i++)
            {
                strcmd += "," + nonPrimaryKeys[i].FieldName;
            }
            strcmd += " FROM " + tablename;
            strcmd += " WHERE " + primaryKeys[0].FieldName + "=@" + primaryKeys[0].FieldName;
            for (int i = 1; i < primaryKeys.Count; i++)
            {
                strcmd += " AND " + primaryKeys[i].FieldName + "=@" + primaryKeys[i].FieldName;
            }
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strcmd;
        }

        private static void SelectCommandText(SqlCommand cmd, string tablename, IEnumerable<FieldInfo> fields, string wherestr)
        {
            bool first = true;
            string strcmd = "SELECT ";
            foreach (FieldInfo field in fields)
            {
                if (first)
                {
                    first = false;
                    strcmd += field.FieldName;
                }
                else
                {
                    strcmd += "," + field.FieldName;
                }
            }
            strcmd += " FROM " + tablename;
            wherestr = wherestr.Trim();
            if (!string.IsNullOrEmpty(wherestr))
            {
                strcmd += " WHERE " + wherestr;
            }
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strcmd;
        }

        private static void AddParameters(SqlCommand cmd, object modle, params List<FieldInfo>[] list)
        {
            cmd.Parameters.Clear();
            foreach (List<FieldInfo> fields in list)
            {
                foreach (FieldInfo field in fields)
                {
                    object value = field.GetMethod.Invoke(modle, null);
                    cmd.Parameters.Add("@" + field.FieldName, field.DbType).Value = value == null ? DBNull.Value : value;
                }
            }
        }

        private static Dictionary<string, ModleInfo> modleinfos = new Dictionary<string, ModleInfo>();
        private static object modleinfos_syncRoot = new object();
        private static ModleInfo GetModleInfo(Type type)
        {
            lock (modleinfos_syncRoot)
            {
                string typename = type.FullName;
                if (modleinfos.ContainsKey(typename))
                {
                    return modleinfos[typename];
                }
                Table table = type.GetCustomAttributes(typeof(Table), false)[0] as Table;
                ModleInfo rslt = new ModleInfo();
                rslt.TableName = table.Name;
                PropertyInfo[] properties = type.GetProperties();
                Dictionary<string, FieldInfo> fields = new Dictionary<string, FieldInfo>();
                List<FieldInfo> primaryKeys = new List<FieldInfo>();
                foreach (PropertyInfo property in properties)
                {
                    object[] attrs = property.GetCustomAttributes(typeof(Field), false);
                    if (attrs.Length <= 0)
                    {
                        continue;
                    }
                    Field field = attrs[0] as Field;
                    FieldInfo fieldinfo = new FieldInfo();
                    fieldinfo.FieldName = field.Name;
                    fieldinfo.DbType = field.DbType;
                    fieldinfo.IsPrimaryKey = field.IsPrimaryKey;
                    fieldinfo.GetMethod = property.GetGetMethod();
                    fieldinfo.SetMethod = property.GetSetMethod();
                    fields.Add(property.Name.ToLower(), fieldinfo);
                    if (fieldinfo.IsPrimaryKey)
                    {
                        primaryKeys.Add(fieldinfo);
                    }
                }
                rslt.Fields = fields;
                rslt.PrimaryKeys = primaryKeys;
                modleinfos.Add(typename, rslt);
                return rslt;
            }
        }

        private class ModleInfo
        {
            public string TableName { get; set; }
            public Dictionary<string, FieldInfo> Fields { get; set; }
            public List<FieldInfo> PrimaryKeys { get; set; }

            public string GetFields(string strfields)
            {
                Dictionary<string, FieldInfo> AllFields = new Dictionary<string, FieldInfo>();
                if (strfields == "*")
                {
                    foreach (KeyValuePair<string, FieldInfo> field in Fields)
                    {
                        AllFields.Add(field.Key, field.Value);
                    }
                }
                else
                {
                    string[] fields = strfields.Split(',');
                    foreach (string str in fields)
                    {
                        string field = str.Trim();
                        if (string.IsNullOrEmpty(field))
                        {
                            continue;
                        }
                        if (!Fields.ContainsKey(field))
                        {
                            continue;
                        }
                        if (AllFields.ContainsKey(field))
                        {
                            continue;
                        }
                        FieldInfo fieldinfo = Fields[field];
                        AllFields[field] = fieldinfo;
                    }
                }
                string rslt = string.Empty;
                foreach (KeyValuePair<string, FieldInfo> kvp in AllFields)
                {
                    if (string.IsNullOrEmpty(rslt))
                    {
                        rslt = kvp.Value.FieldName;
                    }
                    else
                    {
                        rslt += "," + kvp.Value.FieldName;
                    }
                }
                return rslt;
            }

            public void GetFields(string strfields, Dictionary<string, FieldInfo> AllFields, Dictionary<string, FieldInfo> nonPrimaryKeys)
            {
                if (string.IsNullOrEmpty(strfields) || strfields == "*")
                {
                    foreach (KeyValuePair<string, FieldInfo> field in Fields)
                    {
                        AllFields.Add(field.Key, field.Value);
                        if (!field.Value.IsPrimaryKey)
                        {
                            nonPrimaryKeys.Add(field.Key, field.Value);
                        }
                    }
                }
                else
                {
                    string[] fields = strfields.Split(',');
                    foreach (string str in fields)
                    {
                        string field = str.Trim().ToLower();
                        if (string.IsNullOrEmpty(field))
                        {
                            continue;
                        }
                        if (!Fields.ContainsKey(field))
                        {
                            continue;
                        }
                        if (AllFields.ContainsKey(field))
                        {
                            continue;
                        }
                        FieldInfo fieldinfo = Fields[field];
                        AllFields[field] = fieldinfo;
                        if (fieldinfo.IsPrimaryKey)
                        {
                            continue;
                        }
                        nonPrimaryKeys.Add(field, fieldinfo);
                    }
                }
            }
        }

        private class FieldInfo
        {
            public string FieldName { get; set; }
            public SqlDbType DbType { get; set; }
            public bool IsPrimaryKey { get; set; }
            public MethodInfo GetMethod { get; set; }
            public MethodInfo SetMethod { get; set; }
        }

        private static void MakeSelectCmd(SqlCommand cmd, string orgCmdStr, ref string preOutFieldStr, string orgWhereStr, string preOrderStr, int pageindex, int pagesize, IEnumerable<SqlParameter> sqlparams)
        {
            Regex regex = new Regex(@"\bselect\s+(.+?)\s+from\s+(.*)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            Match match = regex.Match(orgCmdStr);
            string outCmdStr = string.Empty;
            if (match.Success)
            {
                string orgFieldStr = match.Groups[1].Value.Trim();
                string orgFromStr = match.Groups[2].Value.Trim();

                Dictionary<string, string> orgFields = new Dictionary<string, string>();
                Dictionary<string, string> orgOrderFields = new Dictionary<string, string>();
                AnalyseFields(orgFieldStr, orgFields, orgOrderFields);

                string outFieldStr = BuildSelectFields(orgFields, preOutFieldStr);
                string outOrderStr = BuildOrderFields(orgOrderFields, preOrderStr);

                outCmdStr = outFieldStr + " FROM " + orgFromStr;
                if (!string.IsNullOrEmpty(orgWhereStr))
                {
                    outCmdStr += " WHERE " + orgWhereStr;
                }
                if (!string.IsNullOrEmpty(outOrderStr))
                {
                    outCmdStr += " ORDER BY " + outOrderStr;
                }
                preOutFieldStr = BuildSelectFieldNames(orgFields, preOutFieldStr);
                if (pageindex > 0 && pagesize > 0)
                {
                    outCmdStr = "SELECT TOP " + (pageindex * pagesize).ToString() + " " + outCmdStr;
                    string outFieldNameStr = preOutFieldStr;
                    outCmdStr = "SELECT RowNumber = IDENTITY(INT,1, 1)," + outFieldNameStr + " INTO #tmp_sorttable FROM (" + outCmdStr + @") a
SELECT TOP " + pagesize.ToString() + " " + outFieldNameStr + @" FROM #tmp_sorttable WHERE RowNumber > " + (pagesize * (pageindex - 1)).ToString() + @" ORDER BY RowNumber
DROP TABLE #tmp_sorttable";

                }
                else if (pageindex <= 0 && pagesize > 0)
                {
                    outCmdStr = "SELECT TOP " + pagesize + " " + outCmdStr;
                }
                else
                {
                    outCmdStr = "SELECT " + outCmdStr;

                }
                cmd.CommandText = outCmdStr;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                if (sqlparams != null)
                {
                    foreach (SqlParameter parm in sqlparams)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
            }
            else
            {
                throw new Exception("MakeSelectCmd分析出现问题");
            }
        }

        private static string BuildSelectFieldNames(Dictionary<string, string> orgFields, string preOutFieldStr)
        {
            string outFieldStr = string.Empty;
            Dictionary<string, string> outFields = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(preOutFieldStr))
            {
                string[] fields = preOutFieldStr.Split(',');
                foreach (string field in fields)
                {
                    string key = field.Trim().ToLower();
                    if (orgFields.ContainsKey(key) && !outFields.ContainsKey(key))
                    {
                        outFields[key] = orgFields[key];
                    }
                }
            }

            if (outFields.Count == 0)
            {
                outFields = orgFields;
            }
            foreach (KeyValuePair<string, string> kvp in outFields)
            {
                if (string.IsNullOrEmpty(outFieldStr))
                {
                    outFieldStr = kvp.Key;
                }
                else
                {
                    outFieldStr += "," + kvp.Key;
                }
            }
            return outFieldStr;
        }

        /// <summary>
        /// 生成Order语句
        /// </summary>
        /// <param name="orgOrderFields"></param>
        /// <param name="preOrderStr"></param>
        /// <returns></returns>
        private static string BuildOrderFields(Dictionary<string, string> orgOrderFields, string preOrderStr)
        {
            string outOrderStr = string.Empty;
            Dictionary<string, string> outOrderFields = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(preOrderStr))
            {
                string[] fields = preOrderStr.Split(',');
                foreach (string field in fields)
                {
                    string key = field.Trim().ToLower();
                    string[] arr = key.Split(' ');
                    if (arr.Length > 1)
                    {
                        key = arr[0];
                        string sort = arr[arr.Length - 1];
                        if (orgOrderFields.ContainsKey(key) && !outOrderFields.ContainsKey(key) && (sort == "asc" || sort == "desc"))
                        {
                            outOrderFields[key] = orgOrderFields[key] + " " + sort;
                        }
                    }
                    else
                    {
                        if (orgOrderFields.ContainsKey(key) && !outOrderFields.ContainsKey(key))
                        {
                            outOrderFields[key] = orgOrderFields[key];
                        }
                    }
                }
            }
            foreach (KeyValuePair<string, string> kvp in outOrderFields)
            {
                if (string.IsNullOrEmpty(outOrderStr))
                {
                    outOrderStr = kvp.Value;
                }
                else
                {
                    outOrderStr += "," + kvp.Value;
                }
            }
            return outOrderStr;
        }

        /// <summary>
        /// 生成SELECT语句
        /// </summary>
        /// <param name="orgFields"></param>
        /// <param name="preOutFieldStr"></param>
        /// <returns></returns>
        private static string BuildSelectFields(Dictionary<string, string> orgFields, string preOutFieldStr)
        {
            string outFieldStr = string.Empty;
            Dictionary<string, string> outFields = new Dictionary<string, string>();
            if (!string.IsNullOrEmpty(preOutFieldStr))
            {
                string[] fields = preOutFieldStr.Split(',');
                foreach (string field in fields)
                {
                    string key = field.Trim().ToLower();
                    if (orgFields.ContainsKey(key) && !outFields.ContainsKey(key))
                    {
                        outFields[key] = orgFields[key];
                    }
                }
            }

            if (outFields.Count == 0)
            {
                outFields = orgFields;
            }
            foreach (KeyValuePair<string, string> kvp in outFields)
            {
                if (string.IsNullOrEmpty(outFieldStr))
                {
                    outFieldStr = kvp.Value;
                }
                else
                {
                    outFieldStr += "," + kvp.Value;
                }
            }
            return outFieldStr;
        }

        private static void AnalyseFields(string orgFieldStr, Dictionary<string, string> orgFields, Dictionary<string, string> orgOrderFields)
        {
            string[] selectFieldArr = SplitFields(orgFieldStr);
            string[] pattens = new string[] { @"^\s*(?<sort>(?<key>\w+))\s*=.+$", @"^.+\s+as\s+(?<sort>(?<key>\w+))\s*$", @"^.+\s+(?<sort>(?<key>\w+))\s*$", @"^(?<sort>.*\.\s*(?<key>\w+)\s*)$", @"^\s*(?<sort>(?<key>\w+))\s*$" };

            foreach (string orgField in selectFieldArr)
            {
                foreach (string patten in pattens)
                {
                    Match fiMatch = Regex.Match(orgField, patten, RegexOptions.IgnoreCase | RegexOptions.Singleline);
                    if (fiMatch.Success)
                    {
                        string key = fiMatch.Groups["key"].Value.Trim().ToLower();
                        if (!orgFields.ContainsKey(key))
                        {
                            orgFields[key] = fiMatch.Value.Trim();
                            orgOrderFields[key] = fiMatch.Groups["sort"].Value.Trim();
                        }
                        break;
                    }
                }
            }
        }

        private static string[] SplitFields(string orgFieldStr)
        {
            List<string> rslt = new List<string>();

            string builder = string.Empty;
            Stack<char> states = new Stack<char>();
            for (int i = 0; i < orgFieldStr.Length; i++)
            {
                char cur = orgFieldStr[i];
                if (states.Count == 0)
                {
                    if (cur == ',')
                    {
                        rslt.Add(builder);
                        builder = string.Empty;
                    }
                    else
                    {
                        builder += cur;
                        if (cur == '(' || cur == '\'')
                        {
                            states.Push(cur);
                        }
                    }
                }
                else
                {
                    builder += cur;
                    char curstate = states.Peek();
                    if (curstate == '\'')
                    {
                        if (cur == '\'')
                        {
                            states.Pop();
                        }
                    }
                    else
                    {
                        if (cur == '(' || cur == '\'')
                        {
                            states.Push(cur);
                        }
                        else if (cur == ')')
                        {
                            states.Pop();
                        }
                    }
                }
            }
            rslt.Add(builder);

            return rslt.ToArray();
        }

        private static void PrepareSelectCmd(SqlCommand cmd, string strcmd, string wherestr, IEnumerable<SqlParameter> parms, ref string fields, string orderfields, int pageindex, int pagesize)
        {
            MakeSelectCmd(cmd, strcmd, ref fields, wherestr, orderfields, pageindex, pagesize, parms);
        }

        public static void SelectJoin<T>(SqlCommand cmd, string selectStr, string whereStr, IEnumerable<SqlParameter> parms, string orderByStr, string outputFields, int pageindex, int pagesize, List<T> returnList) where T : new()
        {
            PrepareSelectCmd(cmd, selectStr, whereStr, parms, ref outputFields, orderByStr, pageindex, pagesize);
            IList<PropertyInfo> properties = GetProperties(typeof(T), outputFields);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    T modle = new T();

                    foreach (PropertyInfo property in properties)
                    {
                        MethodInfo setMethod = property.GetSetMethod();
                        if (setMethod == null)
                        {
                            continue;
                        }
                        object value = null;
                        try
                        {
                            value = reader[property.Name];
                        }
                        catch (Exception)
                        {
                            continue;
                        }
                        if (value == null || value == DBNull.Value)
                        {
                            continue;
                        }
                        if (value is string)
                        {
                            value = value.ToString().Trim();
                        }
                        var parmType = setMethod.GetParameters()[0].ParameterType;
                        value = ConvertValue(parmType, value);
                        setMethod.Invoke(modle, new object[] { value });
                    }

                    returnList.Add(modle);
                }
            }
        }

        private static object ConvertValue(Type parmType, object value)
        {
            if (!parmType.IsAssignableFrom(value.GetType()))
            {
                if (parmType.IsAssignableFrom(typeof(byte)))
                {
                    value = Convert.ToByte(value);
                }
                else if (parmType.IsAssignableFrom(typeof(sbyte)))
                {
                    value = Convert.ToSByte(value);
                }
                else if (parmType.IsAssignableFrom(typeof(short)))
                {
                    value = Convert.ToInt16(value);
                }
                else if (parmType.IsAssignableFrom(typeof(ushort)))
                {
                    value = Convert.ToUInt16(value);
                }
                else if (parmType.IsAssignableFrom(typeof(int)))
                {
                    value = Convert.ToInt32(value);
                }
                else if (parmType.IsAssignableFrom(typeof(uint)))
                {
                    value = Convert.ToUInt32(value);
                }
                else if (parmType.IsAssignableFrom(typeof(long)))
                {
                    value = Convert.ToInt64(value);
                }
                else if (parmType.IsAssignableFrom(typeof(ulong)))
                {
                    value = Convert.ToUInt64(value);
                }
                else if (parmType.IsAssignableFrom(typeof(float)))
                {
                    value = Convert.ToSingle(value);
                }
                else if (parmType.IsAssignableFrom(typeof(double)))
                {
                    value = Convert.ToDouble(value);
                }
                else if (parmType.IsAssignableFrom(typeof(decimal)))
                {
                    value = Convert.ToDecimal(value);
                }
                else if (parmType.IsAssignableFrom(typeof(bool)))
                {
                    value = Convert.ToBoolean(value);
                }
                else if (parmType.IsAssignableFrom(typeof(bool)))
                {
                    value = Convert.ToBoolean(value);
                }
                else if (parmType.IsAssignableFrom(typeof(char)))
                {
                    value = Convert.ToChar(value);
                }
                else if (parmType.IsAssignableFrom(typeof(DateTime)))
                {
                    value = Convert.ToDateTime(value);
                }
                else if (parmType.IsAssignableFrom(typeof(string)))
                {
                    value = Convert.ToString(value);
                }
                else
                {
                    throw new Exception(string.Format("不能将{0}转换成{1}", value.GetType(), parmType));
                }
            }
            return value;
        }

        private static IList<PropertyInfo> GetProperties(Type type, string outputFields)
        {
            List<PropertyInfo> rslt = new List<PropertyInfo>();
            Dictionary<string, PropertyInfo> properties = GetProperties(type);
            string[] fields = outputFields.Split(',');
            foreach (string field in fields)
            {
                string key = field.Trim().ToLower();
                if (properties.ContainsKey(key))
                {
                    rslt.Add(properties[key]);
                }
            }
            return rslt;
        }

        private static Dictionary<Type, Dictionary<string, PropertyInfo>> _typeinfoCache = new Dictionary<Type, Dictionary<string, PropertyInfo>>();
        private static object _syncRoot_typeinfoCache = new object();
        private static Dictionary<string, PropertyInfo> GetProperties(Type type)
        {
            lock (_syncRoot_typeinfoCache)
            {
                if (_typeinfoCache.ContainsKey(type))
                {
                    return _typeinfoCache[type];
                }

                PropertyInfo[] properties = type.GetProperties();
                Dictionary<string, PropertyInfo> dirProperties = new Dictionary<string, PropertyInfo>();
                foreach (PropertyInfo property in properties)
                {
                    string key = property.Name.Trim().ToLower();
                    if (!dirProperties.ContainsKey(key) && property.GetGetMethod() != null && property.GetSetMethod() != null)
                    {
                        dirProperties[key] = property;
                    }
                }
                _typeinfoCache[type] = dirProperties;
                return dirProperties;
            }
        }

        public sealed class SqlParameterCollection : List<SqlParameter>
        {
            public SqlParameterCollection() { }

            public SqlParameter Add(string parameterName, SqlDbType sqlDbType)
            {
                var rslt = new SqlParameter(parameterName, sqlDbType);
                base.Add(rslt);
                return rslt;
            }
        }
    }
}