1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'C, C++, C#' started by Scrog, Oct 16, 2009.

  1. Scrog

    Scrog Registered Member

    Joined:
    Jun 18, 2009
    Messages:
    54
    Likes Received:
    37
    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?
     
  2. Scrog

    Scrog Registered Member

    Joined:
    Jun 18, 2009
    Messages:
    54
    Likes Received:
    37
    *Bump.. Anyone?
     
  3. SEO20

    SEO20 Elite Member

    Joined:
    Mar 25, 2009
    Messages:
    2,017
    Likes Received:
    2,260
    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
     
  4. Rambaldi

    Rambaldi Registered Member

    Joined:
    Nov 30, 2009
    Messages:
    81
    Likes Received:
    11
    Occupation:
    Programmer
    Location:
    Sweden
    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
     
  5. theMagicNumber

    theMagicNumber Regular Member

    Joined:
    May 13, 2010
    Messages:
    345
    Likes Received:
    195
    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.
    Code:
    listBox1.Items.Clear();
    OleDbConnection connection = new  OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data  Source=answers.mdb");
    OleDbCommand comm = connection.CreateCommand();
    comm.CommandText = "SELECT [answer], [question] FROM [thetable] WHERE [question] LIKE [email protected]% OR [answer] LIKE [email protected]%";
    comm.Parammeters.AddWithValue("p1", textBox1.Text);
    using(connection)
    {
      connection.Open();
      using(comm)
      {
           using(OleDbDataReader reader = comm.ExecuteReader())
           {
                while(reader.Read())
                {
                     this.listBox1.Items.Add(reader.GetString(0));
                     Application.DoEvents(); //this will make your GUI responsive while looping through the records.
                }
           }
       }
    }
    
    The code is not tested but it should work.
     
    Last edited: May 13, 2010
  6. smack

    smack Junior Member

    Joined:
    Feb 1, 2010
    Messages:
    182
    Likes Received:
    78
    Occupation:
    Software Engineer/Evil Genius
    Location:
    inside .NET
    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.
     
  7. RedSEO

    RedSEO Newbie

    Joined:
    May 13, 2010
    Messages:
    16
    Likes Received:
    1
    .mdb is an access database correct? not much stored procedures or other fancy stuff in them (last time I checked 10 years ago lol)