图书馆归还系统如何运作？ C＃.net和MySQL WB [英] How Library return system works? C#.net and MySQL WB
问题描述
这里是我用于借用物品系统的代码和查询，
这里你可以借用物品表格数据库。表格。
注意：借用物品系统是工作
here's the code and query i had used for Borrowing items System,
here you can borrow an items form database.TableA.
note: the borrowing items System is working
//and here you can put a value to subtract the value of stock from database.TableA
and the value u use will be inserting directly to stock.database.tableB
private void brwBtn_Click(object sender, EventArgs e)
{
try
{
int brValue = 0;
brValue = int.Parse(stockTxt.Text) - int.Parse(brStockTxt.Text);
string modConnection = "datasource=localhost;port=3306;username=root;password=1234";
string modQuery = "UPDATE Database.tableA SET Id = '" + this.idTxt.Text + "',Items = '" + this.itemsTxt.Text + "',Stock = '" + brValue + "' Where Id = '" +this.idTxt.Text + "';";
MySqlConnection modData = new MySqlConnection(modConnection);
MySqlCommand modCommandData = new MySqlCommand(modQuery, modData);
MySqlDataReader MyReader;
modData.Open();
MyReader = modCommandData.ExecuteReader();
MessageBox.Show("Registered Data..");
this.Hide();
while (MyReader.Read())
{
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
////////////////////////////////////// Inserting New Items in database.tableB from database.tableA.
string regString = "datasource=localhost;port=3306;username=root;password=1234";
string regQuery = "INSERT INTO databse.tableB(Id,Items,Stock) VALUES ('" + this.idTxt.Text + "','" + this.itemsTxt.Text + "','" + this.brStockTxt.Text + "');";
MySqlConnection regData = new MySqlConnection(regString);
MySqlCommand regCommandData = new MySqlCommand(regQuery, regData);
MySqlDataReader regReader;
try
{
regData.Open();
regReader = regCommandData.ExecuteReader();
MessageBox.Show("Registered Data..");
this.Hide();
while (regReader.Read())
{
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
这里是图书馆退货项目系统应该有效。但问题是我无法弄清楚，应该使用什么代码。有人想帮助我吗？
and here was the Library Return Items System should works. but the problem is i couldn't figure out, what code should will be use. anyone would like to help me?
//here's the another form called return form
public Form3()
{
InitializeComponent();
load_table();
}
//load_table to automatically loaded a data in datagridview from database.TableB
void load_table()
{
string regString = "datasource=localhost;port=3306;username=root;password=1234";
MySqlConnection regData = new MySqlConnection(regString);
MySqlCommand regCommandData = new MySqlCommand("Select * From database.tableB;", regData);
try
{
MySqlDataAdapter sda = new MySqlDataAdapter();
sda.SelectCommand = regCommandData;
DataTable dbDatabase = new DataTable();
sda.Fill(dbDatabase);
BindingSource bSource = new BindingSource();
bSource.DataSource = dbDatabase;
dataGridView1.DataSource = bSource;
sda.Update(dbDatabase);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//If you choose or selecting an items from datagridview it will show the data directly in a given textbox
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
this.rIdTxt.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
this.rItemsTxt.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
this.rStockTxt.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
}
//this is the return items button
//i added a delete button so if you click the return button the items you chosen from the datagridview that came from database.TableB will be deleted
private void rBtn_Click(object sender, EventArgs e)
{
try
{
string modConnection = "datasource=localhost;port=3306;username=root;password=1234";
string modQuery = "Delete from database.tableB Where Id = '" + this.rIdTxt.Text + "';";
MySqlConnection modData = new MySqlConnection(modConnection);
MySqlCommand modCommandData = new MySqlCommand(modQuery, modData);
MySqlDataReader MyReader;
modData.Open();
MyReader = modCommandData.ExecuteReader();
MessageBox.Show("Registered Data..");
this.Hide();
while (MyReader.Read())
{
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//Here's the problem what code should i use to perform a addition
the value i return from database.tableB to add it to database.TableA??
//lets say there's 3 available stock for BookA borrow(database.TableA) and i borrow 2 stocks from it, so the borrow(database.tableA) stock remaining will be 1, and the 2 stocks i borrowed will be added in return(database.tableB) with name BookA and the 2 stocks value i borrowed, and from return(database.tableB) i return the BookA with 2stocks that i borrowed, and added it again database.tableA
//can anyone help me please???
try
{
string modConnection = "datasource=localhost;port=3306;username=root;password=1234";
string modQuery = "UPDATE database.tableA SET Id = '" + this.rIdTxt.Text + "',Items = '" + this.rItemsTxt.Text + "',Stock = '" + (rStockTxt.Text+??????) + "' Where Id = '" + this.rIdTxt.Text + "';";
MySqlConnection modData = new MySqlConnection(modConnection);
MySqlCommand modCommandData = new MySqlCommand(modQuery, modData);
MySqlDataReader MyReader;
modData.Open();
MyReader = modCommandData.ExecuteReader();
MessageBox.Show("Registered Data..");
this.Hide();
while (MyReader.Read())
{
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
推荐答案
不要连接字符串构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。
然后......更改表名：tableA和tableB不描述表中包含的内容，这使得它成为很难弄清楚发生了什么。
考虑有三张桌子：
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
Then...change your table names: "tableA" and "tableB" are not descriptive of what the tables contain, which makes it a lot harder to work out what is happening.
Consider having three tables:
Books
Id can be INT, IDENTITY
ISBN
Title
Author
Users
ID can be INT, IDENTITY
Name
Address
Loans
ID can be INT, IDENTITY
BookID
UserID
LoanDate DATETIME
DueDate DATETIME
ReturnDate DATETIME NULLABLE
每本书都有一个唯一的ID，但相同的书籍共享一个ISBN - 所以你跟踪单个书而不是通用的其中一个类型栈。
从这里，你可以使用简单的JOIN语句计算出你需要的一切 - 并且您不需要删除记录：您只需更新它们即可追踪历史也是一本书。因此，如果我借了一本书并发现中间部分已被撕掉，你可以回顾一下记录，找出应该责怪的人！
考虑一下它 - 它比你想象的要容易得多，虽然它意味着扔掉你现有的代码并重新开始我不得不说看看你的代码并不是一个坏主意...
设置一个静态连接字符串 - 如果它发生变化（确实如此），你不必在很多地方更改它（最好的方法是使用配置文件，这样你的应用程序就不必连接字符串时更改。
关闭并处理您的连接，命令，读取器等。使用使用块是最简单的方法。
不要将ExecuteReader用于UPDATE命令 - 它们不返回任何内容。改为使用ExecuteNonQuery。
在命名约定中保持一致： modData 和 modCommandData 都可以（虽然大多数人倾向于分别使用 com 和 cmd ），但随后你会使用 MyReader 哪个不适合。
你为什么要隐藏表格？这是一件不寻常的事情......特别是对于基于图书馆的系统...
我知道这是你的作业，而且你是初学者 - 而且这听起来像是我对你很苛刻 - 但这看起来并不像你坐下来想想你要做什么 - 它看起来像你从一开始就开始编码而开始编码！ ：笑：如果你停下来思考，你会得到一个更容易使用且更容易编码的系统 - 诚实！
Each book has a unique ID, but "identical books" share an ISBN - so you track the individual book rather than a generic "one of these" type stack.
From this, you can work out everything you need using simple JOIN statements - and you don't need to delete records: you just update them so you can "trace the history" of a book as well. So if I borrow a book and find the middle section has been torn out, you can look back in the records to find out who to blame!
Have a think about it - it's a lot easier to use than you might think, and while it means throwing away your existing code and starting again I have to say that looking at your code that wouldn't be a bad idea...
Set up a single static connection string - that way if it changes (and it does) you don't have to change it in many places (the best way is to use a configuration file so you app doesn't have to be changed when the connections string does).
Close and Dispose of your Connections, Commands, Readers and so forth. use a using block is the simplest way.
Don't use ExecuteReader for UPDATE commands - they don't return anything. USe ExecuteNonQuery instead.
Be consistent in your naming conventions: modData and modCommandData are ok (though most people tend to use com and cmd respectively) but then you go with MyReader which doesn't fit.
Why are you Hiding a form? This is an unusual thing to do...particularly for a library based system...
I know this is your homework, and that you are a beginner - and that is sounds like I am being harsh on you - but this doesn't look like you sat down and thought about what you have to do - it looks like you leapt in and started coding from the beginning instead! :laugh: If you do stop and think, you will get a system that is easier to use and easier to code - honest!
这篇关于图书馆归还系统如何运作？ C＃.net和MySQL WB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
