Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am trying to store data from datatable to database using bulkcopy. It is working fine when all data in datatable are in correct format. If any records is incorrect such as PersonName contain any Number value that time I want to remove that records from datatable and continue with bulkcopy. Please help me..

My code is this....


C#
private void btnSQLBulkCopyInsert_Click(object sender, EventArgs e)
{
   // Get the DataTable
   DataTable dtInsertRows = GetDataTable();

   using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
   {
        sbc.DestinationTableName = "Person";

        // Number of records to be processed in one go
        sbc.BatchSize = 2;

        // Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table
        sbc.ColumnMappings.Add("PersonId", "PersonId");
        sbc.ColumnMappings.Add("PersonName", "PersonName");

        // Number of records after which client has to be notified about its status
        sbc.NotifyAfter = dtInsertRows.Rows.Count;

        // Event that gets fired when NotifyAfter number of records are processed.
        sbc.SqlRowsCopied+=new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);

        // Finally write to server
        sbc.WriteToServer(dtInsertRows);
        sbc.Close();
    }
}

void sbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
    MessageBox.Show("Number of records affected : " + e.RowsCopied.ToString());
}
Posted
Comments
Maciej Los 27-May-13 2:59am    
What have tried?
Where are you stuck?
Varun Sareen 27-May-13 3:19am    
Dear Friend,

Before sending the code to bulk copy you can loop on the datatable rows for checking the Person column for numeric. if the person name is in numeric format then you can delete the concerned row from the datatable.

Regards

Varun Sareen

1 solution

another poor bugger trying to do the transforms in the load process!

Change you approach to loading data use ELT, load all your data into a staging table that is identical to the bulkcopy source and use a stored procedure to do the tansforms! This works and when you have errors or crappy data or you screw up the mapping you can debug the bloody thing and repeat it again and again until you get it right!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900