CODEWITHSUNDEEP
c#sql-servervstopowerpivot
COG
COG

Reputation: 301

Load a Power Pivot data model in a VSTO Excel application

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?

Upvotes: 0

Views: 663

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

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

Upvotes: 1

Related Questions