public bool Insert(BookLoanModel b)
{
bool isSuccess = false;
//create sql connection to connect database
SqlConnection conn = new SqlConnection(myConnectionString);
try
{
//write the query to insert into database
string sql = "INSERT INTO book_loan (issued_book_id,issued_to_user_id,issued_date,return_date,issued_book_name,issued_user_name,return_status) VALUES(@issued_book_id,@issued_to_user_id,@issued_date,@return_date,@issued_book_name,@issued_user_name,@return_status)";
//create sql command to execute the query
SqlCommand cmd = new SqlCommand(sql, conn);
//pass the value to sql query
cmd.Parameters.AddWithValue("@issued_book_id", b.issued_book_id);
cmd.Parameters.AddWithValue("@issued_to_user_id", b.issued_to_user_id);
cmd.Parameters.AddWithValue("@issued_date", b.issued_date);
cmd.Parameters.AddWithValue("@return_date", b.return_date);
cmd.Parameters.AddWithValue("@issued_book_name", b.issued_book_name);
cmd.Parameters.AddWithValue("@issued_user_name", b.issued_user_name);
cmd.Parameters.AddWithValue("@return_status", b.return_status);
//open database connection
conn.Open();
//create an integer variable to check whether the query was executed succesfully or not
int checkValue = cmd.ExecuteNonQuery();
if (checkValue > 0)
{
isSuccess = true;
userDAL u = new userDAL();
u.Update(b.issued_user_name, countBooksLend(b.issued_user_name));
bookDAL bro = new bookDAL();
bro.Update(b.issued_book_name, b.return_status);
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(BookLoanModel b)
{
bool isUpdateSuccess = false;
//create sql connection
SqlConnection conn = new SqlConnection(myConnectionString);
try
{
string sql = "UPDATE book_loan SET issued_book_id=@issued_book_id,issued_to_user_id=@issued_to_user_id,issued_date=@issued_date,return_date=@return_date,issued_book_name=@issued_book_name,return_status=@return_status WHERE id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@issued_book_id", b.issued_book_id);
cmd.Parameters.AddWithValue("@issued_to_user_id", b.issued_to_user_id);
cmd.Parameters.AddWithValue("@issued_date", b.issued_date);
cmd.Parameters.AddWithValue("@return_date", b.return_date);
cmd.Parameters.AddWithValue("@id", b.id);
cmd.Parameters.AddWithValue("@issued_book_name", b.issued_book_name);
cmd.Parameters.AddWithValue("@issued_user_name", b.issued_user_name);
cmd.Parameters.AddWithValue("@return_status", b.return_status);
conn.Open();
int checkValue = cmd.ExecuteNonQuery();
if (checkValue > 0)
{
isUpdateSuccess = true;
userDAL u = new userDAL();
u.Update(b.issued_user_name, countBooksLend(b.issued_user_name));
bookDAL bro = new bookDAL();
bro.Update(b.issued_book_name, b.return_status);
}
else
{
isUpdateSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isUpdateSuccess);
}