MySQL databases are used, but when I use SQL statements with parameters for Fuzzy queries, I find that MySQL does not recognize the content in my parameters. After many experiments, I finally found the answer and shared it with you. I will not talk about it much more. The details are as follows:
Public datatable getuserlist (string strparam1, string strparam2, string strparam3, string strparam4)
{
Stringbuilder sqlcontent = new stringbuilder ();
Arraylist paramlist = new arraylist ();
Sqlcontent. append ("select ");
Sqlcontent. append ("column1 ");
Sqlcontent. append (", column2 ");
Sqlcontent. append (", column3 ");
Sqlcontent. append (", column4 ");
Sqlcontent. append ("from ");
Sqlcontent. append ("tab_temp ");
Sqlcontent. append ("where 1 = 1 ");
// Determine whether the parameter is null or ""
If (! String. isnullorempty (strparam1 ))
{
Sqlcontent. append ("and column1 like @ param1 ");
// Add Parameters
Paramlist. Add (New mysqlparameter ("@ param1", "%" + strparam1 + "% "));
}
If (! String. isnullorempty (strparam2 ))
{
Sqlcontent. append ("and column2 like @ param2 ");
Paramlist. Add (New mysqlparameter ("@ param2", "%" + strparam2 + "% "));
}
If (! String. isnullorempty (strparam3 ))
{
Sqlcontent. append ("and column3 like @ param3 ");
Paramlist. Add (New mysqlparameter ("@ param3", "%" + strparam3 + "% "));
}
If (! String. isnullorempty (strparam4 ))
{
Sqlcontent. append ("and column4 like @ param4 ");
Paramlist. Add (New mysqlparameter ("@ param4", "%" + strparam4 + "% "));
}
Try
{
// Obtain the database link
Dbconn. getconnection ();
Objdt = new datatable ();
// Call the query method in dbutil
Objdt = dbconn.exe cutequery (sqlcontent. tostring (), paramlist );
}
Catch (exception E)
{
Throw E;
}
Finally
{
// Close the DB Link
Dbconn. closeconnection ();
}
Return objdt;
}
Correct syntax:
Sqlcontent. append ("and column1 like @ param1 ");
// Add Parameters
Paramlist. Add (New mysqlparameter ("@ param1", "%" + strparam1 + "% "));
Incorrect syntax:
Sqlcontent. append ("and column1 like '% @ param1 % '");
// Add Parameters
Paramlist. Add (New mysqlparameter ("@ param1", strparam1 ));
Posted on: Computer Level 2 Examination _ examination large