Binding with ObjectDataSource

It seems like every time I write a page that retrieves data, I end up writing the mega search page. It takes a lot of extra code to search by several criteria including date ranges and sort types. You've probably seen these before. Something like the following image:




Now, one way of handling this might be to write a case statement in the search event and write the sql query dynamically for each search type. That's one way to drive yourself insane. Why? Because your query will be strung out across 100 lines of code mangled in a mess of case statements and code logic. When it comes time to update the query its not going to be fun.

One thing that worked well for me is binding the GridView to an ObjectDataSource. I wrote a simple base class for my queries with a generic function for running a query.
Example:

public virtual DataTable RunQueryRange(string criteria,string dtstart,string dtend)
{

return mDT;
}


For each query type that you have, create a new class that inherits from your base class. Override the RunQueryRange function and place your query in there. Just a nice clean query that might pass that string off to a function in your base class that runs a SqlCommand and returns a dataTable. That way you're not writing the Connection code everytime. Once you're rolling it takes you about 30 seconds to create a new search type and add it to your page. Code might look like this:

public override DataTable RunQuery(string criteria)
{
SqlCommand cmd = new SqlCommand("spSearchByEyeColor");
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@EyeColor", SqlDbType.VarChar).Value = criteria;
//pass dummy date params to procedure.
cmd.Parameters.Add("@DTStart", SqlDbType.DateTime).Value = System.DBNull.Value;
cmd.Parameters.Add("@DTEnd", SqlDbType.DateTime).Value = System.DBNull.Value;

return base.RunSqlCmd(ref cmd);
}


So, now lets say you have 10 different classes that do different queries. In your webpage, you bind the gridview to a new datasource, select object, choose your default search and choose your generic method.

Now, in your search event write a small case statement that will set the datasources class like so:

ObjectDataSource1.TypeName = "classQueryByDate";


Before we say we're done let me explain a little about that generic function we wrote. It expects some data. A string criteria, and some dates. Now, in my case I had to do optional date range searches. Yours might only need the string criteria. What you do is set these parameters to a control-textbox when configuring the objectdatasource. So, whenever the thing fires, it will grab whatever you put into the textbox and pass it to your class. That's the key. Now, in the case of date ranges, I always pass values into my classes but I don't always use them if they're not required. You'll probably need to play with it a little. I found the objectdatasource would hang up sometimes if I didn't have default values for those dates.

Another thing you'll need to juggle is postbacks. I automatically set the ObjectDataSource1.TypeName every time based on the search.SelectedValue in the dropdown. This solved my problems.

So, the idea here is writing code that is easy to update and maintain. If someone says, can you add a search by hair color to that? You can do it in a minute or so.

Keep in mind there may be a hundred ways to do this. This is just one of them. If you have other ways you would like to share I would love to here them. Specifically if you've been able to remove the switch statement in the search event completely by using some factory pattern or something, please share.

Happy Coding!
Jas