public string GetCurrentIdDb(string token) { using (var db = new FBLEntities()) { return(db.Tokens.FirstOrDefault(x => string.Equals(x.Token1, token))?.User_ID); } }
public IEnumerable <string> GetFriendIdList(string cUId) { using (var db = new FBLEntities()) { return(db.FLs.Where(x => x.F_ID == cUId).Select(x => x.F_ID).ToList()); } }
public IEnumerable <string> GetIdPostListByFId(string fId, int limit) { using (var db = new FBLEntities()) { return(db.PLs.Where(x => x.F_ID == fId).OrderBy(x => x.P_Time).Take(limit).Select(x => x.P_ID).ToList()); } }
public List <string> GetPostDoneOfF(string fId) { using (var db = new FBLEntities()) { return(db.PLs.Where(x => string.Equals(x.F_ID, fId)).Select(x => x.P_ID).ToList()); } }
public List <string> GetCommentDoneList(int typeC) { using (var db = new FBLEntities()) { var lst = db.CLs.Where(c => c.CL_TYPE == typeC).Select(x => x.P_ID).ToList(); return(lst); } }
/* * public string DbSource { get; set; } * public string DbName { get; set; } * public string User { get; set; } * public string Pwd { get; set; } * * private SqlConnection GetConnection() * { * try * { * var cnnS = * $"Data Source={DbSource};Initial Catalog={DbName};Persist Security Info=True;User ID={User};Password={Pwd}"; * return new SqlConnection(cnnS); * } * catch (Exception e) * { * Utilities.WriteLog(e.Message); * return null; * } * * } * * private SqlDataReader Reader(string queryString) * { * try * { * using (var cnn = GetConnection()) * { * cnn.Open(); * var command = new SqlCommand(queryString, cnn); * * var reader = command.ExecuteReader(); * return reader; * } * * } * catch (Exception e) * { * Utilities.WriteLog(e.Message); * return null; * } * } * #region POSTS * * public Posts GetTopIdPostsFromDb(int topLimit) * { * var qr = $"select top {topLimit} P_ID,P_Time,Message from PL where Done <> 1 orderby P_Time"; * var lP = new List<Post>(); * try * { * using (var reader = Reader(qr)) * { * if (!reader.HasRows) * { * return null; * } * while (reader.Read()) * { * lP.Add(new Post(reader.GetString(0), reader.GetString(1), reader.GetString(2))); * } * return new Posts { data = lP }; * } * } * catch (Exception e) * { * Utilities.WriteLog(e.Message); * return null; * } * * } * public Posts GetidPostsFromDbByFId(string fid,int limit) * { * var qr = $"select top {limit} P_ID,P_Time,Message from PL where F_ID = '{fid}' and Done <> 1 orderby P_Time"; * var lP = new List<Post>(); * try * { * using (var reader = Reader(qr)) * { * if (!reader.HasRows) * { * return null; * } * while (reader.Read()) * { * lP.Add(new Post(reader.GetString(0), reader.GetString(1), reader.GetString(2))); * } * return new Posts { data = lP }; * } * } * catch (Exception e) * { * Utilities.WriteLog(e.Message); * return null; * } * * } * * public bool InsertPost(string pId, string pTime, string fId, string message) * { * try * { * var qr = $"insert into PL(P_ID,P_Time,F_ID, Message) select '{pId}','{pTime}','{fId}','{message}'" + $" where not exists (select P_ID from PL where P_ID = '{pId}')"; + using (var cnn = GetConnection()) + { + cnn.Open(); + var cmm = new SqlCommand(qr, cnn); + var rs = cmm.ExecuteNonQuery(); + return rs > 0; + } + } + catch (Exception e) + { + Utilities.WriteLog(e.Message); + return false; + } + } + + public bool UpdatePost(string pId, bool done) + { + try + { + var qr = "Update PL " + $"Set Done = {done},Done_Time = '{DateTime.Now}'" +$"where P_ID = '{pId}'"; + using (var cnn = GetConnection()) + { + cnn.Open(); + var cmm = new SqlCommand(qr, cnn); + var rs = cmm.ExecuteNonQuery(); + return rs > 0; + } + } + catch (Exception e) + { + Utilities.WriteLog(e.Message); + return false; + } + } #endregion + #region Friends + + + public Friends GetidFriendsFromDb(string fId) + { + var qr = $"select F_ID,Name from FL where F_OF_ID = '{fId}'"; + var lP = new List<User>(); + try + { + using (var reader = Reader(qr)) + { + //if (!reader.HasRows) + //{ + // return null; + //} + while (reader.Read()) + { + lP.Add(new User(reader.GetString(0), reader.GetString(1))); + } + return new Friends() { data = lP }; + } + } + catch (Exception e) + { + Utilities.WriteLog(e.Message); + return null; + } + + } + + public bool InserFriend(string fid, string name, string userId) + { + try + { + var qr = $"insert into FL(F_ID,Name,F_OF_ID) select '{fid}',N'{name.Replace("'","''")}','{userId}'" + $" where not exists (select F_ID from FL where F_ID = '{fid}')"; + using (var cnn = GetConnection()) + { + cnn.Open(); + var cmm = new SqlCommand(qr, cnn); + var rs = cmm.ExecuteNonQuery(); + return rs > 0; + } + } + catch (Exception e) + { + Utilities.WriteLog(e.Message); + return false; + } + } + + public bool UpdateFriendLikeCount(string fId) + { + try + { + var qr = "Update FL " + $"Set LC = (LC+1) where F_ID = '{fId}'"; + using (var cnn = GetConnection()) + { + cnn.Open(); + var cmm = new SqlCommand(qr, cnn); + var rs = cmm.ExecuteNonQuery(); + return rs > 0; + } + } + catch (Exception e) + { + Utilities.WriteLog(e.Message); + return false; + } + } + #endregion + #region Token + + public bool InserToken(string userId, string name, string token) + { + try + { + var qr = $"insert into Token(User_ID,Name,Token) select '{userId}' as user_id,'{name}' as name,'{token}' as token" + $" where not exists (select User_ID from Token where user_id = '{userId}')"; + using (var cnn = GetConnection()) + { + cnn.Open(); + var cmm = new SqlCommand(qr, cnn); + var rs = cmm.ExecuteNonQuery(); + return rs > 0; + } + } + catch (Exception e) + { + Utilities.WriteLog(e.Message); + return false; + } + } + + public string GetUId(string token) + { + using (var cnn = GetConnection()) + { + if(cnn.State == ConnectionState.Closed) cnn.Open(); + var query = $"select top 1 User_ID from Token where Token like '{token}'"; + var cmm = new SqlCommand(query, cnn); + var reader = cmm.ExecuteReader(); + if (reader.Read()) + return reader.GetString(0); + return null; + } + } + #endregion */ public void InsertToken(Token token) { using (var db = new FBLEntities()) { db.Tokens.Add(token); db.SaveChanges(); } }
public void UpdateLc(string fId) { using (var db = new FBLEntities()) { var en = db.FLs.FirstOrDefault(x => x.F_ID == fId); if (en != null) { en.LC = ++en.LC; } db.SaveChanges(); } }
public void UpdatePostStatus(string pId) { using (var db = new FBLEntities()) { var p = db.PLs.FirstOrDefault(x => x.P_ID == pId); if (p != null) { p.Done = true; p.Done_Time = DateTime.Now.ToString(CultureInfo.InvariantCulture); db.SaveChanges(); } } }
public void InsertPostDone(Post post) { using (var db = new FBLEntities()) { if (!db.PLs.Any(x => x.P_ID == post.Id)) { db.PLs.Add(new PL() { P_ID = post.Id, P_Time = post.Created_time, F_ID = post.Id.Substring(0, post.Id.IndexOf("_")), Message = post.Message, Done_Time = DateTime.Now.ToString() }); } db.SaveChanges(); } }
internal void InsertCommentDone(string idTocomment, string uId, string uCId, string message, int cltype) { var cl = new CL() { P_ID = idTocomment, P_OF_ID = uId, U_CM_ID = uCId, Message = message, CL_TYPE = cltype }; using (var db = new FBLEntities()) { if (!db.CLs.Any(x => string.Equals(x.P_ID, idTocomment))) { db.CLs.Add(cl); } db.SaveChanges(); } }
public string GetCurrentUserId(string token) { using (var db = new FBLEntities()) { try { var user = db.Tokens.FirstOrDefault(x => x.Token1.Contains(token)); return(user?.User_ID); } catch (Exception e) { Utilities.WriteLog(e.Message); return(null); } } }
public void InsertFriends(Friends friends, string cUId) { using (var db = new FBLEntities()) { var frs = friends.data; foreach (var f in frs) { if (!db.FLs.Any(x => x.F_ID == f.Id)) { db.FLs.Add(new FL() { F_ID = f.Id, F_OF_ID = cUId, Name = f.Name }); } } db.SaveChanges(); } }