public Boolean DBLoginSuccessful(LoginRequest loginRequest)
{
using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s19136;Integrated Security=True"))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
com.CommandText = "Select IndexNumber, PassW, Salt From Student " +
"Where IndexNumber=@Index";
com.Parameters.AddWithValue("Index", loginRequest.Login);
com.Transaction = tran;
var dr = com.ExecuteReader();
if (!dr.Read())
{
dr.Close();
return(false);
}
else
{
if (!PasswordEncryptionService.Validate(loginRequest.PassW, dr["Salt"].ToString(), dr["PassW"].ToString()))
{
dr.Close();
return(false);
}
else
{
dr.Close();
return(true);
}
}
}
}
public StudentServiceResponse EnrollStudent(EnrollStudentRequest request)
{
using (var con = new SqlConnection("Data Source=db-mssql;Initial Catalog=s19136;Integrated Security=True"))
using (var com = new SqlCommand())
{
com.Connection = con;
con.Open();
var tran = con.BeginTransaction();
try
{
com.CommandText = "Select * From Studies Where Name=@Name";
com.Parameters.AddWithValue("Name", request.Studies);
com.Transaction = tran;
var dr = com.ExecuteReader();
if (!dr.Read()) //Check if studies exists
{
dr.Close();
return(new StudentServiceResponse
{
studentResponse = null,
Error = "No such studies"
});
}
var IdStudy = (int)dr["IdStudy"];
var IdEnrollment = 1;
com.CommandText = "Select * From Enrollment, Studies Where Semester=1 And Enrollment.IdStudy = Studies.IdStudy and Name=@Name";
dr.Close();
dr = com.ExecuteReader();
if (!dr.Read()) // Check if Enrollment with semester = 1 exists for these studies
{
com.CommandText = "Select max(IdEnrollment) as MaxId From Enrollment";
dr.Close();
dr = com.ExecuteReader();
dr.Read();
IdEnrollment = (int)dr["MaxId"] + 1; //take IdEnrollment that we created
com.CommandText = "insert into Enrollment(IdEnrollment, IdStudy, Semester, StartDate) values " +
"(@IdEnrollment, @IdStudy, @Semester, @StartDate)";
com.Parameters.AddWithValue("IdEnrollment", IdEnrollment);
com.Parameters.AddWithValue("IdStudy", IdStudy);
com.Parameters.AddWithValue("Semester", 1);
com.Parameters.AddWithValue("StartDate", DateTime.Now);
dr.Close();
com.ExecuteNonQuery();
}
else
{
IdEnrollment = (int)dr["IdEnrollment"]; //take existing IdEnrollment to insert in Student later
}
com.CommandText = "Select * From Student Where IndexNumber=@IndexNumber";
com.Parameters.AddWithValue("IndexNumber", request.IndexNumber);
dr.Close();
dr = com.ExecuteReader();
if (dr.Read()) //Check if there is already student with this index number
{
dr.Close();
return(new StudentServiceResponse
{
studentResponse = null,
Error = "There already is student with this index"
});
}
string[] password = PasswordEncryptionService.encrypt(request.PassW);
//Insert student
var role = (request.Role == null)? "user": request.Role;
com.CommandText = "INSERT INTO Student(IndexNumber, PassW, Salt, FirstName, LastName, BirthDate, IdEnrollment) VALUES " +
"(@IndexNumber, @PassW, @Salt, @FirstName, @LastName, @BirthDate, @NewIdEnrollment)";
com.Parameters.AddWithValue("FirstName", request.FirstName);
com.Parameters.AddWithValue("PassW", password[0]);
com.Parameters.AddWithValue("Salt", password[1]);
com.Parameters.AddWithValue("LastName", request.LastName);
com.Parameters.AddWithValue("BirthDate", request.BirthDate);
com.Parameters.AddWithValue("NewIdEnrollment", IdEnrollment);
dr.Close();
com.ExecuteNonQuery();
tran.Commit();
com.CommandText = "Select * From Enrollment " +
"Where IdEnrollment = @NewIdEnrollment";
dr.Close();
dr = com.ExecuteReader();
dr.Read();
return(new StudentServiceResponse
{
studentResponse = new EnrollmentResponse
{
IdEnrollment = dr["IdEnrollment"].ToString(),
IdStudy = dr["IdStudy"].ToString(),
Semester = dr["Semester"].ToString(),
StartDate = dr["StartDate"].ToString()
},
Error = ""
});
}
catch (Exception e)
{
tran.Rollback();
return(new StudentServiceResponse
{
studentResponse = null,
Error = "Error"
});
}
}
}
