oracle 使用 OracleDataAdapter 的参数化查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11843667/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Paramaterized queries using OracleDataAdapter
提问by Sperick
I'm currently using a Controller class in conjunction with a data access layer class (UserDAL class) in order to make changes to a database. However I am aware that my code is vunerable as I am not using parameterised queries.
我目前正在将控制器类与数据访问层类（UserDAL 类）结合使用，以便对数据库进行更改。但是我知道我的代码很脆弱，因为我没有使用参数化查询。
I've seen from excamples how to use the queries using command objects. However I am using an OracleDataAdapter which seems to behave a little bit differently. It is also in a different class from the actual values which would be parameterised as can be seen here in the following sections of code:
我已经从 excamples 中看到了如何使用命令对象来使用查询。但是，我使用的是 OracleDataAdapter，它的行为似乎有点不同。它也与将被参数化的实际值属于不同的类，如下面的代码部分所示：
Controller code, where the three strings are filled from user inputted fields:
控制器代码，其中三个字符串是从用户输入的字段中填充的：
string usrName = mod.UserName;
string role = mod.Role;
string mod.actvInd;
string sql = "UPDATE LD_USER_ROLE" + " SET USERNAME='" + usrName + "', ROLE='" + role + "', ACTIVE_IND='" + actvInd + "' WHERE USER_ROLE_ID=" + id + "";
UserRoleDAL udl = new UserRoleDAL();
udl.ExecuteQuery(sql);
UserDAL class:
UserDAL 类：
public class UserRoleDAL
{
private OracleConnection conn;
public UserRoleDAL()
{
string oradb = ConfigurationManager.ConnectionStrings["db_dbConnectionString"].ConnectionString;
conn = new OracleConnection(oradb);
}
public void ExecuteQuery(string sql)
{
conn.Open();
OracleDataAdapter adapter = new OracleDataAdapter(sql, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);
conn.Close();
}
}
How do I use paramterized queries with the OracleDataAdapter object and will I have to merge the above classes or even pass the fields into the UserRoleDAL object in order to do this?
如何将参数化查询与 OracleDataAdapter 对象一起使用，我是否必须合并上述类，甚至将字段传递到 UserRoleDAL 对象中才能执行此操作？
回答by Adil Mammadov
You can use OracleDataAdapter with OracleCommand like this:
您可以像这样将 OracleDataAdapter 与 OracleCommand 一起使用：
public void ExecuteQuery(string usrName, string role, string activeation, int userId)
{
string sql = "UPDATE LD_USER_ROLE SET USERNAME=:usrName, ROLE=:role, ACTIVE_IND=:actvInd WHERE USER_ROLE_ID=:id";
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.BindByName = true;
cmd.Parameters.Add("usrName", usrName);
cmd.Parameters.Add("role", role);
cmd.Parameters.Add("actvInd", activeation);
cmd.Parameters.Add("id", userId);
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
}
