Click here to Skip to main content
Click here to Skip to main content

EntitySpaces Dynamic Bulk Insertion Mechanism

, 28 Jan 2014
Rate this:
Please Sign up or sign in to vote.
In this article I will show how we can push our data to database as a bulk copy with single connection and efficient manner with EntitySpaces. EntitySpaces gives a way to invoke the SqlBulkCopy class's functionality of .NET Framework with the EntitySpaces.Core.esEntity object.

In my previous article I wrote about the EntitySpaces basic data aquisition and manipulation functions. Now I am come 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 efficient manner with EntitySpaces. EntitySpaces gives a way to invoke the SqlBulkCopy class's functionality of .NET Framework with the EntitySpaces.Core.esEntity object. EntitySpaes provides a mechanism through which we can push the data to any of esEntity type object in database. In below code snippet I wrote a method that can copy your data to database in any of your desired table.

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 enforece data integrity constraints.
          System.Data.SqlClient.SqlTransactionmyTransaction = conn.BeginTransaction();
      
           //To Initialize the SqlBulkCopy object with Connection and Transacion 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 above code snippet the method takes two parameters, a referenced DataTable and a esEntity Type object. This dynamic method decides from esEntity object to in which table of database to copy the data.

Prerequisites and Limitations

  • DataTable must be intilized and filled with appropriate data.
  • The columns sequence of DataTable must be followed by Database Table Columns sequence.
  • Triggers are not invoken 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 developer to cater the database level optimization in object oriented paradigm.

License

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

About the Author

Kashif Akhter
Team Leader
Pakistan Pakistan
A computer software development professional with a proven track record of extensive experience of enterprise software development and building the manageable, scalable, and robust enterprise software architectures.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 28 Jan 2014
Article Copyright 2014 by Kashif Akhter
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid