• 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

    • It's a Developer Beta 1 not even a Public or User Beta, I doubt any of the design is 100% finalized yet.
    • Was going to comment the same thing. It's SO annoying to have to go back to settings every time I install something new or some apps like discord update and the icon is hidden again.
    • XRECODE3 1.167 by Razvan Serea xrecode3 is a converter and audio-grabber which allows you to convert from mp3, mp2, wma, aiff, amr, ogg, flac, ape, cue, ac3, wv, mpc, mid, cue ,tta, tak, wav, wav(rf64), dts, m4a, m4b, mp4, ra, rm, aac, avi, mpg, vob, mkv, mka, flv, swf, mov, ofr, wmv, divx, m4v, spx, 3gp, 3g2, m2v, m4v, ts, m2ts, adts, shn, tak, xm, mod, s3m, it, mtm, umx, mlp to m4a, alac, ape, flac, mp3, mp4 (using NeroAAC), ogg, raw, wav, wav(rf64), wma, WavPack, mpc, mp2, Speex, ofr, ac3, aiff, tak, snd and Shorten formats. Command Line parameters are supported. XRECODE3 features: Works on XP, Vista, Windows 7, Windows 8, 10 32/64 bit versions and under Wine. Parallel conversion by utilizing power of multi-core CPUs. Support of embedded CUE sheets (for FLAC, WavPack, APE and TAK files). Support of mp4, mka chapters (can split mp4, mka by chapters to any supported format). Built-in Metadata editor with Cover Art support. Has support for LossyWav. Supports portable mode. Merge input files to one large audio file and create CUE sheet. Converting to many formats at once using "Multiple" output mode. Grabbing of multi-channel Audio CDs to the desired format at once. Informative and resizable UI suited even for netbooks. Extracting audio from flv, avi, mov etc. video files (multiple audio streams are supported). Can export/import Metadata to/from external file. Support for 24/32bit audio files. Multilanguage support. Currently program is available in Dutch, English, French, Japanese, Korean, Polish, Russian, Hungarian, Italian, Spanish, Spanish Traditional, Swedish, Brazilian Portuguese, German, Finnish, Bulgarian, Czech, Danish and Chinese (simplified) languages. What's new in XRECODE3: Native 64bit support. Added support for DSD/DST and DFF formats (including handling of SACD ISOs). Added option to extract audio without transcoding. Added option to encode several files to one multi-channel file. Added option to split file into individual track-per-channel for all available output formats. Added option to merge files per folder. Output and Metadata settings are now output format specific. Enhanced Metadata settings. Added support for multiple Cover pictures in Metadata editor. Added 32bit int/float output for formats which support them (e.g. WAV). Added dithering option in Output Settings. Added option to use EBUR128 in Normalize. Added option to Album Mode Normalize. Added option to configure Matrices under Output Settings. Added more output file pattern elements. Tabbed UI. CUE files are now displayed more nicely. Enhanced Shell Extension. XRECODE3 1.167 changelog: Added option to handle YEAR and DATE tags (under Settings/Metadata/Advanced). Updated to the latest qaac (2.85). Download: XRECODE3 v1.167 (64-bit) | Portable | ~30.0 MB (Shareware) Download: XRECODE3 v1.167 (32-bit) | Portable Link: XRECODE3 Homepage | XRECODE3 Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Hasleo Disk Clone 5.2.2.1 by Razvan Serea Hasleo Disk Clone is a free and all-in-one disk cloning software for Windows 11/10/8/7/Vista and Windows Server that can help you migrate Windows OS to another disk, clone one disk to another disk or clone one partition to another location quickly and efficiently. Completely Free Windows Migration and Disk/Partition Cloning Software Migrate Windows from one disk to another without reinstalling Windows, apps. Clone one disk to another and makes the data on 2 disks are exactly the same. Clone a partition to another location without losing any data. Easily adjust the size and location of the destination partition. Convert MBR to GPT or convert GPT to MBR by cloning. Creation of Windows PE emergency disk. Extremely fast cloning speed and multi-language support. Supported OS: Windows Vista/Server 2008 or later, fully compatible with GPT and UEFI. Note: Hasleo Disk Clone 5.2.2.1 changelog is not yet available. Download: Hasleo Disk Clone 5.2.2.1 | 28.8 MB (Freeware) Link: Hasleo Disk Clone Home Page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Shouldn't using "High Performance" mode prevent c-states from initializing? Maybe AMD hasn't built a proper power plan for their x3d processors like they used to for their ryzen chips.
  • Recent Achievements

    • First Post
      Johnny Mrkvička earned a badge
      First Post
    • Week One Done
      viraltui earned a badge
      Week One Done
    • One Month Later
      serfegyed earned a badge
      One Month Later
    • Dedicated
      firey earned a badge
      Dedicated
    • Dedicated
      fettermanj earned a badge
      Dedicated
  • Popular Contributors

    1. 1
      +primortal
      627
    2. 2
      ATLien_0
      226
    3. 3
      Michael Scrip
      217
    4. 4
      Xenon
      149
    5. 5
      Steven P.
      139
  • Tell a friend

    Love Neowin? Tell a friend!