代码之家 › 专栏 › 技术社区 › sam

带where like子句的参数化查询

parameterized-query .net-4.0 sql-server-2008-r2 c#

sam · 技术社区 · 7 年前

我试图用where子句执行参数化查询,比如在sql server studio上我得到了正确的结果

最后一个问题,在某些情况下,我可能不会传递一个类似的参数,然后如何避免绑定该参数进行查询?

public void GetPatientInfoFilter()
{
    string connStr = ConfigurationManager.ConnectionStrings["SRJDconnstr"].ToString();
    string cmdStr = @"SELECT ID,
                                             DocNUM,
                                             NAM+' '+LFNAME as FirstLastName,
                                             FNAME,
                                             SEX,
                                             BIRTHDAY,
                                             PHONE,
                                             MOBILE,
                                             ADDRESS
                                  FROM SICK
                               WHERE DocNUM LIKE @DocNUM
                                    AND NAM+' '+LFNAME LIKE @FLNAME
                                    AND FNAME LIKE @FNAME";

    using (SqlConnection conn = new SqlConnection(connStr))
    using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
    {
        conn.Open();
        cmd.CommandText = cmdStr;
        cmd.CommandType = CommandType.Text;

        dtPatientInfo.Rows.Clear();
           cmd.Parameters.Add(new SqlParameter("@DocNUM", SqlDbType.VarChar,10)).Value = "%" + TB_DocNum.Text + "%";
            cmd.Parameters.Add(new SqlParameter("@FLNAME", SqlDbType.VarChar,200)).Value = "%" + TB_FirstLastName.Text + "%";
            cmd.Parameters.Add(new SqlParameter("@FNAME", SqlDbType.VarChar,100)).Value = "%" + TB_FatherName.Text + "%";


        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dtPatientInfo);
        dataGridView1.DataSource = dtPatientInfo;
    }
}

下面是我如何在SSMS中执行查询

SELECT ID,
                                                     DocNUM,
                                                     NAM+' '+LFNAME as FirstLastName,
                                                     FNAME,
                                                     SEX,
                                                     BIRTHDAY,
                                                     PHONE,
                                                     MOBILE,
                                                     ADDRESS
                                          FROM SICK
                                       WHERE DocNUM LIKE '%1%'
                                            AND NAM+' '+LFNAME LIKE '%sa%'
                                            AND FNAME LIKE '%b%'

1 回复 | 直到 7 年前

Joel Coehoorn 7 年前

你把 %

string cmdStr = 
    @"SELECT ID,
         DocNUM,
         NAM+' '+LFNAME as FirstLastName,
         FNAME,
         SEX,
         BIRTHDAY,
         PHONE,
         MOBILE,
         ADDRESS
     FROM SICK
     WHERE DocNUM LIKE '%' + @DocNUM + '%'
         AND NAM+' '+LFNAME LIKE '%' + @FLNAME + '%'
         AND FNAME LIKE '%' + @FNAME + '%'";

您还可以考虑删除前导通配符,并且只执行“以”匹配,因为这里的内容防止了任何索引帮助此查询的可能性。