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>
/// sets user's picture to specified image in the database
/// </returns>
public void SaveImage(Int32 userId, byte[] image)
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add(new SqlParameter("@userId", userId));
sqlCommand.Parameters.Add(new SqlParameter("@image", image));
string sqlText = @"UPDATE [sukotto1_skypath2008].[dbo].[Teacher]
SET [picture] = @image
where [sukotto1_skypath2008].[dbo].[Teacher].id_User = @userId
";
sqlCommand.CommandText = sqlText;
SQLHelper sqlHelper = new SQLHelper();
sqlHelper.SQL_Insert(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;
}
/// <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 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;
}
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;
}
public DataTable InsertNewAppointment(DateTime newAppointmentStart, DateTime newAppointmentEnd, int idTeacher)
{
DataTable dtResults = new DataTable();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add(new SqlParameter("@appointmentStart", newAppointmentStart));
sqlCommand.Parameters.Add(new SqlParameter("@appointmentEnd", newAppointmentEnd));
sqlCommand.Parameters.Add(new SqlParameter("@idTeacher", idTeacher));
string sqlText = @"Insert into [sukotto1_skypath2008].[dbo].[Appointment]
(appointmentStart, appointmentEnd, id_Teacher)
values(@appointmentStart, @appointmentEnd, @idTeacher)
";
sqlCommand.CommandText = sqlText;
SQLHelper sqlHelper = new SQLHelper();
sqlHelper.SQL_Insert(sqlCommand);
return dtResults;
}
