Using Query Strings (Series based on field: SplitBy)
To get multiple series automatically using the data engine you must specify a third column in the query string. This feature is referred to as 'SplitBy'.
Consider:
'SELECT name, age, division FROM Employees GROUP BY division'
Three columns are returned and rows are grouped by the division column. Because the third column is supplied, two series are created, one for each unique value of the division column. The resulting chart will look something like this:
Figure 1: Returning a third column splits the data into series.
Using Data Fields with SplitBy
If you are getting many columns from the database and would like to specify which column to use as the splitBy column you can do so using the DataFields property.
[C#] myDataEngine.DataFields = "Name=name,YValue=age,SplitBy=Division";
[Visual Basic] myDataEngine.DataFields = "Name=name,YValue=age,SplitBy=Division"
Using a single SQL Statement ( Series Per Column )
Multiple series can be automatically generated for each column using multiple YValue tokens in the DataFields property.
For example, consider the following code:
[C#]myDataEngine.SQLStatement = "SELECT myDate, GroupA, GroupB, GroupC FROM Stats";
myDataEngine.DataFields = "XValue=myDate,YValue=GroupA,YValue=GroupB,YValue=GroupC";
[Visual Basic]myDataEngine.SQLStatement = "SELECT myDate, GroupA, GroupB, GroupC FROM Stats"
myDataEngine.DataFields = "XValue=myDate,YValue=GroupA,YValue=GroupB,YValue=GroupC"
Assigning Series Names
This will generate three series, all of which will use the myDate column for the x values and their individual groupX columns for y values. Custom series names can also be specified in the data fields. The following code does the same as the above but will assign custom series names.
[C#]myDataEngine.SQLStatement = "SELECT myDate, GroupA, GroupB, GroupC FROM Stats";
myDataEngine.DataFields = "XValue=myDate,YValue=GroupA=Group Alpha,YValue=GroupB=Group Beta,YValue=GroupC=Group Gamma";
[Visual Basic]myDataEngine.SQLStatement = "SELECT myDate, GroupA, GroupB, GroupC FROM Stats"
myDataEngine.DataFields = "XValue=myDate,YValue=GroupA=Group Alpha,YValue=GroupB=Group Beta,YValue=GroupC=Group Gamma"
Query Database Multiple Times
Another way to do this, is to query your database separately for each series.
[C#]
DataEngine de = new DataEngine(connectionString);
de.SqlStatement = "SELECT name, dataOne FROM table";
Chart.SeriesCollection.Add(de.GetSeries());
de.SqlStatement = "SELECT name, dataTwo FROM table";
Chart.SeriesCollection.Add(de.GetSeries());
[Visual Basic]
Dim de As New DataEngine(connectionString)
de.SqlStatement = "SELECT name, dataOne FROM table"
Chart.SeriesCollection.Add(de.GetSeries())
de.SqlStatement = "SELECT name, dataTwo FROM table"
Chart.SeriesCollection.Add(de.GetSeries())
Avoid Querying Multiple Times
If querying the database multiple times is not desirable the same can be accomplished by using a DataTable.
[C#]
DataEngine de = new DataEngine();
DataTable dt = new DataTable();
// Populate the datatable from your database.
dt = (...);
de.Data = dt;
de.DataFields = "YAxis=name,XAxis=DataOne";
Chart.SeriesCollection.Add(de.GetSeries());
de.DataFields = "YAxis=name,XAxis=DataTwo";
Chart.SeriesCollection.Add(de.GetSeries());
[Visual Basic]
Dim de As New DataEngine()
Dim dt As New DataTable()
' Populate the datatable from your database.
dt = (...)
de.Data = dt
de.DataFields = "YAxis=name,XAxis=DataOne"
Chart.SeriesCollection.Add(de.GetSeries())
de.DataFields = "YAxis=name,XAxis=DataTwo"
Chart.SeriesCollection.Add(de.GetSeries())