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));
}
