/// <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>
/// <param name="db"></param>
/// <returns></returns>
public List <string> GetTableNames(SqlSugarClient db)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string sql = SqlSugarTool.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);
}
db.IsEnableLogEvent = isLog;
return(tableNameList);
}
/// <summary>
/// 根据表名获取列名
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
internal static List <string> GetColumnsByTableName(SqlSugarClient db, string tableName)
{
string key = "GetColumnNamesByTableName" + tableName;
var cm = CacheManager <List <string> > .GetInstance();
if (cm.ContainsKey(key))
{
return(cm[key]);
}
else
{
string sql = @" select COLUMN_name
from user_tab_columns c
where c.Table_Name='" + tableName.ToOracleTableName() + @"'
order by c.column_name";
var reval = db.SqlQuery <string>(sql);
cm.Add(key, reval, cm.Day);
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);
}
/// <summary>
/// 获取所有需要生成多语言的视图名称
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
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>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public List <PubModel.DataTableMap> GetTableColumns(SqlSugarClient db, string tableName)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string cacheKey = "GlassGenerating.GetTableColumns" + tableName;
var cm = CacheManager <List <PubModel.DataTableMap> > .GetInstance();
if (cm.ContainsKey(cacheKey))
{
db.IsEnableLogEvent = isLog;
return(cm[cacheKey]);
}
else
{
string sql = SqlSugarTool.GetTtableColumnsInfo(tableName);
var reval = db.SqlQuery <PubModel.DataTableMap>(sql);
db.IsEnableLogEvent = isLog;
cm.Add(cacheKey, reval, cm.Day);
return(reval);
}
}
/// <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
}