inserting buld records to SQL table from FoxPro table

[mak101]

I have couple of tables on SQL server, and I need to insert records from Foxpro tables. Every month I need to delete old records from SQL tables and inserting new records from FoxPro tables. I don't want to do it with SPT, SPT requires looping for each record and I have over a million records and it is time consuming. Also I want to reset autoid fields to 1 on SQL tables everytime I delete old records.

I think remote view is the solution but I don't know how to use it and how can I accomplish autoid reset.

I would appreciate if someone can help me with sample code.

Thanks.

 

[cbasoz]

SPT doesn't require looping each record but it is still slow. There are other ways and here are the ones I find fast:

1) Create a linked server and use OpenQuery() to get data from VFP tables. Something like this:

insert into XX ( field1, field2 ... fieldN )
from OpenQuery(VFPLINKEDSERVER,
'Select f1, f2, .. fN from myVFPTable')

You can execute this from SSMS or via SPT. ADO.

2) Where applicaple copy data to text file and use SQL server bulk copy.

3) (This one I found to be fastest and does millions of records upload in a few minutes - depends on factors, bottomline it is fastest I have ever encountered)
Use .Net SqlBulkCopy class. You can find code on

http://www.foxite.com

and

http://www.fox4um.com

(Turkish fox site) if you search SqlBulkCopy class.

Cetin Basoz
MS Foxpro MVP, MCP

 

[mak101]

Thanks Cetin for quick response.

1 and 2 out of questions for me, since this I have to include in FoxPro application where user can do it from application menu. I will check on third option, but not sure how to reset auto id to 1 everytime.

I will post back once I figure out the final solution.

Thanks.

 

[mak101]

Hi Cetin,

I checked the link you sent me, but I can not figure out how to use SQlBulkCopy from FoxPro, can you please explain me in details?

Thanks.

 

[cbasoz]

Which link did you check? Here there were a sample:

http://www.foxite.com/archives/cursor-adapter-0000207817.htm

And here the same code + how to register and use it:

http://www.fox4um.com/topic/1214/sqlbulkcopy/

I don't understand why 1 and 2 are out of question. All of those are totally runnable things from within VFP. For example for the first item I remember that I posted full code from creating the linked server to uploading data using VFP code to MSDN VFP forum.
(However it is the 3rd one I currently use within my applications - I especially like it because it runs asynch, I fire multiple table uploads at the same time using slightly different code).

Cetin Basoz
MS Foxpro MVP, MCP

 

[mak101]

Hi Cetin,

Thanks for your patient. I am still not sure about your example, do I need to cut and paste c# code in VS project and compile it and then use in test.prg? Can you please explain me?

 

[cbasoz]

Yes you need to compile the code to create dll. If you use VS then VS takes care of command line things you need to do.
-Create a class library project (name it SbcOle),
-Delete default class1.cs and add new class giving it a more meaningful name such as the class' name itself (cbSBC).
-Replace the code with the C# code there
-Right click project and select properties
-You may target framework 2.0 if you want
-In build tab specify x86 as platform target (important-otherwise on a 64 bits OS it would fail when called from VFP)
-Check 'register for COM interop'
-In signing tab Check sign the assembly and create a key file
-Build the DLL

Then you can use VFP test program to test < g >

You could use also compiler from command line (csc). Then follow the steps shown in second link.
That version has restrictions that you may read in comments.

Cetin Basoz
MS Foxpro MVP, MCP

 

[mak101]

I created VS project and compile it. And I tried to run test program and I got following error on line o.DoBulkLoad(0):

OLE IDispatch exception code 0 from System.Data: Keyword not supported: 'driver'...

Is this code VS 2008 compatible? Or do I need any changes?

Thanks.

 

[cbasoz]

That code is compatible with .Net 2.0 and up. Where did find "driver" in that code?

Probably you are erronously replaced Provider with driver in VFP sample. "Driver" doesn't exist in OLEDB connection string.

Cetin Basoz
MS Foxpro MVP, MCP

 

[mak101]

I tried with XML code and it bit slow but worked fine. Thank you so much.