C#拼接sql條件的類

cg4f 10年前發布 | 2K 次閱讀 C#
#region  public enum Comparison
    public enum Comparison
    {
        /// <summary>
        /// 等于號 =
        /// </summary>
        Equal,
        /// <summary>
        /// 不等于號 <>
        /// </summary>
        NotEqual,
        /// <summary>
        /// 大于號 >
        /// </summary>
        GreaterThan,
        /// <summary>
        /// 大于或等于 >=
        /// </summary>
        GreaterOrEqual,
        /// <summary>
        /// 小于 <
        /// </summary>
        LessThan,
        /// <summary>
        /// 小于或等于 <=
        /// </summary>
        LessOrEqual,
        /// <summary>
        /// 模糊查詢 Like
        /// </summary>
        Like,
        /// <summary>
        /// 模糊查詢  Not Like
        /// </summary>
        NotLike,
        /// <summary>
        /// is null
        /// </summary>
        IsNull,
        /// <summary>
        /// is not null
        /// </summary>
        IsNotNull,
        /// <summary>
        /// in
        /// </summary>
        In,
        /// <summary>
        /// not in
        /// </summary>
        NotIn,
        /// <summary>
        /// 左括號 （
        /// </summary>
        OpenParenthese,
        /// <summary>
        /// 右括號 )
        /// </summary>
        CloseParenthese,
        Between,
        StartsWith,
        EndsWith
    }

#endregion

public class ConditionHelper
{
    #region 變量定義
    string parameterPrefix = "@";
    string parameterKey = "P";
    /// <summary>
    /// 用來拼接SQL語句
    /// </summary>
    StringBuilder conditionBuilder = new StringBuilder();
    /// <summary>
    /// 為True時表示字段為空或者Null時則不作為查詢條件
    /// </summary>
    bool isExcludeEmpty = true;
    /// <summary>
    /// 是否生成帶參數的sql
    /// </summary>
    bool isBuildParameterSql = true;
    /// <summary>
    /// 參數列表
    /// </summary>
    public List<SqlParameter> parameterList = new List<SqlParameter>();
    int index = 0;

    const string and = " AND ";
    const string or = " OR ";
    #endregion

    #region 構造函數

    /// <summary>
    /// 創建ConditionHelper對象
    /// </summary>
    /// <param name="isBuildParameterSql">是否生成帶參數的sql</param>
    /// <param name="isExcludeEmpty">為True時表示字段為空或者Null時則不作為查詢條件</param>
    public ConditionHelper(bool isBuildParameterSql = true, bool isExcludeEmpty = true)
    {
        this.isBuildParameterSql = isBuildParameterSql;
        this.isExcludeEmpty = isExcludeEmpty;
    }
    #endregion

    #region 公共方法
    /// <summary>
    /// 添加and 條件
    /// </summary>
    /// <param name="fieldName">字段名稱</param>
    /// <param name="comparison">比較符類型</param>
    /// <param name="fieldValue">字段值</param>
    /// <returns>返回ConditionHelper</returns>
    public ConditionHelper AddAndCondition(string fieldName, Comparison comparison, params object[] fieldValue)
    {
        conditionBuilder.Append(and);
        this.AddCondition(fieldName, comparison, fieldValue);
        return this;
    }

    /// <summary>
    /// 添加or條件
    /// </summary>
    /// <param name="fieldName">字段名稱</param>
    /// <param name="comparison">比較符類型</param>
    /// <param name="fieldValue">字段值</param>
    /// <returns>返回ConditionHelper</returns>
    public ConditionHelper AddOrCondition(string fieldName, Comparison comparison, params object[] fieldValue)
    {
        conditionBuilder.Append(or);
        this.AddCondition(fieldName, comparison, fieldValue);
        return this;
    }

    /// <summary>
    /// 添加and+左括號+條件  
    /// </summary>
    /// <param name="comparison">比較符類型</param>
    /// <param name="fieldName">字段名稱</param>
    /// <param name="fieldValue">字段值，注：Between時，此字段必須填兩個值</param>
    /// <returns>返回ConditionHelper</returns>
    public ConditionHelper AddAndOpenParenthese(string fieldName, Comparison comparison, params object[] fieldValue)
    {
        this.conditionBuilder.AppendFormat("{0}{1}", and, GetComparisonOperator(Comparison.OpenParenthese));
        this.AddCondition(fieldName, comparison, fieldValue);
        return this;
    }

    /// <summary>
    /// 添加or+左括號+條件
    /// </summary>
    /// <returns></returns>
    /// <param name="comparison">比較符類型</param>
    /// <param name="fieldName">字段名稱</param>
    /// <param name="fieldValue">字段值，注：Between時，此字段必須填兩個值</param>
    /// <returns>返回ConditionHelper</returns>
    public ConditionHelper AddOrOpenParenthese(string fieldName, Comparison comparison, params object[] fieldValue)
    {
        this.conditionBuilder.AppendFormat("{0}{1}", or, GetComparisonOperator(Comparison.OpenParenthese));
        this.AddCondition(fieldName, comparison, fieldValue);
        return this;
    }

    /// <summary>
    /// 添加右括號
    /// </summary>
    /// <returns></returns>
    public ConditionHelper AddCloseParenthese()
    {
        this.conditionBuilder.Append(GetComparisonOperator(Comparison.CloseParenthese));
        return this;
    }


    /// <summary>
    /// 添加條件
    /// </summary>
    /// <param name="comparison">比較符類型</param>
    /// <param name="fieldName">字段名稱</param>
    /// <param name="fieldValue">字段值，注：Between時，此字段必須填兩個值</param>
    /// <returns>返回ConditionHelper</returns>
    public ConditionHelper AddCondition(string fieldName, Comparison comparison, params object[] fieldValue)
    {
        //如果選擇IsExcludeEmpty為True,并且該字段為空值的話則跳過
        if (isExcludeEmpty && string.IsNullOrEmpty(fieldValue.ToString()))
            return this;

        switch (comparison)
        {
            case Comparison.Equal:
            case Comparison.NotEqual:
            case Comparison.GreaterThan:
            case Comparison.GreaterOrEqual:
            case Comparison.LessThan:
            case Comparison.LessOrEqual:
                this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(fieldValue[0]));
                break;
            case Comparison.IsNull:
            case Comparison.IsNotNull:
                this.conditionBuilder.AppendFormat("{0}{1}", GetFieldName(fieldName), GetComparisonOperator(comparison));
                break;
            case Comparison.Like:
            case Comparison.NotLike:
                this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("%{0}%", fieldValue[0])));
                break;
            case Comparison.In:
            case Comparison.NotIn:
                this.conditionBuilder.AppendFormat("{0}{1}({2})", GetFieldName(fieldName), GetComparisonOperator(comparison), string.Join(",", GetFieldValue(fieldValue)));
                break;
            case Comparison.StartsWith:
                this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("{0}%", fieldValue[0])));
                break;
            case Comparison.EndsWith:
                this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("%{0}", fieldValue[0])));
                break;
            case Comparison.Between:
                this.conditionBuilder.AppendFormat("{0}{1}{2} AND {3}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(fieldValue[0]), GetFieldValue(fieldValue[1]));
                break;
            default:
                throw new Exception("條件為定義");
        }
        return this;
    }


    public override string ToString()
    {
        return this.conditionBuilder.ToString();
    }

    #endregion

    #region 私有方法
    /// <summary>
    /// 取得字段值
    /// </summary>
    /// <param name="fieldValue"></param>
    /// <returns></returns>
    private string GetFieldValue(params object[] fieldValue)
    {
        if (isBuildParameterSql == false)
        {
            if (fieldValue.Length < 2)
            {
                return string.Format("'{0}'", fieldValue[0]);
            }
            else
            {
                return string.Format("'{0}'", string.Join("','", fieldValue));
            }
        }
        else
        {
            if (fieldValue.Length < 2)
            {
                return AddParameter(fieldValue[0]);
            }
            else
            {
                List<string> parameterNameList = new List<string>();
                foreach (var value in fieldValue)
                {
                    parameterNameList.Add(AddParameter(value));
                }
                return string.Join(",", parameterNameList);
            }
        }
    }

    /// <summary>
    /// 添加參數
    /// </summary>
    /// <param name="fieldValue"></param>
    /// <returns></returns>
    private string AddParameter(object fieldValue)
    {
        index++;
        string parameterName = string.Format("{0}{1}{2}", parameterPrefix, parameterKey, index);
        parameterList.Add(new SqlParameter()
        {
            ParameterName = parameterName,
            Value = fieldValue
        });
        return parameterName;
    }

    private string GetFieldName(string fieldName)
    {
        return string.Format("[{0}]", fieldName);
    }
    private static string GetComparisonOperator(Comparison comparison)
    {
        string result = string.Empty;
        switch (comparison)
        {
            case Comparison.Equal:
                result = " = ";
                break;
            case Comparison.NotEqual:
                result = " <> ";
                break;
            case Comparison.GreaterThan:
                result = " > ";
                break;
            case Comparison.GreaterOrEqual:
                result = " >= ";
                break;
            case Comparison.LessThan:
                result = " < ";
                break;
            case Comparison.LessOrEqual:
                result = " <= ";
                break;
            case Comparison.Like:
            case Comparison.StartsWith:
            case Comparison.EndsWith:
                result = " LIKE ";
                break;
            case Comparison.NotLike:
                result = " NOT LIKE ";
                break;
            case Comparison.IsNull:
                result = " IS NULL ";
                break;
            case Comparison.IsNotNull:
                result = " IS NOT NULL ";
                break;
            case Comparison.In:
                result = " IN ";
                break;
            case Comparison.NotIn:
                result = " NOT IN ";
                break;
            case Comparison.OpenParenthese:
                result = " (";
                break;
            case Comparison.CloseParenthese:
                result = ") ";
                break;
            case Comparison.Between:
                result = " BETWEEN ";
                break;
        }
        return result;
    }
    #endregion

}</pre> 


 比如說要實現這樣的一個例子：
 UserName In ('張三','李四','王五') and Age between 1 and 17  and (Gender='Male' or Gender='Female')  
 實現代碼：  
ConditionHelper helper = new ConditionHelper(false);
helper.AddCondition("UserName", Comparison.In, "張三", "李四", "王五")
      .AddAndCondition("Age",Comparison.Between,1,17)
      .AddAndOpenParenthese("Gender",Comparison.Equal,"Male")
      .AddOrCondition("Gender",Comparison.Equal,"Female")
      .AddCloseParenthese();
 string condition=helper.ToString(); 

 還有要提一下的是這個類中的isExcludeEmpty變量，這個是借鑒了園子里伍華聰的想法，由于是很早以前看的，具體是哪一篇文章就不太清楚了， 有興趣的可以去他博客http://www.cnblogs.com/wuhuacong/里找下看。這變量在這有什么用呢？不要小看這小小的變量，它讓 我們在實際中少了很多重復的代碼。比如界面上有一個條件文本框txtUserName,那我們一般拼接條件如下： 
1 if(!string.IsNullOrEmpty(txtUserName.Text.Trim())
2 {
3      condition=string.Format("UserName like '%{0}%'",txtUserName.Text.Trim()）
4 } 

 簡單說就是每次在拼接條件時都要判斷文本框里的值是否為空，只有在不為空的情況才加入條件里去。 
 現在在ConditonHelper里加了isExcludeEmpty變量，我們在使用的時候就不要加判斷了，在ConditionHelper中拼接條件時它會自動去判斷，是不是這樣讓代碼變得更簡潔？
本文由用戶 cg4f 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！
本文地址：http://www.baiduhome.net/code/view/1436767463818
C#拼接sql條件的類

相關代碼

相關文檔

相關經驗

目錄
