C# SQL 辅助工具

{/// <summary>/// sql 辅助工具/// </summary>public class SqlStructureHelps{#region 增删改查/// <summary>/// 截断/// </summary>/// <typeparam name="T"></typeparam>/// <returns></returns>public static string TruncateTable<T>(){try{Type type = typeof(T);var tableName = GetClassName(type);return GetSql(SqlType.TruncateTable, tableName);}catch (Exception ex){throw ex;}}/// <summary>/// 修改/// </summary>/// <typeparam name="T"></typeparam>/// <param name="condition">修改条件 (1=1and2=2)</param>/// <param name="param">更新字段</param>/// <returns></returns>public static string Update<T>(string condition = "", List<string> param = null){try{Type type = typeof(T);var tableName = GetClassName(type);var conditionStr = "";if (!string.IsNullOrEmpty(condition)){conditionStr += " WHERE " + condition;}List<string> arrStr = new List<string>();if (param != null && param.Count() > 0){arrStr = param.ToList();}else{var columns = GetTableNames(type, param);arrStr = columns.Select(x => x.name).ToList();}return GetSql(SqlType.Update, tableName, arrStr, conditionStr);}catch (Exception ex){throw ex;}}/// <summary>/// 新增/// </summary>/// <typeparam name="T"></typeparam> /// <param name="param">新增字段</param>/// <returns></returns>public static string Add<T>(List<string> param = null){try{Type type = typeof(T);var tableName = GetClassName(type);List<string> arrStr = new List<string>();if (param != null && param.Count() > 0){arrStr = param.ToList();}else{var columns = GetTableNames(type, param);arrStr = columns.Select(x => x.name).ToList();}return GetSql(SqlType.Insert, tableName, arrStr);}catch (Exception ex){throw ex;}}/// <summary>/// 删除/// </summary>/// <typeparam name="T"></typeparam>/// <param name="condition">删除条件 (1=1and2=2)</param>/// <returns></returns>public static string Delete<T>(string condition = ""){try{Type type = typeof(T);var tableName = GetClassName(type);var conditionStr = "";if (!string.IsNullOrEmpty(condition)){conditionStr += " WHERE " + condition;}return GetSql(SqlType.Delete, tableName, null, conditionStr);}catch (Exception ex){throw ex;}}/// <summary>/// 查询/// </summary>/// <typeparam name="T"></typeparam>/// <param name="condition">查询条件 (1=1and2=2)</param>/// <param name="v1">当前页</param>/// <param name="v2">条数</param>/// <param name="param">查询指定字段</param>/// <returns></returns>public static string Query<T>(string condition = "", int v1 = 0, int v2 = 0, List<string> param = null){try{// 查询的列var arrStr = new List<string>();// 条件var conditionStr = "";if (!string.IsNullOrEmpty(condition)){conditionStr += " WHERE " + condition;}if (v1 > 0 && v2 > 0){conditionStr += string.Format(@" LIMIT {1} OFFSET ({0} - 1) * {1}", v1, v2);}Type type = typeof(T);var tableName = GetClassName(type);if (param != null && param.Count() > 0){arrStr = param.ToList();}else{var columns = GetTableNames(type, param);arrStr = columns.Select(x => x.name).ToList();}return GetSql(SqlType.Select, tableName, arrStr, conditionStr);}catch (Exception ex){throw ex;}}/// <summary>/// 获取sql/// </summary>/// <param name="sqlType">类型</param>/// <param name="table">表名</param>/// <param name="columns">新增字段 / 更新字段  / 查询字段(默认全部)</param>/// <param name="condition">更新字段条件 / 查询条件 / 删除条件</param>/// <returns></returns>private static string GetSql(SqlType sqlType, string table, List<string> columns = null, string condition = ""){var s = "";switch (sqlType){case SqlType.Insert:if (columns == null || columns.Count < 1){throw new Exception("新增字段为空");}else{s += string.Format(@"INSERT INTO {0} ({1}) VALUES ({2});", table, string.Join(",", columns), string.Join(",", columns.Select(x => "@" + x)));}break;case SqlType.Delete:s += string.Format(@"DELETE FROM {0} {1};", table, condition);break;case SqlType.Update:if (columns == null || columns.Count < 1){throw new Exception("更新字段为空");}s += string.Format(@"UPDATE {0} SET {1} {2};", table, string.Join(",", columns.Select(x => x + "=@" + x)), condition);break;case SqlType.Select:if (columns == null || columns.Count < 1){s += string.Format(@"SELECT * FROM {0} {1};", table, condition);}else{s += string.Format(@"SELECT {1} FROM {0} {2};", table, string.Join(",", columns), condition);}break;case SqlType.TruncateTable:s += string.Format(@"TRUNCATE TABLE {0};", table);break;}return s;}/// <summary>/// 获取表名,默认为类名/// </summary>/// <param name="type"></param>/// <returns></returns>private static string GetClassName(Type type){var name = type.Name;object[] attrClassName = type.GetCustomAttributes(typeof(MyIsStructureAttribute), true);if (attrClassName != null && attrClassName.Length > 0){var myIsStructureAttribute = (MyIsStructureAttribute)attrClassName.First();name = myIsStructureAttribute.TableName;}return name;}/// <summary>/// 获取字段集合/// </summary>/// <param name="type"></param>/// <param name="obj"></param>/// <returns></returns>/// <exception cref="Exception"></exception>private static List<Column> GetTableNames(Type type, object obj = null){var list = new List<Column>();PropertyInfo[] properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);if (properties.Length > 0){foreach (PropertyInfo property in properties){if (property.CanWrite && property.CanRead){var a = property.GetCustomAttributes(typeof(MyIsStructureAttribute), true);var isNext = true;if (a != null && a.Length > 0){var temp = (MyIsStructureAttribute)a.First();isNext = temp.IsStructure;}if (isNext){var tempuu = new Column();tempuu.name = property.Name;tempuu.type = property.GetMethod.ReturnType.Name;if (obj != null){tempuu.value = property.GetMethod.Invoke(obj, null);}list.Add(tempuu);}}}}else{throw new Exception("未查询到字段");}return list;}#endregion#region 扩展/// <summary>/// 获取所有的表信息/// </summary>/// <param name="sqlTypes"></param>/// <param name="database"></param>/// <returns></returns>public static string GetTable(SqlTypes sqlTypes, string database = ""){try{string sql = "";switch (sqlTypes){case SqlTypes.Mysql:if (string.IsNullOrEmpty(database)) throw new Exception("数据库名称不能为空");sql = string.Format(@"SELECT TABLE_NAME AS `TableName`, TABLE_COMMENT AS `TableExegesis` FROM information_schema.TABLES   WHERE   TABLE_SCHEMA = '{0}' ORDER BY   TABLE_NAME;", database);break;case SqlTypes.SqlServer:sql = string.Format(@"SELECT t.name AS TableName, ep.value AS TableExegesis  FROM  sys.tables t LEFT JOIN sys.extended_properties ep ON t.object_id = ep.major_id AND ep.minor_id = 0  AND ep.name = 'MS_Description' AND ep.class = 1 WHERE t.type = 'U'  AND t.is_ms_shipped = 0  ORDER BY  t.name;");break;default:throw new Exception("未扩展的数据库");}return sql;}catch (Exception ex){throw ex;}}/// <summary>/// 获取所有的列信息/// </summary>/// <param name="sqlTypes"></param>/// <param name="database"></param>/// <param name="tablename"></param>/// <returns></returns>public static string GetColumn(SqlTypes sqlTypes, string database, string tablename){try{string sql = "";switch (sqlTypes){case SqlTypes.Mysql:sql = string.Format(@"SELECT   COLUMN_NAME AS `columnName`,  COLUMN_COMMENT AS `columnExegesis`,  DATA_TYPE AS `columnType`,  CHARACTER_MAXIMUM_LENGTH AS `columnMax` FROM     information_schema.COLUMNS   WHERE   TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'; ", database, tablename);break;case SqlTypes.SqlServer:sql = string.Format(@"SELECT   c.name AS columnName,  ty.name AS columnType,  CASE   WHEN ty.name IN ('nchar', 'nvarchar', 'ntext') THEN c.max_length / 2  WHEN ty.name IN ('text') THEN -1 ELSE c.max_length    END AS columnMax,   ep.value AS columnExegesis  FROM  sys.columns c INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id LEFT JOIN sys.extended_properties ep ON c.object_id = ep.major_id    AND c.column_id = ep.minor_id AND ep.name = 'MS_Description'  WHERE c.object_id = OBJECT_ID('{0}.dbo.{1}'); ", database, tablename);break;default:throw new Exception("未扩展的数据库");}return sql;}catch (Exception ex){throw ex;}}/// <summary>/// mysql 类型  转 c# 类型/// </summary>/// <param name="mysqlType"></param>/// <returns></returns>/// <exception cref="NotSupportedException"></exception>/// <exception cref="ArgumentException"></exception>public static Type MySqlTypeToCSharpType(string mysqlType){switch (mysqlType.ToLowerInvariant()){case "int":case "tinyint":case "smallint":case "mediumint":case "bigint":case "bigint unsigned":case "int unsigned":return typeof(int);case "float":case "double":case "decimal":case "numeric":return typeof(double);case "varchar":case "char":case "text":case "tinytext":case "mediumtext":case "longtext":return typeof(string);case "datetime":case "timestamp":case "date":case "time":case "year":return typeof(DateTime);case "blob":case "tinyblob":case "mediumblob":case "longblob":case "binary":case "varbinary":return typeof(byte[]);case "json":return typeof(string);case "bit":return typeof(bool);default:throw new ArgumentException($"MySQL type: {mysqlType}");}}/// <summary>/// sqlserver类型  转 c#类型/// </summary>/// <param name="sqlType"></param>/// <returns></returns>/// <exception cref="ArgumentException"></exception>public static Type SqlServerTypeToCSharpType(string sqlType){switch (sqlType.ToLowerInvariant()){case "int":case "smallint":case "tinyint":return typeof(int);case "bigint":return typeof(long);case "bit":return typeof(bool);case "decimal":case "numeric":return typeof(decimal);case "float":return typeof(float);case "real":return typeof(float);case "money":case "smallmoney":return typeof(decimal);case "char":case "nchar":case "varchar":case "nvarchar":case "text":case "ntext":return typeof(string);case "datetime":case "smalldatetime":case "date":case "time":case "datetime2":case "datetimeoffset":return typeof(DateTime);case "binary":case "varbinary":case "varbinary(max)":return typeof(byte[]);case "uniqueidentifier":return typeof(Guid);case "sql_variant":return typeof(object);case "xml":return typeof(string);default:throw new ArgumentException($"SQL Server type: {sqlType}");}}#endregion}#region 内部辅助使用/// <summary>/// 类型/// </summary>enum SqlType{/// <summary>/// 增加/// </summary>Insert,/// <summary>/// 删除/// </summary>Delete,/// <summary>/// 更新/// </summary>Update,/// <summary>/// 查询/// </summary>Select,/// <summary>/// 截断/// </summary>TruncateTable}/// <summary>/// 列/// </summary>class Column{/// <summary>/// 名称/// </summary>public string name { get; set; }/// <summary>/// 值/// </summary>public object value { get; set; }/// <summary>/// 类型/// </summary>public string type { get; set; }}#endregion#region 外部配合使用/// <summary>/// 自定义属性/// </summary>public class MyIsStructureAttribute : Attribute{/// <summary>/// /// </summary>/// <param name="isStructure"></param>public MyIsStructureAttribute(bool isStructure = false){IsStructure = isStructure;}/// <summary>/// /// </summary>/// <param name="tableName"></param>public MyIsStructureAttribute(string tableName){TableName = tableName;}/// <summary>/// 是否为表结构中的数据/// </summary>public bool IsStructure { get; }/// <summary>/// 表名称/// </summary>public string TableName { get; }}/// <summary>/// 数据库类型/// </summary>public enum SqlTypes{/// <summary>/// mysql/// </summary>Mysql,/// <summary>/// SqlServer/// </summary>SqlServer}/// <summary>/// 表/// </summary>public class Tables{/// <summary>/// 名称/// </summary>public string TableName { get; set; }/// <summary>/// 注释/// </summary>public string TableExegesis { get; set; }}/// <summary>/// 列/// </summary>public class Columns{/// <summary>/// 名称/// </summary>public string columnName { get; set; }/// <summary>/// 注释/// </summary>public string columnExegesis { get; set; }/// <summary>/// 类型/// </summary>public string columnType { get; set; }/// <summary>/// 最大长度/// </summary>public string columnMax { get; set; }}#endregion}