如何使用 C# 在 Oracle 关系数据库中插入日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/698339/
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
How to insert date in an Oracle relational database using C#
提问by Gold
I have Date Var in Oracle, and I try to insert Data from my C# program
我在 Oracle 中有日期变量，我尝试从我的 C# 程序中插入数据
sql = "insert into Table(MyDate) values (" + convert.todatetime(txt) + ")";
I get an Error, what can i do ?
我收到一个错误，我该怎么办？
回答by Otávio Décio
Use parameters. It's going to solve your problem and prevent injection.
使用参数。它将解决您的问题并防止注射。
回答by Chad Birch
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'));
回答by jle
Try using DateTime.TryParse(text) or DateTime.Parse(text)
尝试使用 DateTime.TryParse(text) 或 DateTime.Parse(text)
回答by Adam Fyles
cmd.CommandText = "INSERT INTO Table (myDate)VALUES(:dateParam)";
cmd.Parameters.Add(new OracleParameter("dateParam", OracleDbType.Date))
.Value = DateTime.Now;
cmd.ExecuteNonQuery();
回答by tuinstoel
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注入攻击。 ”
回答by vapcguy
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 DateTimevariable:
将日期解析为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 OracleConnectionas connection, set up your command and add your parameter:
假设您已经有一个OracleConnectionas connection，请设置您的命令并添加您的参数：
OracleCommand cmd = new OracleCommand(sql, connection);
cmd.Parameters.Add(param);
Execute:
执行：
cmd.ExecuteNonQuery();
Do NOTwaste your time on any of the TO_DATEnonsense. 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_DATEexpects and that you assign within the TO_DATEconstruct 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 DateTimevariable and assigning that to an OracleParameterwith an OracleDbTypeof OracleDbType.Date, assuming you have a DATEfield in your table and can parse txtinto a DateTimevariable, 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一个OracleDbTypeof是最好和最简单的。 OracleDbType.DateDATEtxtDateTime
回答by EnterTheBlackDragon
Easiest way possible:
最简单的方法：
DateTime inputDate = Convert.ToDateTime("01/01/2019"); //<---Input Sample Date in format
string queryParameters = String.Format("SELECT * FROM TABLE WHERE DATE = '{0}')", inputDate.ToString("dd-MMM-yyyy")); //<-- Converts System.DateTime into Oracle DateTime
//Forget looking anywhere else for an answer, copy and paste and reform this very code
//and see the results
