[C#] 不用動態組SQL字串的查詢程式碼

2011-06-16

7783
0
ASP.net WebForm
2012-03-15

[C#/Java] 不用動態組SQL字串的查詢程式碼

從公司SA學來的技巧，紀錄一下

aspx畫面：

aspx畫面

一般如果要依據畫面上的使用者輸入值，下Select查詢語法的話

送出按鈕的Click事件

protected void cmd_query_Click(object sender, EventArgs e)
    {
        //Step 1.先下可以撈全部資料集的SQL語法
        StringBuilder sql = new StringBuilder("Select CategoryID,CategoryName From Categories Where 1 = 1");

        //Step 2.
        #region 篩選條件(動態組SQL字串)
        if (ddl_CategoryID.SelectedIndex != 0)//如果有選擇特定編號的話
        {
            sql.Append(" And CategoryID = '" + ddl_CategoryID.SelectedValue.Replace("'","''") + "'");
        }

        if (txt_CategoryName.Text.Trim() != "")//如果有輸入關鍵字的話
        {
            sql.Append(" And CategoryName Like '%"+txt_CategoryName.Text.Trim().Replace("'","''")+"%'");
        }
        #endregion
        //Step 3.最後再組上排序語法
        sql.Append(" Order by CategoryID ASC");
        
        gv_Categroies.DataSource = this.getDataTable(sql.ToString());
        gv_Categroies.DataBind();

    }

    protected DataTable getDataTable(string sql)
    {
        using (SqlConnection conn=new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=NorthwindChinese;Integrated Security=True"))
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            conn.Open();
            da.Fill(ds);
            conn.Close();
            if (ds.Tables.Count>0)
            {
                return ds.Tables[0];
            }
            else
            {
                return new DataTable();
            }
        }
    
    }

提供另一種不需動態組SQL字串的做法：

在Where 1=1之後插入，And ( ( 欄位變數 = 欄位變數預設值 ) Or ( Table欄位 = 欄位變數) )

所以以上的程式碼SQL語法可以改成這樣：

//查詢按鈕Click事件
protected void cmd_query_Click(object sender, EventArgs e)
{
    string categoryID = ddl_CategoryID.SelectedValue.Replace("'","''");
    string categoryName = txt_CategoryName.Text.Trim().Replace("'","''");
    //Step 1.直接下SQL語法
    string sql =  " Select CategoryID,CategoryName From Categories Where 1 = 1 "+
                  " And ( ( '" + categoryID + "' = '-1' ) Or (CategoryID = '" + categoryID + "') ) " +
                  " And ( ( '" + categoryName + "' = '' ) Or (CategoryName Like '%"+categoryName+"%') ) " +
                  " Order by CategoryID ASC";
    
    gv_Categroies.DataSource = this.getDataTable(sql);
    gv_Categroies.DataBind();

}

查詢效果和第一種做法一樣。

以下為觀察第二種做法的SQL：

second

有特定條件時：

third

如果前端有兩個下拉選單or兩個以上輸入值(都共用同一個Table欄位時)，這種SQL語法可以再多加Or的篩選條件，前端程式就可以不用if來else去

例如：

" And (  ('" + 輸入值1 + "' = '' and '" + 輸入值2 + "' = '') " + //輸入值1和輸入值2都是空白(預設值)時，撈全部資料

             //輸入值1有Value，輸入值2沒有Value時，只篩選輸入值1的Value                   
            " Or ( '" + 輸入值1 + "'<>'' and '" + 輸入值2 + "'= '' and Categories.CategoryName Like '%" + 輸入值1 + "%') " +

            //輸入值1有Value，輸入值2也有Value時，篩選輸入值1串接輸入值2兩個Value的資料
            " Or ( '" + 輸入值1 + "'<>'' and '" + 輸入值2 + "'<>'' and Categories.CategoryName Like '%" + 輸入值1 + 輸入值2 + "%') " +

"     ) "; //結束And篩選

總之把握這樣的想法就行了

And ( ( 欄位變數 = 欄位變數預設值 ) Or ( Table欄位 = 欄位變數) )

And ( (一定要設定變數預設值 ) Or (至少有一個篩選條件) [Or之後的條件自由料理可加可不加] )

回首頁