I have an Excel document-level customisation (VSTO, C# - but not an Add-In) whereby the user can dynamically retrieve data from a SQL Server database. Right now I place the results on a worksheet, but there could be more than a million rows. Does anybody know if it's possible to load the data into the Power Pivot data model?
Here is the VBA equivalent as blogged about by Dany Hoter. It should be a good pattern to follow in C#:
Sub AddSQLtables()
‘One or more tables from a SQL server database
‘Relationships are detected if they are declared in the database
ActiveWorkbook.Connections.Add2 _
“Connection Name”, “”, _
“OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=<SQL server – name>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=AdventureWorksDW2008R2” _
, “””DimCustomer””,””DimGeography”””, 6, True, True
End Sub