• 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

    • But ads are the main source of revenue only for Google. Otherwise, why would mobile Edge have a built-in AdBlock?
    • That makes no sense. Of course the average user knows that Edge exists, because it's built into Windows as standard. Hence the normie joke about it just being a downloader for Chrome. People use Chrome because Google have spent the past 15+ years aggressively ramming it down people's throats through massive advertising campaigns, both on their own services and beyond. It's become just the standard internet browser and the average person doesn't care enough about the minutiae of browser differences to switch. Mozilla and Firefox simply cannot compete with Google and Microsoft when it comes to getting Firefox's name out there. They don't have a platform like Google's network of websites or Windows to constantly push adoption. The idea that people pick a browser based on milliseconds of page loading time that's imperceptible outside of a benchmark is nonsense.
    • Microsoft Weekly: useful PowerToys modules, Microsoft Store updates, and video gen in Bing by Taras Buria This week's news recap is here, bringing you the latest stories from the Microsoft world, including useful PowerToys modules, fresh Windows 11 preview builds, AI video generation in Bing, Office updates, gaming news, and more. Quick links: Windows 10 and 11 Windows Insider Program Updates are available Gaming news Great deals to check 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. This week's Windows 11 section kicks off with some stats. StatCounter published its monthly report, showing that Windows 11 slowed its climb a little bit in May 2025. On the gaming side, however, things are much better, with Windows 11 occupying the majority of PCs on Steam. Now, here are some Windows updates you might have missed. Windows 11 versions 23H2 and 22H2 received KB5062170, a small emergency patch that resolved errors when installing recent updates. The patch is available only through the Microsoft Update Catalog, and the company recommends installing it only if your system experiences the 0xc0000098 code when installing the May 2025 security update. Finally, Microsoft released a new Defender update for Windows 11 and 10 installations, fresh recovery updates, and a script for recovering the inetpub folder, which showed up unannounced on systems in April. As Windows 10 is getting closer to the end of support, more companies are urging users to switch. AMD, Dell, and ASUS all urge users to prepare for the "mandatory Windows 11 upgrade," while other companies shamelessly poach Windows 10 users, luring them to Linux. To finish this week's Windows section, here is an ancient CD-burning app that made a surprising 64-bit comeback and now works on modern operating systems, including Windows 11. Windows Insider Program Here is what Microsoft released for Windows Insiders this week: Builds Canary Channel Build 27871 This week's Canary build introduced Start menu improvements (more Phone Link features), small taskbar tweaks, and a long list of various fixes to improve different parts of the operating system. Dev Channel Build 26200.5622 (KB5058512) This build brought new Click to Do features, a dedicated Settings section for Quick Machine recovery, improved Windows Widgets, a new spec card for the Settings app, and a few fixes here and there. Build 26200.5622 also contains a new "Your Device Info" card on the Settings Home page, which makes it easier to find your computer's specs with fewer clicks. Beta Channel Build 26120.4230 (KB5058506) This one is almost identical to build 26200.5622 from the Dev Channel. Release Preview Channel Nothing in the Release Preview Channel this week Besides new builds, Microsoft announced a new update for the Windows Photos app, which is now available to Windows Insiders in all channels. The update introduces AI-powered light controls (Relight), which let you place and control up to three light sources on your photo, and AI-powered search with natural language support. 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 announced a batch of new features for the Microsoft Store. The app is getting an improved Home page with personalized recommendations based on your recent activities, region, and deals. Search now considers additional information when ranking apps in the results, and a Copilot button lets you ask AI about a certain app. Microsoft also brags about significant performance improvements under the hood. This week, we had plenty of various Office updates. Microsoft 365, for one, is getting significant changes to its update channels beginning July 2025. Rollback support will be expanded to two months, the Semi-Annual Enterprise Channel (Preview) is being deprecated, and the Semi-Annual Enterprise Channel will be supported for eight months instead of the current 14. Microsoft also announced the general availability of the new Message Trace in the Exchange Admin Center in Exchange Online, some big updates for the new Outlook for Windows in the June 2025 update, and acknowledged a few issues with Outlook after a recent Calendar feature upgrade. Teams is also getting a "major" change for third-party app settings, and Word is getting SharePoint eSignature support. Bing received a surprising update this week. OpenAI's Sora video generator is now available for free in Bing Video Creator. Now, you do not have to pay for an OpenAI subscription to generate short videos using AI. Way to boost Bing stats, Microsoft! PowerToys Run, a useful and convenient launcher for Windows 10 and 11, recently received three new third-party modules that let you test your internet speed, download videos from hundreds of websites, and check out word definitions, usage, synonyms, and more. Microsoft announced some long-requested changes for Microsoft Edge, but only for those living in the EEA region. Windows will no longer annoy you with setting Edge as your default browser, and Windows Widgets will respect your default browser. Also, Microsoft will let you uninstall the Microsoft Store app, and Windows Search will be able to use other search providers. Speaking of browsers, Microsoft published a blog post that explained why Edge is a faster and smarter alternative to Chrome. If you are picking between the two, the article might help you make the choice (Google has an answer to that with its own article explaining that Chrome is now faster than ever). Also, the company released Edge 138 in the Beta Channel, bringing some important changes and new features, such as a new (sort of new) media control center, AI-powered history search, and more. Here are other updates and releases you may find interesting: Microsoft expanded LinkedIn's CEO role to manage Office apps. Microsoft announced the general availability of two new reasoning AI agents: Research and Analyst. Microsoft and Crowdstrike announced a partnership on threat actor naming. Microsoft will invest $400 million in Switzerland to bolster cloud and AI infrastructure. The annual Build conference is moving away from Seattle. Here are the latest drivers and firmware updates released this week: Intel 32.0.101.6876 non-WHQL with support for four new games and a single fix for intermittent display artifacts. Nvidia 576.66 Hotfix with patches for FC 25 crashes, video bugs in browsers, and more. In addition to that, Nvidia released a new version of the Nvidia App, which introduced a light theme (and automatic theme switching), support for more games, and some bug fixes. AMD Software Pro Edition 25 Q2 with support for Windows Server 2025, new Ryzen processors, and a few fixes. AMD Radeon Software 25.6.1 with the RX 9060 XT support and FSR 4 support for more games. On the gaming side Learn about upcoming game releases, Xbox rumors, new hardware, software updates, freebies, deals, discounts, and more. Hello Games continues relentlessly improving No Man's Sky. The game's latest update, "Beacon," was announced this week. It offers space explorers overhauled settlements, player overseer duties, and much more. The update is now available on all supported platforms, including Nintendo Switch 2. The Witcher 4 from CD Projekt RED might be a few years away. Still, at the State of Unreal 2025 keynote, the developers revealed a tech demo showcasing the capabilities of Unreal Engine 5 on the base PlayStation 5, which managed to pull it off at a solid 60 FPS. Nvidia announced new games that are now available in the GeForce NOW cloud streaming service (you have to own them to play them). The latest drop is a massive one: 25 new games, including FBC: Firebreak, Dune: Awakening, 7 Days to Die, DREADZONE, and more. Game Pass is also getting new games, and the first drop in June is also a pretty big one. You will soon get access to Kingdom: Two Crowns, EA Sports FC 25, FBC: Firebreak, Crash Bandicoot 4: It's About Time, The Alters, and more. Some games are leaving the service, so check out the full list here. Xbox Games Showcase 2025 is happening today. With the show kicking off in just a few hours, check out our recap of what to expect at the show and how to watch it. On the hardware side, we have a new Xbox Storage Expansion Card from Seagate. At a whopping $429.99 price tag, the new card offers an immense amount of space for your games, doubling that of the previously biggest expansion card. Now, you can get an Xbox Storage Expansion Card with 4TB. By the way, it costs as much as the 1TB Xbox Series S. Deals and freebies If you are looking for some new games at lower prices, check out this week's Weekend PC Game Deals, which covers multiple specials and discounts, including some freebies, such as Deathloop from the Epic Games Store. Other gaming news includes the following: Ubisoft is skipping its Forward game showcase for the first time since 2020. Valve released a new beta version of Steam for Linux to address sluggish update installations. Elden Ring Nighteign received its first update with reduced difficulty for solo runs. The Expanse TV show is getting a narrative-driven sci-fi action RPG. Black Myth: Wukong is coming to Xbox in August. Atomic Heart is getting a sequel and an MMO RPG spin-off. Great deals to check Every week, we cover many deals on different hardware and software. The following discounts are still available, so check them out. You might find something you want or need. Crucial X10 8TB Portable SSD - $439.99 | 44% off Apple 2025 MacBook Air 13-inch Laptop with M4 chip - $849 | 15% off 4TB WD_BLACK SN7100 PCIe Gen4 Solid-State Drive - $249.99 | 16% off SAMSUNG Q-Series Soundbar HW-Q900F - $997.99 | 29% off KEF Q Concerto Meta Three-Way Bookshelf Speaker - $1,199.99 | 14% off 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.
    • No, they aren't because they don't do with that online advertising, they sell with that "spots" in their store. Apple is not an online advertising company. The business model of Microsoft ads is exactly the same with Google ads. They both are ad companies which do online advertising and make though their sites user profiling. Just because Microsoft never managed to be as successful as Google in that business that doesn't mean they are not exactly what Google is. An online advertising company. Both Google, Microsoft and Amazon are online advertising companies. Apple isn't.
  • Recent Achievements

    • Week One Done
      LunaFerret earned a badge
      Week One Done
    • Week One Done
      Ricky Chan earned a badge
      Week One Done
    • Week One Done
      maimutza earned a badge
      Week One Done
    • Week One Done
      abortretryfail earned a badge
      Week One Done
    • First Post
      Mr bot earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      483
    2. 2
      +FloatingFatMan
      262
    3. 3
      snowy owl
      240
    4. 4
      ATLien_0
      227
    5. 5
      Edouard
      185
  • Tell a friend

    Love Neowin? Tell a friend!