如何在sql server数据库中插入系统日期时间 [英] How to insert system datetime in sql server database

问题描述

您好我已经写了一个程序在数据库中插入一些值，但我在日期得到sql formate异常。以下是我的计划

Hi I have written a program to insert some values in database but i am getting sql formate exception in date. below is my program

enum RefundStatus
   {
       Dispute = 1,
       DisputeRes = 2
   };
   class Program
   {
       public void InsertRecord(int i)
       {
          int RFStatus= Convert.ToInt16((i%2 ==0)?RefundStatus.Dispute:RefundStatus.DisputeRes);
          Random rnd = new Random();
           long XUID = rnd.Next(1,100);
           DateTime myDateTime = DateTime.Now;


           SqlConnection cnn = new SqlConnection("Data Source=XBLVMTST005;Initial Catalog=RefundsDB;Integrated Security=SSPI");
           cnn.Open();

           String str = string.Format(@"insert into [dbo].[CustomerInfo] (UserXUID,RefundType,RefundDate,
           RefundDeniedReason,DisputeCustData,CustomerEmail,CustomerName,Currency,Country,
           Refund,FraudRuleStatus,RefundStatus,DisputeStatus,AllowComments,LastUpdatedTime)
           values ('{0}','{1}','{2},{3}','{4}','{5},{6}','{7}','{8},{9}','{10}','{11},{12}','{13}','{14}'",
 XUID, "", "", "", "", "", "", "", "", "", "", RFStatus, "", "", myDateTime);
           SqlCommand cmd = new SqlCommand(str, cnn);

           cmd.ExecuteNonQuery();
       }

       static void Main(string[] args)
       {
           Program obj = new Program();
           {
           for (int i = 0; i < 2; i++)
               obj.InsertRecord(i);
               Thread.Sleep(2000);
           }
       }
   }

我来这里了



SQLException - '6/8/2014 7:14:56 AM'附近的语法不正确。

Here I am getting

SQLException-Incorrect syntax near '6/8/2014 7:14:56 AM'.

推荐答案

你最好在SQL语句中使用参数

示例代码：

You better use parameters in SQL statement
sample code:

SqlCommand cmd = new SqlCommand("insert into  [dbo].[CustomerInfo] (UserXUID, LastUpdatedTime) values (@UserXUID, @LastUpdatedTime)");
cmd.Parameters.AddWithValue("@UserXUID", XUID);
cmd.Parameters.AddWithValue("@LastUpdatedTime", DateTime.Now);

这也可以帮助你免受SQL注入攻击。



很少有其他东西，你在循环中调用insert但没有正确处理关闭连接。你最好使用使用块，如下所示，它将关闭/处置对象，即使你在进程的中间得到异常。设置空值时;使用 DBNull.Value

This also will save you from SQL injection attacks.

Few additional things, you are calling insert in a loop but not correctly handling the closing of the connection. you better use using block as below, it will close/dispose the object even you get exception middle of the process. When you set null values; use DBNull.Value

using(SqlConnection cnn = new SqlConnection(yourconnectionString))
{
   cnn.Open();
   using(SqlCommand cmd = new SqlCommand("insert into  [dbo].[CustomerInfo] (UserXUID,RefundType, LastUpdatedTime) values (@UserXUID,@RefundType, @LastUpdatedTime)"))
   {
       cmd.Parameters.AddWithValue("@UserXUID", XUID);
       cmd.Parameters.AddWithValue("@RefundType", DBNull.Value); //when you need null value
       cmd.Parameters.AddWithValue("@LastUpdatedTime", DateTime.Now);
       cmd.ExecuteNonQuery();
   }
}

这篇关于如何在sql server数据库中插入系统日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！