• 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

    • Microsoft confirms Windows Outlook breaks in many ways after major Calendar feature upgrade by Sayan Sen Microsoft has been trying to get more users onto New Outlook for Windows, and it is doing so not just by enforcing the newer app but also by making improvements along the way. In doing so, though, the company has caused the Classic Outlook app to bug out in the past. The classic app received a major Shared Calendar-related upgrade recently, with many " long-awaited improvements" as well as "small changes in form and function." As the name suggests, the Outlook Shared Calendar essentially allows multiple people to interact with and manage the calendar. With Shared Calendar improvements enabled, users will see the following changes: Instant sync and view of shared calendars Editing series end date does not reset the past Accepting meeting without having to send a response Last Modified By no longer shown in the meeting item Adding same calendar multiple times can't be done Duplicate calendars simultaneously selection Attachments addition not possible when responding to a meeting invitation Event drafts auto-save changes The "Download shared folders" setting is ignored Unfortunately, as with any major feature upgrade, there are bugs, and Microsoft has confirmed this is no different. The tech giant has shared official guidance for it so that users can work around the problems. According to the company, "Shared Calendar improvements are now enabled by default in the most recent versions of Outlook, in all update channels for Microsoft 365 Apps," and thus, the bugs are likely to affect many. Here are some of the bugs Microsoft is investigating, as well as their workarounds: Bug Workaround Meeting cancellation sent unexpectedly to some attendees in classic Outlook In a REST shared calendar, after adding or removing an attendee, or forwarding a meeting, a meeting cancellation may be sent unexpectedly to some attendees. Use the Outlook Web App or new Outlook when adding or removing an attendee or forwarding a meeting. Attendees do not get updates on attachment changes by Delegate When a delegate sends an update on a meeting that requires removing an attachment on an occurrence of a meeting series, the recipients may not get some or all of the attachment changes. In the delegate's Sync Issues folder, you'll see sync errors. Example: 17:23:26 Synchronizer Version 16.0.15313 17:23:26 Synchronizing Mailbox 'Delegate User' 17:23:26 Synchronizing local changes in folder 'Manager User' 17:23:27 Uploading to server 'https://outlook.office365.com/mapi/emsmdb/?xxxxxxxx-xx' 17:23:30 Error synchronizing folder 17:23:30 [0-320] There is no known workaround. It is recommended, whenever possible, to save attachments to SharePoint or to OneDrive and share with a link. After an attachment is deleted from an existing meeting, it may reappear after being deleted Please wait approximately one minute to give the sync time to complete. Additionally, it is advisable to save attachments to SharePoint or OneDrive whenever possible and share them using a link. A meeting created by a delegate with limited calendar access disappears and is unsent when a sensitivity label other than "Normal" is selected Three potential solutions to address this issue, each with their own implications for functionality: Manager can update delegate's permissions to allow viewing of private items. Delegate can change the sensitivity label of the meeting to "Normal". Delegate can disable Shared Calendar Improvements (not recommended). Aside from these, Microsoft has also fixed several other bugs, which you can find in the official support article here on the company's website.
    • I’ve just paid £290/$390 for a 4TB Samsung 990 Pro for my PS5 Pro so it’s not too far from the going rate. Microsoft should definitely copy Sony and let users buy their own SSD in their next consoles rather than this proprietary stuff. I paid £374/$505 for the 2TB Seagate card for my Series X a few years ago so it’s not exactly over priced. 4TB of NVMe storage ain’t cheap!
    • The EU regulations force companies to respect users privacy, choice and data. Something all tech companies have abused to the hilt and would continue to do so if it wasn’t for important legislation and laws the EU brought in, which have been adopted elsewhere around the world. The EU can be a nuisance, but they actually do more good than harm. Forcing Apple, Google, Microsoft etc to make changes hasn’t negatively impacted anyone apart from their financials as they aren’t free to pillage our data like they once were, unless they explicitly provide options to obtain consent.
    • Windows 10 Enterprise IoT LTSC will continue getting updates until January 2032. I would expect support from most programs to continue until then. Firefox still supports Windows 7 (until the end of August), which will be just over 16 years since release. Windows 10 will be of a very similar age in January 2032. I'm sure some things like games will move on earlier, but I imagine a Windows 10 machine will be safe and usable for a long time to come yet, despite the pressure and fearmongering from those who stand to gain from selling you a new PC.
    • Refined dock and bug fixes land in latest Elementary OS 8 updates by David Uzondu If you're running Elementary OS 8, there's a new round of updates available, bringing some neat enhancements, particularly to its signature Dock and the underlying window manager, Gala. If you are not familiar, Elementary OS positions itself as a polished alternative to Windows and macOS. It runs its own custom desktop environment called Pantheon, with Gala handling all the window management magic, like animations and how windows behave. In the new update, the Dock gets some notable new tricks, including the return of a couple of features that old-school Plank (the Dock's foundation) users might remember. For starters, the Dock now shows multiple indicator dots beneath an app icon if you have more than one window open for that application, which is useful for quickly seeing what is running. Plus, if you are dragging something and hover over an app icon in the Dock, it will cycle through that app's open windows, making it easier to drop your item into the right place. You can also now long-press an app icon to bring up its context menu, a nice touch for those who prefer that interaction. The elementary OS team also squashed some bugs related to hide modes and memory usage, keeping things running smoothly. Gala itself recently got a massive update, addressing around 20 reported issues and introducing a brand new Gesture Controller. This means users can now swipe up in the Multitasking View to close windows, a slick and intuitive gesture. App titles are now always shown in Multitasking View, a significant improvement for touchscreen users. Users also get notified when they take a screenshot with a keyboard shortcut, and this notification lets them jump straight to the image in Files. Some other welcome Gala improvements include saving window states on sleep and shutdown, and fixing an annoying bug where menus might only show once. For gamers, a fix for Lutris Flatpak installations causing Gala to crash with GE Proton setups will be a relief, and users of the Postman app will be happy to know that window captures for it are no longer partially rendered. Shifting back to Elementary OS 8, in System Settings, choosing light or dark mode properly snoozes your schedule instead of outright disabling it. The Reduce Motion setting has been expanded to cover a wider array of animations, which is a blessing for folks prone to motion sickness. Hotcorners got some fixes too, and there is a new option to keep them active even when an application is full screen. Other notable updates include added screen reader support for notifications and the shortcut overlay, fixes for Flatpak sandbox issues that affected apps like Steam, and the latest version of GNOME Web, which brought better performance and a redesigned bookmarks sidebar. You can download all these updates by opening System Settings, heading to System, and hitting "Update All."
  • Recent Achievements

    • Enthusiast
      Epaminombas went up a rank
      Enthusiast
    • Posting Machine
      Fiza Ali earned a badge
      Posting Machine
    • One Year In
      WaynesWorld earned a badge
      One Year In
    • First Post
      chriskinney317 earned a badge
      First Post
    • Week One Done
      Nullun earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      186
    2. 2
      snowy owl
      131
    3. 3
      ATLien_0
      129
    4. 4
      Xenon
      121
    5. 5
      +Edouard
      91
  • Tell a friend

    Love Neowin? Tell a friend!