{
/// <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
}