public Remark GetRemark(int remarkId)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("Select * From tbl_Remark where ID = " + remarkId);
SqlCommandObj.CommandText = query;
SqlDataReader dataReader = SqlCommandObj.ExecuteReader();
Remark aRemark = new Remark();
while (dataReader.Read())
{
aRemark.Id = Convert.ToInt32(dataReader["ID"]);
aRemark.Name = dataReader["Name"].ToString();
}
return(aRemark);
}
catch (Exception ex)
{
throw new Exception("Remarks can not loaded from your system", ex);
}
finally
{
if (SqlCommandObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
}
public AutoCompleteStringCollection autoComplete()
{
AutoCompleteStringCollection collection = new AutoCompleteStringCollection();
try
{
SqlConnectionObj.Open();
string query = "select * from tbl_room";
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
string roomNo = reader["roomNo"].ToString();
collection.Add(roomNo);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(collection);
}
public List <VehicleInformation> GetAllVehicle()
{
List <VehicleInformation> allVehicles = new List <VehicleInformation>();
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from VehicleInformation");
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
VehicleInformation aVehicleInformation = new VehicleInformation();
aVehicleInformation.RegNo = reader[0].ToString();
aVehicleInformation.EngineNo = reader[1].ToString();
allVehicles.Add(aVehicleInformation);
}
}
catch (Exception exceptionObj)
{
throw new Exception("Vehicle inforamtion number can not be loaded.", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(allVehicles);
}
public string Save(Student aStudent)
{
string message = "";
aStudent.RoomNo = aStudent.RoomPosition.Remove(aStudent.RoomPosition.Length - 2, 2);
try
{
SqlConnectionObj.Open();
string query = String.Format("insert into tbl_Student values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')", aStudent.StudentId, aStudent.Name, aStudent.Department, aStudent.Session, aStudent.HallName, aStudent.Email, aStudent.RoomPosition, aStudent.RoomNo, aStudent.Fathername, aStudent.Address, aStudent.School, aStudent.College, aStudent.District);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
message = "Employee: " + aStudent.Name + " has been saved";
query = String.Format("delete from tbl_room where roomNo='{0}'", aStudent.RoomPosition);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
}
catch (Exception exception)
{
throw new Exception("Error!!!", exception);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(message);
}
public List <SchedulesOfAVehicle> GetSchedulesOfASingleVehicle(VehicleInformation vehicle)
{
List <SchedulesOfAVehicle> schedulesOfAVehicle = new List <SchedulesOfAVehicle>();
try
{
SqlConnectionObj.Open();
string query = string.Format("SELECT Date,Shift FROM ScheduleVehicle WHERE VehicleRegNo='{0}'", vehicle.RegNo);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
SchedulesOfAVehicle singleScheduleOfAVehicle = new SchedulesOfAVehicle();
singleScheduleOfAVehicle.DateTime = (DateTime)reader[0];
singleScheduleOfAVehicle.Shift = reader[1].ToString();
schedulesOfAVehicle.Add(singleScheduleOfAVehicle);
}
return(schedulesOfAVehicle);
}
}
catch (Exception exceptionObj)
{
throw new Exception("Exception Occured in GetScheduleOfASingleVehicle method!!!", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(null);
}
public Student GetStudent(string regNo)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_Student where RegNo={0}", regNo);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
Student aStudent = new Student();
while (reader.Read())
{
aStudent.Name = reader[1].ToString();
aStudent.RegNo = reader[3].ToString();
aStudent.Email = reader[2].ToString();
aStudent.ContactNo = Convert.ToInt32(reader[4]);
}
return(aStudent);
}
catch (Exception exception)
{
throw new Exception("Error occurred during student management system.", exception);
}
finally
{
SqlConnectionObj.Close();
}
}
public bool ShowStudentInfo()
{
try
{
SqlConnectionObj.Open();
SqlCommandObj.Connection = SqlConnectionObj;
string query = String.Format("SELECT * FROM tblStudent");
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader != null)
{
return(reader.Read());
}
return(false);
}
catch (Exception ex)
{
throw new Exception("Information couldn't loaded from your system", ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
}
public bool HasThisRemarkName(string name)
{
try
{
SqlConnectionObj.Open();
string query = String.Format("SELECT * FROM tbl_Remark WHERE Name='{0}'", name);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader != null)
{
return(reader.HasRows);
}
return(false);
}
catch (Exception ex)
{
throw new Exception("Remark couldn't loaded from your system", ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
}
public List <IssueBook> GetIssueBooks(string regNo)
{
List <IssueBook> issueBooks = new List <IssueBook>();
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_Issueed where student_Reg='{0}' and Return_date is null", regNo);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
IssueBook issueBook = new IssueBook();
issueBook.Book = bookGateway.GetBooks(Convert.ToInt32(reader[0]));
issueBook.IssueCopy = Convert.ToInt32(reader[1]);
issueBook.DateTime = Convert.ToDateTime(reader[3]);
issueBook.Student = studentGateway.GetStudent(regNo);
issueBooks.Add(issueBook);
}
}
catch (Exception exception)
{
throw new Exception("Error occurred during search this book.", exception);
}
finally
{
SqlConnectionObj.Close();
}
return(issueBooks);
}
public Student SearchStudent(string studentId)
{
Student aStudent = new Student();
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_Student where regNo='{0}'", studentId);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
aStudent.Id = Convert.ToInt32(reader["Id"]);
aStudent.Name = reader["Name"].ToString();
aStudent.Email = reader["Email"].ToString();
aStudent.RegNo = reader["RegNo"].ToString();
aStudent.ContactNo = Convert.ToInt32(reader["ContactNo"]);
}
}
catch (Exception exception)
{
throw new Exception("System doesnot load this student reg no.", exception);
}
finally
{
SqlConnectionObj.Close();
}
return(aStudent);
}
public List <Remark> GetAll()
{
List <Remark> remarks = new List <Remark>();
try
{
SqlConnectionObj.Open();
string query = String.Format("Select * From tbl_Remark");
SqlCommandObj.CommandText = query;
SqlDataReader dataReader = SqlCommandObj.ExecuteReader();
while (dataReader.Read())
{
Remark aRemark = new Remark();
aRemark.Id = Convert.ToInt32(dataReader["ID"]);
aRemark.Name = dataReader["Name"].ToString();
remarks.Add(aRemark);
}
}
catch (Exception ex)
{
throw new Exception("Remarks can not loaded from your system", ex);
}
finally
{
if (SqlCommandObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(remarks);
}
public List <Transaction> GetDetailTransaction(string date)
{
List <Transaction> transactions = new List <Transaction>();
try
{
SqlConnectionObj.Open();
string query = "Select * From tbl_Transaction where Date = '" + date + "'";
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
Transaction aTransaction = new Transaction();
aTransaction.Amount = Convert.ToDouble(reader[1]);
aTransaction.Type = reader[3].ToString();
aTransaction.Remarks = remarkGateway.GetRemark(Convert.ToInt32(reader[4]));
transactions.Add(aTransaction);
}
}
catch (Exception exception)
{
throw new Exception("Error occurred during transactions loading from your system.", exception);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(transactions);
}
public string Save(Transaction transaction)
{
string message = "";
try
{
SqlConnectionObj.Open();
string query = string.Format("Insert into tbl_Transaction values('{0}','{1}','{2}','{3}')", transaction.Amount, transaction.TransactionDate, transaction.Type, transaction.Remarks.Id);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
message = transaction.Type + " type transaction has been completed successfully.";
}
catch (Exception ex)
{
throw new Exception("Error occurred during to data save to database. Try again", ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(message);
}
public void UpdateBook(int id, int outsideCopy, int i)
{
try
{
SqlConnectionObj.Open();
string query = "";
if (i == 1)
{
query =
string.Format(
"update tbl_book set InitialCopy=InitialCopy -{0}, OutsideCopy=OutsideCopy + {1} where Id={2}",
outsideCopy, outsideCopy, id);
}
if (i == 2)
{
query = string.Format("update tbl_book set InitialCopy=InitialCopy +{0}, OutsideCopy=OutsideCopy - {1} where Id={2}",
outsideCopy, outsideCopy, id);
}
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
}
catch (Exception exception)
{
throw new Exception("Problem on this system.", exception);
}
finally
{
SqlConnectionObj.Close();
}
}
public Book GetBooks(int toInt32)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_book where Id={0}", toInt32);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
Book aBook = new Book();
while (reader.Read())
{
aBook.Id = Convert.ToInt32(reader[0]);
aBook.Title = reader[1].ToString();
aBook.AuthorName = reader[2].ToString();
aBook.ISbnNo = reader[3].ToString();
aBook.Price = Convert.ToInt32(reader[4]);
}
return(aBook);
}
catch (Exception exception)
{
throw new Exception("System can not return those books.", exception);
}
finally
{
SqlConnectionObj.Close();
}
}
public Book SearchBook(int bookId)
{
Book aBook = new Book();
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_book where Id='{0}'", bookId);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
aBook.Id = Convert.ToInt32(reader["Id"]);
aBook.Title = reader["Title"].ToString();
aBook.AuthorName = reader["AuthorName"].ToString();
aBook.ISbnNo = reader["IsbnNo"].ToString();
aBook.InitialCopy = Convert.ToInt32(reader["InitialCopy"]);
aBook.OutsideCopy = Convert.ToInt32(reader["OutsideCopy"]);
aBook.Price = Convert.ToInt32(reader["Price"]);
}
}
catch (Exception exception)
{
throw new Exception("Error occurred during search this book.", exception);
}
finally
{
SqlConnectionObj.Close();
}
return(aBook);
}
public int GetBookId()
{
try
{
int bookId = 0;
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_book");
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
bookId = (int)reader[0];
}
bookId++;
return(bookId);
}
catch (Exception)
{
throw;
}
finally
{
SqlConnectionObj.Close();
}
}
public List <string> GetAllEngineNo()
{
List <string> allEngineNo = new List <string>();
try
{
SqlConnectionObj.Open();
string query = string.Format("select EngineNo from VehicleInformation");
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
string regNo = reader[0].ToString();
allEngineNo.Add(regNo);
}
}
catch (Exception exceptionObj)
{
throw new Exception("Engine Num. can not be loaded.", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(allEngineNo);
}
public ScheduleVehicle IsScheduleBooked(VehicleInformation vehicle, DateTime selectedDate, string shift)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("SELECT * FROM ScheduleVehicle WHERE VehicleRegNo='{0}' AND Date='{1}' AND Shift='{2}'", vehicle.RegNo, selectedDate, shift);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
ScheduleVehicle vehicleSchedule = new ScheduleVehicle();
vehicleSchedule.Vehicle.RegNo = reader[0].ToString();
vehicleSchedule.SelectedDate = (DateTime)reader[1];
vehicleSchedule.SelectShift = reader[2].ToString();
vehicleSchedule.BookedBy = reader[3].ToString();
vehicleSchedule.Address = reader[4].ToString();
return(vehicleSchedule);
}
}
catch (Exception exceptionObj)
{
throw new Exception("Exception occured! In Schedule Booking.", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(null);
}
public string DeleteStudent(Student selectedStudent)
{
string message = "";
try
{
SqlConnectionObj.Open();
string query = String.Format("DELETE FROM tbl_Student WHERE studentId='{0}'", selectedStudent.StudentId);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
message = "Student: " + selectedStudent.Name + " has been deleted.";
}
catch (Exception ex)
{
throw new Exception("Error occurred during student delete operation " + selectedStudent.Name, ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
InsertRoom(selectedStudent);
return(message);
}
public string GetBook(List <Book> books, DateTime toShortDateString, Student aStudent)
{
SqlConnectionObj.Open();
foreach (Book book in books)
{
string query = string.Format("Insert into tbl_Issueed(Book_id,Lend_copy,Issue_date,student_Reg) values({0},{1},'{2}', '{3}')", book.Id, book.OutsideCopy, toShortDateString, aStudent.RegNo);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
}
return("Book Lend Successfully");
}
public List <Student> GetAllStudent(string roomNo)
{
List <Student> students = new List <Student>();
try
{
SqlConnectionObj.Open();
string query = String.Format("select * from tbl_Student WHERE roomNo LIKE '%{0}%'", roomNo);
//query += "order by studentName";
//SqlConnectionObj.Open();
//
//SqlCommandObj.CommandText = query;
//SqlDataReader reader = SqlCommandObj.ExecuteReader();
//SqlCommandObj.CommandText = query;
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
while (reader.Read())
{
Student aStudent = new Student();
aStudent.StudentId = reader["studentId"].ToString();
aStudent.Name = reader["name"].ToString();
aStudent.Department = reader["department"].ToString();
aStudent.Session = reader["session"].ToString();
aStudent.HallName = reader["hall"].ToString();
aStudent.Email = reader["email"].ToString();
aStudent.RoomPosition = reader["roomPosition"].ToString();
aStudent.RoomNo = reader["roomNo"].ToString();
aStudent.Fathername = reader["fatherName"].ToString();
aStudent.Address = reader["address"].ToString();
aStudent.School = reader["school"].ToString();
aStudent.College = reader["college"].ToString();
aStudent.District = reader["district"].ToString();
students.Add(aStudent);
}
}
catch (Exception ex)
{
throw new Exception("can't populate students", ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(students);
}
public string Update(Student aStudent)
{
string message = "";
aStudent.RoomNo = aStudent.RoomPosition.Remove(aStudent.RoomPosition.Length - 2, 2);
try
{
SqlConnectionObj.Open();
string roomPos;
string roomQuery = String.Format("select roomPosition from tbl_Student where studentId='{0}'", aStudent.StudentId);
SqlCommandObj.CommandText = roomQuery;
object roomp = SqlCommandObj.ExecuteScalar();
roomPos = roomp.ToString();
if (roomPos != aStudent.RoomPosition)
{
string queryRoom = "insert into tbl_room";
queryRoom = String.Format("insert into tbl_room values ('{0}')", roomPos);
SqlCommandObj.CommandText = queryRoom;
SqlCommandObj.ExecuteNonQuery();
}
SqlConnectionObj.Close();
SqlConnectionObj.Open();
string query = String.Format(
"UPDATE tbl_Student SET name='{0}',department='{1}',session='{2}',hall='{3}',email='{4}',roomPosition='{5}',roomNo='{6}',fathername='{7}',address='{8}',school='{9}',college='{10}',district='{11}' WHERE studentId='{12}'",
aStudent.Name, aStudent.Department, aStudent.Session, aStudent.HallName, aStudent.Email, aStudent.RoomPosition, aStudent.RoomNo, aStudent.Fathername, aStudent.Address, aStudent.School, aStudent.College, aStudent.District, aStudent.StudentId);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
message = "Employee: " + aStudent.Name + " has been Updated";
query = String.Format("delete from tbl_room where roomNo='{0}'", aStudent.RoomPosition);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
}
catch (Exception exception)
{
throw new Exception("Error!!!", exception);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(message);
}
public string Save(Book aBook)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("INSERT INTO tbl_book VALUES({0},'{1}','{2}','{3}',{4},{5},{6})", aBook.Id, aBook.Title, aBook.AuthorName, aBook.ISbnNo, aBook.Price, aBook.InitialCopy, aBook.OutsideCopy);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
return("Book have been saved.");
}
catch (Exception ex)
{
throw new Exception("Book couldn't be saved.", ex);
}finally
{
SqlConnectionObj.Close();
}
}
public bool Save(Remark remark)
{
try
{ SqlConnectionObj.Open();
string query = string.Format("Insert into tbl_Remark values('{0}')", remark.Name);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery(); }
catch (Exception ex)
{
throw new Exception("Remark couldn't saved", ex);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(true);
}
private void UpdateReturn(IssueBook selectedIssueBook)
{
DateTime now = DateTime.Now;
try
{
SqlConnectionObj.Open();
string query = string.Format("Update tbl_Issueed set Return_date='{0}' where Book_id={1} and student_Reg='{2}' and Issue_date='{3}'", now, selectedIssueBook.Book.Id, selectedIssueBook.Student.RegNo, selectedIssueBook.DateTime);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
}
catch (Exception exception)
{
throw new Exception("Error occurred during return this book.", exception);
}
finally
{
SqlConnectionObj.Close();
}
}
public string Save(Student aStudent)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("insert into tbl_Student values('{0}','{1}','{2}','{3}')",
aStudent.Name, aStudent.Email, aStudent.RegNo,
aStudent.ContactNo);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
}
catch (Exception exception)
{
throw new Exception("Student does not load this student.", exception);
}
finally
{
SqlConnectionObj.Close();
}
return(aStudent.Name + " hase been saved.");
}
public void InsertRoom(Student selectedStudent)
{
try
{
SqlConnectionObj.Open();
string query = String.Format("insert into tbl_room values ('{0}')", selectedStudent.RoomPosition);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
}
public bool Save(string regNo, string engineNo)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("insert into vehicleinformation values('{0}','{1}')", regNo, engineNo);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
}
catch (Exception exceptionObj)
{
throw new Exception("Can not be saved in your system", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(true);
}
public bool HasBookName(string title)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("select * from tbl_book where Title='{0}'", title);
SqlCommandObj.CommandText = query;
SqlDataReader reader = SqlCommandObj.ExecuteReader();
if (reader != null)
{
return(reader.HasRows);
}
return(false);
}
catch (Exception exception)
{
throw new Exception("Title couldn't loaded from your system", exception);
}finally
{
SqlConnectionObj.Close();
}
}
