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.
Tom Brothers
Leave a Reply