• 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

    • LAV Filters 0.80.0 by Razvan Serea LAVFSplitter is a multi-format media splitter that uses libavformat (the demuxing library from ffmpeg) to demux all sorts of media files. LAV Splitter is a Souce Filter/Splitter required to demux the files into their separate elementary streams. LAV Audio and Video Decoder are powerful decoders with a focus on quality and performance, without any compromises. Supported Formats: MKV/WebM, AVI, MP4/MOV, MPEG-TS/PS (including basic EVO support), FLV, OGG, and many more that are supported by ffmpeg! LAV Filters are based on ffmpeg and libbluray and is aimed to offer a all-around solution to perfect playback of file-based Media as well as Blu-rays. LAV Filters 0.80.0 changelog: LAV Splitter NEW: Introduced the IURLSourceFilterLAV interface to allow opening URLs with custom user agent and referrer NEW: Added support for WebP images Changed: Increased the length of the advanced subtitle selection field, so its no longer cut off after 255 characters Changed: Improved buffering behavior on badly interleaved video files Fixed: Audio streams with an unknown/unsupported codec are no longer selected for playback, as long as others are present Fixed: Improved accuracy of reported FPS from AviSynth scripts LAV Video NEW: D3D11 support for HEVC 4:2:2 and 4:4:4 hardware decoding NEW: Dolby Vision extension metadata is exported for renderers to use Changed: Added additional media types to support more video streams Changed: Updated dav1d for significant AV1 decoding improvements Fixed: Improved handling of H.264 4:4:4 files encoded by certain versions of x264 Fixed: VP9 DXVA2/D3D11 decoding could result in artifacts on some clips Fixed: Decoding ProRes reports more accurate color details LAV Audio Changed: Added support for additional ADPCM audio codecs Download: LAV Filters 0.80.0 | 15.5 MB (Open Source) View: LAV Filters Website | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Haven't really used it much for years, not specific to Windows 11.. it's there but I mostly ignore it. I do install StartAllBack though bust mostly for the other bits that it brings. I prefer the "alt-space" type launchers (KRunner in Plasma, Flow in Windows or the like), a lot less clutter and more smarts like bookmark/history searches and other useful plugins.
    • Calibre 8.5 by Razvan Serea  Calibre is an open source e-book library management application that enables you to manage your e-book collection, convert e-books between different formats, synchronize with popular e-book reader devices, and read your e-books with the included viewer. It acts as an e-library and also allows for format conversion, news feeds to e-book conversion, as well as e-book reader sync features and an integrated e-book viewer. Calibre's features include: library management; format conversion (all major ebook formats); syncing to e-book reader devices; fetching news from the Web and converting it into ebook form; viewing many different e-book formats, giving you access to your book collection over the internet using just a browser. Calibre 8.5 changelog: New features The scrollbars used in calibre in light mode are now the same style as the ones in dark mode, this improves the contrast making the scrollbar more accessible Kobo driver: add an option to change the how the Kobo displays series numbers using a template. Manage data files dialog: Add a button to cancel remaining books when managing multiple books Kobo driver: add support for new Tolino firmware Bug fixes Prevent Windows 11 from starting a conhost.exe process for every calibre worker process E-book viewer: Improve highlight grouping with recurring chapter names When sending emails to amazon and pocketbook use random English text instead of UUIDs for subject/body. Improved news sources NYTimes WSJ Financial Times Eenadu Fokus.se Business standard Go comics NZ Herald TLS Magazine Download: Calibre 8.5 | Portable | ~200.0 MB (Open Source) Download: Calibre for MacOS | 316.0 MB Download: Calibre for Linux View: Calibre Home Page | Calibre Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Or, it is Apple simply overinflated the screens.
  • Recent Achievements

    • Week One Done
      Crunchy6 earned a badge
      Week One Done
    • One Month Later
      KynanSEIT earned a badge
      One Month Later
    • One Month Later
      gowtham07 earned a badge
      One Month Later
    • Collaborator
      lethalman went up a rank
      Collaborator
    • Week One Done
      Wayne Robinson earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      678
    2. 2
      ATLien_0
      277
    3. 3
      Michael Scrip
      221
    4. 4
      +FloatingFatMan
      169
    5. 5
      Steven P.
      162
  • Tell a friend

    Love Neowin? Tell a friend!