/// <summary> /// 获取分页数据(注意:该函数不可以在事务内使用) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="unqueField">数据库中数据唯一的列(建议:主键GUID)</param> /// <param name="sql"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="pageCount"></param> /// <param name="orderByTypes">排序信息集合</param> /// <param name="whereObj">参数 例如: new { id="1",name="张三"}</param> /// <returns></returns> public List <T> TaskableWithPage <T>(string unqueField, string sql, int pageIndex, int pageSize, ref int pageCount, List <OrderByDictionary> orderByTypes, object whereObj = null) where T : class { if (orderByTypes == null || orderByTypes.Count == 0) { throw new ArgumentNullException("CloudClient.TaskableWithPage.orderByTypes"); } if (pageIndex == 0) { pageIndex = 1; } int configCount = configList.Count; string sqlCount = string.Format("SELECT COUNT(*) FROM ({0}) t ", sql); pageCount = Taskable <int>(sqlCount, whereObj).Count(); if (pageCount == 0) { return(new List <T>()); } int totalPage = (pageCount + pageSize - 1) / pageSize; var lastPage = (totalPage - pageIndex) + 1; var isLast = totalPage == pageIndex; string fullOrderByString = string.Join(",", orderByTypes.Select(it => it.OrderByString)) + "," + unqueField + " ASC "; string fullOrderByStringReverse = string.Join(",", orderByTypes.Select(it => it.OrderByStringReverse)) + "," + unqueField + " DESC "; string orderByFieldsString = string.Join(",", orderByTypes.Select(it => it.OrderByField)); string[] orderByFieldArray = orderByTypes.Select(it => it.OrderByField).ToArray(); string whereCompare = string.Join(" AND ", orderByTypes.Select(it => string.Format(" {0}{1}'$:->{0}<-:$' ", it.OrderByField, it.Symbol, it.Symbol))); /***one nodes***/ #region one nodes var isOneNode = configCount == 1; if (isOneNode) { var connName = configList.Single().ConnectionString; var db = new SqlSugarClient(connName); SettingConnection(db); var sqlPage = string.Format(@"SELECT * FROM ( SELECT *,ROW_NUMBER()OVER(ORDER BY {1}) AS ROWINDEX FROM ({0}) as sqlstr ) t WHERE t.rowIndex BETWEEN {2} AND {3} ", sql, fullOrderByString, (pageIndex - 1) * pageSize + 1, pageSize * pageIndex); var list = db.SqlQuery <T>(sql, whereObj); return(list.OrderBy(orderByTypes).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList()); } #endregion /***small data***/ #region small data var isSmallData = pageCount <= this.PageMaxHandleNumber || int.MaxValue == pageSize;//page size等于int.MaxValue不需要分页 if (isSmallData) { var tasks = Taskable <T>(sql + " ORDER BY " + fullOrderByString, whereObj); return(tasks.Tasks.SelectMany(it => it.Result.Entities).OrderBy(orderByTypes).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList()); } #endregion /***small index***/ #region small index var isSmallPageIndex = CloudPubMethod.GetIsSmallPageIndex(pageIndex, pageSize, configCount, this.PageMaxHandleNumber); if (isSmallPageIndex) { var sqlPage = string.Format(@"SELECT * FROM ( SELECT *,ROW_NUMBER()OVER(ORDER BY {1}) AS ROWINDEX FROM ({0}) as sqlstr ) t WHERE t.rowIndex BETWEEN {2} AND {3} ", sql, fullOrderByString, 1, pageSize * configCount); var tasks = Taskable <T>(sqlPage, whereObj); return(tasks.Tasks.SelectMany(it => it.Result.Entities).OrderBy(orderByTypes).ThenBy(unqueField, OrderByType.asc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList()); } #endregion /***small index by reverse***/ #region small index by reverse var isSmallPageIndexByReverse = CloudPubMethod.GetIsSmallPageIndexByReverse(totalPage, pageIndex, pageSize, configCount, this.PageMaxHandleNumber); if (isSmallPageIndexByReverse) { var sqlPage = string.Format(@"SELECT * FROM ( SELECT *,ROW_NUMBER()OVER(ORDER BY {1}) AS ROWINDEX FROM ({0}) as sqlstr ) t WHERE t.rowIndex BETWEEN {2} AND {3} ", sql, fullOrderByStringReverse, 1, lastPage * configCount * pageSize); var tasks = Taskable <T>(sqlPage, whereObj); var lastPageSize = pageCount % pageSize; if (lastPageSize == 0) { lastPageSize = pageSize; } var list = tasks.Tasks.SelectMany(it => it.Result.Entities).OrderByReverse(orderByTypes).ThenBy(unqueField, OrderByType.desc); if (isLast) { return(list.Skip(0).Take(lastPageSize).OrderBy(orderByTypes).ThenBy(unqueField, OrderByType.asc).ToList()); } else { var skipIndex = (lastPage - 1) * pageSize + lastPageSize - pageSize; return(list.Skip(skipIndex).Take(pageSize).OrderBy(orderByTypes).ThenBy(unqueField, OrderByType.asc).ToList()); } } #endregion /***other***/ #region other //单节点最大索引 var maxDataIndex = pageIndex * pageSize * configCount; //分页最大索引 var pageEnd = pageIndex * pageSize; var pageBegin = pageIndex * pageSize - pageSize; //节点间距 var dataSampleIndex = pageBegin / configCount; string sqlOtherPage = GetSqlSampleRowSql(unqueField, sql, fullOrderByString, orderByFieldsString, dataSampleIndex); DataRow sampleRow = null; int sampleRowIndex; int sampleEachIndex; List <DataRow> innerDataSampleList; InitSampleRow(unqueField, sql, orderByTypes, whereObj, configCount, fullOrderByString, ref whereCompare, pageBegin, ref sqlOtherPage, ref sampleRow, out sampleRowIndex, out sampleEachIndex, out innerDataSampleList); sampleRow = innerDataSampleList[sampleEachIndex]; //获取分页索引所需参数实体 PageRowInnerParamsResultMultipleOrderBy beginEndRowParams = new PageRowInnerParamsResultMultipleOrderBy() { RowIndex = sampleRowIndex, Row = sampleRow, Begin = pageBegin, End = pageEnd, PageIndex = pageIndex, PageSize = pageSize, Sql = sql, UnqueField = unqueField, isGreater = sampleRowIndex > pageBegin, UnqueValue = sampleRow[0], FullOrderByString = fullOrderByString, FullOrderByStringReverse = fullOrderByStringReverse, ConfigCount = configCount, orderByFieldsString = orderByFieldsString, OrderByTypes = orderByTypes, WhereObj = whereObj, Count = pageCount, SampleEachIndex = sampleEachIndex }; PageRowInnerParamsResultMultipleOrderBy beginEndRow = null; var isBeginRow = (Math.Abs(sampleRowIndex - pageBegin) * configCount < PageMaxHandleNumber || Math.Abs(pageBegin - sampleRowIndex) * configCount < PageMaxHandleNumber); beginEndRow = isBeginRow?beginEndRowParams: GetListByPage_GetPageBeginRowMultipleOrderBy(beginEndRowParams); Dispose(false); var reval = GetListByPage_GetPageListMultipleOrderBy <T>(beginEndRow); Dispose(false); return(reval); #endregion }
/// <summary> /// 判段是否包含主键 /// </summary> /// <param name="db"></param> /// <param name="tableName"></param> /// <returns></returns> internal static bool IsPrimaryKey(SqlSugarClient db, string tableName) { return(GetPrimaryKeyByTableName(db, tableName) != null); }
//public static string ToOracleTableName(this string value) //{ // return value; //} //public static object ToOracleParValue(this object value) //{ // if (value == null) // { // return DBNull.Value; // } // var type = value.GetType(); // if (type == SqlSugarTool.BoolType) // { // return Convert.ToBoolean(value) ? 1 : 0; // } // else if (type == SqlSugarTool.GuidType) // { // return value.ToString(); // } // return value; //} internal static void SetParType(string typeName, bool isBinary, string name, OracleParameter par, SqlSugarClient db) { var colInfos = db.ClassGenerating.GetTableColumns(db, typeName.ToUpper()).Where(it => it.COLUMN_NAME.ObjToString().ToLower() == name.GetOracleParameterNameNoParSymbol().ToLower()).ToList(); if (colInfos.IsValuable()) { var colInfo = colInfos.Single(); if (colInfo.DATA_TYPE.ObjToString() == "BFILE") { par.OracleDbType = OracleDbType.BFile; } else if (colInfo.DATA_TYPE.ObjToString() == "BLOB") { par.OracleDbType = OracleDbType.Blob; } else if (colInfo.DATA_TYPE.ObjToString() == "RAW") { par.OracleDbType = OracleDbType.Raw; } else if (colInfo.DATA_TYPE.ObjToString() == "LONGRAW") { par.OracleDbType = OracleDbType.LongRaw; } } }
internal static void SetParType(string typeName, PropertyInfo prop, OracleParameter par, SqlSugarClient db) { var isBinary = prop.PropertyType == SqlSugarTool.ByteArrayType; if (isBinary) { var colInfos = db.ClassGenerating.GetTableColumns(db, typeName.ToUpper()).Where(it => it.COLUMN_NAME.ObjToString().ToLower() == prop.Name.ToLower()).ToList(); if (colInfos.IsValuable()) { var colInfo = colInfos.Single(); if (colInfo.DATA_TYPE.ObjToString() == "BFILE") { par.OracleDbType = OracleDbType.BFile; } else if (colInfo.DATA_TYPE.ObjToString() == "BLOB") { par.OracleDbType = OracleDbType.Blob; } else if (colInfo.DATA_TYPE.ObjToString() == "RAW") { par.OracleDbType = OracleDbType.Raw; } else if (colInfo.DATA_TYPE.ObjToString() == "LONGRAW") { par.OracleDbType = OracleDbType.LongRaw; } } } }
/// <summary> /// 创建多语言视图,带有LanguageId=1的所有有视图1替换成languageValue 并且新创视图 名称为 原有视图名+_$_+suffix /// </summary> /// <returns></returns> public static void UpdateView(PubModel.Language lan, SqlSugarClient db) { if (lan == null) { return; } if (lan.Suffix.IsNullOrEmpty()) { Check.Exception(true, "LanguageHelper.lan.Suffix is Null Or Empty"); } if (PreSuffix.IsNullOrEmpty()) { Check.Exception(true, "LanguageHelper.PreSuffix is Null Or Empty"); } if (!lan.Suffix.StartsWith(PreSuffix)) { lan.Suffix = PreSuffix + lan.Suffix; } string sql = @" --验证参数传递规则 if LEFT(ltrim(@Suffix),3)<>'" + PreSuffix + @"' begin raiserror('参数传递格式不规范',16,1) return; end else if(ISNULL(" + lan.LanguageValue + @",'')='') begin raiserror('参数传递格式不规范',16,1) return; end declare @name varchar(100), --视图名称 @definition varchar(max) --视图脚本 --删除数据库里面所有带传递参数几号的视图 declare my_cursor cursor for select a.name,b.[definition] from sys.objects a JOIN sys.sql_modules b on a.[object_id]=b.[object_id] where [type]='v' and b.[definition] like '%" + lan.ReplaceViewStringKey + @"%' and a.name not like '%" + PreSuffix + @"%' --打开处理器 open my_cursor fetch next from my_cursor into @name,@definition while @@FETCH_STATUS=0 begin --脚本查询语言ID更改,并且更改新脚本语言的对象名称 set @definition=REPLACE( REPLACE( @definition, '" + lan.ReplaceViewStringKey + @"', '" + string.Format(lan.ReplaceViewStringValue, lan.LanguageValue) + @"' ), @name, @name+@Suffix ) --判断新脚本语言的对象名称是否存在,存在删除 exec( ' if object_id('''+@name+@Suffix+''',''v'') is not null begin drop view '+@name+@Suffix+' end ' ) exec(@definition) fetch next from my_cursor into @name,@definition end close my_cursor deallocate my_cursor "; db.ExecuteCommand(sql, new { Suffix = lan.Suffix }); }