RadarCube ASP.NET Direct: How to create the cube using IDataReader?
Posted by Vladimir Lyutetsky on 12 September 2008 11:39 AM

This article applies to:

  • RadarCube ASP.NET Direct

 


 
This article applies to: RadarCube ASP.NET Direct

This simple article shows how to make the cube use an IDataReader interface to retrieve the cube data. Let assume we have created a new empty asp.net site named "ASPUsingDataReader". The first thing we need to do is prepare the data source for our cube. Here we use the standard Northwind.mdb MS Access Database. So let's create the DataSet type for the Northwind Database.

 

Creating the data source for the cube

When you click Add, Visual Studio will ask if the new dataset should be placed inside the "App_Code" folder. Answer "Yes" and then close the Master that will appear next. Now you have the empty DataSet in your project, and you have to define its metadata or structure so that the cube could analyze it when getting opened. So define a new Data Connection in your Server Explorer and select the OLAPDemo.mdb file as a database:

Now that you have the connection for you database, drag and drop the tables you need from the data connection:

Visual Studio will automatically place these tables in the schema and create all needed relations between the tables:

Creating the cube and grid on the web page

Now that we have the data source, we may create the cube and grid on our web form. It is easy – just place the TOLAPCube and TOLAPGrid controls onto your web page:

If you don't have such controls in your Toolbox window then you may need to add needed assemblies into your project:

When you're done, set the DataSourceID property of the TOLAPCube1 control to the DataSet named "Northwind" that you created in the previous step, and Active property to True.

And also you have to set the CubeID property of the TOLAPGrid1 control to TOLAPCube1:

Constructing the cube

The last thing you need to do is define the dimensions and measures you will want in your cube. You can do it in design time using the Cube Editor. The first time you run the Cube Editor it will show the Master to help you define the cube structure. In this master choose the "Order Details" as the fact table and then let it auto-fill the cube structure. Then you will be able to delete unneeded hierarchies or measures and add additional if you need to. But for now your cube structure will look like this:

Providing the IDataReader interfaces

This is all you need to do to, actually. Now you can run your project, and you will get the perfectly working cube on your web page. However, the aim of this article was to show how to use IDataReader interface for data retrieving.

To make the cube use IDataReader you need to provide the table adapters inside your dataset with the property which may look like this:

public System.Data.IDataReader DataReader { get {...} }

This property must be declared as public, must be named "DataReader", and must return any object which supports iDataReader interface or the interface itself. When the cube finds this "magic" property in the source code of the table adapter class, it doesn't fill the table. Instead it calls on the DataReader property to get the IDataReader interface which is to be used to retrieve data from the database, when the right moment comes.

Let's assume you have created the dataset named "Northwind" in your project. And this dataset contains these tables: "Products", "Customers", "Orders", and so on. This means that the Visual Studio creates the following definition code for the table adapters in this dataset.

public partial class ProductsTableAdapter : System.ComponentModel.Component {
...
}

public partial class CustomersTableAdapter : System.ComponentModel.Component {
...
}

public partial class OrdersTableAdapter: System.ComponentModel.Component {
...
}

As you can see these classes are defined as "partial", so there's nothing to prevent you from writing the code of your own in additional to original definition. So you can write the code like the following in any module of your project:

public partial class ProductsTableAdapter
{
public System.Data.IDataReader DataReader
{
get
{
string queryString = "SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products";
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\OLAPDemo.mdb";
OleDbCommand command = new OleDbCommand(queryString, new OleDbConnection(connectionString));
command.Connection.Open();
return command.ExecuteReader();
}
}
}

public partial class CustomersTableAdapter
{
public System.Data.IDataReader DataReader
{
get
{
string queryString = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers";
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\OLAPDemo.mdb";
OleDbCommand command = new OleDbCommand(queryString, new OleDbConnection(connectionString));
command.Connection.Open();
return command.ExecuteReader();
}
}
}

public partial class OrdersTableAdapter
{
public System.Data.IDataReader DataReader
{
get
{
string queryString = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders";
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\OLAPDemo.mdb";
OleDbCommand command = new OleDbCommand(queryString, new OleDbConnection(connectionString));
command.Connection.Open();
return command.ExecuteReader();
}
}
}

That's all about the magic "DataReader" property. The bottom-line would be that all you need to do is define this property in the source code. Nothing else. Then the cube will find it and make use of it to get the IDataReader interface supposed to be used for data retrieving. If the cube cannot find this property then it fills the original dataset to read the data from later on.

And the last point worth to emphasize is that the DataReader property can be defined either in the table adapter class as shown above or in the table's class. For example, in the example above the table's classes look like this:

public partial class Northwind : System.Data.DataSet {
...
public partial class ProductsDataTable : System.Data.DataTable {
...
}

public partial class CustomersDataTable : System.Data.DataTable {
...
}

public partial class OrdersDataTable : System.Data.DataTable {
...
}
}

These classes defined as "partial" just like table adapter's classes, so we can declare the properties in the same way.

Conclusion

The method of using IDataReader interface for data retrieving may be extremely useful in a number of ways. First, when using IDataReader the cube takes much less memory than it would without it. See the articles "Creating the data source" and "How to make the cube work faster?" for more details.

Secondly, this method may be useful when you can"t open your data source in design time. For example when you only know your data sources in run time and then you need to create the cube using this data.

(310 vote(s))
This article was helpful
This article was not helpful

Help Desk Software by Kayako Resolve