不使用实体框架的ASP. NET MVC中基于动态SQL的搜索

Question

有人能给一些自定义搜索的想法吗？我正在寻找一种使用ASP. NET MVC实现此逻辑的方法。

我想要的是要么搜索用户谁是在纽约或性别是男性-或也搜索用户谁是在纽约和性别是男性使用AND OR逻辑与使用2个单选按钮一个'AND'另一个'OR'。查看我的客户端视图截图：

下面是我的代码示例：

DemoSearchController：

public ActionResult Index(string loactionsearch,string txtGenderSkill)
{
    string mainconn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

    SqlConnection sqlconn = new SqlConnection(mainconn);
        
    string sqlquery = "select * from [dbo].[LocationInfo] where LocationName like '%" + loactionsearch + "%' AND Gender like '%" + txtGenderSkill + "%'";

    SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);

    sqlconn.Open();

    SqlDataAdapter sda = new SqlDataAdapter(sqlcomm);
    DataSet ds = new DataSet();
    sda.Fill(ds);

    List<LocationInfo> location = new List<LocationInfo>();

    foreach (DataRow dr in ds.Tables[0].Rows)
    {
        location.Add(new LocationInfo
                {
                    ID = Convert.ToInt64(dr["ID"]),
                    LocationName = Convert.ToString(dr["LocationName"]),
                    Gender = Convert.ToString(dr["Gender"])
                });
    }

    sqlconn.Close();
    ModelState.Clear();

    return View(location);                       
}

索引。cshtml：

@model IEnumerable<CM_CRUD_BootstrapPopUp.Models.LocationInfo>

@using (Html.BeginForm("Index", "DemoSearch", FormMethod.Get))
{
    <div class="row">
        <div class="col-md-6">
            <p>
                <!--TextBox-->
                <input type="text" id="txtNewMultSkill" name="loactionsearch" placeholder="Enter Location" class="form-control placeholder-no-fix">

                <!--Radio Button-->
                <input type="radio" id="html" name="fav_language" value="AND">
                <label for="html">AND</label><br>
                <input type="radio" id="css" name="fav_language" value="OR">
                <label for="css">OR</label><br>

                <!--TextBox-->
                <input type="text" id="txtGenderSkill" name="gendersearch" placeholder="Enter Gender" class="form-control placeholder-no-fix">
                <br />
                <!--SearchButton-->
                <input type="submit" value="Search" name="submit" />
            </p>

            <div class="col-md-6" id="div_Keyskills">
                <div class="" id="txtKeyskill">
                </div>
            </div>
            <input type="hidden" id="hdnSkill" />
        </div>
    </div>
   // ....
   // other table view code here
}

Answer 1

在方法中再添加一个参数，如

public ActionResult Index(string loactionsearch, string txtGenderSkill, string concatinator)
{
            string mainconn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            SqlConnection sqlconn = new SqlConnection(mainconn);

            string sqlquery = "select * from [dbo].[LocationInfo] where LocationName like '%" + loactionsearch + "%' ";
            sqlquery += concatinator;
            sqlquery += " Gender like '%" + txtGenderSkill + "%'";
            SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
            sqlconn.Open();

在此新参数中传递和/或

这既不是处理SQL查询的最佳方法，也不是处理敏感数据的方法。请至少使用参数化查询