RadarCube Windows Forms Desktop: Creating the data source
Posted by Ivan Pashkov on 24 August 2007 05:39 PM

This article applies to:

  • RadarCube Windows Forms Desktop

How to provide the Cube with a data source?

Before defining the Cube structure in "Cube Editor", you must inform the Cube where it can find the data schema. For that purpose, the Cube has a property named DataSource (or DataSet). The Cube can accept any typed dataset created in Visual Studio. So, if you already have a typed dataset in your project you may set this property at once. Once you assign the typed dataset you have all the information for opening Cube Editor to define dimensions and measures.

If you don't have a dataset yet, you can create it using standard means of Visual Studio. Note that you don't have to create an instance of the DataSet type, because the Cube can manage a type, not only an object of dataset. You may as well have an instance of the dataset - it doesn’t matter, but keep in mind that the Cube will try to create an instance for you, if you provide it with a type rather then an object.

In addition, the DataSource property can accept an object of System.Data.DataTable type. So, you can assign the DataSource property with either a System.Data.DataSet or a System.Data.DataTable object. If the source object is of System.Data.DataTable kind then the Cube assumes that the entire data schema consists of a single table (fact table) and creates the DataSet with an single table in it. Of course, the source object can be bound through the standard BindingSource object. This gives the Visual Studio an ability to create a BindingSource 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 DataSource property. Once you do that, you can 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 DataSource 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 are provided with the dataset. Later on, when you open the Cube in run time, it creates all the needed table adapters and fills them with data (provided that no IDataReader interface is used - see below).

So there's nothing you have to do to fill the tables with data - the Cube will do it for you. And you are ready to open the Cube once you set up a few 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 the needed table adapters for your dataset and fills all the tables with data. This may be a 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 IDataReader interface (see below). When the dataset gets ready (filled up with data), the Cube activation process starts.

First, the Cube examines the data schema defined by the DataSource property and reads all the tables in the DataSet into an internal structure convenient, which is convenient, because you have always have an access it. This internal structure is stored not in the memory, but on your hard drive that allows to free the dataset resources. When the Cube finishes building the internal structure, it frees the memory occupied by the in-memory DataSet, and from now on uses only the internal structure to retrieve data from.
Now the Cube is able to calculate all needed sub-totals, and so it gets open with the predefined Cube structure (see "Using Cube Editor to construct the Cube").

That's all, and one thing worth mentioning here is that the you don’t actually need to fill up the DataSet in order to open the cube. Instead, you can provide your dataset with the IDataReader interfaces. If you do, the Cube will use these interfaces to retrieve data from the tables and will not fill them itself. We'll discuss it in the next section.

Using IDbCommand or IDataReader for retrieving data

As we mentioned earlier before the Cube is open the whole data set is read from the database and stored in the memory to provide a constant data source. As you can imagine the great 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 has an internal storage buffer. If filling the in-memory data set could be avoided, it would save a great deal of time and memory. And there is a way to do that.

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 up the DataSet component. You will still need the DataSet component though, because it always serves as the initial data schema for the Cube and contains the schema metadata, but this way you can reduce the memory (and therefore time) needed to open the Cube.

The 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 an IDataReader object using the ExecuteReader() method. The Cube uses these features of the IDbCommand to retrieve data from the dataset. In addition, this method provides faster fetching as IDataReader allows reading forward-only streams of resulting sets. If you provide the IDbCommand interface, the Cube will use it to obtain IDataReader with the command's ExecuteReader method. However, you can as well set the IDataReader interface directly. If this is the case, the Cube won't use an IDbCommand interface but instead will use IDataReader (see below).

To make the Cube use IDbCommand instead of filling 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 the 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 retrieve data from the database.

Let's assume you have created an example 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 there the following definition code for the table adapters.

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 you can write your own code in addition to the original definition. So, you can write a code like this 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 Visual Studio's ability to automatically generate a collection of commands for every table adapter in the data schema, so we can use the first of them in our DbCommand property. There are other means as well. You can 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 would be all about the magic "DbCommand" property. In the upshot, all you need to do is to define this property in the source code. Nothing else. Then the Cube will find it and use 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.

In addition to the "DbCommand" there is another special 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 the IDataReader interface or the interface itself. When the Cube finds this property in the source code of the table adapter class, it doesn't fill the table, just like with the DbCommand, but calls on the property in order to obtain IDataReader interface. This IDataReader will be used to retrieve table's data later on.

In fact, you may consider DbCommand and DataReader properties as two ways to obtain the IDataReader interface. If DataReader is defined then the resulting IDataReader is used. If not, then the DbCommand's ExecuteReader method is called on to get IDataReader. If neither DbCommand, nor DataReader is defined then the table 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 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 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 ready template properties to 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 the IDbCommand or the IDataReader interface for retrieving data.

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

Help Desk Software by Kayako Resolve