Bulk insert CRM Data into SQL table using sqlbulkcopy

This blog will explain how we can bulk insert any source data [e.g. MS CRM data] into SQL table using C# code.

We will follow below steps:

1.  We will create DataTable in C# with same table name and column names of SQL table
2.  Retrieve source data that needs to be retrieved
3. Add retrieved data from source into DataTable
4. Use SQL Connection and sqlBulkCopy to bulk insert source data into SQL

First we will create DataTable with same column name available in SQL table

For creating DataTable use below syntax:

public static DataTable ActionableAudit()
{
   DataTable actionableAudit;
   // Create a new DataTable.
   actionableAudit = new DataTable("ActionableAudit");
   actionableAudit.Columns.Add(new DataColumn("ChangedOn", typeof(DateTime)));
   actionableAudit.Columns.Add(new DataColumn("CreatedBy", typeof(string)));
   actionableAudit.Columns.Add(new DataColumn("CreatedById", typeof(Guid)));

   return actionableAudit
            
}

After creating DataTable move CRM data one by one to DataTable and then use sqlbulkcopy to insert rows in sql

DataTable actionableAuditTable = ActionableAudit(); //this will create datatable
actionableAuditTable.Rows.Add(ChangedOn, CreatedBy, CreatedById); //Add crm retrived data in dataTable in same sequence like we folllowed during creation of datatable

InsertRowsIntoActionableAuditDB(actionableAuditTable, connection);


//below method will move data of dataTable in sql table

 public static void InsertRowsIntoActionableAuditDB(string connectionString, DataTable actionableAuditTable, SqlConnection connection)
 {
    /pass connection string
    try
    {
      SqlBulkCopy sqlbc = new SqlBulkCopy(connection);
      sqlbc.DestinationTableName = "ActionableAudit"; //make sure tablename is same as sql table name
      SqlMappingWithDatatable(actionableAuditTable, sqlbc);
      sqlbc.WriteToServer(actionableAuditTable);
      Console.WriteLine("Record inserted Sucessfully!!!");             
     }
     catch (Exception exception)
     {
       Console.WriteLine("Error Occured : " + exception.Message);               
     }
 }
               

If you want to update data, then you can write stored procedure and execute that stored procedure using c#.

https://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp