public void Update(InternetShopFilter filter) { DatabaseProvider.CreateConnectionAndCommand(); string cmd = "UPDATE Names SET "; List <string> columnsToUpdate = new List <string>(); if (!string.IsNullOrEmpty(filter.Name)) { columnsToUpdate.Add(string.Format("Name = '{0}'", filter.Name)); } if (!string.IsNullOrEmpty(filter.Category)) { columnsToUpdate.Add(string.Format("Category = '{0}'", filter.Category)); } if (!string.IsNullOrEmpty(filter.Price)) { columnsToUpdate.Add(string.Format("Price = '{0}'", filter.Price)); } string where = string.Format(" WHERE id = {0};", filter.Id); cmd = cmd + string.Join(", ", columnsToUpdate.ToArray()) + where; DatabaseProvider.ExecuteCommand(cmd); DatabaseProvider.CloseConnection(); }
public void Add(InternetShopFilter filter) { DatabaseProvider.CreateConnectionAndCommand(); // string cmd = "INSERT INTO Names (id, Name, Category, Price) VALUES "; cmd = cmd + "("; List <string> valueComponents = new List <string>(); if (filter.Id.HasValue) { valueComponents.Add(filter.Id.ToString()); } if (!string.IsNullOrEmpty(filter.Name)) { valueComponents.Add(string.Format("'{0}'", filter.Name)); } if (!string.IsNullOrEmpty(filter.Category)) { valueComponents.Add(string.Format("'{0}'", filter.Category)); } if (!string.IsNullOrEmpty(filter.Price)) { valueComponents.Add(string.Format("'{0}'", filter.Price)); } string join = string.Join(", ", valueComponents); cmd = cmd + join; cmd = cmd + ");"; DatabaseProvider.ExecuteCommand(cmd); DatabaseProvider.CloseConnection(); }
public void Delete(InternetShopFilter filter) { DatabaseProvider.CreateConnectionAndCommand(); string cmd = string.Format("DELETE FROM Names WHERE Id = {0}", filter.Id); DatabaseProvider.ExecuteCommand(cmd); DatabaseProvider.CloseConnection(); }
public List <InternetShop> Get(InternetShopFilter filter) { List <InternetShop> ResultInternetShop = new List <InternetShop>(); DatabaseProvider.CreateConnectionAndCommand(); // string cmd = "SELECT * FROM Names"; List <string> conditions = new List <string>(); // if (filter.Id.HasValue) { conditions.Add("Id = " + filter.Id.ToString()); } if (!string.IsNullOrEmpty(filter.Name)) { conditions.Add(string.Format("Name = '{0}'", filter.Name)); } if (!string.IsNullOrEmpty(filter.Category)) { conditions.Add(string.Format("Category = '{0}'", filter.Category)); } if (!string.IsNullOrEmpty(filter.Price)) { conditions.Add(string.Format("Price = '{0}'", filter.Price)); } if (conditions.Count() > 0) { cmd += " WHERE " + string.Join(" AND ", conditions.ToArray()); } DatabaseProvider.ExecuteCommand(cmd); // DatabaseProvider.BeginReader(); while (DatabaseProvider.Reader.Read()) { int id = (int)DatabaseProvider.Reader["id"]; string name = (string)DatabaseProvider.Reader["name"]; string category = (string)DatabaseProvider.Reader["category"]; string price = (string)DatabaseProvider.Reader["Price"]; InternetShop collection = new InternetShop(id, name, category, price); ResultInternetShop.Add(collection); } DatabaseProvider.FinishReader(); // DatabaseProvider.CloseConnection(); return(ResultInternetShop); }
public int AddTeacher(TeacherDto teacher) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter[] parameters = { new SqlParameter("@name", teacher.Name), new SqlParameter("@account", teacher.Account.Id) }; using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, ADDTEACHER, CommandType.StoredProcedure, parameters)) { if (reader.Read()) { return(int.Parse(reader["Id"].ToString())); } return(0); } } }
public bool CheckEmail(string email) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter parameter = new SqlParameter("@email", email); using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, CHECKEMAIL, CommandType.StoredProcedure, parameter)) { if (reader.Read()) { return(true); } } } return(false); }
public int GetAccountIdByTeacherName(string name) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter parameters = new SqlParameter("@teacherName", name); using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, GETACCOUNTIDBYTEACHERNAME, CommandType.StoredProcedure, parameters)) { if (reader.Read()) { return(int.Parse(reader["Id"].ToString())); } return(0); } } }
public int LogIn(string email, string password) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter[] parameter = { new SqlParameter("@email", email), new SqlParameter("@password", password) }; using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, LOGIN, CommandType.StoredProcedure, parameter)) { if (reader.Read()) { return((int)reader["id"]); } } } return(-1); }
public int Register(string email, string password, int foreignLanguage) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter[] parameter = { new SqlParameter("@email", email), new SqlParameter("@password", password), new SqlParameter("@foreignLanguage", foreignLanguage) }; using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, REGISTER, CommandType.StoredProcedure, parameter)) { if (reader.Read()) { return((int)reader["id"]); } } } return(-1); }
public int GetForeignLanguageByName(string foreignLanguage) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter parameter = new SqlParameter("@foreignLanguage", foreignLanguage); using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, GETFOREIGNLANGUAGEBYNAME, CommandType.StoredProcedure, parameter)) { if (reader.Read()) { return((int)reader["id"]); } } } return(-1); }
public int AddGroup(GroupDto group) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter parameter = new SqlParameter("@name", group.Name); using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, ADDGROUP, CommandType.StoredProcedure, parameter)) { if (reader.Read()) { return(int.Parse(reader["Id"].ToString())); } return(0); } } }
public int AddSubject(SubjectDto subject) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter parameter = new SqlParameter("@name", subject.Name); using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, ADDSUBJECT, CommandType.StoredProcedure, parameter)) { if (reader.Read()) { return(int.Parse(reader["Id"].ToString())); } return(0); } } }
public int AddFeedback(FeedbackDto feedback) { SqlParameter[] parameters = { new SqlParameter("@actualClassId", feedback.ActualClass.Id), new SqlParameter("@usefulness", feedback.Usefulness), new SqlParameter("@novelty", feedback.Novelty), new SqlParameter("@highScientificLevel", feedback.HighScientificLevel), new SqlParameter("@rigorousScientificLevel", feedback.RigorousScientificLevel), new SqlParameter("@attractivenes", feedback.Attractiveness), new SqlParameter("@clearness", feedback.Clearness), new SqlParameter("@correctness", feedback.Correctness), new SqlParameter("@interactivity", feedback.Interactivity), new SqlParameter("@comprehension", feedback.Comprehension), new SqlParameter("@comment", feedback.Comment) }; return(DatabaseProvider.ExecuteCommand <int>(DatabaseProvider.GetSqlConnection(), ADD, CommandType.StoredProcedure, parameters)); }
public GroupDto GetGroupByName(string name) { GroupDto groupDto = new GroupDto(-1, ""); using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter[] parameters = { new SqlParameter("@name", name) }; using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, GETGROUPBYNAME, CommandType.StoredProcedure, parameters)) { while (reader.Read()) { groupDto = DtoHelper.GetDto <GroupDto>(reader); } } } return(groupDto); }
public int AddClass(ClassDto classDto) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { int type = 0; switch (classDto.ClassType) { case ClassTypeEnum.S: type = 1; break; case ClassTypeEnum.C: type = 2; break; case ClassTypeEnum.L: type = 3; break; case ClassTypeEnum.E: type = 4; break; default: break; } SqlParameter[] parameters = { new SqlParameter("@subject", classDto.Subject.Id), new SqlParameter("@type", type) }; using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, ADDCLASS, CommandType.StoredProcedure, parameters)) { if (reader.Read()) { return(int.Parse(reader["Id"].ToString())); } return(0); } } }
public IList <AppointmentDto> GetAllAppointmentDtosForStudentDto(int studentId) { IList <AppointmentDto> feedbacks = new List <AppointmentDto>(); using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter[] parameters = { new SqlParameter("@id", studentId) }; using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, GETAPPOINTMENTS, CommandType.StoredProcedure, parameters)) { while (reader.Read()) { AppointmentDto app = DtoHelper.GetDto <AppointmentDto>(reader); feedbacks.Add(app); } } } return(feedbacks); }
public IList <FeedbackDto> GetAllFeedbacksForActualClass(int classId) { IList <FeedbackDto> feedbacks = new List <FeedbackDto>(); using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter[] parameters = { new SqlParameter("@actualClassId", classId) }; using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, GETFEEDBACKS, CommandType.StoredProcedure, parameters)) { while (reader.Read()) { FeedbackDto fb = DtoHelper.GetDto <FeedbackDto>(reader); feedbacks.Add(fb); } } } return(feedbacks); }
public int AddAccount(AccountDto accountDto) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("@email", accountDto.Email), new SqlParameter("@password", accountDto.Password), new SqlParameter("@isAdmin", accountDto.IsAdmin), }; using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, ADDACCOUNT, CommandType.StoredProcedure, parameter)) { if (reader.Read()) { return(int.Parse(reader["Id"].ToString())); } return(0); } } }
public int AddAppointment(AppointmentDto appointmentDto) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter[] parameters = { new SqlParameter("@class", appointmentDto.Class.Id), new SqlParameter("@group", appointmentDto.Group.Id), new SqlParameter("@teacher", appointmentDto.Teacher.Id), new SqlParameter("@classroom", appointmentDto.ClassRoom), new SqlParameter("@hour", appointmentDto.Hours), new SqlParameter("@day", appointmentDto.Day), }; using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, ADDAPPOINTMENT, CommandType.StoredProcedure, parameters)) { if (reader.Read()) { return(int.Parse(reader["Id"].ToString())); } return(0); } } }
public void AddStudent(StudentDto studentDto) { using (SqlConnection connection = DatabaseProvider.GetSqlConnection()) { SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("@accountId", studentDto.Account.Id), new SqlParameter("@groupId", studentDto.Group.Id), new SqlParameter("@langId", studentDto.ForeignLanguage.Id) }; using (IDataReader reader = DatabaseProvider.ExecuteCommand <IDataReader>(connection, ADDSTUDENT, CommandType.StoredProcedure, parameter)) { /*if (reader.Read()) * { * * /*return int.Parse(reader["Id"].ToString());#1# * } * return 0;*/ } } }
protected int exec(DatabaseCommand databaseCommand) { return(DatabaseProvider.ExecuteCommand(databaseCommand)); }