private void SetDataSource()
{
// Build the connection string.
string connectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Data\NWind.mdb";
// Build the query string. Note that we use a StringBuilder here
// for readability, so we can separate the different clauses
// of the SQL statement.
//
// Note that the placement of parentheses near the INNER JOIN
// clauses is important; the Microsoft.Jet.OLEDB data provider
// expects these, and their absence will cause a vague exception
// to be thrown, making it extremely difficult to find the code
// location of the error.
//
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT");
sb.AppendLine("Customers.CompanyName,");
sb.AppendLine("Orders.OrderDate,");
sb.AppendLine("[Order Details].Quantity,");
sb.AppendLine("[Order Details].UnitPrice,");
sb.AppendLine("Products.ProductName");
sb.AppendLine("FROM ((Customers");
sb.AppendLine("INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)");
sb.AppendLine("INNER JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID)");
sb.AppendLine("INNER JOIN Products ON Products.ProductID = [Order Details].ProductID");
// Create a FlatDataSourceInitialSettings instance
// to initialize the row, column, and measures axes.
// This will be passed in as a parameter to the
// FlatDataSource constructor.
FlatDataSourceInitialSettings settings = new FlatDataSourceInitialSettings();
settings.Rows = "[Hierarchies].[CompanyName]";
settings.Columns = "[Hierarchies].[ProductName], [Hierarchies].[OrderDate]";
settings.Measures = "[Measures].[Quantity]";
// Create a FlatDataSource instance, and set the DisplayName
// property to something meaningful.
FlatDataSource ds = new FlatDataSource(settings);
ds.DisplayName = "Orders by Customer";
// Create an OleDbAdapter instance in a 'using' block
// so that it is disposed of when we're finished with it.
using ( OleDbAdapter adapter = new OleDbAdapter(connectionString) )
{
// Set the QueryString property to the SQL
// statement we constructed previously.
adapter.QueryString = sb.ToString();
// Call the adapter's Fill method to populate the
// data source with the result of the database
// query.
adapter.Fill(ds);
}
// Assign the initialized FlatDataSource instance to
// the pivot grid's DataSource property.
this.pivotGrid.SetDataSource(ds);
}
