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 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 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 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 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 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();
}
}
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 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);
}
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 SaveSchedule(VehicleInformation vehicle, DateTime selectedDate, string shift, string bookedBy, string address)
{
try
{
SqlConnectionObj.Open();
string query = string.Format("INSERT INTO ScheduleVehicle VALUES('{0}',{1},'{2}','{3}','{4}')", vehicle.RegNo, selectedDate, shift, bookedBy, address);
SqlCommandObj.CommandText = query;
SqlCommandObj.ExecuteNonQuery();
return(true);
}
catch (Exception exceptionObj)
{
throw new Exception("Schedule can not be saved!", exceptionObj);
}
finally
{
if (SqlConnectionObj != null && SqlConnectionObj.State == ConnectionState.Open)
{
SqlConnectionObj.Close();
}
}
return(false);
}
