0

My question is pretty much the exact same as Efficient way to bulk insert into Dbase (.dbf) files - Is there an efficient way to bulk insert into dbase IV dbf files? The source data is coming from SQL Server 2012. I have Visual FoxPro available but I'd like to do this programatically from C#.

Community
  • 1
  • 1
reggaeguitar
  • 1,795
  • 1
  • 27
  • 48
  • My question to you is this... Do you have VFP available? It would be much easier... – DRapp Oct 29 '14 at 02:01
  • In VFP, it is SOOOOOOOOOOO Easy to pull into a DBF... Basically done in 3 statements... connect, query down, copy to dbf... done – DRapp Oct 29 '14 at 15:12

1 Answers1

1

As mentioned in comment, if in VFP, you should be able to do quite easily by means of

nHandle = sqlconnect( "connection string to your database" )
sqlexec( nHandle, "select * from YourTable", "localResultSetInVFP" )
*/ At this point, the current work area is the "localResultSetInVFP"
copy to SomeOtherTable    <== this actually creates the final DBF for you locally.

The only other way I can think of that MAY work is as follows.

Create a database CONTAINER in VFP.
Create a connection to the SQL Database.
Create a REMOTE VIEW to the table(s) in the SQL Database.

Then, in C# using the VFP OleDB, create a connection to the path AND include the .DBC database name. Then you might be able to do a select based on the VIEW name... and hopefully the .DBC will kick in, and open / get SQL connection handle, run the remote view query and return all the data to you.

Let me know if this guidance works for you.

One example to create table from existing within VFP's OleDB this also a simple "Create Table" command.

Example of simple insert-into with parameters to help prevent SQL-injection

a very good sample of build the command and parameters ONCE, then do for each row and execute to insert

Another that shows both insert AND update build context samples

Hopefully the above links show you a wide variety to implement creating and inserting into DBF files.

You can create whatever query from other database connection such as SQL, MySQL, etc and use a datareader or dataadapter.fill to download from one, loop into the VFP cycle insert and update the parameters... You'll see the examples and hopefully most are clear to follow.

Community
  • 1
  • 1
DRapp
  • 47,638
  • 12
  • 72
  • 142
  • Thanks for your response, I'll try this and let you know – reggaeguitar Oct 29 '14 at 17:45
  • I'm not sure this will work in my case. I'm writing a C# application that needs to create many dbf files from many different queries, I think the overhead of creating the views etc will take longer than doing row by row inserts with oledb (what I'm currently doing to generate the dbfs). If you have any other ideas about how to do bulk inserts from C# to dbfs I'd love to hear them. P.S. I may be able to use vfp dbf format and not dBase IV – reggaeguitar Oct 29 '14 at 23:15
  • @reggaeguitar, revised answer with suggestive links from others I've offered/posted in the past and there are more of that too. – DRapp Oct 30 '14 at 01:07
  • Thanks, these are helpful. I believe all these options still call ExecuteNonQuery() for every row, which is what I'm trying to speed up. I looked at the dbase IV file format, and it doesn't seem that complicated so I'm going to try to build my own file using a byte[]. Thanks for all your help – reggaeguitar Oct 30 '14 at 15:14