• 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

    • I always disable animations in Windows using sysdm.cpl and yes, it feels faster. I have also reduced animations in my android phone using dev options.
    • Neowin's servers (hosted in the UK).
    • MacOS isn't Linux, but Proton is modified WINE, and WINE runs on MacOS. They would just need to add something like Box86 or Rosetta support.
    • PicPick 7.4.0 by Razvan Serea PicPick is user friendly and full of features for creating your image, suitable for software developers, graphic designers and the home user. It is an all-in-one program that provides a full-featured screen capture tool, intuitive image editor, color picker, color palette, pixel ruler, protractor, crosshair and even whiteboard. It not only has everything that you need, but it loads fast, and sits quietly in the system tray until needed. This software is provided as freeware for personal use only. In this case, you are granted the right to use this program free of charge. Otherwise, you need to pay for a license for commercial use. PicPick key features: For All Windows (Fully support Windows 11, 10, 8.1, 8, 7, Vista and XP both 32-bit and 64-bit) Multi-language is supported. (MORE 28+) All functions are fully supported on a dual screen environment. No Registry, No access to System folder (you can copy these files to portable USB) Screen Capture Auto-scroll, dual monitors and sound effect are supported Various output to File, Printer, Office programs, External program Sharing to FTP, Web, E-mail, Facebook and Twitter are supported as well Full Screen Active Window Window Control Scrolling Window Region, Fixed Region FreeHand Repeat Last Capture Image Editor Intuitive User Interface Windows Ribbon style Standard drawing, shapes, arrows, lines, text, and etc. Blur, sharpen, hue, contrast, brightness, pixelate, rotate, flip, frame effect and etc. Color Picker and Color Palette various color code type (RGB, HTML, C++, Delphi) Photoshop style RGB/HSV conversion is supported. Pick and Save your favorite color! Screen Pixel Ruler Horizontal and vertical orientation various units (Pixels, Inches, Centimeters) DPI setting (72, 96, 120, 300) colorful gradient skins You don't have to install any other screen ruler softwares. Screen Magnifier Zoom 2x to 10x option Stay on top, smooth display, and sizeable window Screen Protractor Have you seen any screen protractor function in other software? Screen Crosshair For aligning objects in graphics or design applications For calculating relative coordinates on screen Some prefer to use this tool than a pixel-ruler. Whiteboard For giving a presentation or just drawing something on screen PicPick 7.4.0 changelog: Added support for saving in WebP file format Added horizontal scrolling in the Image Editor with Shift and mouse wheel Fixed control capture failing to detect specific windows Fixed focus loss during delayed active-window captures. Download: PicPick 7.4.0 | 74.9 MB (Free for personal use only) Download: Portable PicPick 7.4.0 | 73.3 MB View: PicPick Home page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Stellarium 25.2 by Razvan Serea Stellarium is a free open source planetarium for your computer. It shows a realistic sky in 3D, just like what you see with the naked eye, binoculars or a telescope. It is being used in planetarium projectors. Just set your coordinates and go. Stellarium key features: Realistic simulation of the sky, sunrise and sunset Default catalogue of over 600,000 stars Downloadable additional catalogues for up to 210 million stars Catalog data for all New General Catalogue (NGC) objects Images of almost all Messier objects and the Milky Way Artistic illustrations for all 88 modern constellations More than a dozen different cultures with their constellations Solar and lunar eclipse simulation Photorealistic landscapes (more are available on the website) Scripting support with ECMAScript (a few demo scripts are included) Extendable with plug-ins: 8 plug-ins installed by default, including: artificial satellites plug-in (updated from an on-line TLE database) ocular simulation plug-in (shows how objects look like in a given ocular) Solar System editor plug-in (imports comet and asteroid data from the MPC) telescope control plug-in (Meade LX200 and Celestron NexStar compatible) The major changes of this version: Many changes in support of sky cultures Added 2 new plugins: Mosaic Camera and Custom Nebula Textures Many improvements in Core Updates in sky cultures (include new one) [full release notes] Download: Stellarium 25.2 (64-bit) | 361.0 MB (Open Source) View: Stellarium Home Page | Other Operating Systems | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • 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
      649
    2. 2
      Michael Scrip
      224
    3. 3
      ATLien_0
      222
    4. 4
      Xenon
      146
    5. 5
      +FloatingFatMan
      143
  • Tell a friend

    Love Neowin? Tell a friend!