Hi,
I'm looking for a complete sample on how to connect to SQL Server (specific table) with ADOMD and use this as DataSource for a WinPivotGrid.
Can you please explain the steps and provide a sample.
Thank you very much
Thomas
Hello Thomas,
Thank you for contacting Infragistics. We have a topic called Using ADOMD, in the PivotGrid's online documentation as well as a sample to walk you through this in our Windows Forms Samples Browser.
The code for the sample is listed below:
https://www.infragistics.com/samples/windows-forms/pivot-grid/pivot-grid-binding-to-adomd
Let me know if you have any questions.
Sincerely,
Michael Di FilippoAssociate Software DeveloperInfragistics, Inc.www.infragistics.com/support
Hello Michael,
sorry but that doesn't really help me. My question is how to display SQL Server data in PivotGrid. Your sample takes data from Infragistics in some unknown format.
Concrete questions would be:
Which provider is to use?
Is catalog the database?
What is cube, settings.Rows, settings.Columns?
Is it necessary to define settings.Measures, .Filters...
Thank you
I recommend referring to MSDN for more details on accessing Data from ADO. https://msdn.microsoft.com/en-us/library/ms524771(v=vs.90).aspx
The OLE DB Connection string for SQL Server is:
Provider=SQLOLEDB.1;Data Source=path to database on server
The cube is the class specified in the data source as main data holder. This is arbitrary as to what ever the table is. Filter and measures extend how you wish to organize your data and isn't a requirement for setting up. A catalog isn't required either, from my understanding.
You may read more about the pivot grid's features here:https://www.infragistics.com/help/wpf/xampivotgrid-features
The XamPivotGrid supports several different datasource types. You would probably benefit from using the the FlatDataSource.
eg.
SalesDataSample DataSample = new SalesDataSample(); FlatDataSource DataSource = new FlatDataSource(); FlatDataConnectionSettings DataConnectionSettings = new FlatDataConnectionSettings(); DataConnectionSettings.ItemsSource = DataSample; DataSource.ConnectionSettings = DataConnectionSettings; this.PivotGrid.DataSource = DataSource;
The SalesDataSample class that implements ObservableCollection
http://help.infragistics.com/Help/Doc/WPF/2016.1/CLR4.0/html/SalesDataSample.html
ObservableCollection is just one example, as the FlatDataSource can bind with any collection that implements IEnumerable.
Since you are interested in binding to SQL Data you can either use a SqlDataAdapter or LINQ to query your sql data into an object that the FlatDataSource can understand, like a DataTable object. The DataTable exposes a DefaultView property is IEnumerable
eg. https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/creating-a-datatable-from-a-query-linq-to-dataset
code snippet: Using a Data Adapter
var table = new DataTable(); using (var da = new SqlDataAdapter("SELECT * FROM mytable", "connection string")) { da.Fill(table); }
Binding the table with the FlatDataSource:
FlatDataSource DataSource = new FlatDataSource(); FlatDataConnectionSettings DataConnectionSettings = new FlatDataConnectionSettings(); DataConnectionSettings.ItemsSource = table.DefaultView(); DataSource.ConnectionSettings = DataConnectionSettings; this.PivotGrid.DataSource = table.DefaultView();
We also have a SQL Data Binding sample using the XamDataGrid, which would work very similar to the FlatDataSource/XamPivotGrid.
https://www.infragistics.com/samples/wpf/data-grid/sql-data-binding
Thank you but that doesn't work, maybe becuase I'm working with WinForms (not WPF/XAM).
FlatDataConnectionSettings is not available!?
Sincerely
Tom
Hello Tom,
Binding the Windows Forms WinPivotGrid works the same manner, but there is a FlatDataSourceInitialSettings.
For more details please visit our online documentation for the WinPivotGrids's FlatDataSource
Hello Michael - is there a way to bind a DataTable with UltraPivotGrid - none of the links you mentioned above seems to have that. I may have missed it somewhere? FlatDataSource needs IEnumerable items source - as such DataTable cannot be stitched with it - do we need strongly typed datasources to work with pivot grid?
Thanks!
Take a look at this thread:https://www.infragistics.com/community/forums/f/ultimate-ui-for-windows-forms/118862/creating-flatdatasource-from-datatable-dynamically-and-use-it-in-pivotgrid