RadarCube ASP.NET Direct: Creating the data source
Posted by Vladimir Lyutetsky on 20 March 2007 06:53 AM

This article applies to:

  • RadarCube ASP.NET Direct

 


 

How to provide the Cube with a data source.

Before you start defining the Cube structure in "Cube Editor", you must tell the Cube where it can find the data schema. For that purpose, the Cube has the DataSourceID property. The Cube can accept any typed dataset created in Visual Studio. Therefore, if you already have the typed dataset in your project you may set this property at once. Once you assign the typed dataset, you have all the information to open Cube Editor to define dimensions and measures.

If you don't have a dataset yet, you can create it using the standard means of Visual Studio. Note that you don't actually need to create an instance of the DataSet type, because the Cube can manage a type, as well as an object of the dataset. But if you have an instance of the dataset, it doesn’t matter. The only thing you should know here is that the Cube will try to create an instance for you, if you provide it with a type, not an object.

In addition to this, the DataSourceID property can accept almost any object that supports the IDataSource interface. This gives the Visual Studio an ability to create the SqlDataSource object automatically and bind it to the Cube without writing a single line of code.

Thus, the only thing you should do to connect the Cube to a data source is assign a value to its DataSourceID property. Once you do so, you can go on to open the Cube Editor to construct your Cube(see "Using Cube Editor to construct the cube").

How to fill the DataSet with data?

When you assign the DataSourceID property, the Cube doesn't actually create any table adapters or fill them with data. At this moment, the Cube needs only dataset metadata that can be provided with the dataset. Later on, when the Cube gets open in run time it creates all the needed table adapters and fills them with data (if no IDbCommand interface is used - see below).

 

So you don't need to do anything yourself to fill the tables with data - the Cube will do it for you when needed. You are ready to open the Cube once you set up some dimensions and measures to display.

What happens when the Cube gets open?

When you created the dimensions and measures in the Cube Editor you can open the cube. When you do so, the cube creates all needed table adapters for your dataset and fills all the tables with data. This may be kind of time-consuming operation, because the entire dataset must be loaded into memory. Notice here that you can avoid reading the dataset into memory by using IDbCommand interface (see below). When the dataset gets ready (filled up with data), the cube activation process starts.

 

When you activate the Cube, it, first of all, examines the data schema defined by the DataSet property and reads all the tables in the DataSet into the internal structure, which is always accessible to a user. This internal structure is not stored in the memory, but on your hard drive, and allows to free the dataset resources. When the internal structure is completed, the Cube frees the memory occupied by the in-memory DataSet, and from now on, it only uses this internal structure to retrieve data from.

Now the Cube is able to calculate all the needed sub-totals, and so it gets open with the predefined Cube structure(see "Using Cube Editor to construct the cube").

 

One last thing worth mentioning is that the Cube doesn't actually need to fill up the DataSet in order to get open. Instead, you can provide your dataset with the IDbCommand interfaces. And, if you do so the Cube will use this interface to retrieve data from and will not fill the dataset's tables. This is discussed in the next chapter.

Using IDbCommand and IDataReader for retrieving data

The way of providing Cube with the data source was deailed above. However, there is another thing worth considering. As it was shown earlier the whole data set is read from the database before the Cube is opened and stored in the memory to provide the Cube with a constant data source. As you can imagine the biggest bulk of the occupied memory falls on the DataSet component as it stores the whole schema in the local memory. Still the Cube doesn't need at all the data to be stored in the DataSet because the it also has an internal storage buffer. And, if there were a chance to avoid filling the in-memory data set, the great deal of time and memory would be saved. And this can be done!

 

You can significantly cut down the memory requirements if you use the IDbCommand interface (or IDataReader) to read data from the database instead of filling the DataSet component. It doesn't mean that you won’t need the DataSet component at all; because it always serves as an initial data schema for the Cube and contains the schema metadata, but it certainly means you can reduce the memory (and therefore time) needed to open the Cube.

As you may know, IDbCommand interface has the CommandText property that is a command to run against the database, and the Connection property that represents the connection to the data source. Also it can return the result of the command execution as a IDataReader object using the ExecuteReader() method. These features of the IDbCommand can be used by the Cube to retrieve data from the dataset. In addition, this method provides faster fetching as IDataReader allows reading forward-only streams of result sets. If you provide the IDbCommand interface, the Cube will obtain IDataReader with the command's ExecuteReader method. However, you can provide the IDataReader interface directly. If this is the case, the Cube will use not the IDbCommand interface but take IDataReader instead (see below).

To make the Cube use IDbCommand and not fill the dataset's tables, you need to provide the table adapters inside your dataset with the property that should look like this:

public System.Data.IDbCommand DbCommand { get {...} }

This property must be declared as public, be named "DbCommand", and return any object, which supports IDbCommand interface or the interface itself. When the Cube detects this property in the source code of the table adapter class, it doesn't fill the table. Instead, it calls on the DbCommand property to get the IDbCommand interface to be used to retrieve data from the database.

 

Let's create an example dataset named "Northwind" in your project. This dataset will contain tables "Products", "Customers", "Orders", and so on. It 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", this means that you are able to write your own code in addition to the original definition. So, you can write the code like the following in any module of your project:

public partial class ProductsTableAdapter
{
public OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}

public partial class CustomersTableAdapter
{
public OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}

public partial class OrdersTableAdapter
{
public OleDbCommand DbCommand { get { return this.CommandCollection[0]; } }
}

 

Here we use the fact that the Visual Studio automatically generates a collection of commands for every table adapter in the data schema, so we can use the first of them in our DbCommand property. But this is not the only way. You can as well create your own IDbCommand of any actual type, for example, OleDbCommand, OracleCommand, and return it from the DbCommand property. It gives you additional flexibility that you may use.

That's all about the "DbCommand" property. In short, all you need to do here is define this property in the source code. Nothing else. Then the Cube will detect and use it to get the IDbCommand interface for retrieving data. If the Cube cannot find this property then it fills the original dataset to read the data from later on.

Except for "DbCommand" there is another magic property named "DataReader". It looks like this:

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

 

This property must be declared as public, be named "DataReader", and return any object that supports IDataReader interface or the interface itself. When the Cube detects this property in the source code of the table adapter class, it doesn't fill the table, just like in case of DbCommand, but calls on the property in order to obtain the IDataReader interface. This IDataReader will be used to retrieve table's data later on.

In fact, you may consider the DbCommand and the DataReader properties just like two ways to obtain the IDataReader interface. If DataReader is defined then the resulting IDataReader is used. If not, then DbCommand's ExecuteReader method is called on to get IDataReader. If neither DbCommand, nor DataReader is defined, then the table itself is filled with the Fill method and is then used to retrieve data from.

 

And the last thing worth mentioning is that these properties can be defined either in the table adapter class as shown above or in the table's class. For instance, 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 are defined as "partial" just like table adapter's classes, so we can declare the properties in the same way. Of course, if you define the properties in the table's class, you won’t have the template properties ready for use like in table adapter (for example, the CommandCollection property).

 

This ability may be extremely useful if you, for some reason, don't have any table adapters in your dataset. In this case, you can define the properties in the table's class and use either IDbCommand or IDataReader interface for retrieving data.

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

Help Desk Software by Kayako Resolve