实体转SQL

单表插入SQL

方式1

     #region 

        /// <summary>
        /// 根据实体数据获取插入SQL
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="tableName">表名称</param>
        /// <param name="model">实体</param>
        /// <returns></returns>
        public static string GetInsertSqlByModel<T>(string tableName, T model)
        {
            List<string> columnList = new List<string>();
            List<string> valueList = new List<string>();
            System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties();
            foreach (System.Reflection.PropertyInfo property in properties)
            {
                string value = property.GetValue(model, null).ToString().Trim();
                //处理特殊符号
                if (value.Contains("'"))
                {
                    value = value.Replace("'", "");
                }
                string name = property.Name;
                if (value != null && value.Length > 0)
                {
                    columnList.Add(name);
                    valueList.Add($"'{value}'");
                }
            }
            string sql = $"Insert into {tableName}( {string.Join(",", columnList)} ) Values ({string.Join(",", columnList)})";

            return sql;
        }

        /// <summary>
        /// 根据实体集合数据获取批量存入SQL
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="tableName">表名称</param>
        /// <param name="modelList">实体集合</param>
        /// <returns></returns>
        public static string GetInsertSqlByModel<T>(string tableName, List<T> modelList)
        {
            List<string> columnList = new List<string>();
            List<string> valueList = new List<string>();
            //取列
            System.Reflection.PropertyInfo[] columnProperties = modelList[0].GetType().GetProperties();
            foreach (System.Reflection.PropertyInfo property in columnProperties)
            {
                
                string name = property.Name;
                columnList.Add(name);
            }

            //取值
            foreach(T model in modelList)
            {
                List<string> modelvalue = new List<string>();
                System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties();
                foreach (System.Reflection.PropertyInfo property in properties)
                {
                    string value = property.GetValue(modelList, null).ToString().Trim();
                    //处理特殊符号
                    if (value.Contains("'"))
                    {
                        value = value.Replace("'", "");
                    }
                    if(value==null)
                    {
                        modelvalue.Add($"null");
                    }
                    else
                    {
                        modelvalue.Add($"'{value}'");
                    }
                   
                }

                valueList.Add($"({string.Join(",", modelvalue)})");
            }

            string sql = $"Insert into {tableName}( {string.Join(",", columnList)} ) Values {string.Join(",", valueList)}";

            return sql;
        }



        /// <summary>
        /// 根据实体数据获取更新SQL
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="tableName">表名称</param>
        /// <param name="model">实体</param>
        /// <returns></returns>
        public static string GetUpdateSqlByModel<T>(string tableName, T model, List<string> whereNameList)
        {
            List<string> setValueList = new List<string>();
            List<string> whereList = new List<string>();
            System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties();
            foreach (System.Reflection.PropertyInfo property in properties)
            {
                string name = property.Name;
                string value = property.GetValue(model, null).ToString().Trim();
                //处理特殊符号
                if (value.Contains("'"))
                {
                    value = value.Replace("'", "");
                }


                if (whereList.Contains(name))
                {
                    whereList.Add($"{name}='{value}'");
                }
                else
                {
                    if (value != null && value.Length > 0)
                    {
                        setValueList.Add($"{name}='{value}',");
                    }
                }

            }
           

            string sql = $"Update {tableName} set {string.Join(",", setValueList)} where {string.Join(" and ", setValueList)}";

            return sql;
        }
        #endregion

 

 方式2

  /// <summary>
        /// 根据实体数据获取插入SQL
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="tableName">表名称</param>
        /// <param name="model">实体</param>
        /// <returns></returns>
        public static string GetInsertSqlByModel<T>(string tableName, T model)
        {
            StringBuilder sbInsert = new StringBuilder($"Insert into {tableName}( ");
            StringBuilder sbValues = new StringBuilder($" Values ( ");
            System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties();
            foreach (System.Reflection.PropertyInfo property in properties)
            {
                string value = property.GetValue(model, null).ToString().Trim();
                //处理特殊符号
                if (value.Contains("'"))
                {
                    value = value.Replace("'", "");
                }
                string name = property.Name;
                if (value != null && value.Length > 0)
                {
                    sbInsert.Append(name + ",");
                    sbValues.Append($"'{value}',");
                }
            }
            sbInsert.Remove(sbInsert.Length - 1, 1);
            sbValues.Remove(sbValues.Length - 1, 1);
            sbInsert.Append(")");
            sbValues.Append(" )");
            string insertSql = sbInsert.ToString() + sbValues.ToString();

            return insertSql.ToString();
        }


        /// <summary>
        /// 根据实体数据获取更新SQL
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="tableName">表名称</param>
        /// <param name="model">实体</param>
        /// <returns></returns>
        public static string GetUpdateSqlByModel<T>(string tableName, T model, List<string> whereList)
        {
            StringBuilder sbUpdate = new StringBuilder($"Update {tableName} set ");
            StringBuilder sbWheres = new StringBuilder($"where 1=1 ");
            System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties();
            foreach (System.Reflection.PropertyInfo property in properties)
            {
                string name = property.Name;
                string value = property.GetValue(model, null).ToString().Trim();
                //处理特殊符号
                if(value.Contains("'"))
                {
                    value = value.Replace("'", "");
                }
               

                if (whereList.Contains(name))
                {
                    sbWheres.Append($" and {name}='{value}'");
                }
                else
                {
                    if (value != null && value.Length > 0)
                    {
                        sbUpdate.Append($"{name}='{value}',");

                    }
                }

            }
            sbUpdate.Remove(sbUpdate.Length - 1, 1);
           
            string insertSql = sbUpdate.ToString() + sbWheres.ToString();

            return insertSql.ToString();
        }