根据实体类生成sql(实体类字段与数据库保持一致)
实体类
public class AccountEntity:IEntity
{
public int? ID { get; set; }
public string Names { get; set; }
public string TYPEID { get; set; }
public string PRICE { get; set; }
public string ISOUT { get; set; }
public string Dates { get; set; }
public string Remark { get; set; }
/// <summary>
/// 设置数据库表名称
/// </summary>
/// <returns></returns>
public string TableName()
{
return "bus_account";
}
}
反射生成Sql方法
/// <summary>
/// 通过反射得到sql
/// </summary>
/// <typeparam name="T">类名称</typeparam>
/// <param name="t">类对象</param>
/// <param name="WhereId">判断依据,新增还是修改</param>
/// <returns></returns>
public static string GetSql<T>(T t,string WhereId="ID")
{
var type = t.GetType(); //获取类型
var pr=type.GetProperties();
var where = "";
var tableMethod = type.GetMethod("TableName");
object obj = Activator.CreateInstance(type);
var tableName = tableMethod.Invoke(obj, null);
//INSERT INTO `bus_account` (ID,Names,TYPEID,PRICE,ISOUT,Dates,Remark) VALUES ('0', '早晨', '1', '22.22', '0', '', '这是一条备注信息,不知道他有多长,反正就是很长');
var insertSqlStr = string.Format("INSERT INTO {0} ({1}) values ({2}) ", tableName, "{0}", "{1}");
var insertColTemp = "";
var insertValTemp = "";
var isUpdate = false;
//UPDATE BUS_ACCOUNT SET NAMES='123',TYPEID='2' WHERE ID='0'
var updateSqlStr = string.Format("UPDATE {0} set {1} {2}", tableName, "{0}", "{1}");
var updateSet = "";
for (var i = 0; i < pr.Length; i++)
{
var name = pr[i].Name;
var value = pr[i].GetValue(t, null);
//和判断条件一致,并且值不为空,那么为修改语句
if (name.Trim().ToLower() == WhereId.Trim().ToLower() && value.ToString().Trim().Length > 0)
{
where = string.Format(" where {0}='{1}'", name, value);
isUpdate = true;
}
else
{
insertColTemp += name + (i >= pr.Length - 1 ? "" : ",");
insertValTemp += string.Format("'{0}'", value) + (i >= pr.Length - 1 ? "" : ",");
updateSet += string.Format("{0}='{1}'", name, value) + (i >= pr.Length - 1 ? "" : ","); ;
}
}
return isUpdate ? string.Format(updateSqlStr, updateSet, @where) : string.Format(insertSqlStr, insertColTemp, insertValTemp);
}
调用
var account = JsonHelper.DeserializeJsonToObject<AccountEntity>(json);
var sql = GetSql<AccountEntity>(account);