前言
领导不让自己连数据库，只能用他写的、（我）没有修改权限的DII连接（用的dapper），只能在sql语句组合方面玩点花样了。
自己写了SQL语句的拼接方法，使用的时候只需要传入Model就可以生成对应的SQL并执行，目的是简化重复写的sql语句。使用后只需要给方法传入一些参数，就可以实现对数据库的访问。
思路
根据传入的Model获取其特性中的tableName（详细见上一篇文章），再将Model的属性名称作为SQL的执行Field，与Where限定条件拼接SQL语句，最后将Modle与Sql一起传入DII中的对应方法执行（实际上就是Dapper的各种方法）
实现
public class DBHelper
{
IDatabase dbMysql = new DBMysql();
//返回单个数据，condiction里面存放where 限定条件
public string GetData(object condiction, string tableName, string fields)
{
string sql = "SELECT ";
sql += fields;
sql += $" FROM {tableName} ";
sql = AddWhereCondiction(sql, condiction);
return dbMysql.Query<string>(sql, condiction);
}
/*返回泛型List，condiction为传入的Model，根据该Model获取其特性中的tableName，
根据Model的属性名称与Where限定条件拼接SQL语句。最后将Modle与Sql一起传入DII
（实际上就是Dapper的Query）*/
public List<T> GetDataList<T>(object condiction)
{
TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
string tableName = tableAttribute.tableName;
string sql = "SELECT ";
sql = AddModelPrppertyTToField<T>(sql);
sql += $" FROM {tableName} ";
sql = AddWhereCondiction(sql, condiction);
return dbMysql.QueryList<T>(sql, condiction).ToList();
}
public T GetData<T>(object condiction)
{
TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
string tableName = tableAttribute.tableName;
string sql = "SELECT ";
sql = AddModelPrppertyTToField<T>(sql);
sql += $" FROM {tableName} ";
sql = AddWhereCondiction(sql, condiction);
return dbMysql.Query<T>(sql, condiction);
}
public bool InsertData<T>(T model)
{
TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
string tableName = tableAttribute.tableName;
List<T> insertList = new List<T>() { model };
string sql = $"INSERT INTO {tableName} (";
sql = AddModelPrppertyTToField<T>(sql);
sql += ") VALUES(";
sql = AddModelPrppertyTToField<T>(sql, "@");
sql += ")";
int insertNumber = dbMysql.Insert(insertList, sql);
if (insertNumber == 1)
return true;
return false;
}
public bool UpdateData<T>(T condiction, string whereCondiction)
{
TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
string tableName = tableAttribute.tableName;
string sql = $"UPDATE {tableName} SET ";
foreach (var property in typeof(T).GetProperties())
{
sql += property.Name + " = @" + property.Name + ", ";
}
sql = sql.Substring(0, sql.Length - 2);
List<string> whereCondictionList = new List<string>() { whereCondiction };
sql = AddWhereCondiction(sql, whereCondictionList.ToList<string>());
return dbMysql.Update(sql, condiction);
}
public bool UpdateData<T>(T condiction, List<string> whereCondictionList)
{
TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
string tableName = tableAttribute.tableName;
string sql = $"UPDATE {tableName} SET ";
foreach (var property in typeof(T).GetProperties())
{
sql += property.Name + " = @" + property.Name + ", ";
}
sql = sql.Substring(0, sql.Length - 2);
sql = AddWhereCondiction(sql, whereCondictionList);
return dbMysql.Update(sql, condiction);
}
/*将泛型Model中的属性名称添加到SQL的执行Field中，dynamicData 为自定义变量添加，
可以在根据数据库不同在变量Field前增加想要的字符,比如Oracle变量需要添加':'，mysql需
要添加'@'。*/
public string AddModelPrppertyTToField<T>(string sql, string dynamicData = "")
{
if (dynamicData == "")
{
foreach (var property in typeof(T).GetProperties())
{
sql += property.Name + ", ";
}
}
else
{
foreach (var property in typeof(T).GetProperties())
{
sql += dynamicData + property.Name + ", ";
}
}
sql = sql.Substring(0, sql.Length - 2);
return sql;
}
//给SQL添加Where限定条件，whereCondiction中为需要限定条件的字段
public string AddWhereCondiction(string sql, List<string> whereCondiction)
{
sql += $" WHERE {whereCondiction[0]} = @{whereCondiction[0]} ";
whereCondiction.RemoveAt(0);
if (whereCondiction.Count > 0)
{
foreach (var whereLimit in whereCondiction)
{
sql += $" AND {whereLimit} = @{whereLimit} ";
}
}
return sql;
}
//给SQL添加Where限定条件，condiction的属性名称为限定条件的字段
public string AddWhereCondiction<T>(string sql,T condiction)
{
var properties = condiction.GetType().GetProperties();
sql += $" WHERE {properties[0].Name} = @{properties[0].Name} ";
if (typeof(T).GetProperties().Count()>1)
{
for(int count = 1;count<properties.Count();count++)
{
sql += $" AND {properties[count].Name} = @{properties[count].Name} ";
}
}
return sql;
}
}```