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") }); }