動態組合WHERE條件

2015-11-07

摘要:動態組合WHERE條件

使用方式

const string commandText = @"
SELECT *
FROM TA A
INNER JOIN TB B ON A.AID=B.AID
INNER JOIN TC C ON B.BID=C.CID
LEFT JOIN TD D ON C.CID=D.CID
### WHERE ### 
";
var builder = new CommandBuilder(commandText, "### WHERE ###");
builder.Add(tbName.Text != "", "B.Name LIKE @Name", "@Name", tbName.Text);
builder.Add(tbCategory.Text != "", "A.Category=@Category", "@Category", tbCategory.Text);
builder.Add(tbFormID.Text != "", "C.FormID=@FormID", "@FormID", tbCategory.Text);
builder.Add(tbKeyword.Text != "", "D.Keyword LIKE @Keyword", "@Keyword", tbKeyword.Text);
builder.Add(cbLevel.SelectedIndex > -1, "B.Level=@Level", "@Level", cbLevel.SelectedValue);
using (var command = new SqlCommand())
{
    builder.Prepare(command);
    Console.WriteLine(command.CommandText);
    foreach (SqlParameter p in command.Parameters)
        Console.WriteLine("{0} = {1}", p.ParameterName, p.Value);
}

執行結果

SELECT*
FROM TA A
INNER JOIN TB B ON A.AID = B.AID
INNER JOIN TC C ON B.BID = C.CID
LEFT JOIN TD D ON C.CID = D.CID
WHERE B.Name LIKE @Name AND A.Category = @Category AND C.FormID = @FormID

@Name = qwer
@Category = test
@FormID = test

CommandBuilder

public class CommandBuilder
{
    readonly List _conditions;
    readonly List _params;
    readonly string _whereTag;
    readonly string _commandText;
    public CommandBuilder(string commandText, string whereTag)
    {
        _commandText = commandText;
        _whereTag = whereTag;
        _conditions = new List();
        _params = new List();
    }
    public void Add(bool isValid, string condition, string parameterName, object parameterValue)
    {
        if (!isValid) return;
        _conditions.Add(condition);
        _params.Add(new SqlParameter(parameterName, parameterValue));
    }
    public void Prepare(SqlCommand command)
    {
        var where = _conditions.Count > 0
            ? " WHERE " + string.Join(" AND ", _conditions.ToArray())
            : string.Empty;
        command.CommandText = _commandText.Replace(_whereTag, where);
        if (_params.Count > 0)
            command.Parameters.AddRange(_params.ToArray());
    }
}

回首頁