public List<LibraryLibrarian> AllLibrarians()
{
//Displaying all books in the library for Admin to update or delete accordingly
SqlConnection con;
DataTable table = new DataTable();
con = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\LMS_DB.mdf;Integrated Security = True");
using (var cmd = new SqlCommand("Administrator_AllLibrarians", con))
using (var da = new SqlDataAdapter(cmd))
{
cmd.CommandType = CommandType.StoredProcedure;
da.Fill(table);
}
List<LibraryLibrarian> Librarian_List = new List<LibraryLibrarian>();
foreach (DataRow row in table.Rows)
{
LibraryLibrarian librarian = new LibraryLibrarian();
librarian.Id = Convert.ToInt32(row["ID"].ToString().Trim());
librarian.FirstName = (row["FirstName"].ToString().Trim());
librarian.LastName = (row["LastName"].ToString().Trim());
librarian.EmailID = (row["Email_ID"].ToString().Trim());
librarian.Password = (row["Password"].ToString().Trim());
Librarian_List.Add(librarian);
}
return Librarian_List;
}
public String AddLibrarian(LibraryLibrarian librarian)
{
if (!librarian.IsValid(librarian.EmailID)) { return "Not valid Email ID"; }
try
{
SqlConnection conn = null;
conn = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\LMS_DB.mdf;Integrated Security = True");
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.Administrator_AddLibrarian", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@email_id", SqlDbType.NVarChar).Value = librarian.EmailID;
cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = librarian.Password;
cmd.Parameters.Add("@firstname", SqlDbType.NVarChar).Value = librarian.FirstName;
cmd.Parameters.Add("@lastname", SqlDbType.NVarChar).Value = librarian.LastName;
cmd.ExecuteNonQuery();
return "Successfully added the librarian.";
}
catch (SqlException sqlEx)
{
if (sqlEx.Message.StartsWith("Violation of UNIQUE KEY constraint"))
{
return "Duplicate Email_ID. Check again.";
}
else
throw;
}
}
public ActionResult AddLibrarian(LibraryLibrarian librarian)
{
if (ModelState.IsValid)
{
ViewBag.Message = Singleton.Instance.administrator.AddLibrarian(librarian);
}
return ViewIfAdminLoggedIn();
}
public ActionResult UpdateDeleteLibrarian(LibraryLibrarian librarian)
{
if (ModelState.IsValid)
{
if (Request.Form["delete"] == "yes") { ViewBag.Message = Singleton.Instance.administrator.DeleteLibrarian(librarian); }
else { ViewBag.Message = Singleton.Instance.administrator.UpdateLibrarian(librarian); }
}
ViewBag.Results = Singleton.Instance.administrator.AllLibrarians();
return ViewIfAdminLoggedIn();
}
public String DeleteLibrarian(LibraryLibrarian librarian)
{
if (!librarian.IsValid(librarian.EmailID)) { return "Not valid Email ID"; }
SqlConnection conn = null;
conn = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\LMS_DB.mdf;Integrated Security = True");
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.Administrator_DeleteLibrarian", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Id", SqlDbType.NVarChar).Value = librarian.Id;
cmd.ExecuteNonQuery();
return "Successfully Deleted the librarian.";
}