using System;using System.Collections;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.Common;using System.Data.OleDb;using System.Data.SqlClient;using MySql.Data.MySqlClient;using System.IO;using System.Linq.Expressions;using System.Reflection;using System.Runtime.Serialization;using System.Runtime.Serialization.Formatters.Binary;using System.Text;namespace Mines{    public static class Lavery    {        #region ORM        #region 属性        private static readonly Hashtable htSql = Hashtable.Synchronized(new Hashtable(10240));        /// <summary>        /// 表前缀        /// </summary>        private static readonly string TbPrefix = ConfigurationManager.AppSettings["TbPrefix"];        /// <summary>        /// 数据库类型 支持MySql,MSSQL,OleDb        /// </summary>        private static readonly string Dbtype = ConfigurationManager.AppSettings["Dbtype"];        /// <summary>        /// 默认数据库连接字符串        /// </summary>        private static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;        #endregion        #region Exists        /// <summary>        /// 判断是否存在        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="id">主键</param>        /// <returns></returns>        public static bool Exists<T>(int id)        {            return Exists(string.Format("select count(0) from {0} where id=@id", TbPrefix + typeof(T).Name), new DbParameter[] { Parameter("id", id) });        }        /// <summary>        /// 判断是否存在        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="connstr"></param>        /// <param name="id"></param>        /// <returns></returns>        public static bool Exists<T>(string connstr, int id)        {            return Exists(connstr, string.Format("select count(0) from {0} where id=@id", TbPrefix + typeof(T).Name), new DbParameter[] { Parameter("id", id) });        }        #endregion        #region GetCount        /// <summary>        /// 查询个数        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="id"></param>        /// <returns></returns>        public static int GetCount<T>(string strWhere)        {            return Convert.ToInt32(ExecuteScalar(string.Format("select count(0) from {0} {1}", TbPrefix + typeof(T).Name, !string.IsNullOrEmpty(strWhere) ? " where " + strWhere : "")));        }        /// <summary>        /// 查询个数        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="connstr"></param>        /// <param name="strWhere"></param>        /// <returns></returns>        public static int GetCount<T>(string connstr, string strWhere)        {            return Convert.ToInt32(ExecuteScalar(connstr, string.Format("select count(0) from {0} {1}", TbPrefix + typeof(T).Name, !string.IsNullOrEmpty(strWhere) ? " where " + strWhere : "")));        }        /// <summary>        /// 查询个数        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="strWhere"></param>        /// <param name="param"></param>        /// <returns></returns>        public static int GetCount<T>(string strWhere, params DbParameter[] param)        {            return Convert.ToInt32(ExecuteScalar(string.Format("select count(0) from {0} {1}", TbPrefix + typeof(T).Name, !string.IsNullOrEmpty(strWhere) ? " where " + strWhere : ""), param));        }        /// <summary>        /// 查询个数        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="connstr"></param>        /// <param name="strWhere"></param>        /// <param name="param"></param>        /// <returns></returns>        public static int GetCount<T>(string connstr, string strWhere, params DbParameter[] param)        {            return Convert.ToInt32(ExecuteScalar(connstr, string.Format("select count(0) from {0} {1}", TbPrefix + typeof(T).Name, !string.IsNullOrEmpty(strWhere) ? " where " + strWhere : ""), param));        }        #endregion        #region Add        public static int Add<T>(T model)        {            return Add<T>(null, model);        }        public static int Add<T>(string connstr, T model)        {            List<DbParameter> parameters;            string sql = GetAddSqlAndParams<T>(model, out parameters);            if (string.IsNullOrEmpty(sql))                return 0;            var r = ExecuteScalar(connstr, sql, parameters.ToArray());            return r == null ? 0 : Convert.ToInt32(r);        }        public static int Add<T>(DbConnection connection, DbTransaction trans, T model)        {            List<DbParameter> parameters;            string sql = GetAddSqlAndParams<T>(model, out parameters);            if (string.IsNullOrEmpty(sql))                return 0;            var r = ExecuteScalar(sql, connection, trans, parameters.ToArray());            return r == null ? 0 : Convert.ToInt32(r);        }        public static string GetAddSqlAndParams<T>(T model, out List<DbParameter> parameters)        {            var t = typeof(T);            parameters = new List<DbParameter>();            string sql = (string)htSql[t.Name + "Add"];            var PropertyInfos = (PropertyInfo[])htSql[t.Name + "PropertyInfo[]"];            if (PropertyInfos == null)            {                PropertyInfos = t.GetProperties();                htSql[t.Name + "PropertyInfo[]"] = PropertyInfos;            }            if (!string.IsNullOrEmpty(sql))            {                foreach (var p in PropertyInfos)                {                    object v = p.GetValue<T>(model);                    if (p.Name.ToLower().Equals("id") && v != null) continue;                    if (p.GetCustomAttributes(typeof(NotDBFieldAttribute), false).Length == 0)                    {                        if (p.GetCustomAttributes(typeof(SerialAttribute), false).Length > 0)                            parameters.Add(p.Parameter(SerializeObject(v)));                        else                            parameters.Add(p.Parameter(v));                    }                }            }            else            {                List<string> field = new List<string>();                foreach (var p in PropertyInfos)                {                    object v = p.GetValue<T>(model);                    if (p.Name.ToLower().Equals("id") && v != null) continue;                    if (p.GetCustomAttributes(typeof(NotDBFieldAttribute), false).Length == 0)                    {                        if (p.GetCustomAttributes(typeof(SerialAttribute), false).Length > 0)                            parameters.Add(p.Parameter(SerializeObject(v)));                        else                            parameters.Add(p.Parameter(v));                        field.Add(p.Name);                    }                }                sql = string.Format("insert into {0}{1} ([{2}]) values (@{3});select @@IDENTITY", TbPrefix, t.Name, string.Join("],[", field), string.Join(",@", field));                htSql[t.Name + "Add"] = sql;            }            return sql;        }        #endregion        #region Update        public static bool Update<T>(T model)        {            return Update<T>(null, model);        }        public static bool Update<T>(string connstr, T model)        {            List<DbParameter> parameters;            string sql = GetUpdateSqlAndParams<T>(model, out parameters);            return ExecuteNonQuery(connstr, sql, parameters.ToArray()) > 0;        }        public static bool Update<T>(DbConnection conn, DbTransaction trans, T model)        {            List<DbParameter> parameters;            string sql = GetUpdateSqlAndParams<T>(model, out parameters);            return ExecuteNonQuery(sql, conn, trans, parameters.ToArray()) > 0;        }        public static bool UpdateField<T>(string where, string value)        {            return UpdateField<T>(null, where, value);        }        public static bool UpdateField<T>(string connstr, string where, string value)        {            return ExecuteNonQuery(connstr, string.Format("update {1} set {0} where {2}", value, TbPrefix + typeof(T).Name, where)) > 0;                }        public static bool UpdateField<T>(DbConnection conn, DbTransaction trans, string where, string value)        {            return ExecuteNonQuery(string.Format("update {1} set {0} where {2}", value, TbPrefix + typeof(T).Name, where), conn, trans) > 0;        }        public static string GetUpdateSqlAndParams<T>(T model, out List<DbParameter> parameters)        {            parameters = new List<DbParameter>();            var t = typeof(T);            string sql = (string)htSql[t.Name + "Update"];            var PropertyInfos = (PropertyInfo[])htSql[t.Name + "PropertyInfo[]"];            if (PropertyInfos == null)            {                PropertyInfos = t.GetProperties();                htSql[t.Name + "PropertyInfo[]"] = PropertyInfos;            }            if (!string.IsNullOrEmpty(sql))                foreach (var p in PropertyInfos)                {                    if (p.GetCustomAttributes(typeof(NotDBFieldAttribute), false).Length == 0)                    {                        if (p.GetCustomAttributes(typeof(SerialAttribute), false).Length > 0)                            parameters.Add(p.Parameter(SerializeObject(p.GetValue(model))));                        else                            parameters.Add(p.Parameter(p.GetValue(model)));                    }                }            else            {                List<string> strField = new List<string>();                string where = string.Empty;                foreach (var p in PropertyInfos)                {                    if (p.GetCustomAttributes(typeof(NotDBFieldAttribute), false).Length == 0)                    {                        if (p.GetCustomAttributes(typeof(SerialAttribute), false).Length > 0)                            parameters.Add(p.Parameter(SerializeObject(p.GetValue<T>(model))));                        else                            parameters.Add(p.Parameter(p.GetValue(model)));                    }                    if (p.Name.ToLower().Equals("id"))                        where = string.Format(" [{0}]=@{0}", p.Name);                    else                        strField.Add(string.Format(" [{0}]=@{0}", p.Name));                }                sql = string.Format("update {1}{2} set {0} where {3}", string.Join(",", strField), TbPrefix, t.Name, where);                htSql[t.Name + "Update"] = sql;            }            return sql;        }        #endregion        #region Delete        public static bool Delete<T>(string where)        {            return ExecuteNonQuery(string.Format("delete from {0} where {1}", TbPrefix + typeof(T).Name, where)) > 0;        }        public static bool Delete<T>(string connstr, string where)        {            return ExecuteNonQuery(connstr, string.Format("delete from {0} where {1}", TbPrefix + typeof(T).Name, where)) > 0;        }        public static bool Delete<T>(DbConnection conn, DbTransaction trans, string where)        {            return ExecuteNonQuery(string.Format("delete from {0} where {1}", TbPrefix + typeof(T).Name, where), conn, trans) > 0;        }        #endregion        #region GetModel        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="id"></param>        /// <returns></returns>        public static T GetModel<T>(int id)        {            return GetModel<T>(null, id);        }        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="connstr"></param>        /// <param name="id"></param>        /// <returns></returns>        public static T GetModel<T>(string connstr, int id)        {            List<DbParameter> parameters = new List<DbParameter>();            parameters.Add(Parameter("id", id));            string StrSql = string.Format("select * from {0} where id={1} ", TbPrefix + typeof(T).Name, id);            var reader = ExecuteReader(connstr, StrSql, parameters.ToArray());            while (reader.Read())            {                return GetModel<T>(reader);            }            reader.Close();            return default(T);        }        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="where"></param>        /// <param name="param"></param>        /// <returns></returns>        public static T GetModel<T>(string where, params DbParameter[] param)        {            return GetModel<T>(null, where, param);        }        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="connstr"></param>        /// <param name="where"></param>        /// <param name="param"></param>        /// <returns></returns>        public static T GetModel<T>(string connstr, string where, params DbParameter[] param)        {            var StrSql = string.Format("select top 1 * from {0} where {1} ", TbPrefix + typeof(T).Name, where);            var reader = ExecuteReader(connstr, StrSql, param);            T o = default(T);            while (reader.Read())            {                o = GetModel<T>(reader, o, typeof(T));                break;            }            reader.Close();            return o;        }        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="reader"></param>        /// <returns></returns>        public static T GetModel<T>(IDataReader reader)        {            var type = typeof(T);            return GetModel<T>(reader, default(T), type);        }        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="reader"></param>        /// <param name="o"></param>        /// <param name="t"></param>        /// <returns></returns>        public static T GetModel<T>(IDataReader reader, T o, Type t)        {            if (o == null)                o = (T)Activator.CreateInstance(t);            var PropertyInfos = (PropertyInfo[])htSql[t.Name + "PropertyInfo[]"];            if (PropertyInfos == null)            {                PropertyInfos = t.GetProperties();                htSql[t.Name + "PropertyInfo[]"] = PropertyInfos;            }            foreach (var p in PropertyInfos)            {                if (!reader[p.Name].Equals(DBNull.Value))                {                    if (p.GetCustomAttributes(typeof(NotDBFieldAttribute), false).Length == 0)                    {                        if (p.GetCustomAttributes(typeof(SerialAttribute), false).Length > 0)                            p.SetValue<T>(o, DeserializeObject(reader[p.Name].ToString()));                        else                            p.SetValue<T>(o, reader[p.Name]);                    }                }            }            return o;        }        #endregion        #region Query        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="pageSize"></param>        /// <param name="currentPage"></param>        /// <param name="strWhere"></param>        /// <param name="filedOrder"></param>        /// <returns></returns>        public static DataSet Query<T>(int pageSize, int currentPage, string strWhere, string filedOrder)        {            int topSize = pageSize * currentPage;            StringBuilder strSql = new StringBuilder();            if (Dbtype == "MySql")            {                strSql.AppendFormat("select * from {0}", TbPrefix + typeof(T).Name);                if (!string.IsNullOrEmpty(strWhere)) strSql.Append(" where " + strWhere);                if (!string.IsNullOrEmpty(filedOrder)) strSql.Append(" order by " + filedOrder);                strSql.AppendFormat(" limit {0},{1}", topSize, pageSize);            }            else            {                strSql.Append("select top " + pageSize + " * from " + TbPrefix + typeof(T).Name);                strSql.AppendFormat(" where Id not in(select top {0} Id from {1}", topSize, TbPrefix + typeof(T).Name);                if (strWhere.Trim() != "") strSql.Append(" where " + strWhere);                if (!string.IsNullOrEmpty(filedOrder)) strSql.Append(" order by " + filedOrder);                if (strWhere.Trim() != "") strSql.Append(" and " + strWhere);                if (!string.IsNullOrEmpty(filedOrder)) strSql.Append(" order by " + filedOrder);            }            return Query(strSql.ToString());        }        #endregion        #region GetList        /// <summary>        /// 返回T的集合        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="strSQL"></param>        /// <returns></returns>        public static IEnumerable<T> GetList<T>(string strSQL)        {            return GetList<T>(null, strSQL);        }        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="connstr"></param>        /// <param name="strSQL"></param>        /// <returns></returns>        public static IEnumerable<T> GetList<T>(string connstr, string strSQL)        {            var myReader = ExecuteReader(connstr, strSQL);            while (myReader.Read())                yield return GetModel<T>(myReader);            myReader.Close();        }        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="Top"></param>        /// <param name="strWhere"></param>        /// <param name="filedOrder"></param>        /// <returns></returns>        public static IEnumerable<T> GetList<T>(int Top, string strWhere, string filedOrder)        {            return GetList<T>(null, Top, strWhere, filedOrder);        }        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="connstr"></param>        /// <param name="Top"></param>        /// <param name="strWhere"></param>        /// <param name="filedOrder"></param>        /// <returns></returns>        public static IEnumerable<T> GetList<T>(string connstr, int Top, string strWhere, string filedOrder)        {            string strsql = string.Empty;            if (Dbtype == "MySql")                strsql = string.Format(                    "select * from {1} {2} {3} {0}",                    Top > 0 ? " limit 0," + Top : "",                    TbPrefix + typeof(T).Name,                    !string.IsNullOrEmpty(strWhere) ? " where " + strWhere : "",                    !string.IsNullOrEmpty(filedOrder) ? " order by " + filedOrder : ""                    );            else                strsql = string.Format(                    "select {0} * from {1} {2} {3}",                    Top > 0 ? " top " + Top : "",                    TbPrefix + typeof(T).Name, !string.IsNullOrEmpty(strWhere) ? " where " + strWhere : "",                    !string.IsNullOrEmpty(filedOrder) ? " order by " + filedOrder : "");            return GetList<T>(connstr, strsql);        }        /// <summary>        /// 获得查询分页数据        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="pageSize"></param>        /// <param name="currentPage"></param>        /// <param name="strWhere"></param>        /// <param name="filedOrder"></param>        /// <returns></returns>        public static IEnumerable<T> GetList<T>(int pageSize, int currentPage, string strWhere, string filedOrder)        {            return GetList<T>(null, pageSize, currentPage, strWhere, filedOrder);        }        /// <summary>        /// 获得查询分页数据        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="connstr">数据库连接</param>        /// <param name="pageSize"></param>        /// <param name="currentPage"></param>        /// <param name="strWhere"></param>        /// <param name="filedOrder"></param>        /// <returns></returns>        public static IEnumerable<T> GetList<T>(string connstr, int pageSize, int currentPage, string strWhere, string filedOrder)        {            int topSize = pageSize * currentPage;            StringBuilder strSql = new StringBuilder();            if (Dbtype == "MySql")            {                strSql.Append("select * from " + TbPrefix + typeof(T).Name);                if (!string.IsNullOrEmpty(strWhere)) strSql.Append(" where " + strWhere);                if (!string.IsNullOrEmpty(filedOrder)) strSql.Append(" order by " + strWhere);                strSql.AppendFormat(" limit {0},{1}", topSize, pageSize);            }            else            {                strSql.Append("select top " + pageSize + " * from " + TbPrefix + typeof(T).Name);                strSql.AppendFormat(" where Id not in(select top {0} Id from {1}", topSize, TbPrefix + typeof(T).Name);                if (strWhere.Trim() != "") strSql.Append(" where " + strWhere);                strSql.AppendFormat(" order by {0})", !string.IsNullOrEmpty(filedOrder) ? filedOrder : "SortId asc,Id desc");                if (strWhere.Trim() != "") strSql.Append(" and " + strWhere);                strSql.AppendFormat(" order by {0}", !string.IsNullOrEmpty(filedOrder) ? filedOrder : "SortId asc,Id desc");            }            return GetList<T>(connstr, strSql.ToString());        }        /// <summary>        /// 获得查询分页数据        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="strSql"></param>        /// <param name="CmdParams"></param>        /// <returns></returns>        public static IEnumerable<T> GetList<T>(string strSql, params DbParameter[] CmdParams)        {            return GetList<T>(null, strSql, CmdParams);        }        /// <summary>        ///         /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="connstr"></param>        /// <param name="strSql"></param>        /// <param name="CmdParams"></param>        /// <returns></returns>        public static IEnumerable<T> GetList<T>(string connstr, string strSql, params DbParameter[] CmdParams)        {            var myReader = ExecuteReader(connstr, strSql, CmdParams);            while (myReader.Read())                yield return GetModel<T>(myReader);            myReader.Close();        }        #endregion        #region ADO.NET数据库操作        #region 公用方法        public static bool Exists(string strSql)        {            object obj = ExecuteScalar(strSql);            int cmdresult;            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))                cmdresult = 0;            else                cmdresult = int.Parse(obj.ToString());            if (cmdresult == 0)                return false;            else                return true;        }        public static bool Exists(string connstr, string strSql)        {            object obj = ExecuteScalar(connstr, strSql, null);            int cmdresult;            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))                cmdresult = 0;            else                cmdresult = int.Parse(obj.ToString());            if (cmdresult == 0)                return false;            else                return true;        }        public static bool Exists(string strSql, params DbParameter[] cmdParms)        {            object obj = ExecuteScalar(strSql, cmdParms);            int cmdresult;            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))                cmdresult = 0;            else                cmdresult = int.Parse(obj.ToString());            if (cmdresult == 0)                return false;            else                return true;        }        public static bool Exists(string connstr, string strSql, params DbParameter[] cmdParms)        {            object obj = ExecuteScalar(connstr, strSql, cmdParms);            int cmdresult;            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))                cmdresult = 0;            else                cmdresult = int.Parse(obj.ToString());            if (cmdresult == 0)                return false;            else                return true;        }        #endregion        #region  执行简单SQL语句        /// <summary>        /// 执行SQL语句，返回影响的记录数        /// </summary>        /// <param name="SQLString">SQL语句</param>        /// <returns>影响的记录数</returns>        public static int ExecuteNonQuery(string SQLString)        {            return ExecuteNonQuery(string.Empty, SQLString);        }        /// <summary>        ///         /// </summary>        /// <param name="connstr"></param>        /// <param name="SQLString"></param>        /// <returns></returns>        public static int ExecuteNonQuery(string connstr, string SQLString)        {            var connection = Connection(connstr);            var cmd = Command(SQLString, connection);            try            {                connection.Open();                int rows = cmd.ExecuteNonQuery();                return rows;            }            catch (Exception e)            {                connection.Close();                throw e;            }            finally            {                connection.Close();                cmd.Dispose();            }        }        /// <summary>        /// 执行SQL语句，返回影响的记录数        /// </summary>        /// <param name="connection">MySqlConnection对象</param>        /// <param name="trans">MySqlTransaction事件</param>        /// <param name="SQLString">SQL语句</param>        /// <returns>影响的记录数</returns>        public static int ExecuteNonQuery(DbConnection connection, DbTransaction trans, string SQLString)        {            var cmd = Command(SQLString, connection);            try            {                cmd.Connection = connection;                cmd.Transaction = trans;                int rows = cmd.ExecuteNonQuery();                return rows;            }            catch (Exception e)            {                trans.Rollback();                throw e;            }            finally            {                connection.Close();                cmd.Dispose();            }        }        public static int ExecuteNonQuery(string SQLString, int Times)        {            return ExecuteNonQuery(null, SQLString, Times);        }        public static int ExecuteNonQuery(string connstr, string SQLString, int Times)        {            var connection = Connection(connstr);            var cmd = Command(SQLString, connection);            try            {                connection.Open();                cmd.CommandTimeout = Times;                int rows = cmd.ExecuteNonQuery();                return rows;            }            catch (Exception e)            {                connection.Close();                throw e;            }            finally            {                connection.Close();                cmd.Dispose();            }        }        /// <summary>        /// 执行多条SQL语句，实现数据库事务。        /// </summary>        /// <param name="SQLStringList">多条SQL语句</param>            public static bool ExecuteNonQuery(List<String> SQLStringList)        {            return ExecuteNonQuery(null, SQLStringList);        }        public static bool ExecuteNonQuery(string connstr, List<String> SQLStringList)        {            var conn = Connection(connstr);            conn.Open();            var cmd = Command();            cmd.Connection = conn;            var tx = conn.BeginTransaction();            cmd.Transaction = tx;            try            {                int count = 0;                for (int n = 0; n < SQLStringList.Count; n++)                {                    string strsql = SQLStringList[n];                    if (strsql.Trim().Length > 1)                    {                        cmd.CommandText = strsql;                        count += cmd.ExecuteNonQuery();                    }                }                tx.Commit();                return true;            }            catch            {                tx.Rollback();                return false;            }            finally            {                conn.Close();                cmd.Dispose();            }        }        /// <summary>        /// 执行一条计算查询结果语句，返回查询结果（object）。        /// </summary>        /// <param name="SQLString">计算查询结果语句</param>        /// <returns>查询结果（object）</returns>        public static object ExecuteScalar(string SQLString)        {            return ExecuteScalar(null, SQLString);        }        public static object ExecuteScalar(string connstr, string SQLString)        {            DbConnection connection = Connection(connstr);            DbCommand cmd = Command(SQLString, connection);            try            {                connection.Open();                object obj = cmd.ExecuteScalar();                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))                    return null;                else                    return obj;            }            catch (Exception e)            {                connection.Close();                throw e;            }            finally            {                connection.Close();                cmd.Dispose();            }        }        public static object ExecuteScalar(string SQLString, int Times)        {            return ExecuteScalar(null, SQLString, Times);        }        public static object ExecuteScalar(string connstr, string SQLString, int Times)        {            DbConnection connection = Connection(connstr);            DbCommand cmd = Command(SQLString, connection);            try            {                connection.Open();                cmd.CommandTimeout = Times;                object obj = cmd.ExecuteScalar();                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))                    return null;                else                    return obj;            }            catch (Exception e)            {                throw e;            }            finally            {                connection.Close();                cmd.Dispose();            }        }        /// <summary>        /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )        /// </summary>        /// <param name="strSQL">查询语句</param>        /// <returns>MySqlDataReader</returns>        public static IDataReader ExecuteReader(string strSQL)        {            DbConnection connection = Connection();            DbCommand cmd = Command(strSQL, connection);            connection.Open();            IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);            cmd.Dispose();            return myReader;        }        /// <summary>        /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )        /// </summary>        /// <param name="strSQL">查询语句</param>        /// <returns>MySqlDataReader</returns>        public static IDataReader ExecuteReader(string connstr, string strSQL)        {            var connection = Connection(connstr);            DbCommand cmd = Command(strSQL, connection);            connection.Open();            IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);            cmd.Dispose();            return myReader;        }        /// <summary>        /// 执行查询语句，返回DataSet        /// </summary>        /// <param name="SQLString">查询语句</param>        /// <returns>DataSet</returns>        public static DataSet Query(string SQLString)        {            DbConnection connection = Connection();            DataSet ds = new DataSet();            try            {                connection.Open();                DbDataAdapter command = DataAdapter(SQLString, connection);                command.Fill(ds, "ds");            }            catch (Exception ex)            {                throw new Exception(ex.Message);            }            finally            {                connection.Close();            }            return ds;        }        public static DataSet Query(string SQLString, int Times)        {            DbConnection connection = Connection();            DataSet ds = new DataSet();            try            {                connection.Open();                DbDataAdapter command = DataAdapter(SQLString, connection);                command.SelectCommand.CommandTimeout = Times;                command.Fill(ds, "ds");            }            catch (Exception ex)            {                throw new Exception(ex.Message);            }            finally            {                connection.Close();            }            return ds;        }        /// <summary>        /// 执行查询语句，返回DataSet        /// </summary>        /// <param name="connection">DbConnection对象</param>        /// <param name="trans">DbTransaction事务</param>        /// <param name="SQLString">SQL语句</param>        /// <returns>DataSet</returns>        public static DataSet Query(DbConnection connection, DbTransaction trans, string SQLString)        {            DataSet ds = new DataSet();            try            {                DbDataAdapter command = DataAdapter(SQLString, connection);                command.SelectCommand.Transaction = trans;                command.Fill(ds, "ds");            }            catch (Exception ex)            {                throw new Exception(ex.Message);            }            finally            {                connection.Close();            }            return ds;        }        public static DataSet Query(string conn, DbTransaction trans, string SQLString)        {            return Query(Connection(conn), trans, SQLString);        }        #endregion        #region 执行带参数的SQL语句        /// <summary>        /// 执行SQL语句，返回影响的记录数        /// </summary>        /// <param name="SQLString">SQL语句</param>        /// <returns>影响的记录数</returns>        public static int ExecuteNonQuery(string SQLString, params DbParameter[] cmdParms)        {            return ExecuteNonQuery(null, SQLString, cmdParms);        }        /// <summary>        /// 执行SQL语句，返回影响的记录数        /// </summary>        /// <param name="SQLString">SQL语句</param>        /// <returns>影响的记录数</returns>        public static int ExecuteNonQuery(string connstr, string SQLString, params DbParameter[] cmdParms)        {            var connection = Connection(connstr);            var cmd = Command(SQLString, connection);            try            {                PrepareCommand(cmd, connection, null, SQLString, cmdParms);                int rows = cmd.ExecuteNonQuery();                cmd.Parameters.Clear();                return rows;            }            catch (Exception e)            {                throw e;            }            finally            {                connection.Close();                cmd.Dispose();            }        }        /// <summary>        /// 执行SQL语句，返回影响的记录数        /// </summary>        /// <param name="connection">MySqlConnection对象</param>        /// <param name="trans">MySqlTransaction对象</param>        /// <param name="SQLString">SQL语句</param>        /// <returns>影响的记录数</returns>        public static int ExecuteNonQuery(string SQLString, DbConnection connection, DbTransaction trans, params DbParameter[] cmdParms)        {            var cmd = Command();            try            {                PrepareCommand(cmd, connection, trans, SQLString, cmdParms);                int rows = cmd.ExecuteNonQuery();                cmd.Parameters.Clear();                return rows;            }            catch (Exception e)            {                if (trans != null)                    trans.Rollback();                throw e;            }            finally            {                connection.Close();                cmd.Dispose();            }        }        //public static int ExecuteNonQuery(string connstr, DbTransaction trans, string SQLString, params DbParameter[] cmdParms)        //{        //    return ExecuteNonQuery(Connection(connstr), trans, SQLString, cmdParms);        //}        /// <summary>        /// 执行多条SQL语句，实现数据库事务。        /// </summary>        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>        public static int ExecuteNonQuery(Hashtable SQLStringList)        {            var conn = Connection();            if (conn.State != ConnectionState.Open)                conn.Open();            DbTransaction trans = conn.BeginTransaction();            var cmd = Command();            try            {                int val = 0;                foreach (DictionaryEntry myDE in SQLStringList)                {                    string cmdText = myDE.Key.ToString();                    DbParameter[] cmdParms = (DbParameter[])myDE.Value;                    PrepareCommand(cmd, conn, trans, cmdText, cmdParms);                    val = cmd.ExecuteNonQuery();                    cmd.Parameters.Clear();                }                trans.Commit();            }            catch            {                trans.Rollback();                return 0;            }            finally            {                conn.Close();                cmd.Dispose();            }            return 1;        }        /// <summary>        /// 执行一条计算查询结果语句，返回查询结果（object）。        /// </summary>        /// <param name="SQLString">计算查询结果语句</param>        /// <returns>查询结果（object）</returns>        public static object ExecuteScalar(string SQLString, params DbParameter[] cmdParms)        {            return ExecuteScalar(null, null, SQLString, cmdParms);        }        public static object ExecuteScalar(string ConnStr, string SQLString, params DbParameter[] cmdParms)        {            return ExecuteScalar(ConnStr, null, SQLString, cmdParms);        }        /// <summary>        /// 执行一条计算查询结果语句，返回查询结果（object）。        /// </summary>        /// <param name="connection">MySqlConnection对象</param>        /// <param name="trans">MySqlTransaction事务</param>        /// <param name="SQLString">计算查询结果语句</param>        /// <returns>查询结果（object）</returns>        public static object ExecuteScalar(string connstr, DbTransaction trans, string SQLString, params DbParameter[] cmdParms)        {            var connection = Connection(connstr);            var cmd = Command();            try            {                PrepareCommand(cmd, connection, trans, SQLString, cmdParms);                object obj = cmd.ExecuteScalar();                cmd.Parameters.Clear();                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))                    return null;                else                    return obj;            }            catch (Exception e)            {                if (trans != null)                    trans.Rollback();                throw e;            }            finally            {                connection.Close();                cmd.Dispose();            }        }        public static object ExecuteScalar(string SQLString, DbConnection connection, DbTransaction trans, params DbParameter[] cmdParms)        {            var cmd = Command();            try            {                PrepareCommand(cmd, connection, trans, SQLString, cmdParms);                object obj = cmd.ExecuteScalar();                cmd.Parameters.Clear();                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))                    return null;                else                    return obj;            }            catch (Exception e)            {                if (trans != null)                    trans.Rollback();                throw e;            }            finally            {                connection.Close();                cmd.Dispose();            }        }        /// <summary>        /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )        /// </summary>        /// <param name="strSQL">查询语句</param>        /// <returns>MySqlDataReader</returns>        public static IDataReader ExecuteReader(string SQLString, params DbParameter[] cmdParms)        {            var connection = Connection();            var cmd = Command();            PrepareCommand(cmd, connection, null, SQLString, cmdParms);            IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);            cmd.Parameters.Clear();            cmd.Dispose();            return myReader;        }        /// <summary>        /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )        /// </summary>        /// <param name="strSQL">查询语句</param>        /// <returns>MySqlDataReader</returns>        public static IDataReader ExecuteReader(string connstr, string SQLString, params DbParameter[] cmdParms)        {            var cmd = Command();            PrepareCommand(cmd, Connection(connstr), null, SQLString, cmdParms);            IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);            cmd.Parameters.Clear();            cmd.Dispose();            return myReader;        }        /// <summary>        /// 执行查询语句，返回DataSet        /// </summary>        /// <param name="SQLString">查询语句</param>        /// <returns>DataSet</returns>        public static DataSet Query(string SQLString, params DbParameter[] cmdParms)        {            return Query(null, null, SQLString, cmdParms);        }        public static DataSet Query(string connstr, string SQLString, params DbParameter[] cmdParms)        {            return Query(connstr, null, SQLString, cmdParms);        }        /// <summary>        /// 执行查询语句，返回DataSet        /// </summary>        /// <param name="connection">MySqlConnection对象</param>        /// <param name="trans">MySqlTransaction事务</param>        /// <param name="SQLString">查询语句</param>        /// <returns>DataSet</returns>        public static DataSet Query(string connstr, DbTransaction trans, string SQLString, params DbParameter[] cmdParms)        {            var connection = Connection(connstr);            DbCommand cmd = Command();            PrepareCommand(cmd, connection, trans, SQLString, cmdParms);            DbDataAdapter da = DataAdapter(cmd);            DataSet ds = new DataSet();            try            {                da.Fill(ds, "ds");                cmd.Parameters.Clear();            }            catch (Exception ex)            {                if (trans != null)                    trans.Rollback();                throw new Exception(ex.Message);            }            finally            {                connection.Close();                cmd.Dispose();            }            return ds;        }        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms)        {            if (conn.State != ConnectionState.Open)                conn.Open();            cmd.Connection = conn;            cmd.CommandText = cmdText;            if (trans != null)            {                trans = conn.BeginTransaction();                cmd.Transaction = trans;            }            cmd.CommandType = CommandType.Text;            if (cmdParms != null)            {                foreach (DbParameter parameter in cmdParms)                {                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value =http://www.mamicode.com/= null))"MySql":                    return new MySqlConnection(connectionString);                case "OleDb":                    return new MySqlConnection(connectionString);                default:                    return new SqlConnection(connectionString);            }        }        private static DbConnection Connection(out DbTransaction trans)        {            DbConnection conn;            switch (Dbtype)            {                case "MySql":                    conn= new MySqlConnection(connectionString);                    break;                case "OleDb":                    conn= new MySqlConnection(connectionString);                    break;                default:                    conn= new SqlConnection(connectionString);                    break;            }            conn.Open();            trans = conn.BeginTransaction();            return conn;        }        private static DbConnection Connection(string connstring)        {            if (string.IsNullOrEmpty(connstring))                connstring = connectionString;            else                connstring = ConfigurationManager.ConnectionStrings["mines_" + connstring].ConnectionString;            switch (Dbtype)            {                case "MySql":                    return new MySqlConnection(connstring);                case "OleDb":                    return new MySqlConnection(connstring);                default:                    return new SqlConnection(connstring);            }        }        private static DbConnection Connection(string connstring, out DbTransaction trans)        {            DbConnection conn;            if (string.IsNullOrEmpty(connstring))                connstring = connectionString;            else                connstring = ConfigurationManager.ConnectionStrings["mines_" + connstring].ConnectionString;            switch (Dbtype)            {                case "MySql":                    conn = new MySqlConnection(connstring);                    break;                case "OleDb":                    conn = new MySqlConnection(connstring);                    break;                default:                    conn = new SqlConnection(connstring);                    break;            }            conn.Open();            trans = conn.BeginTransaction();            return conn;        }        private static DbCommand Command(string SQLString, DbConnection connection)        {            switch (Dbtype)            {                case "MySql":                    return new MySqlCommand(SQLString, (MySqlConnection)connection);                case "OleDb":                    return new OleDbCommand(SQLString, (OleDbConnection)connection);                default:                    return new SqlCommand(SQLString, (SqlConnection)connection);            }        }        private static DbCommand Command()        {            switch (Dbtype)            {                case "MySql":                    return new MySqlCommand();                case "OleDb":                    return new OleDbCommand();                default:                    return new SqlCommand();            }        }        private static DbDataAdapter DataAdapter(string SQLString, DbConnection connection)        {            switch (Dbtype)            {                case "MySql":                    return new MySqlDataAdapter(SQLString, (MySqlConnection)connection);                case "OleDb":                    return new OleDbDataAdapter(SQLString, (OleDbConnection)connection);                default:                    return new SqlDataAdapter(SQLString, (SqlConnection)connection);            }        }        private static DbDataAdapter DataAdapter(DbCommand cmd)        {            switch (Dbtype)            {                case "MySql":                    return new MySqlDataAdapter((MySqlCommand)cmd);                case "OleDb":                    return new OleDbDataAdapter((OleDbCommand)cmd);                default:                    return new SqlDataAdapter((SqlCommand)cmd);            }        }        #endregion        #endregion        #region 扩展  参数Parameter/属性读写GETVALUE、SETVALUE        public static DbParameter Parameter(string Name, object value)        {            switch (Dbtype)            {                case "MySql":                    return new MySqlParameter("@" + Name, value);                case "OleDb":                    return new OleDbParameter("@" + Name, value);                default:                    return new SqlParameter("@" + Name, value);            }        }        public static DbParameter Parameter(this PropertyInfo p, object value)        {            switch (Dbtype)            {                case "MySql":                    return new MySqlParameter("@" + p.Name, value);                case "OleDb":                    return new OleDbParameter("@" + p.Name, value);                default:                    return new SqlParameter("@" + p.Name, value);            }        }        public static object GetValue<TModel>(this PropertyInfo p, TModel model)        {            var paramTmodel = Expression.Parameter(typeof(TModel), "Tmodel");            var pGetter = Expression.Property(paramTmodel, p);            var returnObj = Expression.Convert(pGetter, typeof(object));            var ExpressionLambda = Expression.Lambda<Func<TModel, object>>(returnObj, paramTmodel).Compile();            return ExpressionLambda(model);        }        private static void SetValue<TModel>(this PropertyInfo p, TModel model, object value)        {            var instanceParam = Expression.Parameter(typeof(TModel), "instance");            var valueParam = Expression.Parameter(typeof(object), "value");            var ConvertValueParam = Expression.Convert(valueParam, value.GetType());            var propertyProperty = Expression.Property(instanceParam, p);            var assignExpression = Expression.Assign(propertyProperty, ConvertValueParam);            var lambdaExpression = Expression.Lambda<Action<TModel, object>>(assignExpression, instanceParam, valueParam).Compile();            lambdaExpression(model, value);        }        #endregion        #region 序列化/反序列化        private static string SerializeObject(object obj)        {            string result = string.Empty;            using (MemoryStream stream = new MemoryStream())            {                IFormatter formatter = new BinaryFormatter();                formatter.Serialize(stream, obj);                byte[] byt = new byte[stream.Length];                byt = stream.ToArray();                result = Convert.ToBase64String(byt);                stream.Flush();            }            return result;        }        private static object DeserializeObject(string str)        {            byte[] byt = Convert.FromBase64String(str);            object obj = null;            using (Stream stream = new MemoryStream(byt, 0, byt.Length))            {                IFormatter formatter = new BinaryFormatter();                obj = formatter.Deserialize(stream);            }            return obj;        }        #endregion        #endregion    }    #region Attribute    /// <summary>    /// 可序列化属性    /// </summary>    public class SerialAttribute : Attribute { }    /// <summary>    /// 非数据库字段属性    /// </summary>    public class NotDBFieldAttribute : Attribute { }    #endregion}