/// <summary>
/// sql 辅助工具
/// </summary>
public class SqlStructureHelps
{
/// <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;
}
return GetSql(SqlType.Update,tableName,param,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);
return GetSql(SqlType.Insert,tableName,param);
}
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("新增字段为空");
}
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;
}
}
/// <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; }
}
/// <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; }
}