Autocomplete Function [c#]

[Solarion]

I've put this handy function together however so far it only works with one Texbox control. I want to make it more dynamic to work across multiple Texboxes. I'm thinking of maybe passing in an array of controls or something, to make it work with both the music styles and last name Texboxes.

The main reason #2 is that I want to put this function into a class instead of in the main form code with is not cool.

Any suggestions are appreciated.

        //"SELECT distinct FirstName FROM People"
        private void Autocomplete(string Sql)
        {
            try
            {
 
                sqlConn.Open();
                SqlCommand cmd = new SqlCommand(Sql, sqlConn);
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds, "People");
                AutoCompleteStringCollection col = new AutoCompleteStringCollection();
                int i = 0;
                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    col.Add(ds.Tables[0].Rows[i]["FirstName"].ToString());


                }
                txtFirstName.AutoCompleteSource = AutoCompleteSource.CustomSource;
                txtFirstName.AutoCompleteCustomSource = col;
                txtFirstName.AutoCompleteMode = AutoCompleteMode.Suggest;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

 

[Necropolis]

1 Thing: How big is that table you are querying?

Wouldn't it be easier to pull the values into memory than to query the db each time the text in the text box changes?

 

[Bighit]

Try and insert about 8000 records, how is the performance of this method then (when the autocomplete is appearing)?

 

[Solarion]

Try and insert about 8000 records, how is the performance of this method then (when the autocomplete is appearing)?

I'll try that. The response is instant (for now). I'll load in a few thousand into the db and test it.

1 Thing: How big is that table you are querying?

Wouldn't it be easier to pull the values into memory than to query the db each time the text in the text box changes?

I had to do a quick test to confirm this (changed Bob to Bobby in the DB) and the form retained the value "Bob" even after clicking around the other controls and returning to Firstname control, so the values are stored in memory.

 

[Spacerat]

so the question is, is it better to filter/search on the client, or is it better to use the SQL engine to do it for you....

 

[prod]

Are you running a query on an event that could potentially be fired hundreds of times...?

 

[Solarion]

Are you running a query on an event that could potentially be fired hundreds of times...?

It's a once off event on Form load.

 

[envo]

Create your own custom input control with the extension/function autocomplete that accepts the database connection byref etc, this will make the autocomplete functionality available to every single input control and you edit it in one spot

 

[Batista]

It's a once off event on Form load.

Just load it into memory once, finish and klaar, and when someone inserts,updates or deletes, reload it.

 

[Solarion]

Create your own custom input control with the extension/function autocomplete that accepts the database connection byref etc, this will make the autocomplete functionality available to every single input control and you edit it in one spot

Just load it into memory once, finish and klaar, and when someone inserts,updates or deletes, reload it.

Thanks guys, busy working on it now. I'll re-post the code when I'm done.

 

[eye_suc]

Thanks guys, busy working on it now. I'll re-post the code when I'm done.

Something along the lines of this perhaps?

using System.Runtime.Caching;

public static List<YourObject> List()
{
            MemoryCache cache = MemoryCache.Default;
            List<YourObject> list = cache["List"] as List<YourObject>;

            if (list == null)
            {
                CacheItemPolicy policy = new CacheItemPolicy();
                policy.AbsoluteExpiration = new DateTimeOffset(DateTime.Now.Add(10));
                using (DBContext db = new DBContext ())
                {
                    list = db.YourObjects.Where(p => p.Active).ToList();
                    cache.Set("List", list, policy);
                }
            }

            return list;
}

Formatting is tough in this editor... blegh...

That example only populates the cache if the cache item is called. If it is not needed, and expires then it stays blank until you call it again. You could set the cache timeout to something like 5 minutes, or an hour, or whatever, to "auto refresh" your data.

Anyways, you can increase the expiration to your desire, and then manually replace the cache when you modify your list if your data doesn't change often. That example is just a getter, but you could place that in a property with a get;set;.