-1

I need a function to transfer data from a .DBF file to SQL Server.

Here is what I do:

  • First step: I use OleDBDataAdapter.Fill to read from .DBF file
  • Second step: insert this table into SQL Server.

The first step takes 74 seconds for 90 column X 80000 rows.

Is there any way to speed this process up?

Also, if there is any way to communicated directly from .DBF to SQL Server, please guide me. BTW, I am using C# and SQL Server 2008.

My mistake, guys. I rarely post here. Here is my code (take over 1 minute to transfer DBF into datatable):

            OleDbCommand oledbcommand = new OleDbCommand();
            OleDbDataAdapter adp = new OleDbDataAdapter();
            oledbcommand.Connection = oledbConnectOpen();
            oledbcommand.CommandText = @"SELECT * FROM " + filename;
            adp.SelectCommand = oledbcommand;

            adp.Fill(dt); //this is the step that consume time
            dt.TableName = filename;
            return dt;
Chao Dai
  • 11
  • 4
  • What does your existing code look like? – Rion Williams Aug 26 '16 at 16:47
  • **What is your question?** I'm thinking it's that you want to speed this process up. – Bob Kaufman Aug 26 '16 at 16:48
  • There are several ways to speed this up however we would need to see what it is that you are currently doing in regards to the current data transfer.. – MethodMan Aug 26 '16 at 16:49
  • You seem to be under the impression that this is a code writing service, where you post your requirements and language of choice and a code monkey churns out code to meet them. You're mistaken. Please include the code you've written to attempt to do this yourself, explain the problem you've encountered in that code, and ask a **specific question** related to that problem. – Ken White Aug 26 '16 at 17:37
  • My suggestion would be that you look at an SSIS package as an option, unless this has to be done as a step in an interactive application. That would allow you to transfer from an OLE data source to a SQL Server table. – Ann L. Aug 26 '16 at 17:48
  • Sorry, Guys. I wasn't expect any code writing service. I am stalling here for days and having got any clue. the code I used is pretty easy one. I was hoping a spark like "don't use adapter.fill(), you idiot, use this." My fault. – Chao Dai Aug 26 '16 at 19:50
  • Would you be able to drive this from Visual FoxPro to Sql Server? In other words, I use VFP to insert and update tons of data with Sql Server. So, if you could use VFP code in your solution, I can help you a lot. Let me know and we can email directly or use Skype. – MattSlay Aug 28 '16 at 13:33

1 Answers1

0

In this scenario you would be better using an OleDbDataReader as opposed to OleDBDataAdapter because the reader is optimised for forward-only, read-only access. See this article.

Alan B
  • 4,086
  • 24
  • 33
  • I tested Reader and Adapter. There is no big difference, although the reader does save 1.31 second.. Thanks Alan, – Chao Dai Aug 31 '16 at 22:46