Large VB ADO.NET Recordset Import into Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CerfurMark - 06 Nov 2007 19:17 GMT

If I use Excel to open an Excel file with 61K records, they appear instantly.
If I fill an ADO.NET recordset in Visual Basic with 61K records, it fills
almost immediately. But if I bind the dataset to a ListObject in Excel in
VSTO, then populating the workshseet takes 20 minutes. I can use a
For...Next loop to read the dataset and fill each row of the worksheet, but
that takes a couple of minutes. What is the way to instantly fill an Excel
spreadsheet with a dataset from SQL Server?

Reply to this Message

Miha Markic - 07 Nov 2007 07:54 GMT

Hi,

I don't think this is ado.net related problem. You should do some
performance measuring, perhaps using a performance profiler to find the
origin of the bottle neck.

Signature

Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

> If I use Excel to open an Excel file with 61K records, they appear
> instantly.
[quoted text clipped - 6 lines]
> Excel
> spreadsheet with a dataset from SQL Server?

Reply to this Message

CerfurMark - 07 Nov 2007 08:13 GMT

Yes, you are correct, it is not an ADO.NET problem. ADO.NET gets the 61K
records almost instantly. The bottleneck occurs in the binding of the
dataset to the ListObject control in Excel. I will try posting this in
another discussion group.
Thank you.

> Hi,
>
[quoted text clipped - 12 lines]
> > Excel
> > spreadsheet with a dataset from SQL Server?

Reply to this Message

Jim Rand - 07 Nov 2007 14:35 GMT

Did putting this code in prior to the data binding help?
this.Application.ScreenUpdating = false;

> Yes, you are correct, it is not an ADO.NET problem. ADO.NET gets the 61K
> records almost instantly. The bottleneck occurs in the binding of the
[quoted text clipped - 20 lines]
>> > Excel
>> > spreadsheet with a dataset from SQL Server?

Reply to this Message

CerfurMark - 07 Nov 2007 16:58 GMT

When I call the myTableAdapter.Fill() operation, a blue rectangle appears
around all of the cells that were bound to the ListObject, but nothing else
is happening to the screen during the loading of the data. So when I tried
your suggestion, all that happened is that the blue rectangle didn't appear.
No performance improvement. The way I know that ADO.NET is fast is because I
tried the myTableAdapter.Fill() operation without being bound to the cells
and it executed in a second or two. I tried loading 2500 records and it
takes about 20 seconds with the bound ListObject for the data to appear. Why
can ADO.NET query 61K records instantly, but it takes 10 minutes to get them
into the Excel cells?

I'm using VB in VSTO, not VBA, so the syntax for me is
Globals.ThisWorkbook.Application.ScreenUpdating = false.

> Did putting this code in prior to the data binding help?
> this.Application.ScreenUpdating = false;
[quoted text clipped - 23 lines]
> >> > Excel
> >> > spreadsheet with a dataset from SQL Server?

Reply to this Message

Jim Rand - 07 Nov 2007 18:26 GMT

I'm just starting with VSTO. One concern that I have, other than deployment
issues, is the cost of interop calls between managed and unmanaged code.
Because of this concern, right from the get-go, I'll be limiting VSTO
programming to just those things that are unique to .NET such as obtaining
data via web services. The rest of the programming is going to be done
using straight VBA in the Excel unmanaged space.

An interesting experiment for you would be to write the data in csv format
to a file like "data.csv". Then, from within Excel, open "data.csv" as just
another worksheet.

> When I call the myTableAdapter.Fill() operation, a blue rectangle appears
> around all of the cells that were bound to the ListObject, but nothing
[quoted text clipped - 48 lines]
>> >> > Excel
>> >> > spreadsheet with a dataset from SQL Server?

Reply to this Message

Tom Garth - 15 Nov 2007 23:06 GMT

Whether it's ADO.NET or ADO in the VBA, copying an array to the worksheet has
been the quickest I have worked with.

There are lots of other options though. Great link here, with additional
links at the bottom.

http://support.microsoft.com/default.aspx/kb/321686

Signature

Tom Garth

> When I call the myTableAdapter.Fill() operation, a blue rectangle appears
> around all of the cells that were bound to the ListObject, but nothing else
[quoted text clipped - 37 lines]
> > >> > Excel
> > >> > spreadsheet with a dataset from SQL Server?

Reply to this Message

Cor Ligthert[MVP] - 08 Nov 2007 05:57 GMT

Hi,

Ado.Net does not know the fenomen Recordset.

The Recordset is from classic Ado therefore you can ask this in my idea
better in a newsgroup for classic Ado.

(You can use the classic ADO methods in VB.Net, that is used by the
conversion from VB6 to VB.Net however it is not Ado.Net)

Cor

Reply to this Message

Large VB ADO.NET Recordset Import into Excel

Free Magazines