public override async Task <IEnumerable <T> > GetListAsync <T>(int topNum = 0, string condition = "", string sort = "", string filter = "*", DbParameter[] conditionParameters = null) { string tableName = AttrAssistant.GetTableName(typeof(T)); var builder = new StringBuilder("select "); if (filter.IsNullOrEmpty()) { filter = "*"; } builder.AppendFormat(" {0} from {1} ", filter, tableName); if (!condition.IsNullOrEmpty()) { builder.AppendFormat(" where {0} ", condition); } if (!sort.IsNullOrEmpty()) { builder.AppendFormat(" order by {0} ", sort); } if (topNum > 0) { builder.AppendFormat(" limit {0} ", topNum); } return(await GetListAsync <T>(builder.ToString(), conditionParameters)); }
public override IEnumerable <T> GetPagerList <T>(string condition, string sort, int pageIndex, int pageSize, string filter = "*", DbParameter[] conditionParameters = null) { var lstResult = new List <T>(); T tItem = default(T); string tableName = AttrAssistant.GetTableName(typeof(T)); //表名 if (string.IsNullOrEmpty(condition)) { condition = "1=1"; } //起始记录(不包括) int startPosition = (pageIndex - 1) * pageSize; StringBuilder builder = new StringBuilder(); //offset代表从第几条记录“之后“开始查询,limit表明查询多少条结果 builder.AppendFormat(@"select {0} from {1} as {6} {2} {3} limit {4} offset {5}", filter, tableName, condition.IsNullOrEmpty() ? "" : " where " + condition, sort.IsNullOrEmpty() ? "" : " order by " + sort, pageSize, startPosition, Utils.SinGooPagerAlias); var reader = GetDataReader(builder.ToString(), conditionParameters); var refBuilder = ReflectionBuilder <T> .CreateBuilder(reader); while (reader.Read()) { tItem = refBuilder.Build(reader, dbProviderType); lstResult.Add(tItem); } reader.Close(); return(lstResult); }
public override async Task <T> FindAsync <T>(object keyValue) { string tableName = AttrAssistant.GetTableName(typeof(T)); string key = AttrAssistant.GetKey(typeof(T)); return(GetModel <T>(await GetDataReaderAsync($"select * from {tableName} where {key} = @KeyValue limit 1", new DbParameter[] { MakeParam("@KeyValue", keyValue) }))); }
public override T Find <T>(object keyValue) { string tableName = AttrAssistant.GetTableName(typeof(T)); string key = AttrAssistant.GetKey(typeof(T)); return(GetModel <T>(GetDataReader($"select top 1 * from {tableName} where {key} = @KeyValue", new DbParameter[] { MakeParam("@KeyValue", keyValue) }))); }
public override async Task <IEnumerable <T> > GetPagerListAsync <T>(string condition, string sort, int pageIndex, int pageSize, string filter = "*", DbParameter[] conditionParameters = null) { var lstResult = new List <T>(); T tItem = default(T); string tableName = AttrAssistant.GetTableName(typeof(T)); //表名 if (condition.IsNullOrEmpty()) { condition = "1=1"; } //起始记录 int startPosition = (pageIndex - 1) * pageSize; //比如第一页是从0开始,而不是1,和sqlserver不同 var builder = new StringBuilder(); //limit 起始行(包括),记录数 builder.AppendFormat(@"select {0} from {1} as {6} {2} {3} limit {4},{5}", filter, tableName, condition.IsNullOrEmpty() ? "" : " where " + condition, sort.IsNullOrEmpty() ? "" : " order by " + sort, startPosition, pageSize, Utils.SinGooPagerAlias); var reader = await GetDataReaderAsync(builder.ToString(), conditionParameters); var refBuilder = ReflectionBuilder <T> .CreateBuilder(reader); while (reader.Read()) { tItem = refBuilder.Build(reader, dbProviderType); lstResult.Add(tItem); } reader.Close(); return(lstResult); }
public virtual async Task <bool> UpdateColumnAsync <T>(Expression <Func <T, T> > columns, string condition = "", DbParameter[] conditionParameters = null) where T : class { var builder = new StringBuilder(); var parameters = new List <DbParameter>(); builder.Append($" update {AttrAssistant.GetTableName(typeof(T))} set "); var bindings = (columns.Body as MemberInitExpression).Bindings; foreach (var item in bindings) { builder.AppendFormat("{0}=@{0},", item.Member.Name); parameters.Add(MakeParam("@" + item.Member.Name, ((item as MemberAssignment).Expression as ConstantExpression).Value)); } string sql = builder.ToString().TrimEnd(','); if (!condition.IsNullOrEmpty()) { sql += " where " + condition; if (conditionParameters != null) { parameters.AddRange(conditionParameters); } } return(await ExecSQLAsync(sql, parameters.ToArray())); }
public override IEnumerable <T> GetList <T>(int topNum = 0, string condition = "", string sort = "", string filter = "*", DbParameter[] conditionParameters = null) { //select * from (select * from table) where rownum <= 3 order by rownum asc string tableName = AttrAssistant.GetTableName(typeof(T)); var builder = new StringBuilder("select * from (select "); if (string.IsNullOrEmpty(filter)) { filter = "*"; } builder.AppendFormat(" {0} from {1} ", filter, tableName); if (!string.IsNullOrEmpty(condition)) { builder.AppendFormat(" where {0} ", condition); } if (!string.IsNullOrEmpty(sort)) { builder.AppendFormat(" order by {0} ", sort); } builder.Append(")"); if (topNum > 0) { builder.AppendFormat(" where rownum<={0}", topNum); } return(GetList <T>(builder.ToString(), conditionParameters)); }
public override async Task <IEnumerable <T> > GetPagerListAsync <T>(string condition, string sort, int pageIndex, int pageSize, string filter = "*", DbParameter[] conditionParameters = null) { if (sort.IsNullOrEmpty()) { throw new ArgumentNullException("sort"); //sort参数必填 } var lstResult = new List <T>(); T tItem = default(T); string tableName = AttrAssistant.GetTableName(typeof(T)); //表名 if (condition.IsNullOrEmpty()) { condition = "1=1"; } //起始记录 int startPosition = (pageIndex - 1) * pageSize + 1; int offsetPosition = (pageIndex - 1) * pageSize; //截止记录 int endPosition = pageIndex * pageSize; var builder = new StringBuilder(); if (dbVersionNo >= 11) { //sqlserver2012及以上可用 builder.AppendFormat(@"select {0} from {1} as {6} where {2} order by {3} offset {4} rows fetch next {5} rows only", filter, tableName, condition, sort, offsetPosition, pageSize, Utils.SinGooPagerAlias); } else { //sqlserver2005及以上可用 builder.AppendFormat(@"select {0} from(select row_number() over(order by {3}) as rownum,* from {1} where {2} ) as {6} where rownum between {4} and {5} order by {3}", filter, tableName, condition, sort, startPosition, endPosition, Utils.SinGooPagerAlias); } var reader = await GetDataReaderAsync(builder.ToString(), conditionParameters); var refBuilder = ReflectionBuilder <T> .CreateBuilder(reader); while (reader.Read()) { tItem = refBuilder.Build(reader, dbProviderType); lstResult.Add(tItem); } reader.Close(); return(lstResult); }
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> DeleteAsync <T>(object keyValue) { if (keyValue.IsNullOrEmpty()) { throw new ArgumentException("参数 keyValue 值不能为空"); } var type = typeof(T); string tableName = AttrAssistant.GetTableName(type); string key = AttrAssistant.GetKey(type); string sql = $" delete from {tableName} where {key}=@{key} "; var parameters = new DbParameter[] { MakeParam($"@{key}", keyValue) }; return(await ExecSQLAsync(sql, parameters)); }
public override bool Delete <T>(object keyValue) { if (keyValue == null) { throw new ArgumentException("参数 keyValue 值不能为 null"); } var type = typeof(T); string tableName = AttrAssistant.GetTableName(type); string key = AttrAssistant.GetKey(type); string sql = $" delete from {tableName} where {key}=:{key} "; var parameters = new DbParameter[] { MakeParam($":{key}", keyValue) }; return(ExecSQL(sql, parameters)); }
public override async Task <IEnumerable <T> > GetPagerListAsync <T>(string condition, string sort, int pageIndex, int pageSize, string filter = "*", DbParameter[] conditionParameters = null) { var lstResult = new List <T>(); T tItem = default(T); string tableName = AttrAssistant.GetTableName(typeof(T)); //表名 if (string.IsNullOrEmpty(condition)) { condition = "1=1"; } //起始记录 int startPosition = (pageIndex - 1) * pageSize + 1; //截止记录 int endPosition = pageIndex * pageSize; StringBuilder builder = new StringBuilder(); builder.AppendFormat(@"select {0} from ( select t.*,rownum as rowno from ( select * from {1} {2} {3} ) t where rownum<={5} ) {6} where rowno>={4}", filter, tableName, condition.IsNullOrEmpty() ? "" : " where " + condition, sort.IsNullOrEmpty() ? "" : " order by " + sort, startPosition, endPosition, Utils.SinGooPagerAlias); var reader = await GetDataReaderAsync(builder.ToString(), conditionParameters); var refBuilder = ReflectionBuilder <T> .CreateBuilder(reader, dbProviderType); while (reader.Read()) { tItem = refBuilder.Build(reader, dbProviderType); lstResult.Add(tItem); } reader.Close(); return(lstResult); }
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 virtual async Task <int> GetCountAsync <T>(string condition = "", DbParameter[] conditionParameters = null) => await GetCountAsync(AttrAssistant.GetTableName(typeof(T)), condition);
public void BulkInsert <T>(DataTable dt) where T : class { BulkInsert(dt, AttrAssistant.GetTableName(typeof(T))); }
public virtual int GetCount <T>(string condition = "", DbParameter[] conditionParameters = null) => GetCount(AttrAssistant.GetTableName(typeof(T)), condition);
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); }