以更简化或有效的编码方式 [英] In more simplify or effecient way of coding

问题描述

我想问一下有没有其他好的方法来修改下面的代码？

实际上这是用于过滤3列。

Hi,
I would like to ask are there any other good way to revamp the following code?
Actually this is for filtering for 3 columns.

protected void ddlCat_SelectedIndexChanged(object sender, EventArgs e)
       {
           string ddlCat = ((DropDownList)sender).SelectedValue;
           Session["ddlCat"] = ddlCat;
           string ddlLoc = (string)Session["ddlLoc"];
           string ddlStatus = (string)Session["ddlStatus"];

           if (ddlCat == "All")
            {
                if (ddlLoc == "All")
                {
                    if (ddlStatus == "All")
                    {
                        SqlDataSource1.SelectCommand = "SELECT manual_cat , process_loc , area , doc_no , rev , title , originator , mrc_no , effective, status  FROM dbo.TrainningManual";
                    }
                    if (ddlStatus != "All")
                    {
                        SqlDataSource1.SelectCommand = "SELECT manual_cat , process_loc , area , doc_no , rev , title , originator , mrc_no , effective, status  FROM dbo.TrainningManual WHERE status = '" + ddlStatus + "'";
                    }
                }

                if (ddlLoc != "All")
                {
                    if (ddlStatus == "All")
                    {
                        SqlDataSource1.SelectCommand = "SELECT manual_cat , process_loc , area , doc_no , rev , title , originator , mrc_no , effective, status  FROM dbo.TrainningManual WHERE process_loc = '" + ddlLoc + "'";
                    }
                    if (ddlStatus != "All")
                    {
                        SqlDataSource1.SelectCommand = "SELECT manual_cat , process_loc , area , doc_no , rev , title , originator , mrc_no , effective, status  FROM dbo.TrainningManual WHERE process_loc = '" + ddlLoc + "' and status = '" + ddlStatus + "'";
                    }
                }

            }
            else //ddlCat != "All"
            {
                if (ddlLoc != "All")
                {
                    if (ddlStatus == "All")
                    {
                        SqlDataSource1.SelectCommand = "SELECT manual_cat , process_loc , area , doc_no , rev , title , originator , mrc_no , effective, status  FROM dbo.TrainningManual WHERE manual_cat = '" + ddlCat + "'  and process_loc = '" + ddlLoc + "' ";
                    }

                    if (ddlStatus != "All")
                    {
                        SqlDataSource1.SelectCommand = "SELECT manual_cat , process_loc , area , doc_no , rev , title , originator , mrc_no , effective, status  FROM dbo.TrainningManual WHERE manual_cat = '" + ddlCat + "'  and process_loc = '" + ddlLoc + "' and status = '" + ddlStatus + "' ";
                    }
                }

                if (ddlLoc == "All")
                {
                    if (ddlStatus == "All")
                    {
                        SqlDataSource1.SelectCommand = "SELECT manual_cat , process_loc , area , doc_no , rev , title , originator , mrc_no , effective, status  FROM dbo.TrainningManual WHERE manual_cat = '" + ddlCat + "'";
                    }

                    if (ddlStatus != "All")
                    {
                        SqlDataSource1.SelectCommand = "SELECT manual_cat , process_loc , area , doc_no , rev , title , originator , mrc_no , effective, status  FROM dbo.TrainningManual WHERE manual_cat = '" + ddlCat + "' and status = '" + ddlStatus + "'";
                    }
                }
           }
           GridView1.DataBind();

}

推荐答案

首先，在构建查询时不要连接字符串。您将应用程序公开给SQLi。

您可以使用所有作为通配符（但NULL会更好）：

... WHERE（field1 = @ param1或@param1 =''ALL''）AND（field2 = @ param2 OR @ param2 =''ALL''）...

等等。它不是一个最佳查询 - 然而优化器将处理它。如果你经常运行这样的查询，它不会影响性能，但如果经常运行你应该小心。



[更新]

这样的事情：

First of all, don''t concatenate strings when you build a query. You expose your application to SQLi.
You could use All as a wildcard (but NULL would be better):
... WHERE (field1 = @param1 OR @param1 = ''ALL'') AND (field2 = @param2 OR @param2 = ''ALL'')...
and so on. It won''t be an optimal query - the optimizer will however deal with it. If you run such query from time to time, it won''t affect performance, but you should be careful if it is ran often.

[UPDATE]
Something like this:

protected void ddlCat_SelectedIndexChanged(object sender, EventArgs e)
{
   Session["ddlCat"] = ((DropDownList)sender).SelectedValue;
   
   string ddlCat = (string)Session["ddlCat"];
   string ddlLoc = (string)Session["ddlLoc"];
   string ddlStatus = (string)Session["ddlStatus"];
   
   SqlDataSource1.SelectCommand = 
      @"SELECT manual_cat , process_loc , area , doc_no , rev , title , originator , mrc_no , effective, status 
		FROM dbo.TrainningManual WHERE 
		(manual_cat = @ddlCat OR  @ddlCat='All') AND
		(process_loc = @ddlLoc OR @ddlLoc='All') AND
		(status = @ddlStatus OR @ddlStatus='All')";
		
   SqlDataSource1.SelectParameters.Add("@ddlCat", SqlDbType.VarChar, 80).Value = ddlCat;
   SqlDataSource1.SelectParameters.Add("@ddlLoc", SqlDbType.VarChar, 80).Value = ddlLoc;
   SqlDataSource1.SelectParameters.Add("@ddlStatus", SqlDbType.VarChar, 80).Value = ddlStatus;   
   
   GridView1.DataBind();
}   

请注意，我还没有测试过，你可能需要改进。



你也可以用声明的方式绑定你的参数，如下所示：

Be aware, that I have not tested it, you might need to refine.

You can also bind your parameters in declarative way, something like this:

<asp:sqldatasource

         id="SqlDataSource1"

         runat="server"

         connectionstring="<%

ConnectionStrings：yourconnectionstring ％>

selectcommand = SELECT manual_cat，process_loc，area，doc_no，rev，title，originator，mrc_no，effective，status FROM dbo.TrainningManual WHERE（manual_cat = @ddlCat OR @ddlCat ='All'）AND（process_loc = @ddlLoc或@ddlLoc ='全'）AND（状态= @ddlStatus或@ddlStatus ='全'） >
< selectparameters >
< asp：controlparameter name = ddlCat controlid = DropDownList1 propertyname = SelectedValue / >
< asp：sessionparameter name = ddlLoc sessionfield = ddlLoc type = String DefaultValue = 所有 / >
< asp：sessionparameter name = ddlStatus sessionfield = ddlStatus 类型 = 字符串 < span class =code-attribute> DefaultValue = 所有 / >
< / selectparameters >
< / asp：sqldatasource >

ConnectionStrings:yourconnectionstring%>" selectcommand="SELECT manual_cat , process_loc , area , doc_no , rev , title , originator , mrc_no , effective, status FROM dbo.TrainningManual WHERE (manual_cat = @ddlCat OR @ddlCat='All') AND (process_loc = @ddlLoc OR @ddlLoc='All') AND (status = @ddlStatus OR @ddlStatus='All')"> <selectparameters> <asp:controlparameter name="ddlCat" controlid="DropDownList1" propertyname="SelectedValue"/> <asp:sessionparameter name="ddlLoc" sessionfield="ddlLoc" type="String" DefaultValue="All" /> <asp:sessionparameter name="ddlStatus" sessionfield="ddlStatus" type="String" DefaultValue="All"/> </selectparameters> </asp:sqldatasource>

您可以使用切换案例语句

You could use switch case statements

switch (ddlCat)

case "All":
{
  switch (ddlLoc)
  case "All" 
  {
    //query+ WHERE status = ''" + ddlStatus + "''";
    break;
  }
  default:
  {
    break;
  }
  break;
}

default:
{
  break;
}

老实说，我认为如果你的情况更容易调试。

Honestly though, I think If else in your case is easier to debug.

这篇关于以更简化或有效的编码方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！