The fastest way to fetch data from SQL Server and fill an Excel range

I had to fill an Excel range from SQL Server according the following scheme

    C开发者_如何学Python1 C2 C3....C29
L1 
L2 
L3 
L4
L5
.....
L120

I wondered what could be the fastest way to fetch each value corresponding to each pair (Li,Cj), value which is stored in SqlServer ?

I could not iterate over each cell.

What could have been your solution ?

I have to say that i managed to retrieve those data in less than 3 seconds

The fastest way VSTO provides is a helper function described in this article: http://social.msdn.microsoft.com/Forums/en/vsto/thread/5cfc24cd-cbeb-4583-b6c8-ad1521e31267

If all you have is an array then you can assign the array directly to the value2 of a range and it populates fairly quickly.

You can set Application.EnableEvents=False and Application.ScreenUpdating=False to speed up your process dramatically. Remember reset them after the process.

Rather than make an individual stored procedure call for each (L,C) pair, make one call that fetches all the pairs in the table. Hopefully there is no precondition that prevents this from happening. Otherwise, because of the SQL calling overhead alone, you will not be able to get the data back in < 3 seconds. Pull the data in to a SqlDataReader if you can.

Next, populate a 2D array variable, according to the (L,C) relationship in your fetched data. Excel uses a 1-based array, which you can emulate (but not strictly required) as below:

// this creates a 1-based 2D array with 5 rows, 2 columns (5,2)
var my2DArray = Array.CreateInstance(
    typeof(object), new int[] { 5, 2 }, new int[] { 1, 1 });

After populating the 2D array, set the array to the Excel worksheet. The code would look roughly like below:

// not sure what your cell refs are, so I'll be arbitrary...
var rng = myWorksheet.get_Range("A1", "B1");
rng = rng.get_Resize(my2DArray.GetUpperBound(0), my2DArrary.GetUpperBound(1));
rng.Value2 = my2DArray;

This should be the fastest way, as compared to setting cell values one by one.