通用baseDAL类（4）

 

namespace Syaccp.Feast.ProcDAL.SqlServer
{
    //系统命名空间
    using System.Collections.Generic;
    using System.Data;
    using System;
    using System.Data.SqlClient;
    using System.Text;
    using System.Configuration;
   
    //第三方命名空间
    using log4net;
    using System.Reflection;
    using Syaccp.Feast.Util;
using Syaccp.Feast.Model.Search.Proc;

    /// <summary>
    /// 实现对SQL数据库的操作的基类
    /// </summary>
    public class BaseDAL
    {
        #region Log日志对象
        /// <summary>
        /// 获得当前应用程序运行的方法,并返回对应方法的对象
        /// </summary>
        private static readonly ILog _log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
        /// <summary>
        /// Log日志对象
        /// </summary>
        public ILog Log
        {
            get { return _log; }
        }
        #endregion

        #region 数据库的连接
        /// <summary>
        /// 数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. 
        /// </summary>
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings["Feast"].ToString();

        /// <summary>
        /// 数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. 
        /// </summary>
        public static string ConnectionString
        {
            get
            {
                if (connectionString != null)
                {
                    return BaseDAL.connectionString;
                }
                else
                {
                    return @"server=192.168.0.100/sa;database=Feast;uid=xlive-1;pwd=xlive";
                }
            }
        }

        #endregion

        #region 创建对象
        /// <summary>
        /// 创建对象
        /// </summary>
        /// <typeparam name="T">要创建的类名</typeparam>
        /// <returns>对象</returns>
        public static T CreateInstance<T>()
        {
            return Activator.CreateInstance<T>();
        }
        #endregion

        #region 数据库的增删改查
        /// <summary>
        /// 验证一条数据是否存在(返回 1：存在;0：不存在 -2：异常)
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns> int</returns>
        protected virtual int Exists(string sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        connection.Open();
                        //cmd.Prepare();
                       
                        int x = (int)cmd.ExecuteScalar();
                        return x >= 1 ? 1 : 0;
                    }
                    catch (SqlException e)
                    {
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }

        /// <summary>
        /// 验证一条数据是否存在(返回1：存在  0：不存在  -2：异常)
        /// </summary>
        /// <param name="paramList">sql语句所对应的参数</param>
        /// <param name="sql">带占位符的sql语句例如：select * from user where id = @id</param>
        /// <returns>int</returns>
        protected virtual int Exists(List<SqlParameter> paramList, string sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.Parameters.AddRange(paramList.ToArray());
                    try
                    {
                        connection.Open();
                        cmd.Prepare();
                        int x = (int)cmd.ExecuteScalar();
                        return x >= 1 ? 1 : 0;
                    }
                    catch (SqlException e)
                    {
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        #region 增加
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns> int</returns>
        protected virtual int Insert(string sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        connection.Open();
                        int x = (int)cmd.ExecuteNonQuery();
                        return x >= 1 ? 1 : 0;
                    }
                    catch (Exception e)
                    {
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        /// <summary>
        /// 增加一条数据(带参数)
        /// </summary>
        /// <param name="sql">要查询的T-SQL例如:insert into user values(@id)</param>
        /// <param name="paramList">sql语句中需要的参数集合</param>
        /// <returns> int</returns>
        protected virtual int Insert(string sql,List<SqlParameter> paramList)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.Parameters.AddRange(paramList.ToArray());
                    try
                    {
                        connection.Open();
                        cmd.Prepare();
                        int x = (int)cmd.ExecuteNonQuery();
                        return x >= 1 ? 1 : 0;
                    }
                    catch (Exception e)
                    {
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        /// <summary>
        /// 插入多条数据(带参数)
        /// </summary>
        /// <param name="paramList">存储过程和参数的集合</param>
        /// <returns>插入数据的条数(int)</returns>
        protected virtual int Inserts(IList<ProcModel> list)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    connection.Open();
                    cmd.Connection = connection;
                    SqlTransaction tx = connection.BeginTransaction();
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Transaction = tx;
                    try
                    {
                        int count = 0;
                        foreach (ProcModel item in list)
                        {
                            cmd.CommandText = item.key;
                            for (int i = 0; i < item.value.Count; i++)
                            {
                                cmd.Parameters.Add(item.value[i]);
                            }
                            count += cmd.ExecuteNonQuery();
                        }
                        tx.Commit();
                        return count >= list.Count ? 3 : 0;
                    }
                    catch (SqlException e)
                    {
                        tx.Rollback();
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        /// <summary>
        /// 插入多条数据
        /// </summary>
        /// <param name="sql">要查询的T-SQL集合</param>
        /// <returns>插入数据的条数(int)</returns>
        protected virtual int Inserts(IList<string> sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    connection.Open();
                    cmd.Connection = connection;
                    SqlTransaction tx = connection.BeginTransaction();
                    cmd.Transaction = tx;
                    try
                    {
                        int count = 0;
                        for (int n = 0; n < sql.Count; n++)
                        {
                            string strsql = sql[n];
                            if (strsql.Trim().Length > 1)
                            {
                                cmd.CommandText = strsql;
                                count += cmd.ExecuteNonQuery();
                            }
                        }
                        tx.Commit();
                        return count >= sql.Count ? 3 : 0;
                    }
                    catch (SqlException e)
                    {
                        tx.Rollback();
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        #endregion

        #region 更新
        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="sql">要执行的T-SQL</param>
        /// <returns>int</returns>
        protected virtual int Update(string sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        connection.Open();
                        int x = (int)cmd.ExecuteNonQuery();
                        return x >= 1 ? 1 : 0;
                    }
                    catch (SqlException e)
                    {
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        /// <summary>
        /// 更新一条数据(需要参数)
        /// </summary>
        /// <param name="sql">要执行的T-SQL例如:update user set id=@id</param>
        /// <param name="parameter">sql语句中需要的参数集合</param>
        /// <returns>int</returns>
        protected virtual int Update(string sql,List<SqlParameter> parameter)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.Parameters.AddRange(parameter.ToArray());
                    try
                    {
                        connection.Open();
                        cmd.Prepare();
                        int x = (int)cmd.ExecuteNonQuery();
                       
                        return x >= 1 ? 1 : 0;
                    }
                    catch (SqlException e)
                    {
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        /// <summary>
        /// 更新多条数据(带参数)
        /// </summary>
        /// <param name="paramList">存储过程名称和参数的集合</param>
        /// <returns>返回受影响的行数(int)</returns>
        protected virtual int Updates(IList<ProcModel> list)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    connection.Open();
                    cmd.Connection = connection;
                    SqlTransaction tx = connection.BeginTransaction();
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Transaction = tx;
                    try
                    {
                        int count = 0;
                        foreach (ProcModel item in list)
                        {
                            cmd.CommandText = item.key;
                            for (int i = 0; i < item.value.Count; i++)
                            {
                                cmd.Parameters.Add(item.value[i]);
                            }
                            count += cmd.ExecuteNonQuery();
                        }
                        tx.Commit();
                        return count >= list.Count ? 3 : 0;
                    }
                    catch (SqlException e)
                    {
                        tx.Rollback();
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        /// <summary>
        /// 更新多条数据
        /// </summary>
        /// <param name="sql">要更新的T-SQL集合</param>
        /// <returns>返回受影响的行数(int)</returns>
        protected virtual int Updates(IList<string> sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    connection.Open();
                    cmd.Connection = connection;
                    SqlTransaction tx = connection.BeginTransaction();
                    cmd.Transaction = tx;
                    try
                    {
                        int count = 0;
                        for (int n = 0; n < sql.Count; n++)
                        {
                            string strsql = sql[n];
                            if (strsql.Trim().Length > 1)
                            {
                                cmd.CommandText = strsql;
                                count += cmd.ExecuteNonQuery();
                            }
                        }
                        tx.Commit();
                        return count > 0 ? 3 : 0;
                    }
                    catch (SqlException e)
                    {
                        tx.Rollback();
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除多条数据
        /// </summary>
        /// <param name="sql">要删除的T-SQL集合</param>
        /// <returns>删除数据的条数(int)</returns>
        protected virtual int Deletes(IList<string> sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    connection.Open();
                    cmd.Connection = connection;
                    SqlTransaction tx = connection.BeginTransaction();
                    cmd.Transaction = tx;
                    try
                    {
                        int count = 0;
                        for (int n = 0; n < sql.Count; n++)
                        {
                            string strsql = sql[n];
                            if (strsql.Trim().Length > 1)
                            {
                                cmd.CommandText = strsql;
                                count += cmd.ExecuteNonQuery();
                            }
                        }
                        tx.Commit();
                        return count > sql.Count ? 3 : 0;
                    }
                    catch (SqlException e)
                    {
                        tx.Rollback();
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        /// <summary>
        /// 删除多条数据(带参数)
        /// </summary>
        /// <param name="sql">要删除的T-SQL集合</param>
        /// <returns>删除数据的条数(int)</returns>
        protected virtual int Deletes(IList<ProcModel> list)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    connection.Open();
                    cmd.Connection = connection;
                    SqlTransaction tx = connection.BeginTransaction();
                    cmd.Transaction = tx;
                    try
                    {
                        int count = 0;
                        foreach (ProcModel item in list)
                        {
                            cmd.CommandText = item.key;
                            for (int i = 0; i < item.value.Count; i++)
                            {
                                cmd.Parameters.Add(item.value[i]);
                            }
                            count += cmd.ExecuteNonQuery();
                        }
                        tx.Commit();
                        return count >= list.Count ? 3 : 0;
                    }
                    catch (SqlException e)
                    {
                        tx.Rollback();
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns>bool</returns>
        protected virtual int Delete(string sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        connection.Open();
                        int x = (int)cmd.ExecuteNonQuery();
                        return x >= 1 ? 1 : 0;
                    }
                    catch (SqlException e)
                    {
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }

        /// <summary>
        /// 删除一条数据(带参数)
        /// </summary>
        /// <param name="sql">要查询的T-SQL例如：delete user where id=@id</param>
        /// <param name="paramList">sql语句中需要的参数集合</param>
        /// <returns>bool</returns>
        protected virtual int Delete(string sql,List<SqlParameter> paramList)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.Parameters.AddRange(paramList.ToArray());
                    try
                    {
                        connection.Open();
                        cmd.Prepare();
                        int x = (int)cmd.ExecuteNonQuery();
                        return x >= 1 ? 1 : 0;
                    }
                    catch (SqlException e)
                    {
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        /// <summary>
        /// 读取一行一列数据
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns>返回 object 类型</returns>
        protected virtual object Select(string sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        connection.Open();
                        object x = cmd.ExecuteScalar();
                        return x;
                    }
                    catch (SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 读取一行一列数据(带参数)
        /// </summary>
        /// <param name="sql">要查询的T-SQL</param>
        /// <param name="paramList">sql语句中需要的参数集合</param>
        /// <returns>返回 object 类型</returns>
        protected virtual object Select(string sql,List<SqlParameter> paramList)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.Parameters.AddRange(paramList.ToArray());
                    try
                    {
                        connection.Open();
                        cmd.Prepare();
                        object x = cmd.ExecuteScalar();
                        return x;
                    }
                    catch (SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 读取多条数据
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns>返回 泛型集合</returns>
        protected virtual IList<T> SelectIn<T>(string sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, connection))
                {
                   
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    return DataSetToIList<T>(ds, 0);
                }
            }
        }
        /// <summary>
        /// 读取多条数据(存储过程)
        /// </summary>
        /// <param name="sql">存储过程名称</param>
        /// <param name="list">包含的参数如果没有为null</param>
        /// <returns>返回 泛型集合</returns>
        protected virtual IList<T> SelectIn<T>(string sql, List<SqlParameter> list)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(list.ToArray());
                command.Prepare();
                SqlDataAdapter sda = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                return DataSetToIList<T>(ds, 0);
               
            }
        }
        /// <summary>
        /// 读取一行数据
        /// </summary>
        /// <param name="sql">存储过程名称</param>
        /// <param name="list">包含的参数如果没有为null</param>
        /// <returns>泛型集合实体</returns>
        protected virtual T SelectByObject<T>(string sql,List<SqlParameter> list)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand(sql, connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(list.ToArray());
                command.Prepare();
                SqlDataAdapter sda = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                sda.Fill(ds);

                return DataSetToT<T>(ds, 0);
            }
        }
        /// <summary>
        /// 读取一行数据
        /// </summary>
        /// <typeparam name="T">泛型T-SQL</typeparam>
        /// <param name="sql">要查询的T-SQL</param>
        /// <returns>泛型集合实体</returns>
        protected virtual T SelectByObject<T>(string sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, connection))
                {
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    return DataSetToT<T>(ds, 0);
                }
            }
        }
        /// <summary>
        /// 操作多条数据的方法
        /// </summary>
        /// <param name="sql">要执行的T-SQL集合</param>
        /// <returns>执行数据的条数(int)</returns>
        protected virtual int Operate(IList<string> sql)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    connection.Open();
                    cmd.Connection = connection;
                    SqlTransaction tx = connection.BeginTransaction();
                    cmd.Transaction = tx;
                    try
                    {
                        int count = 0;
                        for (int n = 0; n < sql.Count; n++)
                        {
                            string strsql = sql[n];
                            if (strsql.Trim().Length > 1)
                            {
                                cmd.CommandText = strsql;
                                count += cmd.ExecuteNonQuery();
                            }
                        }
                        tx.Commit();
                        return count >= sql.Count ? 3 : 0;
                    }
                    catch (SqlException e)
                    {
                        tx.Rollback();
                        connection.Close();
                        Console.WriteLine(e.Message);
                        return -1;
                    }
                }
            }
        }
        #endregion

        #region 泛型和DataSet互换方法

        /// <summary>
        /// DataSet转换为泛型集合
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="p_DataSet">DataSet</param>
        /// <param name="p_TableIndex">待转换数据表索引</param>
        /// <returns>泛型集合</returns>
        private static IList<T> DataSetToIList<T>(DataSet p_DataSet, int p_TableIndex)
        {
            if (p_DataSet == null || p_DataSet.Tables.Count < 0)
                return null;
            if (p_TableIndex > p_DataSet.Tables.Count - 1)
                return null;
            if (p_TableIndex < 0)
                p_TableIndex = 0;
            if (p_DataSet.Tables[p_TableIndex].Rows.Count <= 0)
                return null;

            DataTable p_Data = p_DataSet.Tables[p_TableIndex];
            // 返回值初始化
            IList<T> result = new List<T>();
            for (int j = 0; j < p_Data.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值
                        if (pi.Name.ToLower().Equals(p_Data.Columns[i].ColumnName.ToLower()))
                        {
                            // 数据库NULL值单独处理
                            if (p_Data.Rows[j][i] != DBNull.Value)
                            {
                                pi.SetValue(_t, p_Data.Rows[j][i], null);
                            }
                            else
                                pi.SetValue(_t, null, null);
                            break;
                        }
                    }
                }
                result.Add(_t);
            }
            return result;
        }

        /// <summary>
        /// DataSet转换为实体类
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="p_DataSet">DataSet</param>
        /// <param name="p_TableIndex">待转换数据表索引</param>
        /// <returns>实体类</returns>
        private static T DataSetToT<T>(DataSet p_DataSet, int p_TableIndex)
        {
            if (p_DataSet == null || p_DataSet.Tables.Count < 0)
                return default(T);
            if (p_TableIndex > p_DataSet.Tables.Count - 1)
                return default(T);
            if (p_TableIndex < 0)
                p_TableIndex = 0;
            if (p_DataSet.Tables[p_TableIndex].Rows.Count <= 0)
                return default(T);

            DataTable p_Data = p_DataSet.Tables[p_TableIndex];
            // 返回值初始化
            IList<T> result = new List<T>();
            for (int j = 0; j < p_Data.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值
                        if (pi.Name.ToLower().Equals(p_Data.Columns[i].ColumnName.ToLower()))
                        {
                            // 数据库NULL值单独处理
                            if (p_Data.Rows[j][i] != DBNull.Value)
                            {
                                pi.SetValue(_t, p_Data.Rows[j][i], null);
                            }
                            else
                            {
                                pi.SetValue(_t, null, null);
                            }
                            break;
                        }
                    }
                }
                result.Add(_t);
            }
            return result[0];
        }

        #endregion

        #region 查询删除字符串拼接
        /// <summary>
        /// SQL语句的拼接
        /// </summary>
        /// <typeparam name="T">需要拼接SQL的实体名</typeparam>
        /// <param name="sql">T-SQL</param>
        /// <param name="_t">实体名</param>
        /// <returns>返回一个完整的SQL语句</returns>
        protected virtual string GetSqlBySelectData<T>(string sql, T _t)
        {
            StringBuilder sb = new StringBuilder(sql);
          //  SqlCommand comm = new SqlCommand();
            //comm.Parameters.AddRange(new List<string>().ToArray());
            char[] arr = { ' ', 'd', 'n', 'a' };
            try
            {
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (pi.GetValue(_t, null) != null)
                    {
                        if (pi.PropertyType.Name.Equals("Int32") || pi.PropertyType.Name.Equals("Double"))
                            sb.Append(pi.Name + "=" + pi.GetValue(_t, null) + " and ");
                        else
                            sb.Append(pi.Name + "='" + pi.GetValue(_t, null) + "' and ");
                    }

                }
                return sb.ToString().Trim(arr);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return sb.ToString().Trim(arr);
            }
        }
        #endregion

        #region  添加数据的SQL拼接
        /// <summary>
        /// 添加数据的SQL拼接(例如 insert into jobs values)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="_t"></param>
        /// <returns>返回一个完整的添加SQL语句</returns>
        protected virtual string GetSqlByAddData<T>(string sql, T _t)
        {
            try
            {
                char[] arr = { ' ', ',' };
                StringBuilder sb = new StringBuilder(sql);
                sb.Append("(");
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (pi.GetValue(_t, null) != null)
                    {
                        if (pi.PropertyType.Name.Equals("Int32") || pi.PropertyType.Name.Equals("Double"))
                        {
                            sb.Append(pi.GetValue(_t, null) + " , ");
                        }
                        else
                        {
                            string value = pi.GetValue(_t, null).ToString();

                            for (int i = 0; i < value.Length; i++)
                            {
                                int index1 = value.IndexOf("'");
                                int index2 = value.IndexOf('"');

                                if (index1 >= 0)
                                    value = value.Replace("'", "");
                                if (index2 >= 0)
                                    value = value.Replace('"', ' ');
                            }

                            sb.Append("'" + value + "' , ");
                        }
                    }
                    else
                    {
                        sb.Append("null");
                    }

                }
                string sum = sb.ToString().Trim(arr);
                sum += ")";
                return sum;
            }
            catch (Exception)
            {
                return sql;
            }
        }

        #endregion

        #region  修改数据的SQL拼接
        /// <summary>
        /// 修改数据的SQL拼接(例如 update jobs set )(只返回一个不带条件的T-SQL)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="_t"></param>
        /// <returns>返回一个完整的修改SQL语句</returns>
        protected virtual string GetSqlByUpdateData<T>(string sql, T _t)
        {
            string id_name = "";
            string code_name = "";
            Guid id = Guid.Empty;
            int code = int.MinValue;
            StringBuilder sb = new StringBuilder(sql);
            char[] arr = { ' ', ',' };
            try
            {
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (pi.Name.IndexOf("_ID") < 0 && pi.Name.IndexOf("_Code") < 0)
                    {
                        if (pi.GetValue(_t, null) != null)
                        {
                            if (pi.PropertyType.Name.Equals("Int32") || pi.PropertyType.Name.Equals("Double"))
                            {
                                sb.Append(pi.Name + "=" + pi.GetValue(_t, null) + " , ");
                            }
                            else
                            {
                                string value = pi.GetValue(_t, null).ToString();

                                for (int i = 0; i < value.Length; i++)
                                {
                                    int index1 = value.IndexOf("'");
                                    int index2 = value.IndexOf('"');

                                    if (index1 >= 0)
                                        value = value.Replace("'", "☆");
                                    if (index2 >= 0)
                                        value = value.Replace('"', '★');
                                }

                                sb.Append(pi.Name + "='" + value + "' , ");
                            }
                        }
                    }
                    else
                    {
                        if (pi.Name.IndexOf("_ID") > 0)
                        {
                            id_name = pi.Name;
                            id = (Guid)pi.GetValue(_t, null);
                        }
                        else
                        {
                            code_name = pi.Name;
                            code = (int)pi.GetValue(_t, null);
                        }
                    }
                }
                string sum = sb.ToString().Trim(arr) + "where 1=1 ";
                if (id != Guid.Empty)
                    sum += "and " + id_name + "='" + id + "'";
                if (code > 0)
                    sum += "and " + code_name + "=" + code;
                return sum;
            }
            catch (Exception)
            {
                return sql;
            }
        }

        #endregion

        #region 计算每周一是几号

        /// <summary>
        /// 计算每周一是几号
        /// </summary>
        /// <returns>返回DateTime对象</returns>
        public DateTime GetDateTimeDayToWeek()
        {
            try
            {
                int day = DateTime.Now.Day;
                int month = DateTime.Now.Month;
                switch (DateTime.Now.DayOfWeek.ToString().ToLower())
                {
                    case "monday":
                        return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day)));
                       
                    case "tuesday":
                        if (day - 2 < 0)
                        {
                            if (month != 1)
                                return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 0));
                            else
                                return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 0));
                        }
                        else
                            return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 1)));
                       
                    case "wednesday":
                        if (day - 3 < 0)
                        {
                            if (month != 1)
                                return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 1));
                            else
                                return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 1));
                        }
                        else
                            return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 2)));
                       
                    case "thursday":
                        if (day - 4 < 0)
                        {
                            if (month != 1)
                                return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 2));
                            else
                                return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 2));
                        }
                        else
                            return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 3)));
                       
                    case "friday":
                        if (day - 5 < 0)
                        {
                            if (month != 1)
                                return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 3));
                            else
                                return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 3));
                        }
                        else
                            return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 4)));
                       
                    case "saturday":
                        if (day - 6 < 0)
                        {
                            if (month != 1)
                                return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 4));
                            else
                                return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 4));
                        }
                        else
                            return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 5)));
                       
                    case "sunday":
                        if (day - 7 < 0)
                        {
                            if (month != 1)
                                return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 5));
                            else
                                return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 5));
                        }
                        else
                            return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 6)));
                       
                }
                return new DateTime();
            }
            catch (Exception)
            {
                return new DateTime();
            }
        }

        /// <summary>
        /// 计算当前季度的第一天是几号
        /// </summary>
        /// <returns>返回DateTime对象</returns>
        protected DateTime GetDateToQuarter()
        {
            switch (DateTime.Now.Month)
            {
                case 1:
                case 2:
                case 3:
                    return new DateTime(DateTime.Now.Year, 1, (1));
                case 4:
                case 5:
                case 6:
                    return new DateTime(DateTime.Now.Year, 4, (1));
                case 7:
                case 8:
                case 9:
                    return new DateTime(DateTime.Now.Year, 7, (1));
                case 10:
                case 11:
                case 12:
                    return new DateTime(DateTime.Now.Year, 10, (1));
            }
            return new DateTime();
        }
        /// <summary>
        /// 计算上个月是几号
        /// </summary>
        /// <returns>返回DateTime对象</returns>
        protected DateTime GetDateToMonth()
        {
            int month = DateTime.Now.Month;
            if (month == 1)
                return new DateTime(DateTime.Now.Year - 1, 12, 1);
            else
                return new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, 1);
        }

        protected DateTime GetDateToMonthLast()
        {
            int month = DateTime.Now.Month;
            if (month == 1)
                return new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year - 1, 12));
            else
                return new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year - 1, DateTime.Now.Month - 1));
        }
        /// <summary>
        /// 计算上两个月是几号
        /// </summary>
        /// <returns>返回DateTime对象</returns>
        protected DateTime GetDateToMonthTwo()
        {
            int month = DateTime.Now.Month;
            if (month == 2)
                return new DateTime(DateTime.Now.Year - 1, 12, 1);
            else if(month == 1)
                return new DateTime(DateTime.Now.Year - 1, 11, 1);
            else
                return new DateTime(DateTime.Now.Year, DateTime.Now.Month - 2, 1);
        }
        #endregion

        #endregion
    }
}