[Help] C# Database (.mdb) Record Retrieval

Whatsup.

At the moment I'm using this code to filter results from my database file when I type text into my textbox:

Does anybody know a faster way to filter the results based on the text entered in the textbox?

*Bump.. Anyone?

Is it Winforms or ASP.NET?

From the code it's most likely to be winforms.
Make use of caching mechanisms.

This will help you: http://www.codeproject.com/KB/cs/cacheinwinformapps.aspx

I really hope I'm not gravedigging here, but what you need to do is turn off auto refresh for listbox..

I had similiar problems in a project where I used flexgrid recently and it took a while for me to figure out
I can paste some code if you don't figure it out

Scrog said:

Whatsup.

At the moment I'm using this code to filter results from my database file when I type text into my textbox:

Code:

listBox1.Items.Clear();

string sqlstring = "SELECT * FROM [thetable] WHERE [question] LIKE '%" + textBox1.Text + "%' or [answer] LIKE '%" + textBox1.Text + "%'";

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=answers.mdb");

DataSet ds = new DataSet("mydataset");

OleDbDataAdapter adapter = new OleDbDataAdapter(sqlstring, connection);
adapter.Fill(ds);

foreach (DataRow dr in ds.Tables[0].Rows)
{
       listBox1.Items.Add("[" + Convert.ToString(dr["answer"]) + "] " + Convert.ToString(dr["question"]));
}

Does anybody know a faster way to filter the results based on the text entered in the textbox?

Click to expand...

You should use parametric sql queries. If you need only the results and you don't plan to modifiy or delete them you should use reader not adapter.
The code is not tested but it should work.

like theMagicNumber said you should be using either parametrized statements or stored procedures.

if you're going to use dynamically generated sql statements though, make sure you're sanitizing your statements for escape characters and the like. since this appears to be a local database i would imagine the risk of sql injection is pretty much non existent, but even so you don't want to be generating exceptions if someone accidentally types in a ' or something similar.

depending on the size of your dataset, you may want to consider storing it in memory while doing your filtering and then committing the changes once the filtering operation is complete. if you decide to go that route you have two options. datatables allow for their own flavor of T-SQLish queries to be written against them, you can also use linq to query your data.

another consideration is trying to cut down on the amount of queries you are firing off. it almost looks like what you are doing there is an auto suggest or something similar. how often is that query getting fired? does it happen with the OnKeyPress event? if so you could be shooting off bunches of queries every time a user starts typing. maybe consider using a "search" button that the user must click after entering their whole search term. it doesn't have that nifty Gee-Whiz functionality of an auto suggest, but it will help performance by cutting down on the number of times that query is executed.

these are just a few guesses, since you only posted a little snippet of code it's a bit hard to say how exactly you're using it but just a few thoughts to get your mind turning in the right direction.

one last thing. if this query is going to be user many times while the program is open, you could consider making the connection settings and other related objects larger in scope. in other words you want to open that connection and set it up when the application loads and then run your queries when you need instead of re-instantiating the connections and such.

.mdb is an access database correct? not much stored procedures or other fancy stuff in them (last time I checked 10 years ago lol)