C# SQL 辅助工具

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