Miles MS.HelloWorld

在SQL比較複雜的情況底下，用LINQ來寫並不是那麼容易，所以自己還是會寫到原生的SQL。  而當寫到原生的SQL時，就會有SQL組where條件字串與傳遞SqlParameter的需求，最近剛好寫了一個ClauseAssembler類別來處理這個需求。

ClauseAssembler用法:

string commandText = @"SELECT *
                         FROM Employee
                         {0}";

ClauseAssembler assembler = new ClauseAssembler();

assembler.Add<string>("WHERE Name LIKE '%' + @Name + '%'", "Miles");
assembler.Add<int>("AND Position = @Position", 3);

var clause = assembler.Output();

commandText = string.Format(commandText, clause.Item1);

var data = _db.Database.SqlQuery<Employee>(commandText, clause.Item2);

clause說明:

• clause.Item1 : 拿來取代{0}的字串
• clause.Item2 : @Name跟@Position的SqlParameter

產生的SQL如下:

SELECT * 
FROM Employee
WHERE Name LIKE '%'+@Name+'%'
AND Position = @Position

心得:這樣where條件字串跟SqlParameter一起寫，整個方便很多。

 

附上ClauseAssembler Source Code

/// <summary>
/// SQL條件組裝器
/// </summary>
public class ClauseAssembler
{
    public ClauseAssembler()
    {
        this.Clauses = new List<string>();
        this.Parameters = new List<SqlParameter>();
    }

    /// <summary>
    /// 組裝字串
    /// </summary>
    private List<string> Clauses { get; set; }

    /// <summary>
    /// 條件參數
    /// </summary>
    private List<SqlParameter> Parameters { get; set; }

    /// <summary>
    /// 新增clause
    /// </summary>
    /// <typeparam name="T">參數型別</typeparam>
    /// <param name="clause">查詢子句</param>
    /// <param name="value">參數值</param>
    /// <returns>Fluent ClauseAssembler</returns>
    public ClauseAssembler Add<T>(string clause, T value)
    {
        string removedSpaceClause = clause.Trim();
        this.Clauses.Add(removedSpaceClause);
        Regex regex = new Regex("@.*[a-zA-Z_]");
        Match match = regex.Match(removedSpaceClause);
        string argumentName = string.Empty;

        if (match.Success)
        {
            argumentName = match.Value;
        }

        SqlParameter parameter = new SqlParameter(argumentName, value);
        this.Parameters.Add(parameter);
        return this;
    }

    /// <summary>
    /// 新增clause
    /// </summary>
    /// <typeparam name="T">參數型別</typeparam>
    /// <param name="clause">查詢子句</param>        
    /// <returns>Fluent ClauseAssembler</returns>
    public ClauseAssembler Add(string clause)
    {
        string removedSpaceClause = clause.Trim();
        this.Clauses.Add(removedSpaceClause);
        return this;
    }

    /// <summary>
    /// 取得結果條件子句與SqlParameter
    /// </summary>
    /// <param name="isClear">是否清除clause</param>
    /// <returns>
    /// Item1:條件子句
    /// Item2:SqlParameter[]
    /// </returns>
    public Tuple<string, SqlParameter[]> Output(bool isClear = true)
    {
        string clause = string.Join(" ", this.Clauses);
        SqlParameter[] parameters = this.Parameters.ToArray();

        if (isClear)
        {
            this.Clauses.Clear();
            this.Parameters.Clear();
        }

        return Tuple.Create(clause, parameters);
    }
}

 

如果任何建議或問題歡迎留言討論

 

 

一天一分享，身體好健康。

該追究的不是過去的原因，而是現在的目的。