public List <T> Fetch <T>(MySearchUtil util = null, string cols = "*", int top = 0)
{
using (var conn = GetConnection())
{
return(conn.Fetch <T>(util, cols, top));
}
}
public static int Remove <T>(this SqlConnection connection, MySearchUtil util, SqlTransaction trans = null)
where T : BaseEntity, IRemoveAble
{
var table = MyContainer.Get(typeof(T)).Table;
return(connection.Remove(table, util, trans));
}
public int Count(string table, MySearchUtil util = null)
{
using (var conn = GetConnection())
{
return(conn.Count(table, util));
}
}
public int Delete(string table, MySearchUtil searchUtil)
{
using (var conn = GetConnection())
{
return(conn.Delete(table, searchUtil));
}
}
public int Update(string table, KeyValuePairs kvs, MySearchUtil searchUtil)
{
using (var conn = GetConnection())
{
return(conn.Update(table, kvs, searchUtil));
}
}
public int Count <T>(MySearchUtil util = null) where T : BaseEntity
{
using (var conn = GetConnection())
{
return(conn.Count <T>(util));
}
}
public int Remove(string table, MySearchUtil util)
{
using (var conn = GetConnection())
{
return(conn.Remove(table, util));
}
}
public int Update <T>(KeyValuePairs kvs, MySearchUtil searchUtil)
{
using (var conn = GetConnection())
{
return(conn.Update <T>(kvs, searchUtil));
}
}
public PageList Query(string table, int pageIndex, int pageSize, MySearchUtil util, string cols = "*")
{
using (var conn = GetConnection())
{
return(conn.PageList(table, pageIndex, pageSize, util, cols));
}
}
public T Load <T>(MySearchUtil searchUtil, string table = "")
{
using (var conn = GetConnection())
{
return(conn.Load <T>(searchUtil, null, table));
}
}
public List <dynamic> Fetch(string table, MySearchUtil util = null, string cols = "*", int top = 0)
{
using (var conn = GetConnection())
{
return(conn.Fetch(table, util, cols, top));
}
}
public int Delete <T>(MySearchUtil util) where T : BaseEntity
{
using (var conn = GetConnection())
{
return(conn.Delete <T>(util));
}
}
public static int Count(this SqlConnection connection, string table, MySearchUtil util = null, SqlTransaction trans = null)
{
util = util ?? new MySearchUtil();
var where = util.GetWhere();
var param = util.GetParam();
var sql = $"SELECT COUNT(0) FROM [{table}] WHERE {where}";
return(connection.ExecuteScalar <int>(sql, param, trans));
}
public static int Remove(this SqlConnection connection, string table, MySearchUtil util, SqlTransaction trans = null)
{
if (util == null)
{
throw new ArgumentNullException(nameof(util));
}
var where = util.GetWhere();
var param = util.GetParam();
var sql = $"UPDATE [{table}] SET IsDel=1 WHERE {where}";
return(connection.Execute(sql, param, trans));
}
public static T Load <T>(this SqlConnection connection, MySearchUtil util = null, SqlTransaction trans = null, string table = "")
{
util = util ?? new MySearchUtil();
table = string.IsNullOrWhiteSpace(table) ? MyContainer.Get(typeof(T)).Table : table;
var where = util.GetWhere();
var orderBy = util.GetOrderBy();
var param = util.GetParam();
var sql = $"SELECT TOP 1 * FROM [{table}] WHERE {where} ORDER BY {orderBy}";
return(connection.QueryFirst(sql, param, trans));
}
public static int Remove(this SqlConnection connection, string table, BaseAppUser user,
MySearchUtil util, SqlTransaction trans = null)
{
if (user == null)
{
throw new ArgumentNullException(nameof(user));
}
if (util == null)
{
throw new ArgumentNullException(nameof(util));
}
var where = util.GetWhere();
var param = util.GetParam();
param.Add("Updator", user.Name);
var sql = $"UPDATE [{table}] SET IsDel=1,UpdateAt=GETDATE(),Updator=@Updator WHERE {where}";
return(connection.Execute(sql, param, trans));
}
public static PageList PageList(this SqlConnection connection,
string table, int pageIndex, int pageSize, MySearchUtil util, string cols = "*")
{
if (util == null)
{
throw new ArgumentNullException(nameof(util), "查询参数不能为空，至少要提供一个排序");
}
var where = util.GetWhere();
var param = util.GetParam(true);
var orderBy = util.GetOrderBy();
var sql = SqlBuilder.GetPaginSelect(table, cols, where, orderBy, pageIndex, pageSize);
var body = connection.Query(sql, param);
return(new PageList
{
Body = body.ToList(),
PageIndex = pageIndex,
PageSize = pageSize,
RecordCount = param.Get <int>("RecordCount")
});
}
public static PageList <T> PageList <T>(this SqlConnection connection, int pageIndex, int pageSize, MySearchUtil util, string cols = "*")
{
var table = MyContainer.Get(typeof(T)).Table;
return(connection.PageList <T>(table, pageIndex, pageSize, util, cols));
}
public static List <T> Fetch <T>(this SqlConnection connection, MySearchUtil util = null, string cols = "*", int top = 0)
{
var table = MyContainer.Get(typeof(T)).Table;
return(connection.Fetch <T>(table, util, cols, top));
}
public static List <T> Fetch <T>(this SqlConnection connection, string table, MySearchUtil util = null, string cols = "*", int top = 0)
{
cols = string.IsNullOrWhiteSpace(cols) ? "*" : cols;
util = util ?? new MySearchUtil();
var where = util.GetWhere();
var param = util.GetParam();
var orderBy = util.GetOrderBy();
var topStr = top > 0 ? $" TOP {top}" : "";
var sql = $"SELECT{topStr} {cols} FROM [{table}] WHERE {where} ORDER BY {orderBy}";
return(connection.Query <T>(sql, param).ToList());
}
public static int Delete <T>(this SqlConnection connection, MySearchUtil util = null)
{
var table = MyContainer.Get(typeof(T)).Table;
return(connection.Delete(table, util));
}
public static int Update <T>(this SqlConnection connection, KeyValuePairs kvs, MySearchUtil util, SqlTransaction trans = null)
{
var table = MyContainer.Get(typeof(T)).Table;
return(connection.Update(table, kvs, util, trans));
}
public static int Update(this SqlConnection connection, string table, KeyValuePairs kvs, MySearchUtil util = null, SqlTransaction trans = null)
{
if (string.IsNullOrWhiteSpace(table))
{
throw new ArgumentNullException(nameof(table), "数据表不能为空");
}
if (kvs == null || kvs.Any(kv => kv.Key == "Id"))
{
throw new ArgumentNullException(nameof(kvs), "要更新的列不能为空，并且不能包含Id列");
}
util = util ?? new MySearchUtil();
var cols = string.Join(",", kvs.Where(kv => kv.Key != "Id").Select(kv => $"{kv.Key}=@{kv.Key}"));
var where = util.GetWhere();
var param = util.GetParam();
var sql = $"UPDATE {table} SET {cols} WHERE {where}";
return(connection.Execute(sql, param, trans));
}
