public DataTable GetTeacherAppointments(int idTeacher) { DataTable results = new DataTable(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Parameters.Add(new SqlParameter("@idTeacher", idTeacher)); string sqlText = @" select [sukotto1_skypath2008].[dbo].[Appointment].id, [sukotto1_skypath2008].[dbo].[Appointment].appointmentStart, [sukotto1_skypath2008].[dbo].[Appointment].appointmentEnd, [sukotto1_skypath2008].[dbo].[User].userName from [sukotto1_skypath2008].[dbo].[Teacher] inner join [sukotto1_skypath2008].[dbo].[Appointment] on [sukotto1_skypath2008].[dbo].[Appointment].id_Teacher = [sukotto1_skypath2008].[dbo].[Teacher].id inner join [sukotto1_skypath2008].[dbo].[User] on [sukotto1_skypath2008].[dbo].[Teacher].id_User = [sukotto1_skypath2008].[dbo].[User].id where [sukotto1_skypath2008].[dbo].[Teacher].id = @idTeacher order by [sukotto1_skypath2008].[dbo].[Appointment].appointmentStart "; sqlCommand.CommandText = sqlText; SQLHelper sqlHelper = new SQLHelper(); results = sqlHelper.SQL_Select(sqlCommand); return results; }
public void CancelAppointment(int appointmentId) { DataTable results = new DataTable(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Parameters.Add(new SqlParameter("@idAppointment", appointmentId)); string sqlText = @" update [sukotto1_skypath2008].[dbo].[Appointment] set id_Student = null where [sukotto1_skypath2008].[dbo].[Appointment].id = @idAppointment "; sqlCommand.CommandText = sqlText; SQLHelper sqlHelper = new SQLHelper(); sqlHelper.SQL_Select(sqlCommand); }
/// <summary> /// check login for a user/password, single result is authenticated /// /// </summary> /// <param name="userName"></param> /// <param name="password"></param> /// <returns> /// returns image saved by user for display in page /// </returns> public DataTable GetImage(Int32 userId) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Parameters.Add(new SqlParameter("@userId", userId)); string sqlText = @"SELECT [picture] FROM [sukotto1_skypath2008].[dbo].[Teacher] where [sukotto1_skypath2008].[dbo].[Teacher].id_User = @userId "; sqlCommand.CommandText = sqlText; SQLHelper sqlHelper = new SQLHelper(); DataTable dtResults = sqlHelper.SQL_Select(sqlCommand); return dtResults; }
public string CreateUser(string userName, string password, bool isStudent, string firstName, string lastName) { string encryptedPassword = Encrypt.EncryptString(password); string insert = ""; if (isStudent) { insert = @" INSERT INTO [sukotto1_skypath2008].[dbo].[Student] ([id_User]) VALUES(SCOPE_IDENTITY()) "; } else { insert = @" INSERT INTO [sukotto1_skypath2008].[dbo].[Teacher] ([id_User]) VALUES(SCOPE_IDENTITY()) "; } SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Parameters.Add(new SqlParameter("@userName", userName)); sqlCommand.Parameters.Add(new SqlParameter("@password", encryptedPassword)); sqlCommand.Parameters.Add(new SqlParameter("@firstName", firstName)); sqlCommand.Parameters.Add(new SqlParameter("@lastName", lastName)); string sqlText = @"insert into [sukotto1_skypath2008].[dbo].[User] (userName, password, firstName, lastName) values(@userName, @password, @firstName, @lastName) " + insert; sqlCommand.CommandText = sqlText; SQLHelper sqlHelper = new SQLHelper(); sqlHelper.SQL_Select(sqlCommand); // make error trapping on string results = ""; return results; }
public DataTable DeleteAppointment(int idAppointment) { DataTable results = new DataTable(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Parameters.Add(new SqlParameter("@idAppointment", idAppointment)); string sqlText = @" Delete from [sukotto1_skypath2008].[dbo].[Appointment] where [sukotto1_skypath2008].[dbo].[Appointment].id = @idAppointment "; sqlCommand.CommandText = sqlText; SQLHelper sqlHelper = new SQLHelper(); results = sqlHelper.SQL_Select(sqlCommand); return results; }
/// <summary> /// check login for a user/password, single result is authenticated /// </summary> /// <param name="userName"></param> /// <param name="password"></param> /// <returns> /// returns datatable populated form user data in db /// </returns> public DataTable Login(string userName, string password) { SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Parameters.Add(new SqlParameter("@userName", userName)); sqlCommand.Parameters.Add(new SqlParameter("@password", password)); string sqlText = @"SELECT [id] FROM [sukotto1_skypath2008].[dbo].[User] where [sukotto1_skypath2008].[dbo].[User].userName = @userName and [sukotto1_skypath2008].[dbo].[User].password = @password"; sqlCommand.CommandText = sqlText; SQLHelper sqlHelper = new SQLHelper(); DataTable dtResults = sqlHelper.SQL_Select(sqlCommand); return dtResults; }
public DataTable GetAllOpenAppointments() { DataTable results = new DataTable(); SqlCommand sqlCommand = new SqlCommand(); string sqlText = @" select [sukotto1_skypath2008].[dbo].[Appointment].id, [sukotto1_skypath2008].[dbo].[Appointment].appointmentStart, [sukotto1_skypath2008].[dbo].[Appointment].appointmentEnd, [sukotto1_skypath2008].[dbo].[User].userName from [sukotto1_skypath2008].[dbo].[Teacher] inner join [sukotto1_skypath2008].[dbo].[Appointment] on [sukotto1_skypath2008].[dbo].[Appointment].id_Teacher = [sukotto1_skypath2008].[dbo].[Teacher].id inner join [sukotto1_skypath2008].[dbo].[User] on [sukotto1_skypath2008].[dbo].[Teacher].id_User = [sukotto1_skypath2008].[dbo].[User].id where [sukotto1_skypath2008].[dbo].[Appointment].id_Student is null order by [sukotto1_skypath2008].[dbo].[Appointment].appointmentStart "; sqlCommand.CommandText = sqlText; SQLHelper sqlHelper = new SQLHelper(); results = sqlHelper.SQL_Select(sqlCommand); return results; }
public int? GetStudentIdByUserName(string userName) { int? result = -1; SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Parameters.Add(new SqlParameter("@userName", userName)); var resultParameter = sqlCommand.Parameters.Add("@idTeacher", 0); resultParameter.Direction = ParameterDirection.Output; string sqlText = @" select @idTeacher = [sukotto1_skypath2008].[dbo].[Student].id from [sukotto1_skypath2008].[dbo].[Student] inner join [sukotto1_skypath2008].[dbo].[User] on [sukotto1_skypath2008].[dbo].[User].id = [sukotto1_skypath2008].[dbo].[Student].id_User where [sukotto1_skypath2008].[dbo].[User].userName = @userName "; sqlCommand.CommandText = sqlText; SQLHelper sqlHelper = new SQLHelper(); sqlHelper.SQL_Select(sqlCommand); if (!String.IsNullOrEmpty(resultParameter.Value.ToString())) { result = int.Parse(resultParameter.Value.ToString()); } return result; }