MySQL实体类转SQL脚本

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = false)]/*用于给扩展类的属性增加特性标识；如果实体类属性有此标识，则不参与脚本运算*/    public class IsExtendFieldAttribute:Attribute    {    }

public class RepUtility//说明sys_是数据库表名的前缀；HashSet是集合属性；本例只支持单表操作    {        public string Sql = "select * from {0} {1} order by {2} {3} ;";        /// <summary>        /// P0:表名，P1:查询条件，P2:排序字段名，P3:排序方式，P4:第几页，P5:每页显示记录数        /// </summary>        public string PageSql = "select * from {0} {1} order by {2} {3} limit {4},{5};";        /*         * Mysql分页：            select * from tableName where 条件 limit (当前页码-1)*页面容量 , 页面容量         */        #region 注意此方法不通用，可根据相应场景进行调整        /// <summary>        /// 不通用，还在整理中；根据实体类对象生成查询条件        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="instance"></param>        /// <returns></returns>        public string GetCondition<T>(T instance) where T : class        {            if (instance.IsNull())                return string.Empty;            StringBuilder builder = new StringBuilder(" where 1=1");            Type objType = typeof(T);            var properties = objType.GetProperties();            foreach (var property in properties)            {                if (property.IsNotNull() && property.Name.Contains("sys_").IsFalse())                {                    object[] objAttrs = property.GetCustomAttributes(typeof(IsExtendFieldAttribute), true);                    if (objAttrs.IsNotNull() && objAttrs.Length > 0)                    { }                    else                    {                        var value = property.GetValue(instance);                        if (value.IsNotNull())                        {                            if (value.ToString().Trim() == string.Empty || value.ToString().Trim() == "0")                                continue;                            if (value.GetType().Name.Contains("HashSet"))                                continue;                            builder.Append(string.Format(" and {0}='{1}'", property.Name, value));                        }                    }                }            }            return builder.ToString();        }        /// <summary>        /// 根据实体类对象生成更新脚本        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="instance"></param>        /// <param name="key">主键，这里单独把可以作为参数是为了更方便扩展</param>        /// <returns></returns>        public string GetUpdateSql<T>(T instance, string key) where T : class        {            if (instance.IsNull())                return string.Empty;            StringBuilder builder = new StringBuilder();            Type objType = typeof(T);            builder.AppendLine("update " + objType.Name + " set");            var properties = objType.GetProperties();            foreach (var property in properties)            {                if (property.Name.Contains("sys_").IsFalse())                {                    object[] objAttrs = property.GetCustomAttributes(typeof(IsExtendFieldAttribute), true);                    if (objAttrs.IsNotNull() && objAttrs.Length > 0)                    { }                    else                    {                        if (property.IsNotNull() && property.Name.ToLower() != key.ToLower())                        {                            var value = property.GetValue(instance);                            //如果是int Not null类型的需要过滤默认值是0的字段                            if (value.IsNotNull() && value.ConverToString().IsNullOrEmpty().IsFalse() && value.ToString().Trim() != "0")                            {                                builder.AppendLine(string.Format("{0}='{1}',", property.Name, value));                            }                        }                    }                }            }            builder.Remove(builder.Length - 3, 3);//移除最有一个逗号和换行符，\r\n占两位；"\r\n".Length=2;            builder.AppendLine(string.Format("where {0}='{1}';", key, objType.GetProperty(key).GetValue(instance)));            return builder.ToString();        }        /// <summary>        /// 根据实体类对象生成插入脚本        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="instance"></param>        /// <param name="key"></param>        /// <returns></returns>        public string GetAddSql<T>(T instance, string key) where T : class        {            if (instance.IsNull())                return string.Empty;            Type objType = typeof(T);            var properties = objType.GetProperties();            var tmpProperties = properties.Where(t => t.Name.ToLower() != key.ToLower()).ToList();            //1.获得不为NULL的字段、字段值            Dictionary<string, object> dict = new Dictionary<string, object>();            foreach (var property in tmpProperties)            {                if (property.Name.Contains("sys_").IsFalse())                {                    object[] objAttrs = property.GetCustomAttributes(typeof(IsExtendFieldAttribute), true);                    if (objAttrs.IsNotNull() && objAttrs.Length > 0)/*有特性标识则不参与脚本运算*/                    { }                    else                    {                        var value = property.GetValue(instance);                        if (value.IsNull())                            continue;                        dict.Add(property.Name, value);                    }                }            }            //2.生成列            StringBuilder builder = new StringBuilder();            builder.AppendLine("insert into " + objType.Name + "(");            string tmp = string.Empty;            foreach (var item in dict)            {                tmp += item.Key + ",";            }            tmp = tmp.TrimEnd(new char[] { ',' }) + ") values(";            builder.AppendLine(tmp);            //3.生成值            tmp = string.Empty;            foreach (var item in dict)            {                tmp += "'" + item.Value.ToString() + "',";            }            tmp = tmp.TrimEnd(new char[] { ',' }) + ");";            builder.AppendLine(tmp);            //4.返回            return builder.ToString();        }        /// <summary>        /// 拼接查询脚本        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="orderby"></param>        /// <param name="sort"></param>        /// <param name="where"></param>        /// <returns></returns>        public string GetSql<T>(string orderby = "id", string sort = "asc", string where = " where 1=1 ") where T : class        {            return string.Format(Sql, typeof(T).Name, where, orderby, sort);        }        #endregion        /// <summary>        /// 拼接查询数量的脚本        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="where"></param>        /// <returns></returns>        public string GetCountSql<T>(string where = "where 1=1") where T : class        {            string tbName = typeof(T).Name;            string safeSql = string.Format("select count(*) as count from {0} {1} ;", tbName, where);            return safeSql;        }        /// <summary>        /// 拼接分页的脚本        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="where"></param>        /// <param name="orderby"></param>        /// <param name="sort"></param>        /// <param name="pageCurrentIndex"></param>        /// <param name="pageSize"></param>        /// <returns></returns>        public string GetPageSql<T>(string where = "where 1=1", string orderby = "id", string sort = "asc", int pageCurrentIndex = 0, int pageSize = 10) where T : class        {            string tbName = typeof(T).Name;            string safeSql = string.Format(PageSql, tbName, where, orderby, sort, (pageCurrentIndex - 1) * pageSize, pageSize);            return safeSql;        }        #region 可用列表        /// <summary>        /// 根据参数生成查询条件脚本        /// </summary>        /// <param name="list">List<Tuple<字段, 操作符, 参数名,参数值>></param>        /// <returns></returns>        public string GetConditionWithParam(List<Tuple<string, string, string, string>> listPara, char flag = '?')        {            if (listPara.IsNull() || listPara.Count == 0)                return " where 1=1";            StringBuilder builder = new StringBuilder();            builder.AppendLine(" where 1=1");            foreach (var item in listPara)            {                // and id=?id//and id=@id                builder.AppendLine(string.Format(" and {0}{1}{2}{3}", item.Item1, item.Item2, flag, item.Item3));            }            return builder.ToString();        }        /// <summary>        /// 根据实体类对象生成参数化的更新脚本        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="instance"></param>        /// <param name="key"></param>        /// <param name="listPara"></param>        /// <param name="flag"></param>        /// <returns></returns>        public string GetUpdateSql<T>(T instance, string key, ref List<Tuple<string, string, string, object>> listPara, char flag = '?') where T : class        {            if (instance.IsNull())                return string.Empty;            StringBuilder builder = new StringBuilder();            Type objType = typeof(T);            builder.AppendLine("update " + objType.Name + " set");            var properties = objType.GetProperties().Where(p => p.Name.ToLower() != key.ToLower() && p.Name.ToLower().Contains("sys_") == false).ToList();            foreach (var property in properties)            {                if (property.Name.Contains("sys_").IsFalse())                {                    object[] objAttrs = property.GetCustomAttributes(typeof(IsExtendFieldAttribute), true);                    if (objAttrs.IsNotNull() && objAttrs.Length > 0)                    { }                    else                    {                        if (property.IsNotNull() && property.Name.ToLower() != key.ToLower())                        {                            var value = property.GetValue(instance);                            //如果是int Not null类型的需要过滤默认值是0的字段                            if (value.IsNotNull() && value.ConverToString().IsNullOrEmpty().IsFalse() && value.ToString().Trim() != "0")                            {                                builder.AppendLine(string.Format("{0}={1}{2},", property.Name, flag, property.Name));                                listPara.Add(new Tuple<string, string, string, object>(property.Name, "=", property.Name, value));                            }                        }                    }                }            }            builder.Remove(builder.Length - 3, 1);//移除最有一个逗号，\r\n占两位；"\r\n".Length=2;            builder.AppendLine(string.Format("where {0}={1}{2};", key, flag, key));            listPara.Add(new Tuple<string, string, string, object>(key, "=", flag + key, objType.GetProperty(key).GetValue(instance)));            return builder.ToString();        }        /// <summary>        /// 根据实体类对象生成参数化的插入脚本        /// </summary>        /// <typeparam name="T"></typeparam>        /// <param name="instance"></param>        /// <param name="key"></param>        /// <param name="listPara"></param>        /// <param name="flag"></param>        /// <returns></returns>        public string GetAddSql<T>(T instance, string key, ref List<Tuple<string, string, string, object>> listPara, char flag = '?') where T : class        {            if (instance.IsNull())                return string.Empty;            Type objType = typeof(T);            var properties = objType.GetProperties();            var tmpProperties = properties.Where(p => p.Name.ToLower() != key.ToLower() && p.Name.Contains("sys_") == false).ToList();            //1.获得不为NULL的字段、字段值            Dictionary<string, object> dict = new Dictionary<string, object>();            foreach (var property in tmpProperties)            {                if (property.Name.Contains("sys_").IsFalse())                {                    object[] objAttrs = property.GetCustomAttributes(typeof(IsExtendFieldAttribute), true);                    if (objAttrs.IsNotNull() && objAttrs.Length > 0)                    { }                    else                    {                        var value = property.GetValue(instance);                        if (value.IsNull())                            continue;                        dict.Add(property.Name, value);                    }                }            }            //2.生成列            StringBuilder builder = new StringBuilder();            builder.AppendLine("insert into " + objType.Name + "(");            string tmp = string.Empty;            foreach (var item in dict)            {                tmp += item.Key + ",";            }            tmp = tmp.TrimEnd(new char[] { ',' }) + ") values(";            builder.AppendLine(tmp);            //3.生成值            tmp = string.Empty;            foreach (var item in dict)            {                tmp = tmp + string.Format("{0}{1},", flag, item.Key);                listPara.Add(new Tuple<string, string, string, object>(item.Key, "=", flag + item.Key, item.Value));            }            tmp = tmp.TrimEnd(new char[] { ',' }) + ");";            builder.AppendLine(tmp);            //4.返回            return builder.ToString();        }        #endregion    }