C#-Sql Query If Where子句存在，则在没有结果的情况下工作

Question

请解决我的问题

我有以下代码

jQuery.ajax({                
                type: 'POST',
                url: "Default.aspx/populate_From",
                data: JSON.stringify({ term: aterm }),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (data) {
                    alert(data.d);
                    //jQuery(containerFrom).html(data.d);
                    //forceWidth(containerFrom, jQuery(containerFrom).width());
                },
                error: function (x, e) {
                    alert(e.Message);
                    alert(jsonResponse.message);
                    jQuery(containerFrom).text("Error, unable to retrieve list.");
                }
            });

[WebMethod]
    public static string populate_From(string term)
    {
        string query = "SELECT * FROM route_onlinemaptable WHERE isdelete = 0 AND (route_from LIKE '" + term + "%' OR map_from LIKE '" + term + "%') ORDER BY priority DESC, state_from ASC, map_from ASC";
        if (string.IsNullOrWhiteSpace(term))
        {
            query = "SELECT * FROM route_onlinemaptable";
        }
        SqlCommand cmd = new SqlCommand(query); 
        return GetData(cmd).GetXml();
    }
    public static DataSet GetData(SqlCommand cmd)
    {
        String strConnString = ConfigurationManager.ConnectionStrings["myConnStrng"].ConnectionString;   
        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    return ds;
                }
            }
        }
    }

查询=SELECT*FROM route_onlinemaptable-->不显示任何结果
如果Query=SELECT*FROM route_onlinemaptable where route_FROM='SOME VALUE'-->显示结果

我的数据库附加到应用程序&表包含近500-600行。

请提出任何建议。

Answer 1

请添加一个条件

检查术语是否为null或空，然后不要在条件处附加

string query = "";
    if(term = "")
    query = "SELECT * FROM route_onlinemaptable WHERE isdelete = 0"
    else
    query = "SELECT * FROM route_onlinemaptable WHERE isdelete = 0 AND (route_from LIKE '" + term + "%' OR map_from LIKE '" + term + "%') ORDER BY priority DESC, state_from ASC, map_from ASC";