• 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

    • 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.
    • Vivetool also has a GUI. Literally took me three clicks to enable this from there.
    • Microsoft Weekly: OneDrive horror stories, ramblings about Start menu, and more by Taras Buria This week's news recap is here, delivering you a roundup of the most important Microsoft stories, including a bunch of odd stuff and bugs in Windows, OneDrive horror stories, ramblings about the Start menu, a couple of new Windows 11 preview builds, important news from AMD, and a lot more. Quick links: Windows 10 and 11 Windows Insider Program Updates are available Reviews are in Gaming news Windows 11 and Windows 10 Here, we talk about everything happening around Microsoft's latest operating system in the Stable channel and preview builds: new features, removed features, controversies, bugs, interesting findings, and more. And, of course, you may find a word or two about older versions. Microsoft released a new out-of-band update to fix boot issues on certain Surface devices. The company announced certain Windows 365 updates, such as VBS and HVCI support (by default) and app provisioning in Windows 365 instead of entire cloud PCs. Microsoft is also removing legacy drivers from Windows Update in a new "strategic move." This is the default Windows 365 wallpaper On the negative side, we have the latest Patch Tuesday updates breaking the DHCP Server in all Windows Server editions. Also, there is some bad news for PC users with Windows Hello cameras: after the April 2025 Patch Tuesday updates, Windows Hello does not work in the dark. The change was quietly introduced to address security issues. Windows Goodbye That is not all, though. As it turned out, solid-state drives from WD could still block your computer from installing Windows 11 version 24H2, which was released in October 2024. Also, Microsoft's Family Safety feature is now blocking Chrome, for some reason. Here is an editorial from Usama Jawad (welcome back) about how, four years after the initial release, Windows 11 still does not offer strong enough reasons to upgrade from the outgoing Windows 10. Also, Usama shared his thoughts about the Start menu and why he had stopped caring about its changes altogether. Speaking of the Start menu, check out our overview of what users wanted from Microsoft and what the company delivered in the redesigned Start menu, which was recently announced. Windows Insider Program Here is what Microsoft released for Windows Insiders this week: Builds Canary Channel Build 27881 This week's Canary build introduced context menu improvements, new accessibility features, Settings app tweaks, and more. Dev Channel Nothing in the Dev Channel this week Beta Channel Nothing in the Beta Channel this week Release Preview Channel Build 26100.4482 (KB5060829) This build improves File Explorer and search performance, adds some changes to default browser settings, and fixes multiple bugs. Some hidden stuff in the recent Windows 11 preview builds includes a new adaptive battery saver. This feature dynamically adapts battery saver mode according to your workflow, but in its current form, it is not fully operational. Even though Microsoft acknowledged its existence, the adaptive part still needs improvements. Another useful change in the recent builds is the return of a clock in the notification center. This time, however, Microsoft makes it more customizable, and you can toggle it on or off. Also, the company is moving more Control Panel bits to the Settings app and adding a rather unexpected customization feature that will let you select where system indicators (flyouts and sliders) appear on the screen. Microsoft started rolling out a new update for the Snipping Tool app. The latest release lets you save screen recordings as GIFs. Shortly after that, we posted a guide with a bit more detail about the feature. Updates are available This section covers software, firmware, and other notable updates (released and coming soon) from Microsoft and third parties, delivering new features, security fixes, improvements, patches, and more. Microsoft is working on a Dashboard redesign for PowerToys. Developers published an early look at what is coming to the app in future updates, revealing a better-organized page with quick links, a shortcut overview, and a list of available modules. This week's Office updates are rather mixed. OneDrive, for one, is having problems finding files. Microsoft acknowledged the issue, which affects users on Windows, iOS, Android, and the web. Unfortunately, that is not the only negative story about OneDrive. A new report from a frustrated user revealed a scary tale of Microsoft locking them out of an account full of invaluable content. Outlook also has some issues, this time, with opening emails, and Microsoft 365 will soon disable outdated protocols for file access. Finally, Copilot in Excel received a major update for context awareness, which made the assistant more useful when answering questions about data. This week's browser updates include several releases. Firefox announced a new method for pinning and unpinning tabs. It is now available for testing in the Nightly channel. Microsoft Edge was updated with fixes for two security vulnerabilities (high severity) originating from Chromium. Finally, here is this week's Microsoft 365 Roadmap with an overview of all the new stuff that Microsoft added to the website. Here are other updates and releases you may find interesting: Microsoft 365 security in the spotlight after Washington Post hack. Microsoft expands European sovereign cloud offerings with new data and key controls. Microsoft Defender XDR received TITAN-powered Security Copilot recommendations. Microsoft reportedly plans more layoffs. Watchdog found Microsoft guilty of confusing advertising when it comes to Copilot. Here are the latest drivers and firmware updates released this week: AMD released a new chipset driver for Ryzen processors under version 7.06.02.123, which followed a security update for TPM-Pluton. Nvidia 576.80 WHQL with fixes for the RTX 5090 FE, new game support, and a long list of fixes. You can get some extra performance on certain AMD Ryzen chips with a simple system tweak. Surface Pro 11 and Surface Laptop 7 received big firmware updates with multiple fixes and improvements. Reviews are in Here is the hardware and software we reviewed this week This week, Steven Parker reviewed the TerraMaster D4 SSD, a palm-sized DAS with up to 32TB of storage that you can connect over USB4. This thing is rather impressive, and for a modest price tag, it delivers a tiny footprint, great looks, full RAID support in TOS 6, quick connection, and more. On the gaming side Learn about upcoming game releases, Xbox rumors, new hardware, software updates, freebies, deals, discounts, and more. AMD and Microsoft announced some big news this week. The two companies revealed a new multi-year partnership, which secures AMD as the future maker of chips for Xbox consoles and other hardware. Sarah Bond announced the partnership in a new video on the official Xbox media channels. Turn 10 Studios announced a new Forza Motorsport update. Update 21 brings IndyCar content, Career mode expansion, Featured Tours, new reward cars, and more. It is now available on Xbox and PC via the Microsoft Store and Steam. Minecraft is another Microsoft-owned game that received a big update this week. The long-anticipated graphics overhaul is finally here with directional lighting, volumetric fog, improved shadows, reflections, godrays, and a lot more. In addition, Mojang released Chase the Skies, the latest content drop, which adds happy ghasts, new music disks, a locator bar for players, environmental fog in the overworld, new background music, and all sorts of small gameplay changes. Microsoft announced new games for Game Pass. The latest additions include FBC: Firebreak, Crash Bandicoot 4: It's About Time, Start Trucker, Wildfrost, Rematch, Call of Duty: WWII, Rise of the Tomb Raider, and more. As usual, some games are leaving the subscription. Valve released a big update for the Steam overlay. The latest version introduced major upgrades to CPU and VRAM usage, temperatures, and other important metrics that you might want to track when playing games on your gaming rig. Deals and freebies Also, be sure to check out this week's Weekend PC Game Deals article, which features rhythm bundles, fishing festivals, DRM-free summer sales, and more. Other gaming news includes the following: Take-Two confirmed Borderlands 4 will not cost $80 for the base game. The Coalition expanded the Gears of War: Reloaded beta after its rocky start. Ara: History Untold 1.4 update delivered overhauls to AI, map generation, combat, and more. Star Citizen Alpha 4.2 update lands with radiation hazards, dynamic rain, and more. This link will take you to other issues of the Microsoft Weekly series. You can also support Neowin by registering a free member account or subscribing for extra member benefits, along with an ad-free tier option. Microsoft Weekly image background by steve_a_johnson on Pixabay
  • 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
      634
    2. 2
      ATLien_0
      229
    3. 3
      Michael Scrip
      218
    4. 4
      Xenon
      149
    5. 5
      Steven P.
      140
  • Tell a friend

    Love Neowin? Tell a friend!