Пример #1
0
        /// <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
        }
Пример #2
0
 /// <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);
 }
Пример #3
0
        //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;
                }
            }
        }
Пример #4
0
        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;
                    }
                }
            }
        }
Пример #5
0
        /// <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 });
        }