C# to Access SQL query based on UI controls

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.

Just from the title of your question one could tell there's a problem: UI and data have nothing to do in the same sentence, and seeing the two intertwined in code is a big fishy smell, especially in a WPF/MVVM application.

@George is 100% correct, your ViewModel shouldn't know how to build a SQL string (and +1 for ditching repositories!).

What you have here is basically a search feature. You're not going to search with UI controls, you're going to search with the ViewModel property values that are bound to the values of these UI controls.

Let's see what the search criterion can be:

• Record number
• Employee ID
• Location
• First name
• Last name

Whether these values come from a DatePicker, a RadioButton or a TextBox is completely irrelevant; your code needs to abstract away these things, and leave the UI at the View level.

I don't like string-concatenating SQL. Normally I'd probably write a small stored procedure for such a search, but if I remember correctly Access databases don't have that, so I'd probably try to get something like this to work:

SELECT * FROM Records 
WHERE Record_Number = ISNULL(@recordNumber, Record_Number)
  AND Employee_ID LIKE '%' + ISNULL(@employeeId, Employee_ID) + '%'
  AND Location LIKE '%' + ISNULL(@location, Location) + '%'
  AND First_Name LIKE '%' + ISNULL(@firstName, First_Name) + '%'
  AND Last_Name LIKE '%' + ISNULL(@lastName, Last_Name) + '%'

My Access is very rusty though, but I'm sure there's a way to pass it parameterized queries. If not named ones like above, ordered ones like this:

SELECT * FROM Records 
WHERE Record_Number = ISNULL(?, Record_Number)
  AND Employee_ID LIKE '%' + ISNULL(?, Employee_ID) + '%'
  AND Location LIKE '%' + ISNULL(?, Location) + '%'
  AND First_Name LIKE '%' + ISNULL(?, First_Name) + '%'
  AND Last_Name LIKE '%' + ISNULL(?, Last_Name) + '%'

And then passing in the parameter values in that specific order. The idea is to let the database deal with putting in the parameter values, doing that in code is asking for SQL injection.

The idea is to use a good-for-all-searches query, so that all searches execute the very same query on the database side, only with different parameters. Then you can leverage indexes and caching... although I'm not sure Access does that, but it can't hurt.