public static List<StudentModel> ReadAllStudents()
{
string cmdText =
@"SELECT Id, ExpGraduation, Name, CanCode, CreditsLeft, Advisor FROM Student";
List<StudentModel> list = new List<StudentModel>();
using (SqlCeConnection conn = CreateConnection())
{
using (SqlCeCommand cmd = new SqlCeCommand(cmdText, conn))
{
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
StudentModel item = new StudentModel()
{
Id = (int) reader["Id"],
ExpGraduation = (DateTime) reader["ExpGraduation"],
Name = (string) reader["Name"],
CanCode = (bool) reader["CanCode"],
CreditsLeft = (int) reader["CreditsLeft"],
Advisor = (string) reader["Advisor"]
};
list.Add(item);
}
}
}
}
return list;
}
public static void CreateStudent(StudentModel student)
{
string cmdText =
@"INSERT INTO Student (ExpGraduation, Name, CanCode, CreditsLeft, Advisor)
VALUES (@expGraduation, @name, @canCode, @creditsLeft, @advisor)";
using (SqlCeConnection conn = CreateConnection())
{
using (SqlCeCommand cmd = new SqlCeCommand(cmdText, conn))
{
cmd.Parameters.AddWithValue("@expGraduation", student.ExpGraduation);
cmd.Parameters.AddWithValue("@name", student.Name);
cmd.Parameters.AddWithValue("@canCode", student.CanCode);
cmd.Parameters.AddWithValue("@creditsLeft", student.CreditsLeft);
cmd.Parameters.AddWithValue("@advisor", student.Advisor);
int rowsAffected = cmd.ExecuteNonQuery();
}
}
}
public ActionResult Create(FormCollection collection)
{
try
{
bool canCode = collection["CanCode"].Split(',')[0].Equals("true");
StudentModel student = new StudentModel()
{
ExpGraduation = Convert.ToDateTime(collection["ExpGraduation"]),
Name = collection["Name"],
CanCode = canCode,
CreditsLeft = Int32.Parse(collection["CreditsLeft"]),
Advisor = collection["Advisor"]
};
DatabaseQueries.CreateStudent(student);
return RedirectToAction("Index");
}
catch(Exception ex)
{
return new HttpStatusCodeResult(HttpStatusCode.InternalServerError,
"Error commiting info to database: " + ex.Message);
}
}
public static StudentModel ReadStudent(int id)
{
string cmdText =
@"SELECT Id, ExpGraduation, Name, CanCode, CreditsLeft, Advisor FROM Student
WHERE Id = @id";
StudentModel item = null;
using (SqlCeConnection conn = CreateConnection())
{
using (SqlCeCommand cmd = new SqlCeCommand(cmdText, conn))
{
cmd.Parameters.AddWithValue("@id", id);
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
item = new StudentModel()
{
Id = (int)reader["Id"],
ExpGraduation = (DateTime)reader["ExpGraduation"],
Name = (string)reader["Name"],
CanCode = (bool)reader["CanCode"],
CreditsLeft = (int)reader["CreditsLeft"],
Advisor = (string)reader["Advisor"]
};
}
}
}
}
return item;
}
public static void UpdateStudent(StudentModel student)
{
string cmdText =
@"UPDATE Student
SET
ExpGraduation = @expGraduation,
Name = @name,
CanCode = @canCode,
CreditsLeft = @creditsLeft,
Advisor = @advisor
WHERE Id = @id";
using (SqlCeConnection conn = CreateConnection())
{
using (SqlCeCommand cmd = new SqlCeCommand(cmdText, conn))
{
cmd.Parameters.AddWithValue("@id", student.Id);
cmd.Parameters.AddWithValue("@expGraduation", student.ExpGraduation);
cmd.Parameters.AddWithValue("@name", student.Name);
cmd.Parameters.AddWithValue("@canCode", student.CanCode);
cmd.Parameters.AddWithValue("@creditsLeft", student.CreditsLeft);
cmd.Parameters.AddWithValue("@advisor", student.Advisor);
cmd.ExecuteNonQuery();
}
}
}
public ActionResult Edit(int id, FormCollection collection)
{
try
{
var canCodeStringValue = collection["CanCode"];
bool canCode;
if (canCodeStringValue.Contains(","))
{
canCode = collection["CanCode"].Split(',')[0].Equals("true");
}
else
{
canCode = !canCodeStringValue.Equals("false");
}
StudentModel student = new StudentModel()
{
Id = id,
ExpGraduation = Convert.ToDateTime(collection["ExpGraduation"]),
Name = collection["Name"],
CanCode = canCode,
CreditsLeft = Int32.Parse(collection["CreditsLeft"]),
Advisor = collection["Advisor"]
};
DatabaseQueries.UpdateStudent(student);
return RedirectToAction("Index");
}
catch(Exception ex)
{
return new HttpStatusCodeResult(HttpStatusCode.InternalServerError,
"Error commiting info to database: " + ex.Message);
}
}