none
Bulk copy fails RRS feed

  • Question

  • Hi All, in my code below I never get any results from my datatable even though its filled the datagrid1view, what have I missed?

    MessageBox.Show(dt.Rows.Count.ToString());
                            var con = new SqlConnection(@"Data Source=DOCKETSYS\SQLEXPRESS;Initial Catalog=SLADB;Persist Security Info=True;User ID=XXX;Password=XXX;Connection Timeout=0");
                            con.Open();
                            new SqlCommand("TRUNCATE TABLE ProdDataTB", con).ExecuteNonQuery();
                            var commandRowCount = new SqlCommand("SELECT COUNT(*) FROM " + "dbo.ProdDataTB;", con);
                            long countStart = Convert.ToInt32(commandRowCount.ExecuteScalar());
                            MessageBox.Show(@"Starting row count = {0}", countStart.ToString());
                            using (var bulkCopy = new SqlBulkCopy(con))
                            {
                                bulkCopy.DestinationTableName ="dbo.ProdDataTB";
    
                                try
                                {
                                    // Write from the source to the destination.
                                    var tr = (DataTable)dataGridView1.DataSource;
                                    
                                    bulkCopy.WriteToServer(tr);
                                }
                                catch (Exception ex)
                                {
                                    Console.WriteLine(ex.Message);
                                }
                            }
                            long countEnd = Convert.ToInt32(commandRowCount.ExecuteScalar());
                            MessageBox.Show(@"Ending row count = {0}", countEnd.ToString());

    Jay
    • Edited by Alexander Sun Thursday, November 22, 2012 7:01 AM Edit
    Friday, November 9, 2012 12:20 PM

Answers

  • You should be using the DataTable that was created and bound to the DataGridView when it was populated with data. There is no need to reference the DataGridView DataSource.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Alexander Sun Thursday, November 22, 2012 7:00 AM
    Friday, November 9, 2012 1:52 PM

All replies

  • You should be using the DataTable that was created and bound to the DataGridView when it was populated with data. There is no need to reference the DataGridView DataSource.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Alexander Sun Thursday, November 22, 2012 7:00 AM
    Friday, November 9, 2012 1:52 PM
  • As with bulk copies you dont generate transactions logs, you would not have any count of how many transactions you have generated. But you can use the NotifyAfter property with the total records you spect to insert and then create a delegate with SqlRowsCopied.

    Saturday, November 10, 2012 5:01 PM