public static IList <SourceMaterial> GetList(int sellerId, int pageIndex, int pageSize) { var results = new List <SourceMaterial>(); int skipnum = pageSize * pageIndex; int totalnum = 0; string commandText = @"select * from material where sellerId = ?sellerId LIMIT ?index,?size"; List <MySqlParameter> parameters = new List <MySqlParameter>(); parameters.Add(new MySqlParameter("?sellerId", sellerId)); parameters.Add(new MySqlParameter("?index", pageIndex)); parameters.Add(new MySqlParameter("?size", pageSize)); try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray()); while (reader.Read()) { SourceMaterial sm = new SourceMaterial(); sm.Id = reader.GetInt32(0); sm.Url = reader["Url"].ToString(); sm.Title = reader["Title"].ToString(); sm.Description = reader["Description"].ToString(); sm.Views = (int)reader["Views"]; sm.Commentnum = (int)reader["Commentnum"]; sm.SellerId = (int)reader["SellerId"]; sm.CreateTime = (DateTime)reader["CreateTime"]; sm.ImageType = (UploadType)reader["ImageType"]; results.Add(sm); } } } catch (System.Exception ex) { throw; } return(results); }
/// <summary> /// 活动列表 /// </summary> /// <param name="start"></param> /// <param name="limit"></param> /// <param name="wheresql"></param> /// <param name="ordersql"></param> /// <returns></returns> public static IList<Active> GetList(int start, int limit, string wheresql = "", string ordersql = "") { var results = new List<Active>(); string limitsql = limit == 0 ? string.Empty : " LIMIT ?start,?limit "; string commandText = @"select * from active " + wheresql + ordersql + limitsql; List<MySqlParameter> parameters = new List<MySqlParameter>(); parameters.Add(new MySqlParameter("?start", start)); parameters.Add(new MySqlParameter("?limit", limit)); try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray()); while (reader.Read()) { Active a = new Active(); a.Id = (int)reader["Id"]; a.SellerId = (int)reader["SellerId"]; a.CouponId = (int)reader["CouponId"]; a.CoverImgId = (int)reader["CoverImgId"]; a.CoverImgUrl = reader["CoverImgUrl"].ToString(); a.Title = reader["Title"].ToString(); a.Summary = reader["Summary"].ToString(); a.Description = reader["Description"].ToString(); a.CreateTime = (DateTime)reader["CreateTime"]; a.Views = (int)reader["Views"]; a.Commentnum = (int)reader["Commentnum"]; results.Add(a); } } } catch (System.Exception ex) { throw; } return results; }
/// <summary> /// 获取用户评论列表 /// </summary> /// <param name="uid"></param> /// <param name="ids"></param> /// <returns></returns> public static List <Comment> GetList(int uid, string ids) { var list = new List <Comment>(); //string commandText = @"select * from comment where Id IN(??afsdfsdf);"; string commandText = string.Format("select * from comment where UserId = {0} and Id in({1});", uid, ids); //List<MySqlParameter> parameters = new List<MySqlParameter>(); //parameters.Add(new MySqlParameter("?UserId", uid)); //gidList = new List<int>() { 215, 216 }; //parameters.Add(new MySqlParameter("??afsdfsdf", string.Join(",", gidList.ToArray()))); try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText);//, parameters.ToArray() while (reader.Read()) { Comment c = new Comment(); c.Id = (int)reader["Id"]; c.SellerId = (int)reader["SellerId"]; c.Type = (CommentType)reader["Type"]; c.TypeId = (int)reader["TypeId"]; c.Content = reader["Content"].ToString(); c.Img = reader["Img"].ToString(); c.Title = reader["Title"].ToString(); c.CreateTime = (DateTime)reader["CreateTime"]; c.UserId = (int)reader["UserId"]; list.Add(c); } } } catch (System.Exception ex) { throw; } return(list); }
/// <summary> /// /// </summary> /// <param name="start"></param> /// <param name="limit"></param> /// <param name="wheresql"></param> /// <param name="ordersql"></param> /// <returns></returns> public static IList <SourceMaterial> GetList(int start, int limit, string wheresql = "", string ordersql = "") { var results = new List <SourceMaterial>(); string limitsql = limit == 0 ? string.Empty : " LIMIT ?start,?limit "; string commandText = @"select * from material " + wheresql + ordersql + limitsql; List <MySqlParameter> parameters = new List <MySqlParameter>(); parameters.Add(new MySqlParameter("?start", start)); parameters.Add(new MySqlParameter("?limit", limit)); try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray()); while (reader.Read()) { SourceMaterial sm = new SourceMaterial(); sm.Id = reader.GetInt32(0); sm.Url = reader["Url"].ToString(); sm.Title = reader["Title"].ToString(); sm.Description = reader["Description"].ToString(); sm.Views = (int)reader["Views"]; sm.Commentnum = (int)reader["Commentnum"]; sm.SellerId = (int)reader["SellerId"]; sm.CreateTime = (DateTime)reader["CreateTime"]; sm.ImageType = (UploadType)reader["ImageType"]; results.Add(sm); } } } catch (System.Exception ex) { throw; } return(results); }
public static bool JudgeUserLogin(string phone, string password, int sellerId) { var sql = @"select * from account where pwd=?password and phone=?phone and sellerId=?sellerId limit 1;"; List <MySqlParameter> parameters = new List <MySqlParameter>(); parameters.Add(new MySqlParameter("?password", password)); parameters.Add(new MySqlParameter("?phone", phone)); parameters.Add(new MySqlParameter("?sellerId", sellerId)); //parameters.Add(new MySqlParameter("?RoleType", (int)RoleType.ThirdUser)); try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, sql, parameters.ToArray()); return(reader.HasRows); } } catch (System.Exception ex) { throw; } return(false); }
public static bool JudgeUser(string phone, int sellerId)//, string username { //(username=?username or var sql = @"select * from account where sellerId=?sellerId and phone=?phone and Status >= 0 limit 1;"; List <MySqlParameter> parameters = new List <MySqlParameter>(); //parameters.Add(new MySqlParameter("?username", username)); parameters.Add(new MySqlParameter("?phone", phone)); parameters.Add(new MySqlParameter("?sellerId", sellerId)); try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, sql, parameters.ToArray()); return(reader.HasRows); } } catch (System.Exception ex) { throw; } return(false); }
/// <summary> /// /// </summary> /// <param name="sellId"></param> /// <param name="type"></param> /// <param name="index"></param> /// <param name="size"></param> /// <param name="order">排序sql</param> /// <returns></returns> public static PagResults <Comment> GetPagings(int sellerId, CommentType type, int typeId, int index, int size, string order = "order by CreateTime desc") { var results = new PagResults <Comment>(); results.Results = new List <Comment>(); int skipnum = index * size; int totalnum = 0; string commandText = ""; if (type == CommentType.All) { commandText = @"select * from comment where sellerId = ?sellerId " + order + " LIMIT ?index,?size"; } else { commandText = @"select * from comment where sellerId = ?sellerId and type = ?type and typeId = ?typeId " + order + " LIMIT ?index,?size"; } List <MySqlParameter> parameters = new List <MySqlParameter>(); parameters.Add(new MySqlParameter("?sellerId", sellerId)); if (type != CommentType.All) { parameters.Add(new MySqlParameter("?type", (int)type)); parameters.Add(new MySqlParameter("?typeId", typeId)); } parameters.Add(new MySqlParameter("?index", index)); parameters.Add(new MySqlParameter("?size", size)); try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray()); while (reader.Read()) { Comment c = new Comment(); c.Id = (int)reader["Id"]; c.SellerId = (int)reader["SellerId"]; c.Type = (CommentType)reader["Type"]; c.TypeId = (int)reader["TypeId"]; c.Content = reader["Content"].ToString(); c.CreateTime = (DateTime)reader["CreateTime"]; c.UserId = (int)reader["UserId"]; c.Img = reader["Img"].ToString(); c.Title = reader["Title"].ToString(); c.Feedback = reader["Feedback"].ToString(); results.Results.Add(c); } //一个函数有两次连接数据库 先把连接断开 然后重连 conn.Close(); conn.Dispose(); conn.Open(); if (type == CommentType.All) { commandText = @"select count(*) from comment where sellerId = ?sellerId "; } else { commandText = "select count(*) from comment where sellerId = ?sellerId and type = ?type and typeId = ?typeId"; } parameters.Clear(); parameters.Add(new MySqlParameter("?sellerId", sellerId)); parameters.Add(new MySqlParameter("?type", (int)type)); parameters.Add(new MySqlParameter("?typeId", typeId)); reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray()); while (reader.Read()) { results.TotalCount = reader.GetInt32(0); } } } catch (System.Exception ex) { throw; } return(results); }
/// <summary> /// 获取用户评论 /// </summary> /// <param name="uid"></param> /// <param name="sellerId"></param> /// <param name="type"></param> /// <param name="start"></param> /// <param name="limit"></param> /// <returns></returns> public static PagResults <Comment> GetList(int uid, int sellerId, CommentType type, int start, int limit, int isgetcount = 0) { var results = new PagResults <Comment>(); results.Results = new List <Comment>(); var wheresql = (type == CommentType.All ? string.Empty : "and Type = ?Type "); var ordersql = " order by CreateTime desc "; var limitsql = limit != 0 ? " LIMIT ?Start,?Limit" : string.Empty; string commandText = @"select * from comment where UserId = ?UserId and SellerId = ?SellerId " + wheresql + ordersql + limitsql; List <MySqlParameter> parameters = new List <MySqlParameter>(); parameters.Add(new MySqlParameter("?UserId", uid)); parameters.Add(new MySqlParameter("?SellerId", sellerId)); parameters.Add(new MySqlParameter("?Type", (int)type)); parameters.Add(new MySqlParameter("?Start", start)); parameters.Add(new MySqlParameter("?Limit", limit)); try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray()); while (reader.Read()) { Comment c = new Comment(); c.Id = (int)reader["Id"]; c.SellerId = (int)reader["SellerId"]; c.Type = (CommentType)reader["Type"]; c.TypeId = (int)reader["TypeId"]; c.Content = reader["Content"].ToString(); c.Img = reader["Img"].ToString(); c.Title = reader["Title"].ToString(); c.CreateTime = (DateTime)reader["CreateTime"]; c.UserId = (int)reader["UserId"]; results.Results.Add(c); } if (isgetcount > 0) { //一个函数有两次连接数据库 先把连接断开 然后重连 conn.Close(); conn.Dispose(); conn.Open(); commandText = "select count(*) from comment where UserId = ?UserId and SellerId = ?SellerId " + wheresql; parameters.Clear(); parameters.Add(new MySqlParameter("?UserId", uid)); parameters.Add(new MySqlParameter("?SellerId", sellerId)); parameters.Add(new MySqlParameter("?Type", (int)type)); reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, commandText, parameters.ToArray()); while (reader.Read()) { results.TotalCount = reader.GetInt32(0); } } } } catch (System.Exception ex) { throw; } return(results); }
/// <summary> /// 插入或更新用户 /// </summary> /// <param name="account"></param> /// <returns></returns> public static int SaveAccount(Account account) { var cmdText = String.Empty; List <MySqlParameter> parameters = new List <MySqlParameter>(); if (account.Id > 0) { cmdText = @"UPDATE account SET UserName = ?UserName, NickName = ?NickName, Pwd = ?Pwd, SellerId = ?SellerId, RoleType = ?RoleType, Avatar = ?Avatar, Sex = ?Sex, LinkMan = ?LinkMan, Phone = ?Phone, Address = ?Address, Money = ?Money, Integral = ?Integral, CreateTime = ?CreateTime, RegisterTime = ?RegisterTime , Status = ?Status , Discount = ?Discount , TotalRecharge = ?TotalRecharge , TotalConsume = ?TotalConsume , TotalLoginCount = ?TotalLoginCount , TotalOrdersCount = ?TotalOrdersCount, LastLoginTime = ?LastLoginTime, Remark = ?Remark WHERE Id = ?Id"; parameters.Add(new MySqlParameter("?Id", account.Id)); parameters.Add(new MySqlParameter("?UserName", account.UserName)); parameters.Add(new MySqlParameter("?NickName", account.NickName)); parameters.Add(new MySqlParameter("?Pwd", account.Pwd)); parameters.Add(new MySqlParameter("?RoleType", account.RoleType)); parameters.Add(new MySqlParameter("?Avatar", account.Avatar)); parameters.Add(new MySqlParameter("?Sex", account.Sex)); parameters.Add(new MySqlParameter("?CreateTime", account.CreateTime)); parameters.Add(new MySqlParameter("?Phone", account.Phone)); parameters.Add(new MySqlParameter("?Address", account.Address)); parameters.Add(new MySqlParameter("?Money", account.Money)); parameters.Add(new MySqlParameter("?Integral", account.Integral)); parameters.Add(new MySqlParameter("?LinkMan", account.LinkMan)); parameters.Add(new MySqlParameter("?SellerId", account.SellerId)); parameters.Add(new MySqlParameter("?RegisterTime", account.RegisterTime)); parameters.Add(new MySqlParameter("?Status", account.Status)); parameters.Add(new MySqlParameter("?Discount", account.Discount)); parameters.Add(new MySqlParameter("?TotalRecharge", account.TotalRecharge)); parameters.Add(new MySqlParameter("?TotalConsume", account.TotalConsume)); parameters.Add(new MySqlParameter("?TotalLoginCount", account.TotalLoginCount)); parameters.Add(new MySqlParameter("?TotalOrdersCount", account.TotalOrdersCount)); parameters.Add(new MySqlParameter("?LastLoginTime", account.LastLoginTime)); parameters.Add(new MySqlParameter("?Remark", account.Remark)); } else { cmdText = @"insert into account ( UserName , NickName , Pwd , RoleType , Avatar , Sex , Phone , Address , SellerId , Money , Integral , LinkMan , RegisterTime , Status , Discount , TotalRecharge , TotalConsume , TotalLoginCount , TotalOrdersCount , LastLoginTime , Remark , CreateTime ) values ( ?UserName , ?NickName , ?Pwd , ?RoleType , ?Avatar , ?Sex , ?Phone , ?Address , ?SellerId , ?Money , ?Integral , ?LinkMan , ?RegisterTime , ?Status , ?Discount , ?TotalRecharge , ?TotalConsume , ?TotalLoginCount , ?TotalOrdersCount , ?LastLoginTime , ?Remark , ?CreateTime )"; parameters.Add(new MySqlParameter("?UserName", account.UserName)); parameters.Add(new MySqlParameter("?NickName", account.NickName)); parameters.Add(new MySqlParameter("?Pwd", account.Pwd)); parameters.Add(new MySqlParameter("?RoleType", account.RoleType)); parameters.Add(new MySqlParameter("?Avatar", account.Avatar)); parameters.Add(new MySqlParameter("?Sex", account.Sex)); parameters.Add(new MySqlParameter("?CreateTime", account.CreateTime)); parameters.Add(new MySqlParameter("?Phone", account.Phone)); parameters.Add(new MySqlParameter("?Address", account.Address)); parameters.Add(new MySqlParameter("?Money", account.Money)); parameters.Add(new MySqlParameter("?SellerId", account.SellerId)); parameters.Add(new MySqlParameter("?Integral", account.Integral)); parameters.Add(new MySqlParameter("?LinkMan", account.LinkMan)); parameters.Add(new MySqlParameter("?RegisterTime", account.RegisterTime)); parameters.Add(new MySqlParameter("?Status", account.Status)); parameters.Add(new MySqlParameter("?Discount", account.Discount)); parameters.Add(new MySqlParameter("?TotalRecharge", account.TotalRecharge)); parameters.Add(new MySqlParameter("?TotalConsume", account.TotalConsume)); parameters.Add(new MySqlParameter("?TotalLoginCount", account.TotalLoginCount)); parameters.Add(new MySqlParameter("?TotalOrdersCount", account.TotalOrdersCount)); parameters.Add(new MySqlParameter("?LastLoginTime", account.LastLoginTime)); parameters.Add(new MySqlParameter("?Remark", account.Remark)); } try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { var num = MySqlHelper.ExecuteNonQuery(conn, CommandType.Text, cmdText, parameters.ToArray()); if (account.Id == 0) { cmdText = @"select LAST_INSERT_ID();"; var reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText); if (reader.HasRows) { if (reader.Read()) { num = reader.GetInt32(0); } } } return(num); } } catch (Exception ex) { return(0); } }
public static PagResults <Account> GetUserList(string wheresql = "", string ordersql = "" , int start = 0, int limit = 0, int isgetcount = 0) { var result = new PagResults <Account>(); result.Results = new List <Account>(); if (ordersql == "") { ordersql = " order by CreateTime desc"; } string limitsql = limit != 0 ? " LIMIT ?start,?limit" : String.Empty; var cmdText = @"select * from account " + wheresql + ordersql + limitsql; List <MySqlParameter> parameters = new List <MySqlParameter>(); if (!String.IsNullOrEmpty(limitsql)) { parameters.Add(new MySqlParameter("?start", start)); parameters.Add(new MySqlParameter("?limit", limit)); } try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText, parameters.ToArray()); while (reader.Read()) { Account user = new Account(); user.Id = reader.GetInt32(0); user.UserName = reader["UserName"].ToString(); user.NickName = reader["NickName"].ToString(); user.Pwd = reader["Pwd"].ToString(); user.RoleType = (RoleType)reader["RoleType"]; user.Avatar = reader["Avatar"].ToString(); user.Sex = (SexType)reader["Sex"]; user.Phone = reader["Phone"].ToString(); user.Address = reader["Address"].ToString(); user.Money = (float)reader["Money"]; user.SellerId = (int)reader["SellerId"]; user.CreateTime = (DateTime)reader["CreateTime"]; user.Integral = (int)reader["Integral"]; user.LinkMan = reader["LinkMan"].ToString(); user.Discount = (float)reader["Discount"]; user.TotalRecharge = (float)reader["TotalRecharge"]; user.TotalConsume = (float)reader["TotalConsume"]; user.Status = (int)reader["Status"]; user.TotalLoginCount = (int)reader["TotalLoginCount"]; user.TotalOrdersCount = (int)reader["TotalOrdersCount"]; user.RegisterTime = (DateTime)reader["RegisterTime"]; user.LastLoginTime = (DateTime)reader["LastLoginTime"]; user.Remark = reader["Remark"].ToString(); result.Results.Add(user); } if (isgetcount == 1) { //一个函数有两次连接数据库 先把连接断开 然后重连 conn.Close(); conn.Dispose(); conn.Open(); cmdText = @"select count(*) from account " + wheresql; reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText); if (reader.HasRows) { if (reader.Read()) { result.TotalCount = reader.GetInt32(0); } } } } } catch (Exception ex) { throw; } return(result); }
public static IList <Account> GetUserList(IList <int> uids) { IList <Account> list = new List <Account>(); if (uids.Count < 1) { return(list); } string ids_string = "("; foreach (var uid in uids) { ids_string += (uid + ","); } ids_string = ids_string.TrimEnd(',') + ")"; string cmdText = String.Format(@"select * from account where id in {0}", ids_string); try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText); while (reader.Read()) { Account user = new Account(); user.Id = reader.GetInt32(0); user.UserName = reader["UserName"].ToString(); user.NickName = reader["NickName"].ToString(); user.Pwd = reader["Pwd"].ToString(); user.RoleType = (RoleType)reader["RoleType"]; user.Avatar = reader["Avatar"].ToString(); user.Sex = (SexType)reader["Sex"]; user.Phone = reader["Phone"].ToString(); user.Address = reader["Address"].ToString(); user.Money = (float)reader["Money"]; user.SellerId = (int)reader["SellerId"]; user.CreateTime = (DateTime)reader["CreateTime"]; user.Integral = (int)reader["Integral"]; user.LinkMan = reader["LinkMan"].ToString(); user.Discount = (float)reader["Discount"]; user.TotalRecharge = (float)reader["TotalRecharge"]; user.TotalConsume = (float)reader["TotalConsume"]; user.Status = (int)reader["Status"]; user.TotalLoginCount = (int)reader["TotalLoginCount"]; user.TotalOrdersCount = (int)reader["TotalOrdersCount"]; user.RegisterTime = (DateTime)reader["RegisterTime"]; user.LastLoginTime = (DateTime)reader["LastLoginTime"]; user.Remark = reader["Remark"].ToString(); list.Add(user); } } } catch (Exception ex) { throw; } return(list); }
public static PagResults <Account> GetPagAdmins(int index, int size) { var items = new PagResults <Account>(); string cmdText = @"select * from account where (roletype=1 or roletype=2) and Status != -1 limit ?index,?size"; List <MySqlParameter> parameters = new List <MySqlParameter>(); parameters.Add(new MySqlParameter("?index", index)); parameters.Add(new MySqlParameter("?size", size)); try { using (var conn = Utility.ObtainConn(Utility._gameDbConn)) { MySqlDataReader reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText, parameters.ToArray()); while (reader.Read()) { Account user = new Account(); user.Id = reader.GetInt32(0); user.UserName = reader["UserName"].ToString(); user.NickName = reader["NickName"].ToString(); user.Pwd = reader["Pwd"].ToString(); user.RoleType = (RoleType)reader["RoleType"]; user.Avatar = reader["Avatar"].ToString(); user.Sex = (SexType)reader["Sex"]; user.Phone = reader["Phone"].ToString(); user.Address = reader["Address"].ToString(); user.Money = (float)reader["Money"]; user.SellerId = (int)reader["SellerId"]; user.CreateTime = (DateTime)reader["CreateTime"]; user.Integral = (int)reader["Integral"]; user.LinkMan = reader["LinkMan"].ToString(); user.Discount = (float)reader["Discount"]; user.TotalRecharge = (float)reader["TotalRecharge"]; user.TotalConsume = (float)reader["TotalConsume"]; user.Status = (int)reader["Status"]; user.TotalLoginCount = (int)reader["TotalLoginCount"]; user.TotalOrdersCount = (int)reader["TotalOrdersCount"]; user.RegisterTime = (DateTime)reader["RegisterTime"]; user.LastLoginTime = (DateTime)reader["LastLoginTime"]; user.Remark = reader["Remark"].ToString(); items.Results.Add(user); } //一个函数有两次连接数据库 先把连接断开 然后重连 conn.Close(); conn.Dispose(); conn.Open(); cmdText = @"select count(*) from account where (roletype=1 or roletype=2) and Status != -1 "; reader = MySqlHelper.ExecuteReader(conn, CommandType.Text, cmdText); if (reader.HasRows) { if (reader.Read()) { items.TotalCount = reader.GetInt32(0); } } } } catch (Exception ex) { throw; } return(items); }