public MdlStudent GetStudentData(string value, string type = "email")
{
if (type == "code")
type = "@ConfirmationCode";
else
type = "@Email";
try
{
using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
{
SqlDataAdapter da = new SqlDataAdapter("spGetStudentData", conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue(type, value);
DataSet ds = new DataSet();
da.Fill(ds, "Students"); //opens connection to DB/ executes command/ reads data/ fills Data set/ closing connection
DataRow dr = ds.Tables["Students"].Rows[0];
return GetFilledStudentObj(dr);
}
}
catch (Exception e)
{
MdlStudent emptyObject = new MdlStudent();
return emptyObject;
}
}
public string AddStudent(MdlStudent mdlStudentObj)
{
using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
{
conn.Open();
return GetOutput(conn, mdlStudentObj);
}
}
public string AddStudent(MdlStudent mdlStudentObj)
{
ctrStudentObj = new CtrStudent();
Console.WriteLine();
Console.WriteLine("AddStudent() " + GetExecutionThreadTime());
return ctrStudentObj.AddStudent(mdlStudentObj);
}
public static MdlStudent CalculateProfileScore(MdlStudent mdlStudentObj)
{
int numberOfChildren = Convert.ToInt32(mdlStudentObj.NumberOfChildren);
bool disabled = Convert.ToBoolean(mdlStudentObj.Disabled);
int numberOfCohabiters = Convert.ToInt32(mdlStudentObj.NumberOfCohabiters);
mdlStudentObj.Score = (numberOfChildren * 40) + (numberOfCohabiters * 80);
if (disabled)
mdlStudentObj.Score += 60;
return mdlStudentObj;
}
public static MdlStudent GenerateStudentObj()
{
MdlStudent mdlStudentObj = new MdlStudent();
mdlStudentObj.Email = "*****@*****.**";
mdlStudentObj.Password = "******";
mdlStudentObj.Name = "Miroslav";
mdlStudentObj.Surname = "Pakanec";
mdlStudentObj.Address = "Jernbanegade 12A";
mdlStudentObj.PostCode = "9000";
mdlStudentObj.City = "Aalborg";
mdlStudentObj.Country = "Denmark";
mdlStudentObj.Phone = "+421910245649";
return mdlStudentObj;
}
public bool AddStudent(string email, string password, bool confirmed, bool student,
int score, int numberOfChildren, bool pet, int numberOfCohabiters, bool disabled, DateTime dateOfCreation,
string name, string surname, string address, string postCode, string city, string country, string phone)
{
ServerModel.MdlStudent mdlStudentObj = new ServerModel.MdlStudent(0, email, password, confirmed, student,
score, numberOfChildren, pet, numberOfCohabiters, disabled, dateOfCreation, name, surname, address, postCode, city, country, phone);
ServerDatabase.DbStudent dbStudentObj = new ServerDatabase.DbStudent();
ServerDatabase.DbCheckEmailExists dbCheckEmailObj = new ServerDatabase.DbCheckEmailExists();
if (!dbCheckEmailObj.checkEmailExists(mdlStudentObj.Email))
return dbStudentObj.AddStudent(mdlStudentObj);
Console.WriteLine("Registration has failed due to the existing email");
Console.WriteLine("Thread: " + Thread.CurrentThread.ManagedThreadId.ToString() + " Time: " + DateTime.Now.ToString());
return false;
}
public string AddStudent(MdlStudent mdlStudentObj)
{
DbStudent dbStudentObj = new DbStudent();
CtrEmail ctrEmailObj = new CtrEmail();
mdlStudentObj.Salt = CreateSalt(10);
mdlStudentObj.Password = CreateHash(mdlStudentObj.Password, mdlStudentObj.Salt);
//mdlStudentObj.ConfirmationCode = ctrEmailObj.getCode();
mdlStudentObj.ConfirmationCode = "none";
string Feedback = dbStudentObj.AddStudent(mdlStudentObj);
if (Feedback == "Error")
return Feedback;
//ctrEmailObj.send();
return Feedback;
}
public bool checkCodeExist(string code)
{
//DB find, student update confirmed
MdlStudent studentObj = new MdlStudent();
studentObj.ConfirmationCode = code;
DbStudent dbStudent = new DbStudent();
using (TransactionScope scope = new TransactionScope())
{
//get student by confirmation code
studentObj = dbStudent.GetStudentData(studentObj.ConfirmationCode, "code");
scope.Complete();
}
if (studentObj != null)
return confirm(studentObj);
else
return false;
}
private MdlStudent GetMdlStudent(DataRow row)
{
MdlStudent mdlStudentObj = new MdlStudent();
mdlStudentObj.NumberOfChildren = Convert.ToInt32(row["NumberOfChildren"]);
mdlStudentObj.Disabled = Convert.ToBoolean(row["Disabled"]);
mdlStudentObj.NumberOfCohabiters = Convert.ToInt32(row["NumberOfCohabitors"]);
return mdlStudentObj;
}
private MdlStudent GetStudent(string email, int numberOfChildren, bool pet, int numberOfCohabitors, bool disabled,
string name, string surname, string address, string postCode, string city, string country, string phone)
{
MdlStudent mdlStudentObj = new MdlStudent();
mdlStudentObj.Email = email;
mdlStudentObj.NumberOfChildren = numberOfChildren;
mdlStudentObj.Pet = pet;
mdlStudentObj.NumberOfCohabiters = numberOfCohabitors;
mdlStudentObj.Disabled = disabled;
mdlStudentObj.Name = name;
mdlStudentObj.Surname = surname;
mdlStudentObj.Address = address;
mdlStudentObj.PostCode = postCode;
mdlStudentObj.City = city;
mdlStudentObj.Country = country;
mdlStudentObj.Phone = phone;
return mdlStudentObj;
}
private DataRow UpdateDataRow(DataRow dr, MdlStudent mdlStudentObj)
{
dr["Confirmed"] = mdlStudentObj.Confirmed;
dr["Student"] = mdlStudentObj.Student;
dr["Score"] = mdlStudentObj.Score;
dr["NumberOfChildren"] = mdlStudentObj.NumberOfChildren;
dr["Pet"] = mdlStudentObj.Pet;
dr["NumberOfCohabitors"] = mdlStudentObj.NumberOfCohabiters;
dr["Disabled"] = mdlStudentObj.Disabled;
dr["Name"] = mdlStudentObj.Name;
dr["Surname"] = mdlStudentObj.Surname;
dr["Address"] = mdlStudentObj.Address;
dr["PostCode"] = mdlStudentObj.PostCode;
dr["City"] = mdlStudentObj.City;
dr["Country"] = mdlStudentObj.Country;
dr["Phone"] = mdlStudentObj.Phone;
return dr;
}
private static SqlCommand CreateCommandQueuebased(MdlStudent studentObj)
{
DateTime myDateTime = studentObj.DateOfCreation;
string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss");
string query = "insert into ST_Queue values ("
+ studentObj.Id + ","
+ studentObj.Score + ","
+ studentObj.NumberOfChildren + ","
+ Convert.ToInt32(studentObj.Pet) + ","
+ studentObj.NumberOfCohabiters + ","
+ Convert.ToInt32(studentObj.Disabled) + ",'"
+ sqlFormattedDate + "')";
Console.WriteLine("Thread " + Thread.CurrentThread.ManagedThreadId.ToString() + " Executed query: \n " + query);
//return SQLCommand
return DbConnection.GetDbCommand(query);
}
private static void SetLastId(MdlStudent studentObj)
{
// read Id from first table and make it the same in all other tables that is connected with users
try
{
int score = 0;
string query = "Select ID from ST_Main where email = '" + studentObj.Email + "'";
Console.WriteLine("Thread " + Thread.CurrentThread.ManagedThreadId.ToString() + " Executed query: \n " + query);
using (var connection = new SqlConnection(DbConnection.connectionString))
using (var command = new SqlCommand(query, connection))
{
connection.Open();
using (var sqlReader = command.ExecuteReader())
{
while (sqlReader.Read())
{
studentObj.Id = Convert.ToInt32(sqlReader.GetValue(0));
}
}
}
}
catch (Exception e)
{
Console.WriteLine("Exception catched: " + e + " Thread: " + Thread.CurrentThread.ManagedThreadId.ToString() + " Time: " + DateTime.Now);
DbConnection.Close();
}
}
private static SqlCommand CreateCommandPersonal(MdlStudent studentObj)
{
string query = "insert into ST_Personal values ("
+ studentObj.Id + ",'"
+ studentObj.Name + "','"
+ studentObj.Surname + "','"
+ studentObj.Address + "','"
+ studentObj.PostCode + "','"
+ studentObj.City + "','"
+ studentObj.Country + "','"
+ studentObj.Phone + "')";
Console.WriteLine("Thread " + Thread.CurrentThread.ManagedThreadId.ToString() + " Executed query: \n " + query);
//return SQLCommand
return DbConnection.GetDbCommand(query);
}
private static SqlCommand CreateCommandMain(MdlStudent studentObj)
{
string query = "insert into ST_Main values ('"
+ studentObj.Email + "','"
+ studentObj.Password + "',"
+ Convert.ToInt32(studentObj.Confirmed) + ","
+ Convert.ToInt32(studentObj.Student) + ")";
Console.WriteLine("Thread " + Thread.CurrentThread.ManagedThreadId.ToString() + " Executed query: \n " + query);
//return SQLCommand
return DbConnection.GetDbCommand(query);
}
private string GetOutput(SqlConnection conn, MdlStudent mdlStudentObj)
{
var option = new TransactionOptions();
option.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
option.Timeout = TimeSpan.FromSeconds(3);
using (var scope = new TransactionScope(TransactionScopeOption.Required, option))
{
try
{
SqlCommand cmd = new SqlCommand("spExecuteInsertStudent", conn);
//set command type
cmd.CommandType = CommandType.StoredProcedure;
//input parameters
cmd.Parameters.AddWithValue("@EmailInput", mdlStudentObj.Email);
cmd.Parameters.AddWithValue("@PasswordInput", mdlStudentObj.Password);
cmd.Parameters.AddWithValue("@SaltInput", mdlStudentObj.Salt);
cmd.Parameters.AddWithValue("@NameInput", mdlStudentObj.Name);
cmd.Parameters.AddWithValue("@SurnameInput", mdlStudentObj.Surname);
cmd.Parameters.AddWithValue("@AddressInput", mdlStudentObj.Address);
cmd.Parameters.AddWithValue("@PostCodeInput", mdlStudentObj.PostCode);
cmd.Parameters.AddWithValue("@CityInput", mdlStudentObj.City);
cmd.Parameters.AddWithValue("@CountryInput", mdlStudentObj.Country);
cmd.Parameters.AddWithValue("@PhoneInput", mdlStudentObj.Phone);
//output parameters
SqlParameter outputParameter = new SqlParameter();
outputParameter.ParameterName = "@MessageOutput";
outputParameter.SqlDbType = SqlDbType.Char;
outputParameter.Direction = ParameterDirection.Output;
outputParameter.Size = 100;
cmd.Parameters.Add(outputParameter);
//execute
cmd.ExecuteNonQuery();
scope.Complete();
return outputParameter.Value.ToString();
}
catch (Exception e)
{
Transaction.Current.Rollback();
Console.WriteLine("An Error has accured. Registration Terminated. Err:" + e);
return "Error";
}
finally
{
if (scope != null)
((IDisposable)scope).Dispose();
}
}
}
private bool confirm(MdlStudent student)
{
student.Confirmed = true;
DbStudent dbStudentObj = new DbStudent();
return dbStudentObj.UpdateProfile(student);
}
public bool UpdateProfile(MdlStudent mdlStudentObj)
{
var option = new TransactionOptions();
option.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
option.Timeout = TimeSpan.FromSeconds(3);
using (var scope = new TransactionScope(TransactionScopeOption.Required, option))
{
try
{
using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
{
DataSet ds = GetStudentDataSet(conn, mdlStudentObj.Email);
DataRow dr = ds.Tables["Students"].Rows[0];
SqlDataAdapter da = new SqlDataAdapter("spGetStudentData", conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@Email", mdlStudentObj.Email);
SqlCommandBuilder builder = new SqlCommandBuilder(da);
if (ds.Tables["Students"].Rows.Count == 0)
return false;
dr = UpdateDataRow(dr, mdlStudentObj);
// Update returns number of updated rows
int update = da.Update(ds, "Students");
scope.Complete();
if (update > 0)
return true;
else
return false;
}
}
catch (Exception e)
{
Transaction.Current.Rollback();
return false;
}
finally
{
if (scope != null)
((IDisposable)scope).Dispose();
}
}
}
public bool AddStudent(MdlStudent studentObj)
{
try
{
DateTime myDateTime = studentObj.DateOfCreation;
string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss");
studentObj.Email = studentObj.Email + "T" + Thread.CurrentThread.ManagedThreadId.ToString();
//DbConnection.Open();
//DbStudent.CreateCommandMain(studentObj).ExecuteNonQuery();
//SetLastId(studentObj);
//DbStudent.CreateCommandQueuebased(studentObj).ExecuteNonQuery();
//DbStudent.CreateCommandPersonal(studentObj).ExecuteNonQuery();
//DbConnection.Close();
//Console.WriteLine("REGISTRATION : true");
//return true;
string query1 = "insert into ST_Main values ('"
+ studentObj.Email + "','"
+ studentObj.Password + "',"
+ Convert.ToInt32(studentObj.Confirmed) + ","
+ Convert.ToInt32(studentObj.Student) + ")";
using (var connection = new SqlConnection(DbConnection.connectionString))
using (var command = new SqlCommand(query1, connection))
{
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
SetLastId(studentObj);
string query2 = "insert into ST_Queue values ("
+ studentObj.Id + ","
+ studentObj.Score + ","
+ studentObj.NumberOfChildren + ","
+ Convert.ToInt32(studentObj.Pet) + ","
+ studentObj.NumberOfCohabiters + ","
+ Convert.ToInt32(studentObj.Disabled) + ",'"
+ sqlFormattedDate + "')";
string query3 = "insert into ST_Personal values ("
+ studentObj.Id + ",'"
+ studentObj.Name + "','"
+ studentObj.Surname + "','"
+ studentObj.Address + "','"
+ studentObj.PostCode + "','"
+ studentObj.City + "','"
+ studentObj.Country + "','"
+ studentObj.Phone + "')";
using (var connection = new SqlConnection(DbConnection.connectionString))
using (var command = new SqlCommand(query2, connection))
{
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
using (var connection = new SqlConnection(DbConnection.connectionString))
using (var command = new SqlCommand(query3, connection))
{
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
return true;
}
catch (Exception e)
{
Console.WriteLine("Exception catched: " + e + " Thread: " + Thread.CurrentThread.ManagedThreadId.ToString() + " Time: " + DateTime.Now);
return false;
}
}
private static MdlStudent GetFilledStudentObj(DataRow dr)
{
MdlStudent mdlStudentObj = new MdlStudent();
mdlStudentObj.Email = dr["Email"].ToString();
mdlStudentObj.Confirmed = (bool)dr["Confirmed"];
mdlStudentObj.Student = (bool)dr["Student"];
mdlStudentObj.Score = (int)dr["Score"];
mdlStudentObj.NumberOfChildren = (int)dr["NumberOfChildren"];
mdlStudentObj.Pet = (bool)dr["Pet"];
mdlStudentObj.NumberOfCohabiters = (int)dr["NumberOfCohabitors"];
mdlStudentObj.Disabled = (bool)dr["Disabled"];
mdlStudentObj.DateOfCreation = Convert.ToDateTime(dr["DateOfCreation"]);
mdlStudentObj.Name = dr["Name"].ToString();
mdlStudentObj.Surname = dr["Surname"].ToString();
mdlStudentObj.Address = dr["Address"].ToString();
mdlStudentObj.PostCode = dr["PostCode"].ToString();
mdlStudentObj.City = dr["City"].ToString();
mdlStudentObj.Country = dr["Country"].ToString();
mdlStudentObj.Phone = dr["Phone"].ToString();
return mdlStudentObj;
}
