C# 从 Access 数据库中获取数据的正确方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15148588/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Proper way of getting a data from an Access Database
提问by Red Gabanan
I'm a bit confused of how to get a data from an access database. Is it proper to gather it first in a List then get those data from your List OR it is okay to just directly get it in you database ?
我对如何从访问数据库中获取数据感到有些困惑。首先在列表中收集它然后从列表中获取这些数据是否合适，或者可以直接将其获取到数据库中？
My codes work perfectly fine, but I wanna know if there is a better way to do this?? :
我的代码工作得很好，但我想知道是否有更好的方法来做到这一点？：
private void button3_Click(object sender, EventArgs e)
{
OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\redgabanan\Desktop\Gabanan_Red_dbaseCon\Red_Database.accdb");
connection.Open();
OleDbDataReader reader = null;
OleDbCommand command = new OleDbCommand("SELECT * from Users WHERE LastName='"+textBox8.Text+"'", connection);
reader = command.ExecuteReader();
listBox1.Items.Clear();
while (reader.Read())
{
listBox1.Items.Add(reader[1].ToString()+","+reader[2].ToString());
}
connection.Close();
*I'm getting my records directly from a database then display it in a listbox.
*我直接从数据库中获取我的记录，然后将其显示在列表框中。
采纳答案by Jeremy Thompson
One thing that is sticking out like a sore thumb is the SQLInjection and to use Parameterised queries, eg:
SQLInjection 和使用参数化查询，像拇指酸痛一样突出的一件事，例如：
OleDbCommand command = new OleDbCommand("SELECT * from Users WHERE LastName='@1'", connection);
command.Parameters.AddWithValue("@1", textBox8.Text)
What your doing is perfectly acceptable, although you would generally be better off to use a SQL Database.
您的做法是完全可以接受的，尽管您通常最好使用 SQL 数据库。
Edit: Here is how you seperate your business logic from the GUI:
编辑：以下是将业务逻辑与 GUI 分开的方法：
Class BusLogic
{
public List<string> ListboxItems = new List<string>();
public void PopulateListBoxItems(string userName)
{
string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\redgabanan\Desktop\Gabanan_Red_dbaseCon\Red_Database.accdb";
using (OleDbConnection connection = new OleDbConnection(connString))
{
connection.Open();
OleDbDataReader reader = null;
OleDbCommand command = new OleDbCommand("SELECT * from Users WHERE LastName='@1'", connection);
command.Parameters.AddWithValue("@1", userName)
reader = command.ExecuteReader();
while (reader.Read())
{
ListboxItems.Add(reader[1].ToString()+","+reader[2].ToString());
}
}
}
}
GUI
图形用户界面
private void button3_Click(object sender, EventArgs e)
{
var busLogic = new BusLogic();
busLogic.PopulateListBoxItems(textBox8.Text);
\listBox1.Items.Clear();
ListboxItems.DataSource = busLogic.ListboxItems;
}
回答by Gian Acuna
You can maybe separate your data access functions in different classes or create generic functions to retrieve records.
您可以在不同的类中分离数据访问函数，或者创建通用函数来检索记录。
回答by Mark Stevens
I would say the answer is "yes" to both.
我会说两者的答案都是“是”。
What you're doing now is perfectly acceptable for simple cases. Just be aware that it doesn't "scale" very well. That is, loading 10 or 20 items is fine. But what happens if it becomes 10 thousand or a million?
对于简单的情况，您现在正在做的事情是完全可以接受的。请注意，它不能很好地“扩展”。也就是说，加载 10 或 20 个项目是可以的。但是如果变成一万或一百万会怎样？
In that case you want to look at using a Model-View-Controller (MVC) architecture. That's a topic in itself, but basically you decouple the listbox (the "view") from the data (the "model").
在这种情况下，您需要考虑使用模型-视图-控制器 (MVC) 架构。这本身就是一个主题，但基本上您将列表框（“视图”）与数据（“模型”）分离。
See this site for a C#-centric MVC discussion
In between what you're doing now and a full-blown MVC architecture, you may simply want to do as you suggest - load the list first then add them to the list box. That gains you nothing if you just load it once, but if the list is loaded "all over the place", you can save the database IO overhead each time by just accessing it once.
在您现在正在做的事情和成熟的 MVC 架构之间，您可能只想按照您的建议去做 - 首先加载列表，然后将它们添加到列表框中。如果您只加载一次，这不会给您带来任何好处，但是如果列表“到处”加载，您每次只需访问一次就可以节省数据库 IO 开销。
The fact that you thought to ask the question indicates you're on the right track.
你想提出这个问题的事实表明你在正确的轨道上。
回答by A. Rodas
Although your code works without any problem, I suggest you to perform some exception handling as in this example, since both OleDbConnection.Open()and OleDbCommand.ExecuteReader()might throw an InvalidOperationException.
虽然你的代码工作没有任何问题，我建议你去执行一些异常处理在这个例子中，因为两者OleDbConnection.Open()并OleDbCommand.ExecuteReader()可能抛出InvalidOperationException。
It is also common to wrap the connection with a usingstatement, so in the end connection.close()is called automatically, but this is just a personal preference.
用using语句包装连接也很常见，所以最后connection.close()会自动调用，但这只是个人喜好。
