Alternative to QueryTables.Add method or Range.CopyFromRecordset???

[thomas.b.mann]

I am moving data from an ADO.NET DataTable object in C# and
I am having performance issues.

QueryTables.Add, at least when called from C#,
seems to spin off its own thread. So I go doing
a bunch of other stuff to an invisible sheet AFTER
I want the data transfer to occur, however, even
after everything else completes (including showing
the file) it is still executing.

I can't use Range.CopyFromRecordset as that method
seems to interface with my .NET DataTable.

So I am essentially setting cell values like so:

for (int i=0; i< myDTable.Rows.Count; i++)
{
for (int j=0; j< myDTable.Columns.Count; j++)
{
myCell.Value = myDTable.Rows[j]; //this is pseudo
}
}

and this is horribly inefficient, even for modest
sized data sets.

Any way to dump this to the sheet faster.

I wouldn't mind a non-looping approach
to putting a DataRow object in some Range,
I just don't know how to do it.

Thanks.
thomas

 

[AMIT]

Hi Thomas,



Can you provide some sample of querytable in excel using C#. I am
working for some assignment, but the query table.add method gives
error.

I wonder what the problem is. I am new to programming of excel and
querytables. We are using Excel 11.0 libraray in C#.



The code snippet:-



static void Main(string[] args)

{

try

{

Excel.Application oXL;

Excel._Workbook oWB;

Excel._Worksheet
oSheet;





// start Excel and get
Application object

oXL = new
Excel.Application();

oXL.Visible = true;





// get a new workbook







oWB =
(Excel._Workbook)(oXL.Workbooks.Add(System.Reflection.Missing.Value));

oSheet =
(Excel._Worksheet)oWB.ActiveSheet;









// I had a problem
here;

// import data to excel
sheet


oSheet.QueryTables.Add("server=XXXX;database=XXXX;uid=sa;pwd=sa;",oSheet.get_Range("A1",
"A1"), "exec prc_X");







// NOTE: for the 3rd
argument I do a stored procedure

//call, but this can
also be a straigh sql statement...







// the QueryTables
collection of the worksheet seems to

//be indexed starting
at 1


oSheet.QueryTables[1].Refresh(true);

oXL.Visible = true;

}

catch (Exception ex)

{

Console.WriteLine
(ex.Message + "\n" + ex.StackTrace);

}



The error message is as following:-



Message "Exception from HRESULT: 0x800A03EC."
String

_stackTraceString " at
System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)\r\n at Excel.QueryTables.Add(Object Connection, Range
Destination, Object Sql)\r\n at
ConsoleApplication2.Class1.Main(String[] args) in c:\\documents and
settings\\amitba\\my documents\\visual studio
projects\\consoleapplication2\\class1.cs:line 40" string



Your prompt assistance is highly expected. Thanks for your time and
effort.