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); }