• 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

    • Well that should last us until Sunday, December 4, 292277026596 at 15:30:08.
    • Windows 11 build 27909 is out with fixes for battery indicator and more by Taras Buria This week's Friday Windows 11 preview build comes from the Canary Channel with a few fixes here and there. No new features or noticeable changes in build 27909, so Windows 11 Insiders get to test several fixes and general improvements. Here is the changelog: [General] This update includes a small set of general improvements and fixes that improve the overall experience for Insiders running this build on their PCs. [Administrator Protection] Fixed an underlying issue where the Xbox app wouldn’t launch when administrator protection was enabled. This may have also impacted other apps too, showing error 0xC0000142 or 0xC0000045. [Settings] Fixed an issue where the battery percentage was unexpectedly missing from the top of System > Power & Battery in the last few builds. [Remote desktop] Fixed an issue causing extreme graphical distortion and rendering issues using remote desktop on Arm64 PCs in the last couple builds. [Other] Fixed a high hitting pcasvc.dll crash in the previous build. Fixed an underlying issue where if you disconnected the device you were casting to from outside of Media Player, Media Player would still show an option to disconnect from the device. Fixed an issue which was causing Remote Credential Guard scenarios between the latest Windows 11 builds and Server 2022 (and below) to fail. The list of known issues includes the following: [General] [IMPORTANT NOTE FOR COPILOT+ PCs] If you are joining the Canary Channel on a new Copilot+ PC from the Dev Channel, Release Preview Channel or retail, you will lose Windows Hello pin and biometrics to sign into your PC with error 0xd0000225 and error message “Something went wrong, and your PIN isn’t available”. You should be able to re-create your PIN by clicking “Set up my PIN”. There’s an issue starting with the latest builds causing a small number of Insiders to experience repeated bugchecks with KERNEL_SECURITY_CHECK_FAILURE after upgrading.This may occur when connecting to VPN. This Canary Channel flight comes with a delightful blast from the past and will play the Windows Vista boot sound instead of the Windows 11 boot sound. The fix should be coming in a future Canary Channel flight soon. [Settings] We’re investigating an issue in this build which could cause Settings to crash when interacting with the options under Settings > System > Power & Battery. We’re investigating an issue where some of the apostrophes across text in Settings and settings-related dialogs are not displaying correctly and are showing random characters. You can find the announcement post here.
    • And they didn't use Clippy for that? What a waste
    • Become a PDF Expert on your Mac — One-time purchase now at 42% off by Steven Parker Today's highlighted deal comes via our Apps + Software section of the Neowin Deals store, where for only a limited time you can save 42% on PDF Expert One-Time Purchase. PDFs remain the best way to transmit documents, but editing them isn't possible with standard Mac software. PDF Expert changes that, allowing you to edit PDF text, images, links, and outlines quickly and easily. Typo in a contract? Easy fix. Need to rework a complete section of a document? No problem. PDF Expert provides a series of essential functions that will transform the way you work with documents on your Mac. It recognizes text and OCR, makes edits, and fills out forms. And with the “Enhance” feature powered by AI, it will fix distortions, remove shadows and improve contrast so that even difficult-to-read documents look great. EDIT Change the text. Easily fix typos, update numbers, or add entire paragraphs Insert images. Update logos in a contract or add a new graph to a report Add links. Enrich your PDFs by linking to other pages or external websites ANNOTATE Highlight the important. Make the most valuable content stand out at a glance Comment on PDFs. Add text to PDFs, insert pop-up notes & write your thoughts in the margins Add stamps. Review documents with our set of stamps or create custom stamps for any workflow ORGANIZE Merge PDFs. Combine multiple files into one PDF document Manage pages. Add, delete, rearrange, or rotate PDF pages with ease Split PDFs. Extract pages from PDFs & save them as separate files CONVERT Convert to PDF. Turn JPG, PNG, Word, PPT, and Excel to PDF PDF to Word. Convert PDFs into editable Word documents PDF to image. Turn PDFs into JPG or PNG images PDF to Excel. Convert PDFs into Excel spreadsheets PDF to PPT. Save PDFs as PowerPoint presentations PDF to text. Convert PDFs into editable TXT files FILL OUT Fill out PDF forms. Easily fill out PDF forms by just clicking on them Sign documents. Add your signature to a PDF in a few clicks. Let customers sign documents with handy one-time signatures Redact PDFs. Blackout or erase confidential information from your documents RECOGNIZE TEXT OCR text in PDF. Recognize the text, so you can search, highlight & copy it Enhance scans. Fix distortions, remove shadows & improve contrast Crop & split pages. Split double-page scans into separate pages & remove undesired margins Good to know: Length of access: Lifetime Redemption deadline: redeem your code within 30 days of purchase Access options: Mac Max number of device(s): Unlimited usage on personal macOS devices Version: PDF Expert 3 for Mac (macOS) Updates: Get continuous support and bug fixes. Additional new features may come at an extra cost. PDF Expert One-Time Purchase normally costs $139.99, but you can pick it up for just $79.97 for a limited time, that represents a saving of $60 (42% off). For a full description, specs, and license info, click the link below. Get PDF Expert for just $79.97, or learn more Although priced in U.S. dollars, this deal is available for digital purchase worldwide. We post these because we earn commission on each sale so as not to rely solely on advertising, which many of our readers block. It all helps toward paying staff reporters, servers and hosting costs. Other ways to support Neowin Whitelist Neowin by not blocking our ads Create a free member account to see fewer ads Make a donation to support our day to day running costs Subscribe to Neowin - for $14 a year, or $28 a year for an ad-free experience Disclosure: Neowin benefits from revenue of each sale made through our branded deals site powered by StackCommerce.
    • It all went down hill after he retired from the ring, nothing really to do with him being a trumpster (as are most wrestlers). Hulk in general was a bad person after his retirement.
  • Recent Achievements

    • Week One Done
      CyberCeps666 earned a badge
      Week One Done
    • Very Popular
      d4l3d earned a badge
      Very Popular
    • Dedicated
      Stephen Leibowitz earned a badge
      Dedicated
    • Dedicated
      Snake Doc earned a badge
      Dedicated
    • One Month Later
      Philsl earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      629
    2. 2
      ATLien_0
      240
    3. 3
      Xenon
      163
    4. 4
      neufuse
      124
    5. 5
      +FloatingFatMan
      124
  • Tell a friend

    Love Neowin? Tell a friend!