[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 : IDisposable
{
protected string connectStr;
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 abstract int ExecuteNonQuery(string sql, params DbParameter[] parameters);
public abstract object ExecuteScalar(string sql, params DbParameter[] parameters);
public abstract DataTable Query(string sql, params DbParameter[] parameters);
public virtual List<Table> GetList<Table>(string sql, params DbParameter[] parameters)
{
var list = new List<Table>();
DataTable dt = Query(sql, parameters);
if (dt == null) 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 abstract void Connect(bool reconnect = false);
public abstract int CreateTable<Table>();
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 table = GetDbTableAttribute<Table>();
var tableType = typeof(Table);
ValidateTableAttribute(table, tableType);
bool exist = ExistTable(table.Name);
if (!exist)
CreateTable<Table>();
else
{
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);
}
RepairIndex(tableType);
}
}
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 abstract void Dispose();
public virtual DbTableAttribute GetDbTableAttribute<Table>()
{
Type t = typeof(Table);
var tableAttribute = t.GetCustomAttribute<DbTableAttribute>();
if (tableAttribute == null)
tableAttribute = new DbTableAttribute();
ValidateTableAttribute(tableAttribute, t);
return tableAttribute;
}
public abstract void ValidateTableAttribute(DbTableAttribute tableAttribute, Type type);
public abstract int Insert<Table>(Table model);
public abstract int DeleteByPrimaryKey<Table>(object value);
public abstract int Delete(string table, string column, object value);
public abstract void BeginTransaction();
public abstract bool EndTransaction();
}
public class MySqlDbContext : DbContext
{
MySqlTransaction transaction = null;
private MySqlConnection connection;
public MySqlConnection Connection
{
get { return connection; }
set { connection = value; }
}
public override void Dispose()
{
transaction?.Dispose();
Connection?.Dispose();
}
public override void Connect(bool reconnect = false)
{
if (!reconnect && Connection != null && Connection.State != System.Data.ConnectionState.Closed)
return;
Connection?.Dispose();
Connection = new MySqlConnection(this.connectStr);
Connection.Open();
}
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();
StringBuilder sbIndex = new StringBuilder();
Dictionary<string, List<string>> indexDict = GetIndexs(tableType);
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},");
}
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";
}
public override int ExecuteNonQuery(string sql, params DbParameter[] parameters)
{
Connect();
int i;
using (MySqlCommand cmd = new MySqlCommand(sql, Connection))
{
if(this.transaction != null)
cmd.Transaction = this.transaction;
if (parameters != null && parameters.Length > 0)
cmd.Parameters.AddRange(parameters);
i = cmd.ExecuteNonQuery();
}
return i;
}
public override object ExecuteScalar(string sql, params DbParameter[] parameters)
{
Connect();
using (MySqlCommand cmd = new MySqlCommand(sql, Connection))
{
if (parameters != null && parameters.Length > 0)
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
public override 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}"), ")"));
MySqlParameter[] parameters = new MySqlParameter[dict.Count];
int i = 0;
using (var enumerator = dict.GetEnumerator())
{
KeyValuePair<string, object> item;
while (enumerator.MoveNext())
{
item = enumerator.Current;
parameters[i++] = new MySqlParameter($"@{item.Key}", item.Value);
}
}
return ExecuteNonQuery(sb.ToString());
}
public override 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 override int Delete(string table, string column, object value)
{
return ExecuteNonQuery($"delete from `{table}` where `{column}`=@{column}", new MySqlParameter($"@{column}", value));
}
public override DataTable Query(string sql, params DbParameter[] parameters)
{
using (MySqlCommand cmd = new MySqlCommand(sql, Connection))
{
cmd.Parameters.AddRange(parameters);
using (MySqlDataAdapter command = new MySqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
command.Fill(ds, "ds");
return ds.Tables.Count > 0 ? ds.Tables[0] : null;
}
}
}
public override void BeginTransaction()
{
Connect();
transaction = Connection.BeginTransaction();
}
public override bool EndTransaction()
{
bool r;
try
{
transaction.Commit();
r = true;
}
catch
{
transaction.Rollback();
r = false;
}
transaction.Dispose();
transaction = null;
return r;
}
}使用方法：
[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();
}标签：string,C#,Sql,扩展,column,propertyInfo,null,public,Name From： https://www.cnblogs.com/RedSky/p/18357045