/// <summary>
/// 获取所有数据库表名
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public List <string> GetTableNames(SqlSugarClient db)
{
var tableNameList = db.SqlQuery <string>("select name from sysobjects where xtype in ('U','V') ").ToList();
for (int i = 0; i < tableNameList.Count; i++)
{
var tableName = tableNameList[i];
tableNameList[i] = db.GetClassTypeByTableName(tableName);
}
return(tableNameList);
}
/// <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>
/// 获取Schema和表名的集合
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
internal static List <KeyValue> GetSchemaList(SqlSugarClient db)
{
var cm = CacheManager <List <KeyValue> > .GetInstance();
string cacheKey = "SqlSugarTool.GetSchemaList";
if (cm.ContainsKey(cacheKey))
{
return(cm[cacheKey]);
}
else
{
var reval = db.SqlQuery <KeyValue>(@"select s.name as [Key],t.name as [Value] from sys.sysobjects t , sys.schemas s where t.xtype in ('U','V') and t.uid = s.schema_id");
cm.Add(cacheKey, reval, cm.Day);
return(reval);
}
}
/// <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 Name FROM SysColumns WHERE id=Object_Id('" + tableName + "')";
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);
}
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);
}
}
// 获取表结构信息
public List <PubModel.DataTableMap> GetTableColumns(SqlSugarClient db, string tableName)
{
string sql = @"SELECT Sysobjects.name AS TABLE_NAME ,
syscolumns.Id AS TABLE_ID,
syscolumns.name AS COLUMN_NAME ,
systypes.name AS DATA_TYPE ,
syscolumns.length AS CHARACTER_MAXIMUM_LENGTH ,
sys.extended_properties.[value] AS COLUMN_DESCRIPTION ,
syscomments.text AS COLUMN_DEFAULT ,
syscolumns.isnullable AS IS_NULLABLE
FROM syscolumns
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
LEFT OUTER JOIN sys.extended_properties ON ( sys.extended_properties.minor_id = syscolumns.colid
AND sys.extended_properties.major_id = syscolumns.id
)
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
WHERE syscolumns.id IN ( SELECT id
FROM SYSOBJECTS
WHERE xtype in( 'U','V') )
AND ( systypes.name <> 'sysname' ) AND Sysobjects.name='" + tableName + "' AND systypes.name<>'geometry' AND systypes.name<>'geography' ORDER BY syscolumns.colid";
return(db.SqlQuery <PubModel.DataTableMap>(sql));
}
/// <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 = string.Format(@"SELECT {4},RowIndex,{3} FROM (
SELECT *,ROW_NUMBER()OVER(ORDER BY {1}) AS ROWINDEX FROM ({0}) as sqlstr ) t WHERE t.rowIndex = ({2})
", sql /*0*/,
fullOrderByString /*1*/,
dataSampleIndex /*2*/,
orderByFieldsString /*3*/,
unqueField /*4*/);
DataRow sampleRow = null;
int sampleRowIndex = 0;
int sampleEachIndex = 0;
int nodeSpacing = 6;
var innerDataSampleList = Taskable <DataTable>(sqlOtherPage, whereObj).MergeTable().OrderByDataRow(orderByTypes).ThenByDataRow(unqueField, OrderByType.asc).ToList();
for (int i = 0; i < configCount; i++)
{
if (configCount < nodeSpacing)
{
if (i != configCount / 2)
{
continue;
}
}
else if (i % nodeSpacing != 0)
{
continue;
}
sampleRow = innerDataSampleList[i];
whereCompare = GetWhereCompare(whereCompare, orderByTypes, sampleRow, unqueField, sampleRow[0].ToString());
string whereCompareReverse = GetWhereCompare(null, orderByTypes, sampleRow, unqueField, sampleRow[0].ToString(), true);
sqlOtherPage = string.Format(@"SELECT COUNT(1) FROM (
SELECT *,ROW_NUMBER()OVER(ORDER BY {5}) AS ROWINDEX FROM ({2}) as sqlstr ) t WHERE {0}({3})
",
null /*0*/,
unqueField /*1*/,
sql /*2*/,
whereCompare /*3*/,
sampleRow[0] /*4:UnqueValue*/,
fullOrderByString /*5*/);
var innerSampleRowIndex = Taskable <int>(sqlOtherPage, whereObj).Count();
var isSet = sampleRowIndex == 0 || (Math.Abs(pageBegin - innerSampleRowIndex) < (Math.Abs(pageBegin - sampleRowIndex)));
if (isSet)
{
sampleRowIndex = innerSampleRowIndex;
sampleEachIndex = i;
}
}
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>
public List <PubModel.DataTableMap> GetTableColumns(SqlSugarClient db, string tableName)
{
string sql = SqlSugarTool.GetTtableColumnsInfo(tableName);
return(db.SqlQuery <PubModel.DataTableMap>(sql));
}