如何在图书馆管理中发行书籍时自动更新数据库值
本文关键字:更新 数据库 图书馆管理 | 更新日期: 2023-09-27 18:34:31
我想用C#制作一个库系统。在这个系统中,当一本书发行时,它应该自动减少数据库中的书籍数量。book quantity == 0
时,应该有一个消息框显示"不可用"。
这是我的代码:
private void btnIssue_Click(object sender, EventArgs e)
{
if (cmbResID.Text != "" && cmbMemID.Text != "" && cmbBookID.Text != "" && txtBkTitle.Text != "" && txtCategory.Text != "" && txtAuthor.Text != "" && txtIssueDate.Text != "" && txtActDate.Text != "")
{
SqlCommand Quantity = new SqlCommand("Select * from tblBookDetails where Book_ID = '" + cmbBookID.Text +"'");
DataSet ds = Library.Select(Quantity);
if (ds.Tables[0].Rows.Count > 0)
{
textBox1.Text = ds.Tables[0].Rows[0].ItemArray.GetValue(5).ToString();
int b = Convert.ToInt32(textBox1.Text);
if (b > 0)
{
//a = a - 1;
//int b = Convert.ToInt32(a);
//label15.Text = a.ToString();
SqlCommand update=new SqlCommand("UPDATE tblBookDetails SET Quantity=Quantity-1 WHERE Book_ID='"+ cmbBookID +"'");
Library.ExecuteInsert(update);
SqlCommand save = new SqlCommand("insert into tblBookIssue values(@ResID,@Member_ID,@Book_ID,@Issue_Date,@Act_Ret_Date)");
save.Parameters.AddWithValue("@ResID", cmbResID.Text);
save.Parameters.AddWithValue("@Member_ID", cmbMemID.Text);
save.Parameters.AddWithValue("@Book_ID", cmbBookID.Text);
save.Parameters.AddWithValue("@Issue_Date", txtIssueDate.Text);
save.Parameters.AddWithValue("@Act_Ret_Date", txtActDate.Text);
Library.Insert(save);
MessageBox.Show("Book Issued", "Book Issue", MessageBoxButtons.OK, MessageBoxIcon.Information);
clear();
}
else
{
MessageBox.Show("this book is not available");
}
}
}
else
{
MessageBox.Show("FILL COLUMS");
}
}
基于文本框执行SQL是非常不安全的,并且容易受到SQL注入攻击。此外,为了遵循面向对象程序并制作更干净的代码,建议创建一个 Book 对象,我在下面完成了一些代码,其中显示了包含书籍增量器的示例。最好制作集中的存储过程,执行书籍的获取和书籍结帐的更新。您必须将基本选择转换为存储过程,并编写另一个过程来查看数量,如果数量<1 返回 0,否则返回 1。如果您需要更多信息,请告诉我,此代码应该可以帮助您开始滚动
using System;
using System.Data;
using System.Data.SqlClient;
namespace MockLibrary
{
internal class Book
{
#region Constructors
public Book()
{
}
public Book(string resId, string memberId, string bookId, DateTime issueDate, DateTime actRetDate)
{
this.ResId = resId;
this.MemberId = memberId;
this.BookId = bookId;
this.IssueDate = issueDate;
this.ActRetDate = actRetDate;
}
#endregion
#region Properties
private string _ResID;
private string _MemberID;
private string _BookId;
private DateTime _IssueDate;
private DateTime _ActRetDate;
public string ResId
{
get { return _ResID; }
set { _ResID = value; }
}
public string MemberId
{
get { return _MemberID; }
set { _MemberID = value; }
}
public string BookId
{
get { return _BookId; }
set { _BookId = value; }
}
public DateTime IssueDate
{
get { return _IssueDate; }
set { _IssueDate = value; }
}
public DateTime ActRetDate
{
get { return _ActRetDate; }
set { _ActRetDate = value; }
}
#endregion
public Book GetBookByID(string resId, string memberId)
{
try
{
using (SqlConnection con = new SqlConnection("put your db con string here"))
{
using (SqlCommand cmd = new SqlCommand("sp_GetBookById", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ResId", SqlDbType.VarChar).Value = resId;
cmd.Parameters.Add("@MemberId", SqlDbType.VarChar).Value = memberId;
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Book newBook = new Book(rdr["ResId"].ToString(),rdr["MemberId"].ToString(),rdr["BookId"].ToString(),DateTime.Now,DateTime.Now);
return newBook;
}
}
}
}
catch
{
throw new Exception("something went wrong");
}
return null;
}
public bool CheckoutBook(string resId, string memberId)
{
using (SqlConnection con = new SqlConnection("put your db con string here"))
{
using (SqlCommand cmd = new SqlCommand("sp_CheckoutBook", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ResId", SqlDbType.VarChar).Value = resId;
cmd.Parameters.Add("@MemberId", SqlDbType.VarChar).Value = memberId;
con.Open();
cmd.ExecuteNonQuery();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr["checkoutsuccessful"].ToString() == "1")
{
return true;
}
}
}
}
return false;
}
}
}
当用户返回一本书时:-
MySqlCommand cm1;
cm1 = new MySqlCommand("update addbook set bookquantity=bookquantity+1 where bookname='" + txt_bookname.Text + "'",con);
cm1.ExecuteNonQuery();