Preforming a binary(SQL) search on a datagrid.
 

Hey, all. I have another problem that we're running into with our project.

We use data grids to handle the viewing of the main tables in our program, those tables being Employees, Invoices(Masters + Items), and Purchase Orders(Masters + items), and we're setting up a search function for them. We have one working. The only problem is that it's a serial search, running through the whole data grid to find what it's looking for. That's fine for small tables, but in a search like that, it becomes impractical when there are thousands of records like there would potentially be in an A/P system. There would be tremendous system slowdown, and I'm sure I'm not the only one who sees it as bad practice.

So, I'm wanting to preform a binary search, and I talked it over with my teacher. He said that I need to find a way to move the record pointer in the data grid without refreshing the data in the data grid, as the only thing I can think that's close to a binary search is preforming select queries based on the search parameters and eliminating all irrelevant data. I don't know if that's called anything different, but it's really the only way I know, and my teacher shot that down.

I guess what I'm asking is, is there any way to change the record pointer location of a data grid based on a SQL Select query, or is there a special SQL function for binary searches?

Any help is much appreciated.
Thanks. =]

Re: Preforming a binary(SQL) search on a datagrid.
 

Will the parameter that's being searched for either be an exact match or not?

If it is a 'match or no match' situation, can you not perform a MySQL SELECT statement that searches for that particular row? Then retrieve the row number of that record.

Using the row number you can do whatever you need to your data grid, since the row number should correspond to the same row in your graphical interface's table. I'm assuming your data is being represented in the same order and size as your MySQL table. Or am I missing something?

Re: Preforming a binary(SQL) search on a datagrid.
 

It's going to be a dynamic search, in that it will change the results every time the key is pressed. The where clause is going to be a "LIKE", and not an "=" if that's what you mean.

I'm thinking of doing a Select statement, having that return a row, and then using that row to change the bindingcontext.location property of the form. I just don't know what I would reference to do that. I don't think I'd use the PK of the returned row to find it and then change the location... That seems extremely round-about. But is that how it would be done?

Also, if it helps any, we're using MSSQL.

Re: Preforming a binary(SQL) search on a datagrid.
 

Same thing. The latter is more "round-about" if you want to look at it that way, but retrieving the row number of a query is still the exact same amount of work done by the application as retrieving the pk column in a query. So whichever you find easier from a coding standpoint...

So after you've done that, you then know the row number that matches the search query. Just reference that row number in the grid data and change its appearance. You can figure out how to do this by looking at the documentation for the GUI control you're using. This object will have methods that you can call, one of which should be able to modify the apperance of row #X. These methods will be listed along with their usage in their respective documentation.

Re: Preforming a binary(SQL) search on a datagrid.
 

I think we may have just found it.

When you assign a datasource to a data grid in .Net, it uses something called BindingContext to handle all interfacing with that datasource.

So, after speaking with a co-developer who is also on the project, and looking more in depth at how the search function works, we found that we could bind the Search textbox to the same Datasource as the data grid. By doing this, every time something is changed in the text box, it will change the location of the record pointer in the datasource, and the data grid will act accordingly to that change.

We are testing it now. If it works, I will paste the code for anyone who has a similar problem, complete with documentation.