C# Sql帮助类，可扩展

查看代码

[System.AttributeUsage(AttributeTargets.Class | AttributeTargets.Struct, Inherited = false, AllowMultiple = false)]
public class DbTableAttribute : Attribute
{
    public string Name { get; set; }
    public string Charset { get; set; }
    public string Collate { get; set; }
}
[System.AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]
public class DbColumnAttribute : Attribute
{
    public string Name { get; set; }
    /// <summary>
    /// <para>type[(length)] [[primary key]|[unique]] [unsigned] [zerofill] [not null or null] [default your_value] [comment 'your comment'] [collate 'your encoding'] ...etc.</para>
    /// <para>bigint auto_increment</para>
	/// <para>int default '0'</para>
	/// <para>varchar(50) null default null collate 'utf8_general_ci'</para>
    /// <para>datetime null</para>
    /// <para>datetime null default 'localtime'</para>
	/// <para>timestamp not null default current_timestamp on update current_timestamp</para>
    /// <para>bit(1) null default b'0' comment 'balabala'</para>
    ///  </summary>
    public string Desc { get; set; }
    /// <summary>
    /// Index1,Indx 2,Index3,primary key,unique
    /// </summary>
    public string Index { get; set; }
    public bool NotInsert { get; set; }
}
public abstract class DbContext<TTransaction, TConnection, TCommand, TParameter, TDataAdapter, TDataReader> : IDisposable
        where TTransaction : DbTransaction
    where TConnection : DbConnection
    where TCommand : DbCommand
    where TParameter : DbParameter
    where TDataAdapter : DbDataAdapter
    where TDataReader : DbDataReader
{
    protected string connectStr;
    protected TTransaction transaction = null;
    protected TConnection connection = null;
    public TConnection Connection
    {
        get { return connection; }
        set { connection = value; }
    }
    public virtual int ExecuteNonQuery(string sql, params TParameter[] parameters)
    {
        Connect();
        int i;
        using (TCommand cmd = CreateCommand(sql, parameters))
        {
            if (this.transaction != null)
                cmd.Transaction = (TTransaction)this.transaction;
            i = cmd.ExecuteNonQuery();
        }
        return i;
    }
    public virtual object ExecuteScalar(string sql, params TParameter[] parameters)
    {
        Connect();
        using (TCommand cmd = CreateCommand(sql, parameters))
        {
            return cmd.ExecuteScalar();
        }
    }
    public virtual object ExecuteReader(string sql, params TParameter[] parameters)
    {
        Connect();
        using (TCommand cmd = CreateCommand(sql, parameters))
        {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.NextResult()) { }
                if (reader.Read() && reader.FieldCount > 0)
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    return dt;
                }
                else
                    return reader.RecordsAffected;
            }
        }
    }
    public virtual DataTable Query(string sql, params TParameter[] parameters)
    {
        Connect();
        using (TCommand cmd = CreateCommand(sql, parameters))
        {
            using (TDataAdapter adapter = (TDataAdapter)Activator.CreateInstance(typeof(TDataAdapter), cmd))
            {
                DataSet ds = new DataSet();
                adapter.Fill(ds, "ds");
                return ds.Tables.Count > 0 ? ds.Tables[0] : null;
            }
        }
    }
    public virtual TCommand CreateCommand(string sql, params TParameter[] parameters)
    {
        TCommand cmd = (TCommand)Connection.CreateCommand();
        cmd.CommandText = sql;
        if (parameters != null && parameters.Length > 0)
            cmd.Parameters.AddRange(parameters);
        return cmd;
    }
    public virtual int AddColumn<Table>(DbColumnAttribute dbColumnAttribute)
    {
        if (dbColumnAttribute == null)
            dbColumnAttribute = new DbColumnAttribute();
        Type tableType = typeof(Table);
        DbTableAttribute dbTableAttribute = GetDbTableAttribute<Table>();
        return AddColumn(tableType.Name, dbColumnAttribute.Name, dbColumnAttribute.Desc);
    }
    public virtual int AddColumn(string tableName, string columnName, string options)
    {
        string sql = $"alter table '{tableName}' add  `{columnName}` {options}";
        int r = ExecuteNonQuery(sql);
        return r;
    }
    public virtual void AddIndex(string tableName, string index, string[] columns)
    {
        string sql = $"create index `{index}` on `{tableName}` (`{string.Join(",", columns)}`)";
        ExecuteNonQuery(sql);
    }
    public virtual void DeleteIndex(string tableName, string index)
    {
        ExecuteNonQuery($"drop index `{index}` on `{tableName}`");
    }
    public virtual List<Table> GetList<Table>(string sql, params TParameter[] parameters)
    {
        DataTable dt = Query(sql, parameters);
        return TableToList<Table>(dt);
    }
    public virtual void Connect(bool reconnect = false)
    {
        if (!reconnect && connection != null && connection.State != System.Data.ConnectionState.Closed)
            return;
        connection?.Dispose();
        connection = (TConnection)Activator.CreateInstance(typeof(TConnection), this.connectStr);
        connection.Open();
    }
    public virtual int CreateTable<Table>()
    {
        Type tableType = typeof(Table);
        var tableAttribute = GetDbTableAttribute<Table>();
        StringBuilder sb = new StringBuilder($"create table `{tableAttribute.Name}`");
        StringBuilder columns = new StringBuilder();
        var propertyInfos = tableType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
        foreach (PropertyInfo propertyInfo in propertyInfos)
        {
            var columnInfo = propertyInfo.GetCustomAttribute<DbColumnAttribute>();
            if (columnInfo == null) continue;
            columns.AppendLine($"\t`{columnInfo.Name}` {columnInfo.Desc},");
        }
        if (columns.Length > 0)
        {
            string sColumns = columns.ToString();
            sColumns = sColumns.Remove(sColumns.LastIndexOf(','), 1);
            sb.Append($" (\r\n{sColumns})\r\n");
        }
        return ExecuteNonQuery(sb.ToString());
    }
    public abstract bool ExistColumn(string column, string table);
    public abstract bool ExistIndex(string tableName, string index, string columnName = null);
    public abstract bool ExistTable(string table);
    public virtual Dictionary<string, List<string>> GetIndexs(Type table)
    {
        Dictionary<string, List<string>> indexDict = new Dictionary<string, List<string>>();
        foreach (var propertyInfo in table.GetProperties(BindingFlags.Instance | BindingFlags.Public))
        {
            var column = propertyInfo.GetCustomAttribute<DbColumnAttribute>();
            if (column == null) continue;
            if (!string.IsNullOrWhiteSpace(column.Index))
            {
                foreach (var index in column.Index.Split(','))
                {
                    if (!indexDict.ContainsKey(index))
                        indexDict.Add(index, new List<string> { column.Name });
                    else
                        indexDict[index].Add(column.Name);
                }
            }
        }
        return indexDict;
    }
    public virtual void UseServer(string connectStr)
    {
        this.connectStr = connectStr;
        Connect(true);
    }
    public virtual void Repair<Table>()
    {
        var tableType = typeof(Table);
        RepairTable<Table>();
        RepairColumns<Table>();
        RepairIndex(tableType);
    }
    public virtual void RepairTable<Table>()
    {
        var table = GetDbTableAttribute<Table>();
        var tableType = typeof(Table);
        ValidateTableAttribute(table, tableType);
        bool exist = ExistTable(table.Name);
        if (!exist)
            CreateTable<Table>();
    }
    public virtual void RepairColumns<Table>()
    {
        var table = GetDbTableAttribute<Table>();
        foreach (var propertyInfo in typeof(Table).GetProperties(BindingFlags.Instance | BindingFlags.Public))
        {
            var column = propertyInfo.GetCustomAttribute<DbColumnAttribute>();
            if (column == null) continue;
            if (ExistColumn(column.Name, table.Name))
                AddColumn(table.Name, column.Name, column.Desc);
        }
    }
    public virtual void RepairIndex(Type table)
    {
        Dictionary<string, List<string>> indexDict = GetIndexs(table);
        foreach (var index in indexDict.Keys)
        {
            foreach (var column in indexDict[index])
            {
                if (!ExistIndex(table.Name, index, column))
                {
                    if (ExistIndex(table.Name, index))
                        DeleteIndex(table.Name, index);
                    AddIndex(table.Name, index, indexDict[index].ToArray());
                    break;
                }
            }
        }
    }
    public virtual void Dispose()
    {
        transaction?.Dispose();
        connection?.Dispose();
    }
    public virtual int Insert<Table>(Table model)
    {
        Type tableType = typeof(Table);
        var propertyInfos = tableType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
        Dictionary<string, object> dict = new Dictionary<string, object>();
        foreach (PropertyInfo propertyInfo in propertyInfos)
        {
            var columnInfo = propertyInfo.GetCustomAttribute<DbColumnAttribute>();
            if (columnInfo == null || columnInfo.NotInsert) continue;
            dict.Add(columnInfo.Name, propertyInfo.GetValue(model));
        }
        DbTableAttribute dbTableAttribute = GetDbTableAttribute<Table>();
        StringBuilder sb = new StringBuilder($"insert into {dbTableAttribute.Name}");
        sb.Append(string.Join(",", "(", dict.Keys.Select(s => $"{s}=@{s}"), ")"));
        sb.Append(string.Join(",", "(", dict.Keys.Select(s => $"@{s}"), ")"));
        TParameter[] parameters = new TParameter[dict.Count];
        int i = 0;
        using (var enumerator = dict.GetEnumerator())
        {
            KeyValuePair<string, object> item;
            var typeParam = typeof(TParameter);
            while (enumerator.MoveNext())
            {
                item = enumerator.Current;
                parameters[i++] = (TParameter)Activator.CreateInstance(typeParam, $"@{item.Key}", item.Value);
            }
        }
        return ExecuteNonQuery(sb.ToString(), parameters);
    }
    public virtual int DeleteByPrimaryKey<Table>(object value)
    {
        Type type = typeof(Table);
        var tableInfo = type.GetCustomAttribute<DbTableAttribute>();
        if (tableInfo == null) return 0;
        var properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
        DbColumnAttribute columnInfo;
        foreach (var propertyInfo in properties)
        {
            columnInfo = propertyInfo.GetCustomAttribute<DbColumnAttribute>();
            if (columnInfo == null || !columnInfo.Desc.ToLower().Contains("primary key")) continue;
            return Delete(tableInfo.Name, columnInfo.Name, value);
        }
        return 0;
    }
    public virtual int Delete(string table, string column, object value)
    {
        return ExecuteNonQuery($"delete from `{table}` where `{column}`=@{column}", (TParameter)Activator.CreateInstance(typeof(TParameter), $"@{column}", value));
    }
    public virtual int Delete(string table, string where, params TParameter[] parameters)
    {
        return ExecuteNonQuery($"delete from `{table}` where {where}", parameters);
    }
    public virtual void BeginTransaction()
    {
        Connect();
        transaction = (TTransaction)connection.BeginTransaction();
    }
    public virtual bool EndTransaction()
    {
        bool r;
        try
        {
            transaction.Commit();
            r = true;
        }
        catch
        {
            transaction.Rollback();
            r = false;
        }
        transaction.Dispose();
        transaction = null;
        return r;
    }

    public DbContext() { }
    public DbContext(Action<DbContext<TTransaction, TConnection, TCommand, TParameter, TDataAdapter, TDataReader>> callback) { callback(this); }
    public virtual List<Table> TableToList<Table>(DataTable dt)
    {
        var list = new List<Table>();
        if (dt == null || dt.Rows.Count <= 0) return list;
        Dictionary<string, PropertyInfo> dict = new Dictionary<string, PropertyInfo>();
        foreach (var propertyInfo in typeof(Table).GetProperties(BindingFlags.Instance | BindingFlags.Public))
        {
            var column = propertyInfo.GetCustomAttribute<DbColumnAttribute>();
            if (column == null) continue;
            dict.Add(column.Name, propertyInfo);
        }
        foreach (DataRow row in dt.Rows)
        {
            Table model = Activator.CreateInstance<Table>();
            foreach (DataColumn column in dt.Columns)
            {
                if (!dict.ContainsKey(column.ColumnName)) continue;
                var propertyInfo = dict[column.ColumnName];
                var value = row[column.ColumnName];
                if (value == null || value is DBNull) continue;
                if (propertyInfo.PropertyType == typeof(int))
                    propertyInfo.SetValue(model, Convert.ToInt32(value));
                else if (propertyInfo.PropertyType == typeof(byte))
                    propertyInfo.SetValue(model, Convert.ToByte(value));
                else if (propertyInfo.PropertyType == typeof(long))
                    propertyInfo.SetValue(model, Convert.ToInt64(value));
                else if (propertyInfo.PropertyType == typeof(float))
                    propertyInfo.SetValue(model, Convert.ToSingle(value));
                else if (propertyInfo.PropertyType == typeof(double))
                    propertyInfo.SetValue(model, Convert.ToDouble(value));
                else if (propertyInfo.PropertyType == typeof(decimal))
                    propertyInfo.SetValue(model, Convert.ToDecimal(value));
                else if (propertyInfo.PropertyType == typeof(DateTime))
                    propertyInfo.SetValue(model, Convert.ToDateTime(value));
                else if (propertyInfo.PropertyType == typeof(string))
                    propertyInfo.SetValue(model, value.ToString());
                else if (propertyInfo.PropertyType.IsEnum)
                    propertyInfo.SetValue(model, Enum.Parse(propertyInfo.PropertyType, value.ToString()));
            }
            list.Add(model);
        }
        return list;
    }
    public DbTableAttribute GetDbTableAttribute<Table>() => GetDbTableAttribute(typeof(Table));
    public virtual DbTableAttribute GetDbTableAttribute(Type t)
    {
        var tableAttribute = t.GetCustomAttribute<DbTableAttribute>();
        if (tableAttribute == null)
            tableAttribute = new DbTableAttribute();
        ValidateTableAttribute(tableAttribute, t);
        return tableAttribute;
    }
    public virtual void ValidateTableAttribute(DbTableAttribute tableAttribute, Type type)
    {
        if (string.IsNullOrWhiteSpace(tableAttribute.Name))
            tableAttribute.Name = type.Name;
    }
    public virtual Dictionary<string, object> GetColumns<Table>(Table model)
    {
        Type tableType = typeof(Table);
        Dictionary<string, object> dict = new Dictionary<string, object>();
        var propertyInfos = tableType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
        foreach (PropertyInfo propertyInfo in propertyInfos)
        {
            var columnInfo = propertyInfo.GetCustomAttribute<DbColumnAttribute>();
            if (columnInfo == null) continue;
            dict.Add(columnInfo.Name, propertyInfo.GetValue(model));
        }
        return dict;
    }
    public virtual List<DbColumnAttribute> GetColumnAttributes<Table>() => GetColumnAttributes(typeof(Table));
    public virtual List<DbColumnAttribute> GetColumnAttributes(Type tableType)
    {
        List<DbColumnAttribute> dbColumnAttributes = new List<DbColumnAttribute>();
        var propertyInfos = tableType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
        foreach (PropertyInfo propertyInfo in propertyInfos)
        {
            var columnInfo = propertyInfo.GetCustomAttribute<DbColumnAttribute>();
            if (columnInfo == null) continue;
            dbColumnAttributes.Add(columnInfo);
        }
        return dbColumnAttributes;
    }
    public virtual string NameOf<Table>() => NameOf(typeof(Table));
    public virtual string NameOf(Type type)
    {
        return GetDbTableAttribute(type).Name;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="Table"></typeparam>
    /// <param name="propertyName">nameof(Table.Property)</param>
    /// <returns></returns>
    public virtual string NameOf<Table>(string propertyName) => NameOf(typeof(Table), propertyName);
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="Table"></typeparam>
    /// <param name="propertyName">nameof(Table.Property)</param>
    /// <returns></returns>
    public virtual string NameOf(Type tableType, string propertyName)
    {
        var propertyInfos = tableType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
        foreach (PropertyInfo propertyInfo in propertyInfos)
        {
            var columnInfo = propertyInfo.GetCustomAttribute<DbColumnAttribute>();
            if (columnInfo != null && propertyInfo.Name == propertyName) return columnInfo.Name;
        }
        return propertyName;
    }
}

Mysql:

查看代码

public class MySqlDbContext : DbContext<MySqlTransaction, MySqlConnection, MySqlCommand, MySqlParameter, MySqlDataAdapter, MySqlDataReader>
{
    public MySqlDbContext() { }
    public MySqlDbContext(Action<DbContext<MySqlTransaction, MySqlConnection, MySqlCommand, MySqlParameter, MySqlDataAdapter, MySqlDataReader>> callback) :base(callback) { }
    public override void Repair<Table>()
    {
        var table = GetDbTableAttribute<Table>();
        var tableType = typeof(Table);
        ValidateTableAttribute(table, tableType);
        bool exist = ExistTable(table.Name);
        if (!exist)
            CreateTable<Table>();
        else
        {
            RepairColumns<Table>();
            RepairIndex(tableType);
        }
    }
    public override int CreateTable<Table>()
    {
        Type tableType = typeof(Table);
        var tableAttribute = GetDbTableAttribute<Table>();
        StringBuilder sb = new StringBuilder($"create table `{tableAttribute.Name}`");
        StringBuilder columns = new StringBuilder();
        var propertyInfos = tableType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
        foreach (PropertyInfo propertyInfo in propertyInfos)
        {
            var columnInfo = propertyInfo.GetCustomAttribute<DbColumnAttribute>();
            if (columnInfo == null) continue;
            columns.AppendLine($"\t`{columnInfo.Name}` {columnInfo.Desc},");
        }
        StringBuilder sbIndex = new StringBuilder();
        Dictionary<string, List<string>> indexDict = GetIndexs(tableType);
        foreach (var key in indexDict.Keys)
        {
            string indexType = "index";
            switch (key.ToLower().Trim())
            {
                case "primary":
                case "primary key":
                case "primarykey":
                case "unique":
                case "unique index":
                    continue;
                default:
                    break;
            }
            sbIndex.AppendLine($"\t{indexType} `{key}` (`{string.Join("`,`", indexDict[key])}`),");
        }
        string sIndexs = sbIndex.ToString();
        string sColumns = columns.ToString();
        if (sbIndex.Length > 0)
            sIndexs = sIndexs.Remove(sIndexs.LastIndexOf(','), 1);
        else
            sColumns = sColumns.Remove(sColumns.LastIndexOf(','), 1);
        string content = sColumns + sIndexs;
        sb.Append($" (\r\n{content})\r\n");
        if(!string.IsNullOrWhiteSpace(tableAttribute.Charset))
            sb.AppendLine($"default character set {tableAttribute.Charset}");
        if (!string.IsNullOrWhiteSpace(tableAttribute.Collate))
            sb.AppendLine($"collate {tableAttribute.Collate}");
        return ExecuteNonQuery(sb.ToString());
    }
    public override void ValidateTableAttribute(DbTableAttribute tableAttribute, Type type)
    {
        if(string.IsNullOrWhiteSpace(tableAttribute.Name))
            tableAttribute.Name = type.Name;
        if (string.IsNullOrWhiteSpace(tableAttribute.Charset))
            tableAttribute.Charset = "utf8mb4";
        if (string.IsNullOrWhiteSpace(tableAttribute.Collate))
            tableAttribute.Collate = "utf8mb4_unicode_ci";
    }
    public override bool ExistColumn(string column, string table)
    {
        Connect();
        var r = ExecuteScalar($"select 1 from information_schema.columns where table_schema='{Connection.Database}' and table_name ='{table}' and column_name='{column}';");
        return r != null && r.ToString() == "1";
    }
    public override bool ExistIndex(string tableName, string index, string columnName = null)
    {
        StringBuilder sql = new StringBuilder($"select count(*) from information_schema.statistics where table_schema = database() and table_name = '{tableName}' and index_name = '{index}'");
        if (!string.IsNullOrWhiteSpace(columnName))
            sql.Append($" and column_name='{columnName}'");
        object r = ExecuteScalar(sql.ToString());
        return r != null && (r is int num) && num > 0;
    }
    public override bool ExistTable(string table)
    {
        Connect();
        var r = ExecuteScalar($"select 1 from information_schema.tables where table_schema='{Connection.Database}' and table_name ='{table}';");
        return r != null && r.ToString() == "1";
    }
}

SQLite:

查看代码

public class SqliteDbContext : DbContext<SQLiteTransaction, SQLiteConnection, SQLiteCommand, SQLiteParameter, SQLiteDataAdapter, SQLiteDataReader>
{
    public SqliteDbContext() { }
    public SqliteDbContext(Action<DbContext<SQLiteTransaction, SQLiteConnection, SQLiteCommand, SQLiteParameter, SQLiteDataAdapter, SQLiteDataReader>> callback) : base(callback) { }
    public override void Connect(bool reconnect = false)
    {
        if (!reconnect && Connection != null && (Connection.State == ConnectionState.Open || Connection.State == ConnectionState.Connecting)) return;
        if (!Directory.Exists(Path.GetDirectoryName(connectStr)))
            Directory.CreateDirectory(Path.GetDirectoryName(connectStr));
        if (!File.Exists(connectStr))
            SQLiteConnection.CreateFile(connectStr);
        Connection = new SQLiteConnection(connectStr.ToLower().Contains("data source=") ? connectStr : "data source=" + connectStr);
        Connection.Open();
    }
    public override bool ExistColumn(string column, string table)
    {
        Connect();
        var r = ExecuteScalar($"select 1 from sqlite_master where type='table' and name='{table}' and sql like '%{column}%'");
        return r != null && r.ToString() == "1";
    }
    public override bool ExistIndex(string tableName, string index, string columnName = null)
    {
        StringBuilder sql = new StringBuilder($"select 1 from sqlite_master where type='index' and name='{index}'");
        if (!string.IsNullOrWhiteSpace(columnName))
            sql.Append($" and sql like '%{columnName}%'");
        object r = ExecuteScalar(sql.ToString());
        return r != null && (r is int num) && num > 0;
    }
    public override bool ExistTable(string table)
    {
        Connect();
        var r = ExecuteScalar($"select 1 from sqlite_master where type='table' and name='{table}'");
        return r != null && r.ToString() == "1";
    }
}

使用方法：

[DbTable(Name = "account")]
public class Account
{
    [DbColumn(Name = "id", Desc = "bigint primary key auto_increment", NotInsert = true)]
    public long Id { get; set; }
    [DbColumn(Name = "un", Desc = "varchar(50) not null unique", Index = "un")]
    public string Username { get; set; }
    [DbColumn(Name = "pwd", Desc = "varchar(20) not null")]
    public string Password { get; set; }
    [DbColumn(Name = "is_deleted", Desc = "int(1) default '0")]
    public bool Deleted { get; set; }
    [DbColumn(Name = "create_time", Desc = "timestamp default localtime")]
    public DateTime CreateTime { get; set; }
}
static void Main(string[] args)
{
    MySqlDbContext dbContext = new MySqlDbContext();
    dbContext.UseServer("Data Source=127.0.0.1; Database=tempdb; User ID=admin; Password=123;Charset=utf8mb4;");
    dbContext.Repair<Account>();
    var account = new Account();
    dbContext.Insert(account);
    var list = dbContext.GetList<Account>("select * from account limit 10");
    dbContext.DeleteByPrimaryKey<Account>(account.Id);
    Console.WriteLine("按任意键退出。");
    Console.ReadKey();
}