/// <summary>
/// 获取分页数据
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="util">查询参数</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页条数</param>
/// <param name="table">要查询的表</param>
/// <param name="cols">要查询的列</param>
/// <returns></returns>
public PageList <T> Query <T>(MySearchUtil util, int pageIndex, int pageSize, string table = "", string cols = "*")
{
if (string.IsNullOrWhiteSpace(table))
{
table = GetTableName(typeof(T));
}
string where = util.GetConditionClaus();
string orderby = util.GetOrderByClaus();
DynamicParameters param = util.GetPageListParameters();
string sql = GetPagingQuerySql(cols, table, where, orderby, pageIndex, pageSize);
using (var conn = GetConnection())
{
var list = conn.Query <T>(sql, param);
var total = param.Get <int>("RecordCount");
return(new PageList <T>
{
Body = list,
PageIndex = pageIndex,
PageSize = pageSize,
RecordCount = total
});
}
}
public int Update <T>(object obj, MySearchUtil util)
{
using (var conn = GetConnection())
{
string where = util.GetConditionClaus();
var param = util.GetParameters();
var props = obj.GetType().GetProperties();
var entity = MyContainer.Get(typeof(T));
StringBuilder sb = new StringBuilder();
List <string> _cols = new List <string>();
List <string> propNames = new List <string>();
foreach (var p in props)
{
propNames.Add(p.Name);
param.Add(p.Name, p.GetValue(obj));
_cols.Add($"{p.Name}=@{p.Name}");
}
sb.Append($"UPDATE [{entity.TableName}] SET ");
sb.Append(string.Join(',', _cols));
sb.Append($" WHERE {where}");
return(conn.Execute(sb.ToString(), obj));
}
}
public PageList <dynamic> Query(MySearchUtil util, int pageIndex, int pageSize, string table, string cols = "*")
{
if (string.IsNullOrWhiteSpace(table))
{
throw new ArgumentNullException("表名不能为空");
}
string where = util.ConditionClaus;
string orderby = util.OrderByClaus;
DynamicParameters param = util.PageListParameters;
string sql = GetPagingQuerySql(cols, table, where, orderby, pageIndex, pageSize);
using (var conn = GetConnection())
{
var list = conn.Query(sql, param);
var total = param.Get <int>("RecordCount");
return(new PageList <dynamic>
{
Body = list,
PageIndex = pageIndex,
PageSize = pageSize,
RecordCount = total
});
}
}
/// <summary>
/// 加载实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="util"></param>
/// <param name="cols"></param>
/// <returns></returns>
public T Load <T>(MySearchUtil util, string cols = "*")
{
using (var conn = GetConnection())
{
string sql = "SELECT TOP 1 " + cols + " FROM [" + GetTableName(typeof(T)) + "] WHERE " +
util.GetConditionClaus() + (string.IsNullOrWhiteSpace(util.GetOrderByClaus()) ? "" : " ORDER BY " + util.GetOrderByClaus());
return(conn.Query <T>(sql, util.GetParameters()).SingleOrDefault());
}
}
/// <summary>
/// 指定条件加载实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="util"></param>
/// <param name="cols"></param>
/// <returns></returns>
public T Load <T>(MySearchUtil util, string cols = "*", string tableName = "")
{
using (var conn = GetConnection())
{
tableName = string.IsNullOrWhiteSpace(tableName) ? GetTableName(typeof(T)) : tableName;
string sql = "SELECT TOP 1 " + cols + " FROM [" + tableName + "] WHERE " +
util.ConditionClaus + (string.IsNullOrWhiteSpace(util.OrderByClaus) ? "" : " ORDER BY " + util.OrderByClaus);
return(conn.Query <T>(sql, util.Parameters).SingleOrDefault());
}
}
public string GetUpdateSqlStatement <T>(object obj, MySearchUtil util = null)
{
try
{
var entity = MyContainer.Get(typeof(T));
var sql = MyEntityUtil.BuildUpdateSqlByAnonymous(entity, obj, util == null ? "" : util.GetConditionClaus());
return(sql);
}
catch
{
throw new Exception("指定类型未在MyContainer中注册");
}
}
/// <summary>
/// 获取指定表的所有数据，返回dynamic类型的数据列表
/// </summary>
/// <param name="util">查询参数</param>
/// <param name="table">要查询的表</param>
/// <param name="cols">要查询的列</param>
/// <param name="top">指定数量</param>
/// <returns></returns>
public IEnumerable <dynamic> Fetch(MySearchUtil util, string table, string cols = "*", int?top = null)
{
string where = util.GetConditionClaus();
string orderby = util.GetOrderByClaus();
DynamicParameters param = util.GetParameters();
string sql = GetQuerySql(cols, table, where, orderby);
using (var conn = GetConnection())
{
return(conn.Query(sql, param));
}
}
public int GetCount(MySearchUtil util, string tableName)
{
string condition = "";
DynamicParameters param = null;
condition = util.ConditionClaus;
param = util.Parameters;
using (var conn = GetConnection())
{
string sql = "SELECT COUNT(0) FROM [" + tableName + "] WHERE " + condition;
return(conn.ExecuteScalar <int>(sql, param));
}
}
public int Remove <T>(MySearchUtil mcu, string tableName = "")
{
using (var conn = GetConnection())
{
if (mcu == null)
{
mcu = new MySearchUtil();
}
if (string.IsNullOrWhiteSpace(tableName))
{
tableName = GetTableName(typeof(T));
}
return(conn.Execute(string.Format(@"UPDATE [{0}] SET IsDel=1 WHERE ", tableName, mcu.GetConditionClaus()), mcu.GetParameters()));
}
}
public int Delete <T>(MySearchUtil mcu, string tableName = "")
{
using (var conn = GetConnection())
{
if (mcu == null)
{
mcu = new MySearchUtil();
}
if (string.IsNullOrWhiteSpace(tableName))
{
tableName = GetTableName(typeof(T));
}
return(conn.Execute(string.Format(@"DELETE [{0}] WHERE {1}", tableName, mcu.ConditionClaus), mcu.Parameters));
}
}
public int Delete <T>(MySearchUtil util, string tableName = "")
{
using (var conn = GetConnection())
{
if (util == null)
{
util = new MySearchUtil();
}
if (string.IsNullOrWhiteSpace(tableName))
{
tableName = GetTableName(typeof(T));
}
return(conn.Execute($"DELETE [{tableName}] WHERE {util.GetConditionClaus()}", util.GetParameters()));
}
}
public int GetCount <T>(MySearchUtil util, string tableName = "")
{
string condition = "";
DynamicParameters param = null;
condition = util.ConditionClaus;
param = util.Parameters;
if (string.IsNullOrWhiteSpace(tableName))
{
tableName = GetTableName(typeof(T));
}
using (var conn = GetConnection())
{
string sql = "SELECT COUNT(0) FROM [" + tableName + "] WHERE " + condition;
return(conn.ExecuteScalar <int>(sql, param));
}
}
/// <summary>
/// 指定更新的列和值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="kvs">要更新的列和值</param>
/// <param name="msu">要更新的数据条件</param>
/// <returns></returns>
public int Set <T>(KeyValuePairs kvs, MySearchUtil msu)
{
DynamicParameters param = new DynamicParameters();
List <string> props = new List <string>();
foreach (var kv in kvs)
{
props.Add(kv.Key);
param.Add(kv.Key, kv.Value);
}
var sql = MyContainer.Get(typeof(T)).GetUpdateSql(props.ToArray(), null, msu.ConditionClaus);
using (var conn = GetConnection())
{
return(conn.Execute(sql, param));
}
}
public IEnumerable <T> Fetch <T>(MySearchUtil util, string table = "", string cols = "*")
{
if (string.IsNullOrWhiteSpace(table))
{
table = GetTableName(typeof(T));
}
var where = util.ConditionClaus;
var orderby = util.OrderByClaus;
var param = util.Parameters;
var sql = GetQuerySql(cols, table, where, orderby);
using (var conn = GetConnection())
{
return(conn.Query <T>(sql, param));
}
}
/// <summary>
/// 获取所有数据
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="util">查询参数</param>
/// <param name="table">查询的数据表</param>
/// <param name="cols">要查询的列</param>
/// <param name="top">要获取的数量</param>
/// <returns></returns>
public IEnumerable <T> Fetch <T>(MySearchUtil util, string table = "", string cols = "*", int?top = null)
{
if (string.IsNullOrWhiteSpace(table))
{
table = GetTableName(typeof(T));
}
string where = util.GetConditionClaus();
string orderby = util.GetOrderByClaus();
DynamicParameters param = util.GetParameters();
string sql = GetQuerySql(cols, table, where, orderby, top);
using (var conn = GetConnection())
{
return(conn.Query <T>(sql, param));
}
}
public int Update(KeyValuePairs cols, string table, MySearchUtil util)
{
using (var conn = GetConnection())
{
var _params = util.GetParameters();
List <string> _update_cols = new List <string>();
int idx = 0;
string _paramName = "";
foreach (var kv in cols)
{
_paramName = _paramPrefix + "c" + idx.ToString();
_update_cols.Add(kv.Key + "=" + _paramName);
_params.Add(_paramName, kv.Value);
idx++;
}
string sql = "UPDATE [" + table + "] SET " + string.Join(",", _update_cols) + " WHERE " + util.GetConditionClaus();
return(conn.Execute(sql, _params));
}
}
public int GetCount(string tableName, MySearchUtil util = null)
{
string condition = "";
DynamicParameters param = null;
if (util != null)
{
condition = util.GetConditionClaus();
param = util.GetParameters();
}
else
{
condition = "1=1";
}
using (var conn = GetConnection())
{
string sql = "SELECT COUNT(0) FROM [" + tableName + "] WHERE " + condition;
return(conn.ExecuteScalar <int>(sql, param));
}
}
/// <summary>
/// 获取分页动态数据列表
/// </summary>
/// <param name="util">查询参数</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页条数</param>
/// <param name="table">要查询的表</param>
/// <param name="cols">要查询的列</param>
/// <returns></returns>
public PageList <dynamic> Query(MySearchUtil util, int pageIndex, int pageSize, string table, string cols = "*")
{
string where = util.GetConditionClaus();
string orderby = util.GetOrderByClaus();
DynamicParameters param = util.GetPageListParameters();
string sql = GetPagingQuerySql(cols, table, where, orderby, pageIndex, pageSize);
using (var conn = GetConnection())
{
var list = conn.Query(sql, param);
var total = param.Get <int>("RecordCount");
return(new PageList <dynamic>
{
Body = list,
PageIndex = pageIndex,
PageSize = pageSize,
RecordCount = total
});
}
}
public int Update(KeyValuePair <string, object>[] cols, string table, MySearchUtil mcu = null)
{
using (var conn = GetConnection())
{
if (mcu == null)
{
mcu = new MySearchUtil();
}
var _params = mcu.Parameters;
var updateCols = new List <string>();
var idx = 0;
foreach (var kv in cols)
{
var paramName = _paramPrefix + "c" + idx;
updateCols.Add(kv.Key + "=" + paramName);
_params.Add(paramName, kv.Value);
idx++;
}
var sql = "UPDATE [" + table + "] SET " + string.Join(",", updateCols) + " WHERE " + mcu.ConditionClaus;
return(conn.Execute(sql, _params));
}
}
public int GetCount <T>(MySearchUtil util = null, string tableName = "")
{
string condition;
DynamicParameters param = null;
if (util != null)
{
condition = util.ConditionClaus;
param = util.Parameters;
}
else
{
condition = "1=1";
}
if (string.IsNullOrWhiteSpace(tableName))
{
tableName = GetTableName(typeof(T));
}
using (var conn = GetConnection())
{
return(conn.ExecuteScalar <int>($"SELECT COUNT(0) FROM [{tableName}] WHERE " + condition, param));
}
}
public string GetUpdateSqlStatement <T>(string[] columns, bool isExclude = false, MySearchUtil util = null)
{
try
{
var entity = MyContainer.Get(typeof(T));
var sql = MyEntityUtil.BuildUpdateSqlStatement(entity, columns, isExclude, util == null ? "" : util.GetConditionClaus());
return(sql);
}
catch
{
throw new Exception("指定类型未在MyContainer中注册");
}
}
public int Update <T>(KeyValuePairList cols, MySearchUtil mcu = null)
{
return(Update <T>(cols.ToArray(), mcu));
}
public int Update(KeyValuePairList cols, string tableName, MySearchUtil mcu = null)
{
return(Update(cols.ToArray(), tableName, mcu));
}