C# OracleCommand SQL 参数绑定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11048910/
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
OracleCommand SQL Parameters Binding
提问by Ryan S
I have a problem with the binding of the below parameter. The connection works because I had tested it without using parameters. However, the value of the query before being executed is still using '@userName' instead of 'jsmith' for example.
我对以下参数的绑定有问题。连接有效，因为我在不使用参数的情况下对其进行了测试。但是，执行之前查询的值仍然使用“@userName”而不是例如“jsmith”。
What is the problem? Is this not the right way to go around binding?
问题是什么？这不是解决绑定的正确方法吗？
public static String GetFullName(String domainUser)
{
DataTable dT;
String fullName = "";
OracleConnection db = DatabaseAdapter.GetConn();
db.Open();
OracleCommand oraCommand = new OracleCommand("SELECT fullname FROM user_profile WHERE domain_user_name = '@userName'", db);
oraCommand.BindByName = true;
oraCommand.Parameters.Add(new OracleParameter("@userName", domainUser));
OracleDataReader oraReader = null;
oraReader = oraCommand.ExecuteReader();
if (oraReader.HasRows)
{
while (oraReader.Read())
{
fullName = oraReader.GetString(0);
}
}
else
{
return "No Rows Found";
}
oraReader.Close();
db.Close();
db.Dispose();
return fullName;
}
EDIT: I added @ to the parameter field name, but it still does not fix it.
编辑：我在参数字段名称中添加了 @，但它仍然没有修复它。
采纳答案by Habib
Remove single quotes around @username,and with respect to oracle use :with parameter name instead of @, like:
删除@username 周围的单引号，并且对于 oracle 使用:参数名称而不是@，例如：
OracleCommand oraCommand = new OracleCommand("SELECT fullname FROM sup_sys.user_profile
WHERE domain_user_name = :userName", db);
oraCommand.Parameters.Add(new OracleParameter("userName", domainUser));
Source: Using Parameters
来源：使用参数
回答by Xcalibur37
You need to use something like this:
你需要使用这样的东西：
OracleCommand oraCommand = new OracleCommand("SELECT fullname FROM sup_sys.user_profile
WHERE domain_user_name = :userName", db);
More can be found in this MSDN article: http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters%28v=vs.100%29.aspx
可以在这篇 MSDN 文章中找到更多信息：http: //msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters%28v=vs.100%29.aspx
It is advised you use the :character instead of @for Oracle.
对于 Oracle，建议您使用:字符而不是@。
回答by rehu
string strConn = "Data Source=ORCL134; User ID=user; Password=psd;";
System.Data.OracleClient.OracleConnection con = newSystem.Data.OracleClient.OracleConnection(strConn);
con.Open();
System.Data.OracleClient.OracleCommand Cmd =
new System.Data.OracleClient.OracleCommand(
"SELECT * FROM TBLE_Name WHERE ColumnName_year= :year", con);
//for oracle..it is :object_name and for sql it s @object_name
Cmd.Parameters.Add(new System.Data.OracleClient.OracleParameter("year", (txtFinYear.Text).ToString()));
System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(Cmd);
DataSet myDS = new DataSet();
da.Fill(myDS);
try
{
lblBatch.Text = "Batch Number is : " + Convert.ToString(myDS.Tables[0].Rows[0][19]);
lblBatch.ForeColor = System.Drawing.Color.Green;
lblBatch.Visible = true;
}
catch
{
lblBatch.Text = "No Data Found for the Year : " + txtFinYear.Text;
lblBatch.ForeColor = System.Drawing.Color.Red;
lblBatch.Visible = true;
}
da.Dispose();
con.Close();
回答by visar_uruqi
Oracle has a different syntax for parameters than Sql-Server. So use :instead of @
Oracle 的参数语法与 Sql-Server 不同。所以用:代替@
using(var con=new OracleConnection(connectionString))
{
con.open();
var sql = "insert into users values (:id,:name,:surname,:username)";
using(var cmd = new OracleCommand(sql,con)
{
OracleParameter[] parameters = new OracleParameter[] {
new OracleParameter("id",1234),
new OracleParameter("name","John"),
new OracleParameter("surname","Doe"),
new OracleParameter("username","johnd")
};
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
}
}
When using named parameters in an OracleCommand you must precede the parameter name with a colon (:).
在 OracleCommand 中使用命名参数时，必须在参数名称之前使用冒号 (:)。
http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters.aspx
http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters.aspx
回答by Dominus Farib
// PT: Eu tive este mesmo problema ao utilizar o Namespace Oracle.DataAccess.Client;
// PT: Eu tive este mesmo problema ao utilizar o Namespace Oracle.DataAccess.Client;
// ES: Tenía este mismo problema cuando se utiliza el Namespace Oracle.DataAccess.Client;
// ES: Tenía este mismo problema cuando se utiliza el Namespace Oracle.DataAccess.Client;
// EN: I had this same problem using the Oracle.DataAccess.Client Namespace;
// EN: 我在使用 Oracle.DataAccess.Client 命名空间时遇到了同样的问题；
// PT: Eu resolvi desta forma:
// PT: Eu resolvi desta forma:
// ES: He resuelto de esta manera:
// ES: He resuelto de esta manera:
// EN: I solved it this way
// CN: 我是这样解决的
using Oracle.DataAccess.Client;
使用 Oracle.DataAccess.Client;
string strConnection = ConfigurationManager.ConnectionStrings["oConnection"].ConnectionString;
dataConnection = new OracleConnectionStringBuilder(strConnection);
OracleConnection oConnection = new OracleConnection(dataConnection.ToString());
oConnection.Open();
OracleCommand tmpCommand = oConnection.CreateCommand();
tmpCommand.Parameters.Add("user", OracleDbType.Varchar2, txtUser.Text, ParameterDirection.Input);
tmpCommand.CommandText = "SELECT USER, PASS FROM TB_USERS WHERE USER = :1";
try
{
OracleDataReader tmpReader = tmpCommand.ExecuteReader(CommandBehavior.SingleRow);
if (tmpReader.HasRows)
{
// PT: IMPLEMENTE SEU CóDIGO
// ES: IMPLEMENTAR EL CóDIGO
// EN: IMPLEMENT YOUR CODE
}
}
catch(Exception e)
{
// PT: IMPLEMENTE SEU CóDIGO
// ES: IMPLEMENTAR EL CóDIGO
// EN: IMPLEMENT YOUR CODE
}
