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()); } } }