在MS Access DB连接上使用LIKE运算符进行C＃选择查询 [英] C# select query using LIKE operator on MS Access DB connection

问题描述

我有一个C＃函数访问MS Access数据库



当我在C＃查询字符串中使用下一个语句时

.. 。其中im.insp_mission_number喜欢'％1％'...

它检索包含1的所有insp_mission_number



但是当我是使用参数什么都没有工作

...其中im.insp_mission_number喜欢'％@ insp_mission_number％'......



其中是问题？

是@，'，%%还是什么......？



提前致谢



这是我的代码

I have a C# function accessing an MS Access db

When using the next statements inside my C# query string
"... where im.insp_mission_number like '%1%' ..."
it retrieves all insp_mission_number containing 1

But when i'm using parameters nothing is working
"... where im.insp_mission_number like '%@insp_mission_number%' ..."

where is the problem ?
Is it with @, ', %%, or what ... ?

Thanks in advance

Here is my code

public DataTable getInspectionMissions(InspectionMissionExt ime)
       {
           string query = string.Format("SELECT im.*, i.first_name + i.surname AS inspector_name, b.name AS branch_name, m.name AS ministry_name FROM (((insp_mission AS im left join inspector as i on im.inspector_id = i.id) left join branch as b on im.insp_branch_id = b.id) left join ministry m on im.ministry_id = m.id)where im.insp_mission_number like '%@insp_mission_number%' and im.insp_branch_id = @insp_branch_id and im.inspector_id = @inspector_id and im.ministry_id = @ministry_id");
           OleDbParameter[] oledbParameters = new OleDbParameter[4];
           oledbParameters[0] = new OleDbParameter("@insp_mission_number", OleDbType.VarChar);
           oledbParameters[0].Value = Convert.ToString(ime.insp_mission_number);
           oledbParameters[1] = new OleDbParameter("@insp_branch_id", OleDbType.Integer);
           oledbParameters[1].Value = Convert.ToUInt64(ime.insp_branch_id);
           oledbParameters[2] = new OleDbParameter("@inspector_id", OleDbType.Integer);
           oledbParameters[2].Value = Convert.ToUInt64(ime.inspector_id);
           oledbParameters[3] = new OleDbParameter("@ministry_id", OleDbType.Integer);
           oledbParameters[3].Value = Convert.ToUInt64(ime.ministry_id);
           return conn.executeSelectQuery(query, oledbParameters);
       }

推荐答案

首先，我不知道你的数据库是否包含合理的数据，但是它写在这里的方式，它真的很容易注入SQL。我建议你使用带参数的存储过程，以避免安全问题。



但如果我看一下这个，考虑到我不知道在哪里的事实@insp_mission_number参数来自，我试试这个：



string query = string.Format（SELECT im。*，i.first_name + i.surname AS inspector_name，b.name AS branch_name，m.name AS ministry_name FROM（（（在我的im.inspector_id = i.id上，我的左边连接检查器，我是左边的连接分支，在im.insp_branch_id = b.id上为b）左连接部门在im.ministry_id = m.id）其中im.insp_mission_number 喜欢'％'+ @insp_mission_number +'％'和im.insp_branch_id = @insp_branch_id和im.inspector_id = @inspector_id和im。 ministry_id = @ministry_id）;



但就像我说的那样，因为我不知道参数的定义在哪里，我可能错了。



希望它有所帮助。

First of all, I don't know if your database holds sensible data, but the way it is written here, it is really prone to SQL injections. I'd suggest you use stored procedures with parameters instead, to avoid security issues.

But if I look at this, considering the fact that I don't know where the @insp_mission_number parameters comes from, I'd try this:

string query = string.Format("SELECT im.*, i.first_name + i.surname AS inspector_name, b.name AS branch_name, m.name AS ministry_name FROM (((insp_mission AS im left join inspector as i on im.inspector_id = i.id) left join branch as b on im.insp_branch_id = b.id) left join ministry m on im.ministry_id = m.id)where im.insp_mission_number like '%' + @insp_mission_number + '%' and im.insp_branch_id = @insp_branch_id and im.inspector_id = @inspector_id and im.ministry_id = @ministry_id");

But like I said since I don't know where the parameter is defined, I might be wrong.

Hope it helps.

这篇关于在MS Access DB连接上使用LIKE运算符进行C＃选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！