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

}