• 0

[c#] Database Search as you type functionality


Question

Hi guys,

I have a situation where I need to be able to search a database as the user types. It's running locally in SQL Server 2005, so I've been querying every time the textbox text changes. This doesn't seem to work. Basically, if I search for '2334', '2' might have 5000 results, whereas '2334' might have 7. If I type '2334' really quickly, my current method flashes the results for '2334', then shows me the results for '2'. (D'oh!)

Basically, on text change, I prepare my query, execute it, and databind a datagridview to it. This seems to work, but not when you type quickly.

What is the proper way to do this? Then, how can I have it so that, let's say the user is typing '2334', in the text box, but by the time '2334' is in, '2' hasn't finished yet, so it can ignore the searches for '23', and '233'?

Thanks

9 answers to this question

Recommended Posts

  • 0

I don't know about C#, but I can do that in Java. If C# supports mutilthreading, then it's easy "by concept".

You register a listener on that text box. When the user starts typing, you create a thread to search the database, when the user keeps typing:

1- create a thread the search the database with the user's current value entered

2 - check to see if that previous thread is still running, if so, kill it and let the current thread finish execution.

  • 0

You could use the Binding Source's Filter method to do it, but then the search text would have to come from only one column.

As the user types on the textbox event, just use BindingSource1.Filter = "%COLUMNNAME% - '" + TextBox1.Text + '"

  • 0

I remember i tried to implement this a while back, it caused the biggest issues, it's definitely not trivial, as someone mentioned you have to spawn a new thread and then check if a previous thread is running or not, and because of the nature of STA model, having to route your UI calls back on the UI thread, this causes so many issues, i mean in theory doing the thread thing actually works, but when you act like a stupid user, and type really fast and quickly backspace, it causes issues.

Many of the time filters work off index'ed files, rather than hammering the database on every new text update. The trade-off being, the results won't be up to date until it's next indexed.

I wasn't too sure about the filter's property in the binding source it always looked interesting never tried it, but, i think it's based off the premises that the data is pre-loaded into the datagrid or whatever control it maybe binded to, so the results may not be up to date one very filter call. But i suppose you can just do a re-bind on every filter call.

Also there was one thing i noticed in these filter implementations whether it be in Vista or in Spotlight, there's a delay from when a user types to when the results appear, i think it does this to prevent rapid text typing with crazy users, so maybe you can consider that as well, implementing a delay from the text-entry until the execution of the filtering method.

  • 0

you have to do the threaded approach.

but you have to be VERY careful not to hammer your database.

for instance: you could set up a thread to do the search and only do the search every other second or so while the user is typing. that'd help you NOT kill your db when tons of users are doing the same thing. another thing you could do is index a smaller table in your database of common keywords that the user could search for and search on that (the index that Winston talked)

and about the threads, C# has a you can simply use the BeginInvoke (read the docs on these) methods to call stuff on the secondary thread as if you were on the main thread.

you have to be very careful with the threads, though :)

  • 0

I'm not worried about hammering the database. It's not a multi-user application. It attaches the database at runtime in sql server 2005 express.

Thanks guys, hopefully I can come up with something. Any more tips, or a link to a good article, would be welcome.

Ok. Why BeginInvoke()?

I've been doing stuff like this:

void whatever()

{

if (this.invokerequired)

{

this.invoke(new whateverdelegate(whatever));

}

else

{

do whatever

}

}

Maybe i need to read up more on threading.

  • 0
  Menge said:
and about the threads, C# has a you can simply use the BeginInvoke (read the docs on these) methods to call stuff on the secondary thread as if you were on the main thread.

you have to be very careful with the threads, though :)

Definitely. Also if you want to have it easier, in the case of doing it in .NET 2.0, you can make use of the BackgroundWorker component to help you manage calls to the UI.

  • 0

Okok. Let's see if I have this right.

Every search I want to make initiates a thread. Every thread has a delay, 500ms, or 1sec or something before it does anything. After this delay, it checks to see if it's the last thread in my collection of threads. If it's not, it aborts. If it is, it ensures that there are no threads before it doing stuff. If there are, it aborts them. Then, it performs the search and updates the UI.

Good? Bad? Fundamentally flawed in every way shape and form possible, making you feel bad for my copy of visual studio?

  • 0

Anyone see any problems with this? It seems to work....

private void doThreadWork(object dataholder)
{
	if (so == null) return;
	System.Threading.Thread.Sleep(1000);
	if (System.Threading.Thread.CurrentThread == lastThread)
	{
		for (int i = 0; i < 50; i++)
		{
			if (!aThreadIsWorking) break;
			System.Threading.Thread.Sleep(100);
		}
		try
		{
			aThreadIsWorking = true;
			//Get my list of stuff from the database and process it.
			//Send it off to a threadsafe method that sets the datagridview's DataSource
		}
		finally
		{
			aThreadIsWorking = false;
		}
	}
}

to start it all off:

System.Threading.Thread x = new Thread(new ParameterizedThreadStart(doThreadWork));
lastThread = x;
x.Start(data);

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • I was going to say that search engines and such, have been scraping everyone's copyright, IP and data, since the beginning of the internet.
    • Microsoft is officially making the Xbox app on PC a universal launcher by Pulasthi Ariyasinghe It was earlier this year that an image mockup from Microsoft showed the Xbox app on PC with an interesting change: including support for other PC stores on the app, teasing that it may be becoming a universal launcher like GOG Galaxy or Playnite. Considering the company's new handheld initiative that will house a brand-new gaming-focused version of Windows, it was clear that this feature was on the way. And now, Xbox Insiders have access. Announced today as the 'aggregated gaming library' feature, it's poised to land this holiday on the ROG Xbox Ally and ROG Xbox Ally X to easily manage all their installed games from a single place. But before that, Xbox Insiders on PC can have a crack at it to see how it functions and provide feedback to Microsoft. In its current state, Microsoft says that the feature now supports Xbox, Game Pass, Battle.net, and "other leading PC storefronts," all handled via the Xbox PC app. The company did not detail what these other storefronts are, but Steam, Epic Games Store, Ubisoft Connect, and EA Play apps seem likely candidates. "Whether you’re on a Windows PC or a handheld device, your Xbox library, hundreds of Game Pass titles, and all your installed games from leading PC storefronts will now be at your fingertips," said the company. When a game from a supported store is installed on a PC, Insiders should now see it appear on the Xbox app in the My Library and Most Recent sections for easy access. "And this is just the beginning," adds Microsoft. "We’ll continue rolling out support for additional PC storefronts over time." Insiders can also disable this functionality and hide games from specific stores if needed from the Settings > Library & Extensions menu. Anyone interested in testing out the new 'aggregated gaming library' update can use the Xbox Insider app on PC to enroll in the ongoing Insider Previews.
    • Get this powerful mini PC with Core Ultra 9, 32GB RAM, and 1TB SSD for just $799 by Taras Buria The ASUS NUC 14 Pro+ is a powerful mini PC with capable hardware, and right now, you can get it on Amazon with a big discount. At just $799, this computer offers a Core Ultra 9 processor, 32GB of memory, and a 1TB SSD. The NUC 14 Pro+ features a low-profile aluminum chassis, which can be opened without removing rubber feet or undoing any screws. Its toolless design lets you access the storage without a screwdriver. The computer also has a rich set of ports. On the front side, you will find two USB 3.2 Gen 2 Type-C, one USB 3.2 Gen 2x2 Type-C, and a power button. Unlike the Mac mini, which has a frustrating power button placement, the power button in the NUC 14 Pro+ is located where it should be. The back of the NUC 14 Pro+ has a DC-in port, two Thunderbolt 4 ports, one 2.5G Ethernet port, one USB 3.2 Gen2 Type-A, one USB 2.0 Type-A, two HDMI 2.1, and a Kensington lock. Finally, there is a VESA mount, which lets you place the device on the back of your monitor for a cleaner desk. The computer is powered by Intel's 14th-gen Core Ultra 9 185H processor, 32GB of DDR5 memory, and a 1TB PCIe Gen4 NVMe SSD. Windows 11 Home is preinstalled, so you do not need to bring your own drive, memory, or Windows 11 license. ASUS NUC 14 Pro+ Core Ultra 9 185H, 32GB RAM, 1TB SSD - $799.99 | 27% off on Amazon US This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • This guy is just salty that Waymo is about to get buried by a company with cars that cost significantly less, charge significantly lower fares, and will soon dramatically outnumber their fleet. Waymo made the mistake of not reducing their vehicle cost quick enough and not overcoming their route limitations. Unless they start allowing their cars to use the freeways and have significantly wider geofencing, they're going to soon join the list of discontinued Google products. If Tesla wasn't the one to make them irrelevant, somebody else soon was. There's a long list of companies designing robotaxis right now.
    • LOL. Hard to believe people still fall for this. If you are having some sort of issue, I would work on fixing that instead turning off these settings.
  • Recent Achievements

    • Week One Done
      fredss earned a badge
      Week One Done
    • Dedicated
      fabioc earned a badge
      Dedicated
    • One Month Later
      GoForma earned a badge
      One Month Later
    • Week One Done
      GoForma earned a badge
      Week One Done
    • Week One Done
      ravenmanNE earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      651
    2. 2
      Michael Scrip
      226
    3. 3
      ATLien_0
      218
    4. 4
      +FloatingFatMan
      146
    5. 5
      Xenon
      137
  • Tell a friend

    Love Neowin? Tell a friend!