Random Dev Notes

June 2, 2013

Bulk insert into a visual foxPro table from a DataTable

Filed under: .Net Development — Tags: — Tom Brothers @ 9:07 am

In one of the earlier versions of the Visual FoxPro Entity Framework Provider I ran into a nasty performance problem when generating the schema information for the Entity Framework Designer.  The process that was used to get the schema information involved using the connection’s GetSchema method and then dumping that into a VFP table – for re-querying later.  In my first attempt I was creating the tables and then inserting each record.  What I found was inserting each record was very time consuming.  I later realized that I could pass all the data to VFP as xml and convert that to a cursor so that the cursor could be used for inserting the data.  This made a huge difference in performance.  This blog entry shows how you can do a bulk insert.

Here is an example on doing a Bulk Insert:
   1:  // Get the Northwind OrderDetails data.
   2:  var orderDetails = GetDataTable("OrderDetails");
   3:   
   4:  // Convert the DataTable to an XmlToCursor formatted xml string.
   5:  // ToXmlToCursorFormattedXml is an DataTable extension method in the VfpClient namespace.
   6:  var xml = orderDetails.ToXmlToCursorFormattedXml();
   7:   
   8:  using (var connection = new VfpConnection(ConfigurationManager.ConnectionStrings["FreeTables"].ConnectionString)) {
   9:      connection.Open();
  10:   
  11:      // Create cursor using XmlToCursor with the DataTable xml.
  12:      using (var command = connection.CreateCommand()) {
  13:          command.CommandType = CommandType.StoredProcedure;
  14:          command.CommandText = "XmlToCursor";
  15:          command.Parameters.Add(new VfpParameter("xml", xml));
  16:          command.Parameters.Add(new VfpParameter("cursor", "curXmlTemp"));
  17:          command.ExecuteNonQuery();
  18:      }
  19:   
  20:      // Use the cursor to insert records into the destination table.
  21:      using (var command = connection.CreateCommand()) {
  22:          command.CommandText = "INSERT INTO 'OrderDetailsArchive' SELECT * FROM curXmlTemp";
  23:          command.ExecuteNonQuery();
  24:      }
  25:   
  26:      connection.Close();
  27:  }


* Example code can be found in Visual FoxPro .Net Examples.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: