如何在将Excel导入Server时插入日期

Question

我将一个Excel文件加载到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