/// <summary>

    /// 根据实体来生成批量新增、修改、删除的Sql语句

    /// 在Update和Delete

    /// </summary>

    /// <typeparam name="TEntity">实体</typeparam>

    public class EntityToSQL<TEntity> where TEntity : class

    {

        #region 根据集合获取SQL语句

        /// <summary>

        /// 执行SQL，根据SQL操作的类型

        /// </summary>

        /// <typeparam name="TEntity"></typeparam>

        /// <param name="list"></param>

        /// <param name="sqlType"></param>

        /// <returns></returns>

        /// </summary>

        /// <param name="list"></param>

        /// <param name="sqlType"></param>

        /// <returns></returns>

        public static string GetSql(IEnumerable<TEntity> list, SqlType sqlType)

        {

            return GetSql(list, sqlType, null);

        }

        /// <summary>

        /// 执行SQL，根据SQL操作的类型

        /// </summary>

        /// <typeparam name="TEntity"></typeparam>

        /// <param name="list"></param>

        /// <param name="sqlType"></param>

        /// <returns></returns>

        public static string GetSql(IEnumerable<TEntity> list, SqlType sqlType, params string[] fieldParams)

        {

            var sqlstr = new StringBuilder();

            switch (sqlType)

            {

                case SqlType.Insert:

                    list.ToList().ForEach(i =>

                    {

                        Tuple<string, object[]> sql = CreateInsertSql(i);

                        sqlstr.AppendFormat(sql.Item1, sql.Item2);

                    });

                    break;

                case SqlType.Update:

                    list.ToList().ForEach(i =>

                    {

                        Tuple<string, object[]> sql = CreateUpdateSql(i, fieldParams);

                        sqlstr.AppendFormat(sql.Item1, sql.Item2);

                    });

                    break;

                case SqlType.Delete:

                    list.ToList().ForEach(i =>

                    {

                        Tuple<string, object[]> sql = CreateDeleteSql(i);

                        sqlstr.AppendFormat(sql.Item1, sql.Item2);

                    });

                    break;

                default:

                    throw new ArgumentException("请输入正确的参数");

            }

            return sqlstr.ToString();

        }

        #endregion

        #region 构建Update语句串

        /// <summary>

        /// 构建Update语句串

        /// 注意：如果本方法过滤了int,decimal类型更新为０的列，如果希望更新它们需要指定FieldParams参数

        /// </summary>

        /// <param name="entity">实体列表</param>

        /// <param name="fieldParams">要更新的字段</param>

        /// <returns></returns>

        private static Tuple<string, object[]> CreateUpdateSql(TEntity entity, params string[] fieldParams)

        {

            if (entity == null)

                throw new ArgumentException("数据库实体不能为空");

            List<string> pkList = GetPrimaryKeyName();

            var entityType = entity.GetType();

            var tableFields = new List<PropertyInfo>();

            if (fieldParams != null && fieldParams.Count() > )

            {

                tableFields = entityType.GetProperties().Where(i => fieldParams.Contains(i.Name, StringComparer.OrdinalIgnoreCase)).ToList();

            }

            else

            {

                tableFields = entityType.GetProperties().Where(i =>

                              !pkList.Contains(i.Name)

                              && i.GetValue(entity, null) != null

                              && !i.PropertyType.IsEnum

                              && !(i.PropertyType == typeof(ValueType) && Convert.ToInt64(i.GetValue(entity, null)) == )

                              && !(i.PropertyType == typeof(DateTime) && Convert.ToDateTime(i.GetValue(entity, null)) == DateTime.MinValue)

                              && i.PropertyType != typeof(EntityState)

                              && i.GetCustomAttributes(false).Where(j => j.GetType() == typeof(NavigationAttribute)) != null//过滤导航属性

                              && (i.PropertyType.IsValueType || i.PropertyType == typeof(string))

                              ).ToList();

            }

            //过滤主键，航行属性，状态属性等

            if (pkList == null || pkList.Count() == )

                throw new ArgumentException("表实体没有主键");

            var arguments = new List<object>();

            var builder = new StringBuilder();

            foreach (var change in tableFields)

            {

                if (pkList.Contains(change.Name))

                    continue;

                if (arguments.Count != )

                    builder.Append(", ");

                builder.Append(change.Name + " = {" + arguments.Count + "}");

                if (change.PropertyType == typeof(string)

                    || change.PropertyType == typeof(DateTime)

                    || change.PropertyType == typeof(DateTime?)

                    || change.PropertyType == typeof(bool?)

                    || change.PropertyType == typeof(bool))

                    arguments.Add("'" + change.GetValue(entity, null).ToString().Replace("'", "char(39)") + "'");

                else

                    arguments.Add(change.GetValue(entity, null));

            }

            if (builder.Length == )

                throw new Exception("没有任何属性进行更新");

            builder.Insert(, " UPDATE " + string.Format("[{0}]", entityType.Name) + " SET ");

            builder.Append(" WHERE ");

            bool firstPrimaryKey = true;

            foreach (var primaryField in pkList)

            {

                if (firstPrimaryKey)

                    firstPrimaryKey = false;

                else

                    builder.Append(" AND ");

                object val = entityType.GetProperty(primaryField).GetValue(entity, null);

                Type pkType = entityType.GetProperty(primaryField).GetType();

                builder.Append(GetEqualStatment(primaryField, arguments.Count, pkType));

                arguments.Add(val);

            }

            return new Tuple<string, object[]>(builder.ToString(), arguments.ToArray());

        }

        #endregion

        #region 构建Insert语句串

        /// <summary>

        /// 构建Insert语句串

        /// 主键为自增时，如果主键值为0，我们将主键插入到SQL串中

        /// </summary>

        /// <typeparam name="TEntity"></typeparam>

        /// <param name="entity"></param>

        /// <returns></returns>

        private static Tuple<string, object[]> CreateInsertSql(TEntity entity)

        {

            if (entity == null)

                throw new ArgumentException("数据库实体不能为空");

            Type entityType = entity.GetType();

            var table = entityType.GetProperties().Where(i => i.PropertyType != typeof(EntityKey)

                 && i.PropertyType != typeof(EntityState)

                 && i.Name != "IsValid"

                 && i.GetValue(entity, null) != null

                 && !i.PropertyType.IsEnum

                 && i.GetCustomAttributes(false).Where(j => j.GetType() == typeof(NavigationAttribute)) != null

                 && (i.PropertyType.IsValueType || i.PropertyType == typeof(string))).ToArray();//过滤主键，航行属性，状态属性等

            var pkList = new List<string>();

            if (GetPrimaryKey() != null)//有时主键可能没有设计，这对于添加操作是可以的

                pkList = GetPrimaryKeyName();

            var arguments = new List<object>();

            var fieldbuilder = new StringBuilder();

            var valuebuilder = new StringBuilder();

            fieldbuilder.Append(" INSERT INTO " + string.Format("[{0}]", entityType.Name) + " (");

            foreach (var member in table)

            {

                if (pkList.Contains(member.Name) && Convert.ToString(member.GetValue(entity, null)) == "")

                    continue;

                object value = member.GetValue(entity, null);

                if (value != null)

                {

                    if (arguments.Count != )

                    {

                        fieldbuilder.Append(", ");

                        valuebuilder.Append(", ");

                    }

                    fieldbuilder.Append(member.Name);

                    if (member.PropertyType == typeof(string)

                        || member.PropertyType == typeof(DateTime)

                        || member.PropertyType == typeof(DateTime?)

                        || member.PropertyType == typeof(Boolean?)

                        || member.PropertyType == typeof(Boolean)

                        )

                        valuebuilder.Append("'{" + arguments.Count + "}'");

                    else

                        valuebuilder.Append("{" + arguments.Count + "}");

                    if (value is string)

                        value = value.ToString().Replace("'", "char(39)");

                    arguments.Add(value);

                }

            }

            fieldbuilder.Append(") Values (");

            fieldbuilder.Append(valuebuilder.ToString());

            fieldbuilder.Append(");");

            return new Tuple<string, object[]>(fieldbuilder.ToString(), arguments.ToArray());

        }

        #endregion

        #region 构建Delete语句串

        /// <summary>

        /// 构建Delete语句串

        /// </summary>

        /// <typeparam name="TEntity"></typeparam>

        /// <param name="entity"></param>

        /// <returns></returns>

        private static Tuple<string, object[]> CreateDeleteSql(TEntity entity)

        {

            if (entity == null)

                throw new ArgumentException("数据库实体不能为空");

            Type entityType = entity.GetType();

            List<string> pkList = GetPrimaryKeyName();

            if (pkList == null || pkList.Count == )

                throw new ArgumentException("表实体没有主键");

            var arguments = new List<object>();

            var builder = new StringBuilder();

            builder.Append(" Delete from " + string.Format("[{0}]", entityType.Name));

            builder.Append(" WHERE ");

            bool firstPrimaryKey = true;

            foreach (var primaryField in pkList)

            {

                if (firstPrimaryKey)

                    firstPrimaryKey = false;

                else

                    builder.Append(" AND ");

                Type pkType = entityType.GetProperty(primaryField).GetType();

                object val = entityType.GetProperty(primaryField).GetValue(entity, null);

                builder.Append(GetEqualStatment(primaryField, arguments.Count, pkType));

                arguments.Add(val);

            }

            return new Tuple<string, object[]>(builder.ToString(), arguments.ToArray());

        }

        #endregion

        #region 准备参数

        /// <summary>

        /// 获取Where条件

        /// </summary>

        /// <param name="fieldName"></param>

        /// <param name="paramId"></param>

        /// <param name="pkType"></param>

        /// <returns></returns>

        private static string GetEqualStatment(string fieldName, int paramId, Type pkType)

        {

            if (pkType.IsValueType)

                return string.Format("{0} = {1}", fieldName, GetParamTag(paramId));

            return string.Format("{0} = '{1}'", fieldName, GetParamTag(paramId));

        }

        private static string GetParamTag(int paramId)

        {

            return "{" + paramId + "}";

        }

        /// <summary>

        /// 获取标识为主键或者导航属性的属性

        /// </summary>

        /// <returns></returns>

        private static List<PropertyInfo> GetPrimaryKey()

        {

            Type entityType = typeof(TEntity);

            var retu = entityType.GetProperties().Where(x =>

            x.GetCustomAttributes().Where(j => j.GetType() == typeof(NavigationAttribute)).Count() > );

            if (retu != null && retu.Count() > )

                return retu.ToList();

            return null;

        }

        /// <summary>

        /// 获取特性的名字

        /// </summary>

        /// <returns></returns>

        private static List<string> GetPrimaryKeyName()

        {

            List<string> resu = new List<string>();

            List<PropertyInfo> list = GetPrimaryKey();

            if (list != null && list.Count > )

                resu = list.Select(x => x.Name).ToList();

            return resu;

        }

        #endregion

    }

    /// <summary>

    /// 属性的导航属性（用于标识实体的主键）

    /// </summary>

    public class NavigationAttribute : Attribute

    {

    }

    /// <summary>

    /// SQL操作类型

    /// </summary>

    public enum SqlType

    {

        Insert,

        Update,

        Delete,

    }