/// <summary>
/// 插入
/// 使用说明:sqlSugar.Insert(entity);
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity">插入对象</param>
/// <param name="isIdentity">主键是否为自增长,true可以不填,false必填</param>
/// <returns></returns>
public object Insert <T>(T entity, bool isIdentity = true) where T : class
{
var connName = CloudPubMethod.GetConnection(this.configList);
var db = new SqlSugarClient(connName);
SettingConnection(db);
return(db.Insert <T>(entity, isIdentity));
}
/// <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>
/// 更新
/// 注意：rowObj为T类型将更新该实体的非主键所有列，如果rowObj类型为匿名类将更新指定列
/// 使用说明:sqlSugar.Update《T》(rowObj,whereObj);
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="rowObj">new T(){name="张三",sex="男"}或者new {name="张三",sex="男"}</param>
/// <param name="expression">it.id=100</param>
/// <returns></returns>
public bool Update <T>(object rowObj, System.Linq.Expressions.Expression <Func <T, bool> > expression) where T : class
{
var tasks = new Task <bool> [configList.Count];
for (int i = 0; i < tasks.Length; i++)
{
CloudPubMethod.TaskFactory <bool>(ti =>
{
var connName = configList[ti].ConnectionString;
var db = new SqlSugarClient(connName);
SettingConnection(db);
return(db.Update <T>(rowObj, expression));
}, tasks, i);
}
Task.WaitAll(tasks);
return(tasks.Any(it => it.Result));
}
/// <summary>
/// 更新
/// 注意：rowObj为T类型将更新该实体的非主键所有列，如果rowObj类型为匿名类将更新指定列
/// 使用说明:sqlSugar.Update《T》(rowObj,whereObj);
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="rowObj">new T(){name="张三",sex="男"}或者new {name="张三",sex="男"}</param>
/// <param name="whereIn">new int[]{1,2,3}</param>
/// <returns></returns>
public bool Update <T, FiledType>(object rowObj, params FiledType[] whereIn) where T : class
{
var tasks = new Task <bool> [configList.Count];
for (int i = 0; i < tasks.Length; i++)
{
CloudPubMethod.TaskFactory <bool>(ti =>
{
var connName = configList[ti].ConnectionString;
var db = new SqlSugarClient(connName);
SettingConnection(db);
return(db.Update <T, FiledType>(rowObj, whereIn));
}, tasks, i);
}
Task.WaitAll(tasks);
return(tasks.Any(it => it.Result));
}
/// <summary>
/// 批量删除
/// 注意：whereIn 主键集合
/// 使用说明:Delete《T》(new int[]{1,2,3}) 或者 Delete《T》(3)
/// </summary>
/// <param name="whereIn"> delete ids </param>
public bool FalseDelete <T, FiledType>(string field, params FiledType[] whereIn)
{
var tasks = new Task <bool> [configList.Count];
for (int i = 0; i < tasks.Length; i++)
{
CloudPubMethod.TaskFactory <bool>(ti =>
{
var connName = configList[ti].ConnectionString;
var db = new SqlSugarClient(connName);
SettingConnection(db);;
return(db.FalseDelete <T, FiledType>(field, whereIn));
}, tasks, i);
}
Task.WaitAll(tasks);
return(tasks.Any(it => it.Result));
}
/// <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>
/// <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
}
