Click here to Skip to main content
13,096,798 members (82,596 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

4.3K views
2 bookmarked
Posted 28 Jan 2014

EntitySpaces Dynamic Bulk Insertion Mechanism

, 28 Jan 2014
Rate this:
Please Sign up or sign in to vote.
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.

License

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

Share

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.

You may also be interested in...

Comments and Discussions

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