C# bulkcopy to SQL Server

c# sql sqlbulkcopy sql-server

Question

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");
         }
     }
}
1
0
10/30/2017 5:25:24 AM

Popular Answer

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...:)

1
10/30/2017 5:46:48 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow