public static string BorrowBook(BusinessLayer.Student borrowingStudent, BusinessLayer.Book requestedBook)
{
SqlConnection conn;
using (conn = new SqlConnection(getConnectionString()))
{
string sql = "SP_borrow";
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@studentId", System.Data.SqlDbType.Int);
param[1] = new SqlParameter("@bookId", System.Data.SqlDbType.Int);
param[0].Value = borrowingStudent.stId;
param[1].Value = requestedBook.bkId;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// cmd.ExecuteNonQuery(); // run this if no return expected
return(cmd.ExecuteScalar().ToString());
}
}
}
public static int ExecuteInsertBook(BusinessLayer.Book book, int authorid, int typeid)
{
string sql = "INSERT INTO Book (Title, PageCount, Price, AuthorId, TypeId) VALUES (@Title, @PageCount, @Price, @AuthorId, @TypeId)";
using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@Title", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@PageCount", SqlDbType.Int);
cmd.Parameters.Add("@Price", SqlDbType.Decimal, 2);
cmd.Parameters.Add("@AuthorId", SqlDbType.Int);
cmd.Parameters.Add("@TypeId", SqlDbType.Int);
cmd.Parameters["@Title"].Value = book.Title;
cmd.Parameters["@PageCount"].Value = book.PageCount;
cmd.Parameters["@Price"].Value = book.Price;
cmd.Parameters["@AuthorId"].Value = authorid;
cmd.Parameters["@TypeId"].Value = typeid;
try
{
conn.Open();
return((int)cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(-1);
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
if (this.Page.IsValid)
{
BusinessLayer.Book book = new BusinessLayer.Book();
BusinessLayer.Author author = new BusinessLayer.Author();
BusinessLayer.Type type = new BusinessLayer.Type();
book.Title = this.tb_title.Text;
book.PageCount = Convert.ToInt32(this.tb_pagecount.Text);
book.Price = Convert.ToDecimal(this.tb_price.Text);
author.FirstName = this.tb_authorfirstname.Text;
author.LastName = this.tb_authorlastname.Text;
type.Name = this.tb_type.Text;
int result = DataAccessLayer.UtilityTools.ExecuteInsertBook(book, author, type);
if (result == 1)
{
this.lbl_resultmessage.Text = "Submission Succesful!";
}
/*else if (result == 0)
* this.lbl_resultmessage.Text = "There was an error at the Database level";
* else
* this.lbl_resultmessage.Text = "There was an error at the Method level";*/
else
{
this.lbl_resultmessage.Text = result.ToString();
}
}
}
public static string InsertBook(BusinessLayer.Book newBook)
{
SqlConnection conn;
using (conn = new SqlConnection(getConnectionString()))
{
string sql = "SP_insertBook";
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlParameter[] param = new SqlParameter[6];
param[0] = new SqlParameter("@bookTitle", System.Data.SqlDbType.NVarChar, 50);
param[1] = new SqlParameter("@bookPageCount", System.Data.SqlDbType.Int);
param[2] = new SqlParameter("@bookPrice", System.Data.SqlDbType.Float);
param[3] = new SqlParameter("@authorFirstName", System.Data.SqlDbType.NVarChar, 50);
param[4] = new SqlParameter("@authorLastName", System.Data.SqlDbType.NVarChar, 50);
param[5] = new SqlParameter("@typeName", System.Data.SqlDbType.NVarChar, 50);
param[0].Value = newBook.bkTitle;
param[1].Value = newBook.bkPageCount;
param[2].Value = newBook.bkPrice;
param[3].Value = newBook.bkAuthorFirstName;
param[4].Value = newBook.bkAuthorLasstName;
param[5].Value = newBook.bkTypeName;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// cmd.ExecuteNonQuery(); // run this if no return expected
return(cmd.ExecuteScalar().ToString());
}
}
}
