public CardUserDTO GetUser(int id) { CardUserDTO user; this.ConnectToDatabase(); MySqlCommand command = this.mySQLConnection.CreateCommand(); command.CommandText = "SELECT * FROM USERS WHERE USER_ID = " + id; MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int userId = reader.GetInt32(0); string userName = reader.GetString(1); string password = reader.GetString(2); string name = reader.GetString(3); user = new CardUserDTO(userId, userName, password, name); return(user); } reader.Close(); this.Close(); return(null); }
public List <CardUserDTO> GetAllUser() { List <CardUserDTO> listCardUser = new List <CardUserDTO>(); this.ConnectToDatabase(); MySqlCommand command = this.mySQLConnection.CreateCommand(); command.CommandText = "SELECT * FROM USERS "; MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int userId = reader.GetInt32(0); string userName = reader.GetString(1); string password = reader.GetString(2); string name = reader.GetString(3); CardUserDTO user = new CardUserDTO(userId, userName, password, name); listCardUser.Add(user); } reader.Close(); this.Close(); return(listCardUser); }
public bool UpdateUser(CardUserDTO user) { this.ConnectToDatabase(); string Query = "update CARD set USER_ID='" + user.UserId + "',USERNAME = '******',PASSWORD = '******'"; //This is command class which will handle the query and connection object. MySqlCommand command = new MySqlCommand(Query, mySQLConnection); command.ExecuteNonQuery(); this.Close(); return(true); }
public bool InsertUser(CardUserDTO user) { this.ConnectToDatabase(); string Query = "insert into USERS values('" + user.UserId + "','" + user.UserName + "','" + user.Password + "','" + user.Name + "')"; //This is command class which will handle the query and connection object. MySqlCommand command = new MySqlCommand(Query, mySQLConnection); command.ExecuteNonQuery(); this.Close(); return(true); }
public CardInfoDTO CardInfo(int id) { this.ConnectToDatabase(); MySqlCommand command = this.mySQLConnection.CreateCommand(); command.CommandText = "SELECT * FROM CARD WHERE CARD_ID = " + id; MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int cardId = reader.GetInt32(0); int listId = reader.GetInt32(1); int indexCard = reader.GetInt32(2); string title = reader.GetString(3); string description = reader.GetString(4); int label = reader.GetInt32(5); DateTime dueDate = (reader.IsDBNull(6)) ? DateTime.MinValue : (reader.GetDateTime(6)); float status = reader.GetInt64(7); card = new CardDTO(cardId, listId, indexCard, title, description, label, dueDate, status); } reader.Close(); command.CommandText = "SELECT * FROM CHECKLIST WHERE CARD_ID = '" + id + "' AND STATUS = '" + 1 + "'"; MySqlDataReader reader1 = command.ExecuteReader(); while (reader1.Read()) { int checklistId = reader1.GetInt32(0); int cardId = reader1.GetInt32(1); int checklistIndex = reader1.GetInt32(2); string title = reader1.GetString(3); byte status = reader1.GetByte(4); ChecklistDTO checklist = new ChecklistDTO(checklistId, cardId, checklistIndex, title, status); listCheckedlist.Add(checklist); } countCheckedlist = listCheckedlist.Count(); reader1.Close(); command.CommandText = "SELECT * FROM CHECKLIST WHERE CARD_ID = '" + id + "'"; MySqlDataReader reader2 = command.ExecuteReader(); while (reader2.Read()) { int checklistId = reader2.GetInt32(0); int cardId = reader2.GetInt32(1); int checklistIndex = reader2.GetInt32(2); string title = reader2.GetString(3); byte status = reader2.GetByte(4); ChecklistDTO checklist = new ChecklistDTO(checklistId, cardId, checklistIndex, title, status); listChecklist.Add(checklist); } countChecklist = listChecklist.Count(); reader2.Close(); command.CommandText = "SELECT * FROM USERS WHERE USER_ID = " + id; MySqlDataReader reader3 = command.ExecuteReader(); while (reader3.Read()) { int userId = reader3.GetInt32(0); string username = reader3.GetString(1); string password = reader3.GetString(2); string name = reader3.GetString(3); user = new CardUserDTO(userId, username, password, name); } reader3.Close(); command.CommandText = "SELECT u.NAME " + " FROM USERS u, LAMVIEC l " + " WHERE u.USER_ID = l.USER_ID and CARD_ID = " + id; MySqlDataReader reader4 = command.ExecuteReader(); while (reader4.Read()) { string name = reader4.GetString(0).Substring(0, 1); listNameUser.Add(name); } reader4.Close(); command.CommandText = "SELECT * FROM COMMENT WHERE CARD_ID = '" + id + "'"; MySqlDataReader reader5 = command.ExecuteReader(); while (reader5.Read()) { int userId = reader5.GetInt32(1); string content = reader5.GetString(2); DateTime time = reader5.GetDateTime(3); int cmtIndex = reader5.GetInt32(4); CommentDTO comment = new CommentDTO(id, userId, content, time, cmtIndex); listComment.Add(comment); } countCmt = listComment.Count(); reader5.Close(); this.Close(); CardInfoDTO cardInfoDTO = new CardInfoDTO(this.card, this.listNameUser, countChecklist, countCheckedlist, this.user, countCmt); return(cardInfoDTO); GC.Collect(); }
public bool UpdateCard(CardUserDTO user) { CardUserDAL udal = new CardUserDAL(); return(udal.UpdateUser(user)); }
public bool InsertUser(CardUserDTO user) { CardUserDAL udal = new CardUserDAL(); return(udal.InsertUser(user)); }