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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 <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 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 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(); } }
public bool CheckAll(string username, string password) { try { SqlConnectionObj.Open(); string query = string.Format("select * from tbl_Admin where UserName='******' AND Password='******'", username, password); SqlCommandObj.CommandText = query; SqlDataReader reader = SqlCommandObj.ExecuteReader(); if (reader != null) { return(reader.HasRows); } return(false); } catch (Exception exception) { throw new Exception("Error occurred during your login System.", exception); } finally { SqlConnectionObj.Close(); } }
public bool HasRegNo(string regNo) { try { SqlConnectionObj.Open(); string query = string.Format("select * from tbl_Student where regNo='{0}'", regNo); SqlCommandObj.CommandText = query; SqlDataReader reader = SqlCommandObj.ExecuteReader(); if (reader != null) { return(reader.HasRows); } return(false); } catch (Exception exception) { throw new Exception("System Does not load this registration no.", exception); } finally { SqlConnectionObj.Close(); } }
public List <DateBetweenSchedule> GetDateBetweenSchedule(DateTime startDate, DateTime endDate) { List <DateBetweenSchedule> dateBetweenSchedules = new List <DateBetweenSchedule>(); try { SqlConnectionObj.Open(); string query = string.Format("SELECT Date,VehicleRegNo,Shift FROM ScheduleVehicle WHERE Date BETWEEN '{0}' AND '{1}'", startDate, endDate); SqlCommandObj.CommandText = query; SqlDataReader reader = SqlCommandObj.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { DateBetweenSchedule single = new DateBetweenSchedule(); single.DateTime = (DateTime)reader[0]; single.RegNo = reader[1].ToString(); single.Shift = reader[2].ToString(); dateBetweenSchedules.Add(single); } return(dateBetweenSchedules); } } 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 List <Book> GetAllBooks(string name, string searchType) { List <Book> books = new List <Book>(); try { SqlConnectionObj.Open(); if (searchType == "name") { string quere = string.Format("select * from tbl_book where Title like '%{0}%'", name); SqlCommandObj.CommandText = quere; SqlDataReader reader = SqlCommandObj.ExecuteReader(); while (reader.Read()) { Book aBook = new Book(); aBook.Id = Convert.ToInt32(reader["Id"]); aBook.Title = reader["Title"].ToString(); aBook.AuthorName = reader["AuthorName"].ToString(); aBook.InitialCopy = Convert.ToInt32(reader["InitialCopy"]); aBook.OutsideCopy = Convert.ToInt32(reader["OutsideCopy"]); aBook.Price = Convert.ToInt32(reader["Price"]); books.Add(aBook); } } else if (searchType == "author") { string quere = string.Format("select * from tbl_book where AuthorName like '%{0}%'", name); SqlCommandObj.CommandText = quere; SqlDataReader reader = SqlCommandObj.ExecuteReader(); while (reader.Read()) { Book aBook = new Book(); aBook.Id = Convert.ToInt32(reader["Id"]); aBook.Title = reader["Title"].ToString(); aBook.AuthorName = reader["AuthorName"].ToString(); aBook.InitialCopy = Convert.ToInt32(reader["InitialCopy"]); aBook.OutsideCopy = Convert.ToInt32(reader["OutsideCopy"]); aBook.Price = Convert.ToInt32(reader["Price"]); books.Add(aBook); } } else if (searchType == "bookId") { string quere = string.Format("select * from tbl_book where Id={0}", name); SqlCommandObj.CommandText = quere; SqlDataReader reader = SqlCommandObj.ExecuteReader(); while (reader.Read()) { Book aBook = new Book(); aBook.Id = Convert.ToInt32(reader["Id"]); aBook.Title = reader["Title"].ToString(); aBook.AuthorName = reader["AuthorName"].ToString(); aBook.InitialCopy = Convert.ToInt32(reader["InitialCopy"]); aBook.OutsideCopy = Convert.ToInt32(reader["OutsideCopy"]); aBook.Price = Convert.ToInt32(reader["Price"]); books.Add(aBook); } } } catch (Exception exception) { throw new Exception("Error occurred during books loading from your system.", exception); }finally { SqlConnectionObj.Close(); } return(books); }
public List <Transaction> GetTransactionSummary(string dateFrom, string dateTo) { List <Transaction> transactions = new List <Transaction>(); try { SqlConnectionObj.Open(); string query = "Select a.Date,IsNull(Sum(b.Amount),0) as Income,IsNull(Sum(c.Amount),0) as Expense from tbl_Transaction a"; query += " Left outer Join tbl_Transaction b on b.ID = a.ID and b.Type = 'Income'"; query += " Left Join tbl_Transaction c on c.ID = a.ID and c.Type= 'Expense'"; query += " Where a.Date between '" + dateFrom + "' and '" + dateTo + "'"; query += " Group by a.Date"; query += " Order By a.Date"; SqlCommandObj.CommandText = query; SqlDataReader reader = SqlCommandObj.ExecuteReader(); while (reader.Read()) { Transaction aTransaction = new Transaction(); aTransaction.TransactionDate = reader[0].ToString(); aTransaction.Income = Convert.ToDouble(reader[1]); aTransaction.Expence = Convert.ToDouble(reader[2]); transactions.Add(aTransaction); } List <string> dateList = CreateDateList(dateFrom, dateTo); List <string> tempDateList = new List <string>(); foreach (string obj in dateList) { bool flag = false; foreach (var objt in transactions) { if (objt.TransactionDate == obj) { flag = true; break; } } if (flag) { tempDateList.Add(obj); } } foreach (var obj in tempDateList) { dateList.Remove(obj); } foreach (var obj in dateList) { transactions.Add(new Transaction { TransactionDate = obj }); } } 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); }