Hoping someone can help me understand what I've got wrong here. I have confirmed the data is in the datatable, and that the SQL Server connection works. But I can't seem to push the data into the temp table (or at least I can't see it once I have).
I have looked at various post about using the bulkcopy function, but I must be missing something as it just does not appear to working.
The script runs with no errors, but I cannot see the temp table in the DB after the script runs.
Any help in working out where I've gone wrong is much appreciated.
DataTable dt = new DataTable("Employees");
dt.Columns.Add("Name");
dt.Columns.Add("LastName");
foreach (var employee in context.Employees.AsEnumerable())
{
dt.Rows.Add(employee.Name, employee.LastName);
}
Console.WriteLine(dt.Rows[0]["Name"]);
Console.WriteLine("Loading data into SQL");
using (SqlConnection conn = new SqlConnection("user id=<ID>;" +
"password=<Password>;server=<Server>;" +
"Trusted_Connection=yes;" +
"database=<DB>; " +
"connection timeout=30"))
{
using (SqlCommand command = new SqlCommand("", conn))
{
try
{
conn.Open();
// Creating temp table on database
command.CommandText = "CREATE TABLE ##TmpTable(...)";
command.ExecuteNonQuery();
// Bulk insert into temp table
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
{
bulkcopy.BulkCopyTimeout = 660;
bulkcopy.DestinationTableName = "##TmpTable";
bulkcopy.WriteToServer(dt);
bulkcopy.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("Bulk load to SQL failed");// Handle exception properly
}
finally
{
conn.Close();
Console.WriteLine("table loaded into SQL");
}
}
}
I faced the similar problem during WPF programming...You need to create a mapping between source datatable's columns and destination table's columns. I don't know exactly why it needs to be done after mentioning the destination's table name which should be enough...however the following code worked for me...
using (var command = new SqlCommand("SELECT COUNT(*) FROM Device_GameDevice", sqlConn, transaction) { CommandType = CommandType.Text })
{
SqlBulkCopyColumnMapping mapstep = new SqlBulkCopyColumnMapping("Message", "Message");
SqlBulkCopyColumnMapping maptran = new SqlBulkCopyColumnMapping("DeviceName", "DeviceName");
SqlBulkCopyColumnMapping mapstt = new SqlBulkCopyColumnMapping("dt_datetime", "dt_datetime");
SqlBulkCopyColumnMapping mapfunc = new SqlBulkCopyColumnMapping("GameName", "GameName");
sqlBulk.ColumnMappings.Add(mapstep);
sqlBulk.ColumnMappings.Add(maptran);
sqlBulk.ColumnMappings.Add(mapstt);
sqlBulk.ColumnMappings.Add(mapfunc);
sqlBulk.DestinationTableName ="Device_GameDevice";
sqlBulk.WriteToServer(resultantDataTableForMaxDate);
command.ExecuteNonQuery();
transaction.Commit();
}
Hope this helps...:)