public override async Task <int> InsertModelAsync <T>(T model, string tableName = "")
{
var arrProperty = typeof(T).GetProperties();
var builderSQL = new StringBuilder();
var builderParams = new StringBuilder(" ( ");
var lstParams = new List <DbParameter>();
if (tableName.IsNullOrEmpty())
{
tableName = AttrAssistant.GetTableName(typeof(T));
}
foreach (PropertyInfo property in arrProperty)
{
//NotMapped 是自定义的字段，不属于表，所以要排除，Key默认是有值的，自增int或者GUID
if (AttrAssistant.IsKey(property) || AttrAssistant.IsNotMapped(property))
{
continue;
}
object obj = property.GetValue(model, null);
if (obj == null)
{
continue; //null无法加入到参数，跳过
}
//日期类型没有值时，默认0001-1-1报错：SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM
//因此设置新的默认值是 1900-1-1
if (property.PropertyType.Name.Equals("DateTime") && ((DateTime)obj).Equals(new DateTime(0001, 1, 1)))
{
obj = new DateTime(1900, 1, 1);
}
builderSQL.Append(property.Name + " , ");
builderParams.Append("@" + property.Name + " , ");
lstParams.Add(MakeParam("@" + property.Name, obj));
}
builderSQL.Remove(builderSQL.Length - 2, 2);
builderParams.Remove(builderParams.Length - 2, 2);
builderSQL.Append(" ) values ");
builderSQL.Append(builderParams.ToString() + " ) ");
builderSQL.Append(";select @@IDENTITY;"); //返回最新的ID
object objTemp = await GetObjectAsync(" insert into " + tableName + " ( " + builderSQL.ToString(), lstParams.ToArray());
return(objTemp.ToInt());
}
public virtual async Task <bool> UpdateModelAsync <T>(T model) where T : class
{
var arrProperty = typeof(T).GetProperties();
var builderSQL = new StringBuilder();
var lstParams = new List <DbParameter>();
foreach (var property in arrProperty)
{
//关键字key 和 不属于表的属性NotMapped 不更新
if (AttrAssistant.IsKey(property) || AttrAssistant.IsNotMapped(property))
{
continue;
}
//没有提供数据的字段也不处理
object obj = property.GetValue(model, null);
if (obj == null)
{
continue;
}
builderSQL.AppendFormat("{0}=@{0} , ", property.Name);
lstParams.Add(MakeParam("@" + property.Name, obj));
}
builderSQL.Remove(builderSQL.Length - 2, 2);
string key = AttrAssistant.GetKey(typeof(T));
if (!key.IsNullOrEmpty())
{
//主键值
object primaryKeyUniqueValue = RefProperty.GetPropertyValue(model, key);
builderSQL.AppendFormat(" where {0}=@primaryKeyUniqueValue ", key);
lstParams.Add(MakeParam("@primaryKeyUniqueValue", primaryKeyUniqueValue));
}
return(await ExecSQLAsync(" update " + AttrAssistant.GetTableName(typeof(T)) + " set " + builderSQL.ToString(), lstParams.ToArray()));
}
public override async Task <int> InsertModelAsync <T>(T model, string tableName = "")
{
var dictSql = new Dictionary <string, (string, DbParameter[])>();
var arrProperty = typeof(T).GetProperties();
var builderSQL = new StringBuilder();
var builderParams = new StringBuilder(" ( ");
var lstParams = new List <DbParameter>();
bool isKeyAutoSEQ = false;
if (tableName.IsNullOrEmpty())
{
tableName = AttrAssistant.GetTableName(typeof(T));
}
foreach (PropertyInfo property in arrProperty)
{
//oracle没有自动增长的列，创建了序列也要主动赋值
if (AttrAssistant.IsKey(property) && property.PropertyType.Name == "Int32")
{
isKeyAutoSEQ = true;
builderSQL.Append(property.Name + " , ");
builderParams.AppendFormat("SEQ_{0}.nextval, ", tableName.ToUpper());
}
else
{
//NotMapped 是自定义的字段，不属于表，所以要排除，非自增Key不处理
if (AttrAssistant.IsNotMapped(property) || (AttrAssistant.IsKey(property) && property.PropertyType.Name != "Int32"))
{
continue;
}
object obj = property.GetValue(model, null);
if (obj == null)
{
continue; //null无法加入到参数，跳过
}
//日期类型没有值时，默认0001-1-1报错：SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM
//因此设置新的默认值是 1900-1-1
if (property.PropertyType.Name.Equals("DateTime") && ((DateTime)obj).Equals(new DateTime(0001, 1, 1)))
{
obj = new DateTime(1900, 1, 1);
}
builderSQL.Append(property.Name + " , ");
builderParams.Append(":" + property.Name + " , ");
lstParams.Add(MakeParam(":" + property.Name, obj));
}
}
builderSQL.Remove(builderSQL.Length - 2, 2);
builderParams.Remove(builderParams.Length - 2, 2);
builderSQL.Append(" ) values ");
builderSQL.Append(builderParams.ToString() + " ) ");
dictSql.Add("NOVALUE", (" insert into " + tableName + " ( " + builderSQL.ToString(), lstParams.ToArray()));
/*
* 先建立一个序列
* create sequence SEQ_SINGOO minvalue 1 maxvalue 9999999999 start with 1 increment by 1 nocache
* insert into cms_user(AutoID,UserName) VALUES(SEQ_SINGOO.nextval,'张三');
* select SEQ_SINGOO.currval from dual; //取值
*/
if (isKeyAutoSEQ)
{
dictSql.Add("REVALUE", ($"select SEQ_{tableName.ToUpper()}.currval from dual", null));
}
var result = await ExecOracleTransAsync(dictSql);
return((result != null && result.Count() == 2) ? result.ToList()[1] : 0);
}
