/// <summary> /// 获取所有数据库表名 /// </summary> /// <param name="db"></param> /// <returns></returns> public List <string> GetTableNames(SqlSugarClient db) { string sql = GetCreateClassSql(null); var tableNameList = db.SqlQuery <string>(sql).ToList(); for (int i = 0; i < tableNameList.Count; i++) { var tableName = tableNameList[i]; tableNameList[i] = db.GetClassTypeByTableName(tableName); } return(tableNameList); }
/// <summary> /// 多线程请求所有数据库节点,同步汇总结果 /// </summary> /// <typeparam name="T">支持DataTable、实体类和值类型</typeparam> /// <param name="sql"></param> /// <param name="connectionStringList">连接字符串数组</param> /// <param name="whereObj">参数 例如: new { id="1",name="张三"}</param> /// <returns></returns> public Taskable <T> Taskable <T>(string sql, List <string> connectionStringList, object whereObj = null) { Taskable <T> reval = new Taskable <T>(); reval.Sql = sql; reval.WhereObj = whereObj; var tasks = new Task <CloudSearchResult <T> > [connectionStringList.Count]; for (int i = 0; i < tasks.Length; i++) { CloudPubMethod.TaskFactory <CloudSearchResult <T> >(ti => { string innerSql = sql; var connString = connectionStringList[ti]; var db = new SqlSugarClient(connString); SettingConnection(db); CloudSearchResult <T> itemReval = new CloudSearchResult <T>(); var isDataTable = typeof(T) == typeof(DataTable); var isClass = typeof(T).IsClass; if (innerSql.Contains("$:->connectionString<-:$")) { innerSql = innerSql.Replace("$:->connectionString<-:$", connString); } if (isDataTable) { itemReval.DataTable = db.GetDataTable(innerSql, whereObj); } else if (isClass) { itemReval.Entities = db.SqlQuery <T>(innerSql, whereObj); } else { var obj = db.GetScalar(sql, whereObj); if (obj == DBNull.Value) { itemReval.Value = default(T); } else { obj = Convert.ChangeType(obj, typeof(T)); itemReval.Value = (T)obj; } } itemReval.ConnectionString = connString; return(itemReval); }, tasks, i); } Task.WaitAll(tasks); reval.Tasks = tasks; return(reval); }
/// <summary> /// 多线程请求所有数据库节点,同步汇总结果 /// </summary> /// <typeparam name="T">支持DataTable、实体类和值类型</typeparam> /// <param name="sqlSelect">sql from之前(例如: "select count(*)" )</param> /// <param name="sqlEnd">sql from之后(例如: "from table where id=1" </param> /// <param name="whereObj">参数 例如: new { id="1",name="张三"}</param> /// <returns></returns> public TaskableWithCount <T> TaskableWithCount <T>(string sqlSelect, string sqlEnd, object whereObj = null) { TaskableWithCount <T> reval = new TaskableWithCount <T>(); reval.Sql = sqlSelect + sqlEnd; reval.WhereObj = whereObj; var tasks = new Task <CloudSearchResult <T> > [configList.Count]; for (int i = 0; i < tasks.Length; i++) { CloudPubMethod.TaskFactory <CloudSearchResult <T> >(ti => { var connString = configList[ti].ConnectionString; var db = new SqlSugarClient(connString); SettingConnection(db); CloudSearchResult <T> itemReval = new CloudSearchResult <T>(); var isDataTable = typeof(T) == typeof(DataTable); var isClass = typeof(T).IsClass; if (isClass) { itemReval.Entities = db.SqlQuery <T>(reval.Sql, whereObj); } else if (isDataTable) { itemReval.DataTable = db.GetDataTable(reval.Sql, whereObj); } else { var obj = db.GetScalar(reval.Sql, whereObj); obj = Convert.ChangeType(obj, typeof(T)); itemReval.Value = (T)obj; } itemReval.Count = db.GetInt("SELECT COUNT(1)" + sqlEnd);; itemReval.ConnectionString = connString; return(itemReval); }, tasks, i); } Task.WaitAll(tasks); reval.Tasks = tasks; return(reval); }
internal static List <string> GetLanguageViewNameList(SqlSugarClient db) { string key = "LanguageHelper.GetViewNameList"; var cm = CacheManager <List <string> > .GetInstance(); if (cm.ContainsKey(key)) { return(cm[key]); } else { var list = db.SqlQuery <string>(@" select a.name from sys.objects a JOIN sys.sql_modules b on a.[object_id]=b.[object_id] where [type]='v' and b.[definition] like '%" + db.Language.ReplaceViewStringKey + @"%' and a.name not like '%" + PreSuffix + @"%' ").ToList(); cm.Add(key, list, cm.Day); return(list); } }
/// <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 }