The right way to retrieve data from an Access database

Question

I am a bit confused about how to get data from an access database. Is it right to first collect it in a list and then get this data from your list OR is it normal to just get it in a database?

My codes work fine, but I want to 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 get my records directly from the database and then display them in a list.

Answer 1

One thing that sticks out like a sore thumb is SQLInjection and the use of parameterized queries, for example:

 OleDbCommand command = new OleDbCommand("SELECT * from Users WHERE LastName='@1'", connection); command.Parameters.AddWithValue("@1", textBox8.Text) 

What you do is perfectly acceptable, although usually you are better off using an SQL database.

Edit: This is how you separate your business logic from the 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; } 

Answer 2

I would say the answer is yes for both.

What you are doing now is quite acceptable for simple cases. Just keep in mind that it does not “scale” very well. That is, loading 10 or 20 items in order. But what happens if it becomes 10 thousand or a million?

In this case, you want to look at using the Model-View-Controller (MVC) architecture. This is a topic in itself, but basically you separate the list ("view") from the data ("model").

See this site for a discussion of MVC with C # Center

Between what you are doing now and the full-blown MVC architecture, you might just want to do what you are offering - first load the list and then add them to the list. It doesn’t bring you anything if you just download it once, but if the list is downloaded “everywhere”, you can save the overhead of the IO database each time just by accessing it once.

The fact that you decide to ask a question indicates that you are on the right track.

Answer 3

Despite the fact that your code works without any problems, I suggest you do some exception handling, as in this example , since both OleDbConnection.Open() and OleDbCommand.ExecuteReader() can raise an InvalidOperationException .

A connection to the using statement is also common, so at the end connection.close() is called automatically, but this is only a personal preference.

Answer 4

You can separate your data access functions in different classes or create common functions for retrieving records.