EntitySpaces Dynamic Bulk Insertion Mechanism

In my previous article, I wrote about the EntitySpaces basic data acquisition and manipulation functions. Now I am coming up with bulk data copy to SQL Server with EntitySpaces.

In this article, I will show how we can push our data to database as a bulk copy with single connection and in an efficient manner with EntitySpaces. EntitySpaces gives a way to invoke the SqlBulkCopy class's functionality of .NET Framework with the EntitySpaces.Core.esEntity object. EntitySpaces provides a mechanism through which we can push the data to any of esEntity type object in database. In the below code snippet, I wrote a method that can copy your data to database in any of your desired tables.

void DumpDataToDB(ref DataTable dTable, EntitySpaces.Core.esEntity DALObj)
 {
   try
    {
         //To Initialize the connection object
         using (System.Data.SqlClient.SqlConnection conn = 
           newSystem.Data.SqlClient.SqlConnection
          (EntitySpaces.Interfaces.esConfigSettings.ConnectionInfo.Connections[0].ConnectionString))
      {
          conn.Open();

          // To Initialize the SqlTransaction object to implement the TCL mechanism to avoid 
          // the data inconsistency and to enforce data integrity constraints.
          System.Data.SqlClient.SqlTransactionmyTransaction = conn.BeginTransaction();
      
           //To Initialize the SqlBulkCopy object with Connection and Transaction objects
       using (System.Data.SqlClient.SqlBulkCopy sbc = newSystem.Data.SqlClient.SqlBulkCopy(conn, 
           System.Data.SqlClient.SqlBulkCopyOptions.Default, myTransaction))
       {  
           EntitySpaces.Interfaces.esProviderSpecificMetadatameta = 
             DALObj.es.Meta.GetProviderMetadata
            (EntitySpaces.Interfaces.esConfigSettings.ConnectionInfo.Connections[0].ProviderMetadataKey);
       
                   //Get the Table Name from EntitySpace meta data
           sbc.DestinationTableName = string.Format("{0}.{1}.{2}", 
                                         meta.Catalog, meta.Schema, meta.Destination);

           //Set the transaction time out
           sbc.BulkCopyTimeout = 60 * 2; //2 Minutes
           sbc.WriteToServer(dTable);
           sbc.Close();
           myTransaction.Commit();
          }
         }
       }
       catch (Exceptionex)
       {
         throw ex;
       }
  }

Calling

DumpDataToDB(ref dTUser, new User());

In the above code snippet, the method takes two parameters, a referenced DataTable and an esEntity Type object. This dynamic method decides from esEntity object in which table of database to copy the data.

Prerequisites and Limitations

• DataTable must be initialized and filled with appropriate data.
• The columns sequence of DataTable must be followed by Database Table Columns sequence.
• Triggers are not invoked by default with SqlBulkCopy.

Conclusion

The bulk copy mechanism helps improve the optimization of OLTP in a large amount of transactions environment. Bulk copy mechanism allows the developer to cater to the database level optimization in object oriented paradigm.