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 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, 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 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 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 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));
}
public static PageList <T> PageList <T>(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 <T>(sql, param);
return(new PageList <T>
{
Body = body.ToList(),
PageIndex = pageIndex,
PageSize = pageSize,
RecordCount = param.Get <int>("RecordCount")
});
}
