c# - 如何在将 Excel 导入 SQL Server 时插入日期

Question

我将一个 Excel 文件加载到 SQL Server 中并且工作正常，但我想在导入该文件时附加当前日期。这意味着每一行都会加载日期数据。所以每次我加载文件时，新数据应该采用当前日期，而旧数据仍然具有旧日期。

我该怎么做？

导入Excel文件的代码:

string ssqltable = comboBox1.GetItemText(comboBox1.SelectedItem);
string myexceldataquery = "select * from [" + ssqltable + "$]";

try
{
    OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + imagepath + ";Extended Properties='Excel 12.0 Xml; HDR=YES;IMEX=1;';");

    string ssqlconnectionstring = "Data Source=.;Initial Catalog=Bioxcell;Integrated Security=true";

    OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oconn);
    oconn.Open();

    SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);

    DataTable dt = new DataTable();
    dt.Load(oledbcmd.ExecuteReader());
    bulkcopy.DestinationTableName = ssqltable;

    for (int i = 0; i < dt.Columns.Count; i++)
    {
        bulkcopy.ColumnMappings.Add(i, i);
    }

    bulkcopy.WriteToServer(dt);
    oconn.Close();
}

这个用于插入日期但对我不起作用

if (ssqltable == "Overseas")
{
    conn.Open();

    SqlCommand sqlc = new SqlCommand("delete from Overseas where Bonus = 'Bonus'", conn);
    sqlc.ExecuteScalar();

    SqlCommand Update1 = new SqlCommand("Update Overseas set ID = 1 Where ProductCode = 9630", conn);
    Update1.ExecuteNonQuery();

    SqlCommand Update2 = new SqlCommand("Update Overseas set ID = 2 Where ProductCode = 9628", conn);
    Update2.ExecuteNonQuery();

    SqlCommand Update3 = new SqlCommand("Update Overseas set ID = 3 Where ProductCode = 9629", conn);
    Update3.ExecuteNonQuery();

    SqlCommand Update4 = new SqlCommand("Update Overseas set ID = 4 Where ProductCode = 9632", conn);
    Update4.ExecuteNonQuery();

    SqlCommand Update5 = new SqlCommand("Update Overseas set ID = 5 Where ProductCode = 9631", conn);
    Update5.ExecuteNonQuery();

    SqlCommand Update6 = new SqlCommand("insert into Overseas (Date) Values (GETDATE())", conn);
    Update6.ExecuteNonQuery();
}

没有错误，只插入了一个日期。我该如何解决这个问题？

Answer 1

如果您可以控制该表，则可以创建一个默认值为 GETDATE() 的新日期列:

ALTER TABLE <tablename>
ADD ROW_CREATE_TS DATETIME DEFAULT GETDATE()

引用:https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-default-values-for-columns