what is the best way to store a user filtered query params in a database table?

Question

I have an ASP.NET MVC website. In my backend I have a table called People with the following columns:

1. ID
2. Name
3. Age
4. Location
5. ... (a number of other cols)

I have a generic web page that uses model binding to query this data. Here is my controller action:

public ActionResult GetData(FilterParams filterParams)
{
      return View(_dataAccess.Retrieve(filterParams.Name, filterParams.Age, filterParams.location, . . .)
}

which maps onto something like this:

 http://www.mysite.com/MyController/GetData?Name=Bill .. . 

The dataAccess layer simply checks each parameter to see if its populated to add to the db where clause. This works great.

I now want to be able to store a user's filtered queries and I am trying to figure out the best way to store a specific filter. As some of the filters only have one param in the queryString while others have 10+ fields in the filter I can't figure out the most elegant way to storing this query "filter info" into my database.

Options I can think of are:

1. Have a complete replicate of the table (with some extra cols) but call it PeopleFilterQueries and populate in each record a FilterName and put the value of the filter in each of field (Name, etc)

2. Store a table with just FilterName and a string where I store the actual querystring Name=Bill&Location=NewYork. This way I won't have to keep adding new columns if the filters change or grow.

What is the best practice for this situation?

Answer 1

If the purpose is to save a list of recently used filters, I would serialise the complete FilterParams object into an XML field/column after the model binding has occurred. By saving it into a XML field you're also giving yourself the flexibility to use XQuery and DML should the need arise at a later date for more performance focused querying of the information.

    public ActionResult GetData(FilterParams filterParams)
    {
          // Peform action to get the information from your data access layer here
          var someData = _dataAccess.Retrieve(filterParams.Name, filterParams.Age, filterParams.location, . . .);

          // Save the search that was used to retrieve later here
          _dataAccess.SaveFilter(filterParams);
          return View(someData);
    }

And then in your DataAccess Class you'll want to have two Methods, one for saving and one for retrieving the filters:

public void SaveFilter(FilterParams filterParams){
    var ser = new System.Xml.Serialization.XmlSerializer(typeof(FilterParams));
    using (var stream = new StringWriter())
           {
              // serialise to the stream
              ser.Serialize(stream, filterParams);
           }
  //Add new database entry here, with a serialised string created from the FilterParams obj
  someDBClass.SaveFilterToDB(stream.ToString());
}

Then when you want to retrieve a saved filter, perhaps by Id:

public FilterParams GetFilter(int filterId){

      //Get the XML blob from your database as a string
      string filter = someDBClass.GetFilterAsString(filterId);

      var ser = new System.Xml.Serialization.XmlSerializer(typeof(FilterParams));

      using (var sr = new StringReader(filterParams))
      {
          return (FilterParams)ser.Deserialize(sr);
      }
}

Remember that your FilterParams class must have a default (i.e. parameterless) constructor, and you can use the [XmlIgnore] attribute to prevent properties from being serialised into the database should you wish.

public class FilterParams{
   public string Name {get;set;}
   public string Age {get;set;}

   [XmlIgnore]
   public string PropertyYouDontWantToSerialise {get;set;}
}

Note: The SaveFilter returns Void and there is no error handling for brevity.

Answer 2

Rather than storing the querystring, I would serialize the FilterParams object as JSON/XML and store the result in your database.

Here's a JSON Serializer I regularly use:

using System.IO;
using System.Runtime.Serialization.Json;
using System.Text;

namespace Fabrik.Abstractions.Serialization
{
    public class JsonSerializer : ISerializer<string>
    {
        public string Serialize<TObject>(TObject @object) {
            var dc = new DataContractJsonSerializer(typeof(TObject));
            using (var ms = new MemoryStream())
            {
                dc.WriteObject(ms, @object);
                return Encoding.UTF8.GetString(ms.ToArray());
            }
        }

        public TObject Deserialize<TObject>(string serialized) {
            var dc = new DataContractJsonSerializer(typeof(TObject));
            using (var ms = new MemoryStream(Encoding.UTF8.GetBytes(serialized)))
            {
                return (TObject)dc.ReadObject(ms);
            }
        }
    }
}

You can then deserialize the object and pass it your data access code as per your example above.

Answer 3

You didn't mention about exact purpose of storing the filter.

If you insist to save filter into a database table, I would have following structure of the table.

• FilterId
• Field
• FieldValue

An example table might be

FilterId Field    FieldValue
1        Name     Tom
1        Age      24
1        Location IL       
3        Name     Mike
...