C# to Access SQL query based on UI controls I have a WPF application (using MVVM pattern) that allows users to perform a search for a record/records based on their selection choices and entries into the different UI controls (TextBox, CheckBox, RadioButton, DatePicker, TimerPicker,etc).  It does this by retrieving information from an Access database.

I have an Access query method in my AccessWorker class that looks like this (note that this is simplified a bit, because I can't post everything I have):

public DataSet GetRecords(string customQuery = "")
{
    var data = new DataSet();

    try
    {
        using (var con = new OleDbConnection(ConnectionString))
        {
            string queryString = "SELECT * FROM Records " + customQuery;

            using (var da = new OleDbDataAdapter(queryString, con))
            {
                da.Fill(data, "Records");
            }
        }
    }
    catch (Exception e)
    {
        ExceptionLogger.Record(e.Message);
    }

    return data;
}


As you can see, I pass a custom query argument to this method in a form of a string.  I construct this query string using a StringBuilder in my ViewModel and then I pass it to GetRecords() method.  Here's the GetQueryString() method that does the string building:

private string GetQueryString()
{
    var query = new StringBuilder();

    if (!String.IsNullOrWhiteSpace(RecNumber))
    {
        query.Append("Record_Number = '" + RecNumber + "'");
    }

    if (!String.IsNullOrWhiteSpace(EmployeeId))
    {
        if (!String.IsNullOrWhiteSpace(query.ToString()))
        {
            query.Append(" AND ");
        }

        query.Append("Employee_ID LIKE '%" + EmployeeId + "%'");
    }

    if (!String.IsNullOrWhiteSpace(Location))
    {
        if (!String.IsNullOrWhiteSpace(query.ToString()))
        {
            query.Append(" AND ");
        }

        query.Append("Location LIKE '%" + Location + "%'");
    }

    if (!String.IsNullOrWhiteSpace(FirstName))
    {
        if (!String.IsNullOrWhiteSpace(query.ToString()))
        {
            query.Append(" AND ");
        }

        query.Append("First_Name LIKE '%" + FirstName + "%'");
    }

    if (!String.IsNullOrWhiteSpace(LastName))
    {
        if (!String.IsNullOrWhiteSpace(query.ToString()))
        {
            query.Append(" AND ");
        }

        query.Append("Last_Name LIKE '%" + LastName + "%'");
    }

    //...and so on...
    return String.Format("WHERE {0}", query.ToString());
}


The above method is severely shortened here so that I wouldn't have to post a very long wall of code, but I'm dealing with 25 different controls and so there could be a condition for everyone of them that would have to be part of this query string.  The method itself, as you can imagine, is rather too large by anyone's standard.  When I took a C++ class in college, my instructor told me that if I can help it, I should keep my methods no longer than what I can see on the screen.  I'm bowing my head in shame... heh

I don't believe that this is the most elegant way of accomplishing this task.  Unfortunately, I haven't come up with a better approach.  I do my best at trying to learn new patterns every day, but I've been programming for under a year and a half, so any guidance would be greatly appreciated.

By the way, based on some of the comments, I have to clear up that I'm using properties that are bound to control, not controls' values....
My initial thought is that since view models are only (or should only be) concerned with presentation logic, it shouldn't know how to construct an SQL string. I would move the code which builds up the query into a separate Query Object. Also I would have some helper methods (e.g. extension methods) to try and get rid of the repetitive null/empty/whitespace checks. For example:

public class GetRecordsQuery
{
    // If there's a lot of parameters, maybe refactor this to accept an object
    public void Execute(string recordNumber, string employeeId, string location,
                        string firstName, string lastName)
    {
        var query = new StringBuilder();

        query
            .Eq("Record_Number", recordNumber).And()
            .Like("Employee_ID", employeeId).And()
            .Like("Location", location).And()
            .Like("First_Name", firstName).And()
            .Like("Last_Name", lastName)
            .EndQuery();

        // or however AccessWorker members are invoked
        return AccessWorker.GetRecords(String.Format("WHERE {0}", query));
    }
}

public static class StringBuilderExtensions
{
    private const string AndOp = " AND ";

    public static StringBuilder And(this StringBuilder sb)
    {
        var str = sb.ToString();

        if (String.IsNullOrWhiteSpace(str))
            return sb;

        if (str.EndsWith(AndOp))
            return sb;

        return sb.Append(AndOp);
    }

    public static StringBuilder EndQuery(this StringBuilder sb)
    {
        var str = sb.ToString();

        if (String.IsNullOrWhiteSpace(str))
            return sb;

        if (!str.EndsWith(AndOp))
            return sb;

        var length = AndOp.Length;
        var start = sb.Length - length;

        return sb.Remove(start, length);
    }

    public static StringBuilder Eq(this StringBuilder sb, string column, string value)
    {
        if (String.IsNullOrWhiteSpace(value))
            return sb;

        return sb.AppendFormat("{0} = '{1}'", column, value);
    }

    public static StringBuilder Like(this StringBuilder sb, string column, string value)
    {
        if (String.IsNullOrWhiteSpace(value))
            return sb;

        return sb.AppendFormat("{0} LIKE '%{1}%'", column, value);
    }
}


EDIT 1: Corrected code to prevent AND operators being appended when one already exists.

EDIT 2: Added an EndQuery method to trim off any trailing AND operators.