通过在LINQPad中输入,我可以得到我期望的结果：
SELECT * FROM WorkTable WHERE WTName LIKE "DSD__20090410014953000%"
(它显示了WTName值为DSD__20090410014953000.xml的记录“)
但尝试以编程方式执行此操作正在尝试.我试过了：
const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName%"; using (sqliteConnection con = new sqliteConnection(HHSUtils.GetDBConnection())) { con.Open(); sqliteCommand cmd = new sqliteCommand(qry,con); cmd.Parameters.Add(new sqliteParameter("wtName",tableName)); siteNum = Convert.ToInt32(cmd.ExecuteScalar()); }
…但它会导致应用程序崩溃,我的日志文件告诉我原因：
Message: From application-wide exception handler: System.Data.sqlite.sqliteException: sql logic error or missing database near "%": Syntax error
所以也许它认为查询参数被命名为“wtName％”而不是“wtName”;但是将参数和“what”opertor(“％”)与空格分开也不起作用.
我可以通过将查询参数嵌入到字符串中来实现retro / kludgy,如下所示：
const string qry = String.Format("SELECT SiteNum FROM WorkTable WHERE WTName LIKE {0}%",tableName);
…并且完全不使用查询参数,但是我担心如果我这样做了Troy Hunt会出现在我的房子里,并且在栏杆上关于sql注入时会给我带上床栏.
解决方法
应将通配符％添加到参数值,而不是参数名称
const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName"; using (sqliteConnection con = new sqliteConnection(HHSUtils.GetDBConnection())) { con.Open(); sqliteCommand cmd = new sqliteCommand(qry,con); cmd.Parameters.Add(new sqliteParameter("@wtName",tableName + "%")); siteNum = Convert.ToInt32(cmd.ExecuteScalar()); }
