MVC 3 Full Teacher

Posted by : sinhalamp3 Saturday, October 17, 2015

Teacher Controller

public ActionResult Index()
{
return View();
}

public ActionResult Create()
{
DBOperations db = new DBOperations();

List qualification = db.getAllQualifications();
ViewBag.QualificationList = new SelectList(qualification, "QualificationID", "QualificationType");
List school = db.getAllSchools();
ViewBag.SchoolList = new SelectList(school, "SchoolID", "SchoolName");
return View();
}

[HttpPost]
public ActionResult Create(Teacher teacher)
{
DBOperations db = new DBOperations();

List qualification = db.getAllQualifications();
ViewBag.QualificationList = new SelectList(qualification, "QualificationID", "QualificationType");
List school = db.getAllSchools();
ViewBag.SchoolList = new SelectList(school, "SchoolID", "SchoolName");
db.insertTeacher(teacher);
return View();

}

public ActionResult ViewTeacher()
{
DBOperations db = new DBOperations();
List teachers = db.getAllTeachers();
return View(teachers.AsEnumerable());
}

//public JsonResult View(int id)
//{
// DBOperations db = new DBOperations();
// return Json(db.getTeacherById(id), JsonRequestBehavior.AllowGet);
//}

public ActionResult ViewTeacherById(int id)
{
DBOperations db = new DBOperations();
Teacher teacher = db.getTeacherById(id);
List school = db.getAllSchools();
foreach (var items in school)
{
if (items.SchoolID == teacher.SchoolID)
{
ViewData["SchoolName"] = items.SchoolName;
}
}
List qualifications = db.getAllQualifications();
foreach (var items in qualifications)
{
if (items.QualificationID == teacher.QualificationID)
{
ViewData["Type"] = items.QualificationType;
}
}
return View(teacher);
}

public ActionResult UpdateTeacher(int id)
{

DBOperations db = new DBOperations();
List schools = db.getAllSchools();
ViewBag.SchoolList = new SelectList(schools, "SchoolID", "SchoolName");
List qualifications = db.getAllQualifications();
ViewBag.QualificationList = new SelectList(qualifications, "QualificationID", "QualificationType");
Teacher model = db.getTeacherById(id);

return View(model);

}

[HttpPost]
public ActionResult UpdateTeacher(Teacher teacher)
{
DBOperations db = new DBOperations();
List schools = db.getAllSchools();
ViewBag.SchoolList = new SelectList(schools, "SchoolID", "SchoolName");
List qualifications = db.getAllQualifications();
ViewBag.QualificationList = new SelectList(qualifications, "QualificationID", "QualificationType");
// Teacher model = db.getTeacherById(id);

db.UpdateTeacher(teacher);

return View("ViewTeacher");

}

[HttpDelete]
public ActionResult ViewTeacher(int id)
{
DBOperations db = new DBOperations();
db.DeleteTeacher(id);
return View("ViewTeacher");
}
-------------------------------------------------------------------------------------

DB Operations Class

public void insertTeacher(Teacher teacher)

{

SqlConnection conn = new SqlConnection(Connection.ConnectionString);

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "insert into Teacher values ('"+teacher.TeacherName+"','"+teacher.TeacherNIC+"','"+teacher.TeacherAddress+"',"+teacher.QualificationID+","+teacher.SchoolID+")";

cmd.ExecuteNonQuery();

conn.Close();

}

public List getAllQualifications()

{

SqlConnection conn = new SqlConnection(Connection.ConnectionString);

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "select * from Qualification";

SqlDataReader dr = cmd.ExecuteReader();

List qualifications = new List();

while (dr.Read())

{

Qualification qualification = new Qualification();

qualification.QualificationID = dr.GetInt32(0);

qualification.QualificationType = dr.GetString(1);

qualifications.Add(qualification);

}

return qualifications;

}

public List getAllSchools()

{

SqlConnection conn = new SqlConnection(Connection.ConnectionString);

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "select * from School";

SqlDataReader dr = cmd.ExecuteReader();

List schools = new List();

while (dr.Read())

{

School school = new School();

school.SchoolID = dr.GetInt32(0);

school.SchoolName = dr.GetString(1);

schools.Add(school);

}

return schools;

}

public List getAllTeachers()

{

List teachers = new List();

SqlConnection conn = new SqlConnection(Connection.ConnectionString);

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "SELECT * FROM Teacher";

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

Teacher teacher = new Teacher();

teacher.TeacherID = dr.GetInt32(0);

teacher.TeacherName = dr.GetString(1);

teacher.TeacherNIC = dr.GetString(2);

teacher.TeacherAddress = dr.GetString(3);

teacher.QualificationID = dr.GetInt32(4);

teacher.SchoolID = dr.GetInt32(5);

teachers.Add(teacher);

}

return teachers;

}

public Teacher getTeacherById(int teacher_id)

{

Teacher teacher = new Teacher();

SqlConnection conn = new SqlConnection(Connection.ConnectionString);

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "select * from Teacher where TeacherID="+teacher_id+"";

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

teacher.TeacherID = dr.GetInt32(0);

teacher.TeacherName = dr.GetString(1);

teacher.TeacherNIC = dr.GetString(2);

teacher.TeacherAddress = dr.GetString(3);

teacher.QualificationID = dr.GetInt32(4);

teacher.SchoolID = dr.GetInt32(5);

}

return teacher;

}

public void UpdateTeacher(Teacher teacher)

{

int teacher_id = teacher.TeacherID;

string teacher_name = teacher.TeacherName;

string teacher_address = teacher.TeacherAddress;

string teacher_nic = teacher.TeacherNIC;

int quali_id = teacher.QualificationID;

int school_id = teacher.SchoolID;

SqlConnection conn = new SqlConnection(Connection.ConnectionString);

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "Update Teacher set TeacherName='" + teacher_name + "',TeacherNIC='" + teacher_nic + "',TeacherAddress='" + teacher_address + "',QualificationID=" + quali_id + ",SchoolID=" + school_id + " where TeacherID=" + teacher_id + "";

cmd.ExecuteNonQuery();

conn.Close();

}

public void DeleteTeacher(int id)

{

SqlConnection conn = new SqlConnection(Connection.ConnectionString);

conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "delete from Teacher where TeacherID="+id+"";

cmd.ExecuteNonQuery();

conn.Close();

}

---------------------------------------------------------------------------------------------

DBConnection Class

public class Connection
{
public static string ConnectionString
{
get { return "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SINGADB;Data Source=Lakshika-PC\\SQLEXPRESS"; }
}

}