不知选啥就选C

参考：
SqlBulkCopy 类
http://raylei.cn/index.php/archives/74/
https://www.cxybb.com/article/weixin_30333885/96361368

问题

• 来自数据源的 Decimal 类型的给定值不能转换为指定目标列的类型 datetime
  实际情况可能很复杂，使用的是泛型进行实体类转DataTable,可能会出现各种类型错误提示。
  自己使用的是调用Copy()方法。
  demo:

  demo.cs

  var TableStruct = dal.GetTableStruct();
  DataTable dataTable = TableStruct.Clone();
  foreach (var item in oD.ToList<StdYpkcday>())
  {
      DataRow dataRow = dataTable.NewRow();
      dataRow["id"] = DBNull.Value;//自增主键。赋值DBNull.Value
      dataRow["code"] = item.Hoscode;
      if (item.Gqsj == null )
      {
          dataRow["time"] = DBNull.Value;
      }
      else
      {
          dataRow["time"] = item.Gqsj;
      }
      dataTable.Rows.Add(dataRow);
  }

  参考：https://stackoverflow.com/questions/18140012/sqlbulkcopy-the-given-value-of-type-string-from-the-data-source-cannot-be-conv

Demo

删除并批量插入.cs

/// <summary>
/// 删除并批量插入
/// </summary>
/// <param name="_Type"></param>
/// <param name="TableName">批量插入数据的表名</param>
/// <param name="Dt">批量插入的数据</param>
/// <param name="SqlDeleteText">删除的sql语句</param>
public  void DeleteAndBulkCopy(string Type,string TableName, DataTable Dt, string SqlDeleteText,bool isIncreaPK)
{
    using (SqlConnection conn = new SqlConnection(DBUtility.DbCommon.GetConnectString(Type)))
    {
        conn.Open();
        using (SqlTransaction transaction = conn.BeginTransaction())
        {
            SqlCommand sqlcom = conn.CreateCommand();
            sqlcom.Transaction = transaction;
            sqlcom.CommandText = SqlDeleteText;
            var BulkCopyOptions = SqlBulkCopyOptions.KeepIdentity;
            //自增主键，使用系统生成主键
            if (isIncreaPK)
            {
                BulkCopyOptions = SqlBulkCopyOptions.Default;
            }
            using (SqlBulkCopy bulk = new SqlBulkCopy(conn, BulkCopyOptions, transaction))
            {
                if (Dt.Rows.Count > 0)
                {
                    for (int i = 0; i < Dt.Columns.Count; i++)
                    {
                        bulk.ColumnMappings.Add(Dt.Columns[i].ColumnName, Dt.Columns[i].ColumnName);
                    }
                    bulk.BatchSize = Dt.Rows.Count;
                    bulk.DestinationTableName = TableName;
                }
                try
                {
                    sqlcom.ExecuteNonQuery();

                    bulk.WriteToServer(Dt);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                }
                finally
                {
                    bulk.Close();
                    conn.Close();
                }
            }
        }
    }

}

批量更新.cs

#region 批量更新
/// <summary>
/// 批量更新
/// </summary>
/// <param name="dataTable"></param>
/// <param name="crateTemplateSql">
/// eg:
/// [xxx] varchar(255)  NOT NULL,[xxx] varchar(10)  NOT NULL
/// </param>
/// <param name="updateSql">
/// eg:
/// UPDATE bulktest  set bulktest.name=Temp.tempname 
/// FROM bulktest INNER JOIN #TmpTable Temp 
/// ON Temp.temprdn = bulktest.rdn  ; 
/// DROP TABLE #TmpTable
/// </param>
public void SqlBulkCopyUpdate(DataTable dataTable, string crateTemplateSql, string updateSql)
{
    using (SqlConnection conn = new SqlConnection(DBUtility.DbCommon.GetConnectString(DBType)))
    {
        using (var command = new SqlCommand("", conn))
        {
            try
            {
                conn.Open();
                //数据库并创建一个临时表来保存数据表的数据
                command.CommandText = $"  CREATE TABLE #TmpTable ({crateTemplateSql})";
                command.ExecuteNonQuery();

                //使用SqlBulkCopy 加载数据到临时表中
                using (var bulkCopy = new SqlBulkCopy(conn))
                {
                    foreach (DataColumn dcPrepped in dataTable.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);
                    }

                    bulkCopy.BulkCopyTimeout = 660;
                    bulkCopy.DestinationTableName = "#TmpTable";
                    bulkCopy.WriteToServer(dataTable);
                    bulkCopy.Close();
                }

                // 执行Command命令 使用临时表的数据去更新目标表中的数据  然后删除临时表
                command.CommandTimeout = 300;
                command.CommandText = updateSql;
                int rows = command.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}

#endregion