public bool Delete(Where where) {
using (SqlCommand command = new DbHelper().Command) {
string sql = $"delete from {Table} where {where.Result};";
return command.ExecuteNonQueryExt(sql, where) > 0;
}
}
public static async Task<List<Model.User>> GetListUser(Where where, string fields, string orderby, int pageIndex,
int pageSize) {
return
await
Task<List<Model.User>>.Factory.StartNew(
() => Dal.GetList(where, fields, orderby, pageIndex, pageSize));
}
public static string Edit(HttpContext context) {
if (string.IsNullOrEmpty(App.UserID))
throw new AuthenticationException("用户未登录");
string id = context.Request.Form["_id"].ToStringExt();
if (string.IsNullOrEmpty(id))
throw new ArgumentNullException("id", "缺少参数_id");
string name = context.Request.Form["name"];
string sex = context.Request.Form["sex"];
string birthday = context.Request.Form["birthday"];
string email = context.Request.Form["email"];
Where @where = new Where(new Where.Item("ID", "=", id));
FreedomDB.Bridge.Update update = new Update();
update.Dic = new Dictionary<string, dynamic>() {
{"Email", email},
{"Birthday", birthday.StrToDateTime(DateTime.MaxValue)},
{"Name", name},
{"Sex", sex.ToInt(0)}
};
update.WhereCore = where;
bool success = Bll.User_Bll.UpdateUser(update);
ApiInfo apiInfo = new ApiInfo(SystemCode.Error, "更新失败");
if (success) {
apiInfo = new ApiInfo(SystemCode.Success, "更新成功");
}
return apiInfo.ToJson();
}
public User GetOne(Where @where, string fields) {
using (SqlCommand command = new DbHelper().Command) {
User user = new User();
string sql = $"select top 1 {fields} from {Table} where {where.Result};";
IDataReader reader = command.ExecuteReaderExt(sql, where);
if (reader.Read()) {
user = ReaderModel(reader, fields);
}
return user;
}
}
public int GetRecordCount(Where @where) {
string sql = $"select count(1) from {Table} where {where.Result};";
using (SqlCommand command = new DbHelper().Command) {
command.CommandText = sql;
command.CommandType=CommandType.Text;
object objCount = command.ExecuteScalar();
return objCount.ToInt();
}
}
public List<User> GetList(Where where, string fields, string orderby, int pageIndex, int pageSize) {
string sqlBase = $"select {fields} from {Table} where {@where.Result} ";
const string sqlPageBase = @"select * from(
select *,ROW_NUMBER() OVER (ORDER BY {1}) as rank from ({0})a
)as t where t.rank between {2} and {3}";
int startPageIndex = (pageIndex - 1)*pageSize + 1;
int endPageIndex = pageIndex*pageSize;
string sqlPage = string.Format(sqlPageBase, sqlBase, orderby, startPageIndex, endPageIndex);
using (SqlCommand command = new DbHelper().Command) {
List<User> users = new List<User>();
IDataReader reader = command.ExecuteReaderExt(sqlPage, where);
while (reader.Read()) {
User user = ReaderModel(reader, fields);
users.Add(user);
}
return users;
}
}
public SqlParameter[] CreateWhereSqlParameters(Where where) {
List<Where.Item> items = where.WhereItems;
List<SqlParameter> parameters = items.Select(item => new SqlParameter($"@{item.Field}", item.Value)).ToList();
return parameters.ToArray();
}
public static async Task<Model.User> GetOneUser(Where @where, string fields) {
return await Task<Model.User>.Factory.StartNew(() => Dal.GetOne(where, fields));
}
public static async Task<bool> DeleteUser(Where where) {
return await Task<bool>.Factory.StartNew(() => Dal.Delete(where));
}
public static User_Model GetOneUser(Where @where, string fields) {
return Dal.GetOne(where, fields);
}
/// <summary>
/// 用于读取数据
/// </summary>
/// <param name="command"></param>
/// <param name="sql"></param>
/// <param name="where"></param>
/// <returns></returns>
public static IDataReader ExecuteReaderExt(this System.Data.Common.DbCommand command, string sql, Where where) {
command.CommandText = sql;
command.Parameters.AddRange(command.CreateWhereSqlParameters(where));
return command.ExecuteReader();
}
public static string List(HttpContext context) {
if (string.IsNullOrEmpty(App.UserID))
throw new AuthenticationException("用户未登录");
int pageIndex = context.Request.QueryString["pageIndex"].ToInt(0);
int pageSize = context.Request.QueryString["pageSize"].ToInt(10);
FreedomDB.Bridge.Where where = new Where();
where.Add(new Where.Item("1", "=", "1"));
var listUser = Bll.User_Bll.GetListUser(where, "*", "ID DESC", pageIndex, pageSize);
PageJsonTpl<List<XK.Model.User_Model>> listJsonTpl = new PageJsonTpl<List<User_Model>>();
listJsonTpl.data = listUser;
listJsonTpl.msg = "";
listJsonTpl.code = 1;
listJsonTpl.total = Bll.User_Bll.GetRecordCount(where);
listJsonTpl.pageindex = pageIndex;
string extjson = Common.json.JsonHelper<PageJsonTpl<List<XK.Model.User_Model>>>.Serialize2Object(listJsonTpl);
return extjson;
}
public static string GetOne(HttpContext context) {
string id = context.Request.GetValExt("id");
if (string.IsNullOrEmpty(id))
throw new ArgumentNullException("id", "缺少参数id");
Where where = new Where(new Where.Item("ID", "=", id));
Model.User_Model userModel = Bll.User_Bll.GetOneUser(where, "*");
JsonTpl<Model.User_Model> jsonTpl = new JsonTpl<User_Model>() {code = SystemCode.Error, msg = "exception"};
if (userModel.ID > 0) {
jsonTpl.data = userModel;
jsonTpl.code = SystemCode.Success;
jsonTpl.msg = "success";
}
return jsonTpl.ToJson();
}
public static string Delete(HttpContext context) {
if (string.IsNullOrEmpty(App.UserID))
throw new AuthenticationException("用户未登录");
int uid = context.Request.GetValExt("uid").ToInt();
if (uid < 1)
throw new ArgumentNullException("uid", "用户ID不能为空");
Where where=new Where();
where.Add(new Where.Item("ID", "=", uid));
bool success = Bll.User_Bll.DeleteUser(where);
ApiInfo apiInfo = new ApiInfo(SystemCode.Error, "删除失败");
if (success) {
apiInfo = new ApiInfo(SystemCode.Success, "删除成功");
}
return apiInfo.ToJson();
}
public static int GetRecordCount(Where where) {
return Dal.GetRecordCount(where);
}
public static List<User_Model> GetListUser(Where where, string fields, string orderby, int pageIndex,
int pageSize) {
return Dal.GetList(where, fields, orderby, pageIndex, pageSize);
}
public int GetRecordCount(Where @where) {
string sql = $"select count(1) total from {Table} where {where.Result};";
using (SqlCommand command = new DbHelper().Command) {
command.CommandText = sql;
command.CommandType = CommandType.Text;
IDataReader reader = command.ExecuteReaderExt(sql, where);
if (reader.Read()) {
object countObj = reader["total"];
return countObj.ToInt();
}
return 0;
}
}
public static bool DeleteUser(Where where) {
return Dal.Delete(where);
}