I have Date Var in Oracle, and I try to insert Data from my C# program
我在Oracle中有Date Var,我尝试从C#程序中插入数据
sql = "insert into Table(MyDate) values (" + convert.todatetime(txt) + ")";
I get an Error, what can i do ?
我得到一个错误,我该怎么办?
6 个解决方案
#1
cmd.CommandText = "INSERT INTO Table (myDate)VALUES(:dateParam)";
cmd.Parameters.Add(new OracleParameter("dateParam", OracleDbType.Date))
.Value = DateTime.Now;
cmd.ExecuteNonQuery();
#2
Use parameters. It's going to solve your problem and prevent injection.
使用参数。它将解决您的问题并防止注射。
#3
Oracle expects it to be an actual date value, not just a string that looks like a date. You have to use the TO_DATE() function to explain how your string is formatted, something like this:
Oracle期望它是一个实际的日期值,而不仅仅是一个看起来像日期的字符串。您必须使用TO_DATE()函数来解释字符串的格式,如下所示:
INSERT INTO Table (myDate)
VALUES(TO_DATE('2009-03-30 12:30:00', 'YYYY-MM-DD HH:mi:ss'));
#4
Try using DateTime.TryParse(text) or DateTime.Parse(text)
尝试使用DateTime.TryParse(text)或DateTime.Parse(text)
#5
Please bind your variables (like ocdecio tells) ! Not only does it prevent sql injection it is also much faster. Especially in a multi concurrency situation. Read for example here: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28844/building_odp.htm#CEGCGDAB .
请绑定您的变量(如ocdecio告诉)!它不仅可以防止sql注入,而且速度也快得多。特别是在多并发情况下。例如,请阅读http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28844/building_odp.htm#CEGCGDAB。
"Bind variables are placeholders inside a SQL statement. When a database receives a SQL statement, it determines if the statement has already been executed and stored in memory. If the statement does exist in memory, Oracle Database can reuse it and skip the task of parsing and optimizing the statement. Using bind variables makes the statement reusable with different input values. Using bind variables also improves query performance in the database, eliminates the need for special handling of literal quotation marks in the input, and protects against SQL injection attacks."
“绑定变量是SQL语句中的占位符。当数据库收到SQL语句时,它确定该语句是否已经执行并存储在内存中。如果该语句确实存在于内存中,Oracle数据库可以重用它并跳过任务解析和优化语句。使用绑定变量使语句可以重用不同的输入值。使用绑定变量还可以提高数据库中的查询性能,无需在输入中特殊处理文字引号,并防止SQL注入攻击。 “
#6
I know this was a poorly asked question, but I saw some poor answers when I had the same question and ran into this. This is how I solved it, and I'll answer using the OP's context:
我知道这是一个问题很少的问题,但是当我遇到同样的问题时,我看到了一些不好的答案。这就是我解决它的方法,我将使用OP的上下文回答:
Parse the date in to a DateTime variable:
将日期解析为DateTime变量:
DateTime myDate = DateTime.Parse(txt);
Then parameterize your query:
然后参数化您的查询:
sql = "insert into Table(MyDate) values (:myDate)";
Set up an OracleParameter:
设置OracleParameter:
OracleParameter param = new OracleParameter();
param.ParameterName = "myDate";
param.OracleDbType = OracleDbType.Date;
param.Value = myDate;
Assuming you already have an OracleConnection as connection, set up your command and add your parameter:
假设您已经将OracleConnection作为连接,请设置您的命令并添加您的参数:
OracleCommand cmd = new OracleCommand(sql, connection);
cmd.Parameters.Add(param);
Execute:
cmd.ExecuteNonQuery();
Do NOT waste your time on any of the TO_DATE nonsense. This is for when you are adding something using SQL*Plus or Oracle SQL Developer directly, or MAYBE where you want to send in a STRING variable's value (not a DateTime variable) in the EXACT format that TO_DATE expects and that you assign within the TO_DATE construct within your query or a stored procedure (i.e. to_date('2013-05-13 12:13:14', 'YYYY-MM-DD HH24:MI:SS'). Using a DateTime variable and assigning that to an OracleParameter with an OracleDbType of OracleDbType.Date, assuming you have a DATE field in your table and can parse txt into a DateTime variable, however, is best and easiest.
不要在任何TO_DATE废话上浪费你的时间。这适用于直接使用SQL * Plus或Oracle SQL Developer添加内容的情况,或者您希望以TO_DATE期望的EXACT格式发送STRING变量的值(不是DateTime变量)以及在TO_DATE中分配的内容。在您的查询或存储过程中构造(即to_date('2013-05-13 12:13:14','YYYY-MM-DD HH24:MI:SS')。使用DateTime变量并将其分配给OracleParameter OracleDbType.Date的OracleDbType,假设您的表中有DATE字段并且可以将txt解析为DateTime变量,但这是最好也是最简单的。
#1
cmd.CommandText = "INSERT INTO Table (myDate)VALUES(:dateParam)";
cmd.Parameters.Add(new OracleParameter("dateParam", OracleDbType.Date))
.Value = DateTime.Now;
cmd.ExecuteNonQuery();
#2
Use parameters. It's going to solve your problem and prevent injection.
使用参数。它将解决您的问题并防止注射。
#3
Oracle expects it to be an actual date value, not just a string that looks like a date. You have to use the TO_DATE() function to explain how your string is formatted, something like this:
Oracle期望它是一个实际的日期值,而不仅仅是一个看起来像日期的字符串。您必须使用TO_DATE()函数来解释字符串的格式,如下所示:
INSERT INTO Table (myDate)
VALUES(TO_DATE('2009-03-30 12:30:00', 'YYYY-MM-DD HH:mi:ss'));
#4
Try using DateTime.TryParse(text) or DateTime.Parse(text)
尝试使用DateTime.TryParse(text)或DateTime.Parse(text)
#5
Please bind your variables (like ocdecio tells) ! Not only does it prevent sql injection it is also much faster. Especially in a multi concurrency situation. Read for example here: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28844/building_odp.htm#CEGCGDAB .
请绑定您的变量(如ocdecio告诉)!它不仅可以防止sql注入,而且速度也快得多。特别是在多并发情况下。例如,请阅读http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28844/building_odp.htm#CEGCGDAB。
"Bind variables are placeholders inside a SQL statement. When a database receives a SQL statement, it determines if the statement has already been executed and stored in memory. If the statement does exist in memory, Oracle Database can reuse it and skip the task of parsing and optimizing the statement. Using bind variables makes the statement reusable with different input values. Using bind variables also improves query performance in the database, eliminates the need for special handling of literal quotation marks in the input, and protects against SQL injection attacks."
“绑定变量是SQL语句中的占位符。当数据库收到SQL语句时,它确定该语句是否已经执行并存储在内存中。如果该语句确实存在于内存中,Oracle数据库可以重用它并跳过任务解析和优化语句。使用绑定变量使语句可以重用不同的输入值。使用绑定变量还可以提高数据库中的查询性能,无需在输入中特殊处理文字引号,并防止SQL注入攻击。 “
#6
I know this was a poorly asked question, but I saw some poor answers when I had the same question and ran into this. This is how I solved it, and I'll answer using the OP's context:
我知道这是一个问题很少的问题,但是当我遇到同样的问题时,我看到了一些不好的答案。这就是我解决它的方法,我将使用OP的上下文回答:
Parse the date in to a DateTime variable:
将日期解析为DateTime变量:
DateTime myDate = DateTime.Parse(txt);
Then parameterize your query:
然后参数化您的查询:
sql = "insert into Table(MyDate) values (:myDate)";
Set up an OracleParameter:
设置OracleParameter:
OracleParameter param = new OracleParameter();
param.ParameterName = "myDate";
param.OracleDbType = OracleDbType.Date;
param.Value = myDate;
Assuming you already have an OracleConnection as connection, set up your command and add your parameter:
假设您已经将OracleConnection作为连接,请设置您的命令并添加您的参数:
OracleCommand cmd = new OracleCommand(sql, connection);
cmd.Parameters.Add(param);
Execute:
cmd.ExecuteNonQuery();
Do NOT waste your time on any of the TO_DATE nonsense. This is for when you are adding something using SQL*Plus or Oracle SQL Developer directly, or MAYBE where you want to send in a STRING variable's value (not a DateTime variable) in the EXACT format that TO_DATE expects and that you assign within the TO_DATE construct within your query or a stored procedure (i.e. to_date('2013-05-13 12:13:14', 'YYYY-MM-DD HH24:MI:SS'). Using a DateTime variable and assigning that to an OracleParameter with an OracleDbType of OracleDbType.Date, assuming you have a DATE field in your table and can parse txt into a DateTime variable, however, is best and easiest.
不要在任何TO_DATE废话上浪费你的时间。这适用于直接使用SQL * Plus或Oracle SQL Developer添加内容的情况,或者您希望以TO_DATE期望的EXACT格式发送STRING变量的值(不是DateTime变量)以及在TO_DATE中分配的内容。在您的查询或存储过程中构造(即to_date('2013-05-13 12:13:14','YYYY-MM-DD HH24:MI:SS')。使用DateTime变量并将其分配给OracleParameter OracleDbType.Date的OracleDbType,假设您的表中有DATE字段并且可以将txt解析为DateTime变量,但这是最好也是最简单的。