NETTutorial8

From Steema Software Reference Wiki
Jump to: navigation, search

Net.png

Contents page
Previous | Next

Tutorial8 - ADO.NET Database Access


Introduction

Connecting a TeeChart control to ADO.NET databases can be done at design-time using the TeeChart Editor and at run-time with few lines of code.

Any Series can be connected to ADO.NET tables or queries using the TeeChart Editor. Every Series can be connected to the same or different table or query using the same or different ADO.NET database.

This design gives you total flexibility on how do you connect Charts to databases, as you aren't restricted to only one database or to only one table or query.

Data is retrieved also at design-time so you see the actual data during development.

Connecting to ADO.NET at design-time

DataAdapter

At design-time, the steps necessary to connect a Series to an ADO.NET datasource are:

  • 1. From the Data tab of the VS.NET ToolBox, drag a OleDbDataAdapter onto a WinForm with a TeeChart for .Net object on it.
  • 2. Click "Next" on the Data Adapter Configuration Wizard and on the next page click on the "New Connection..." button.
  • 3. Now click on the "Provider" tab of the new window and select "Microsoft Jet OLE DB Provider" as in the image below:

    Ole1.png

  • 4. Now click on the "Connection" tab of the same window and select the TeeChart.mdb file from the Sample Data folder under the TeeChart for .Net root directory:

    Ole2.png

  • 5. Click on the "Test Connection" button to test the connection and then click on the OK button of the same window.
  • 6. Now click on the "Next" button of the Data Adapter Configuration Wizard window, select "Use SQL Statements" from the window that appears and click on the "Next" button again.
  • 7. Now click on the "Query Builder" button and add in the "Employee" Table. Close the "Add Table" window and select the "*(All Columns)" CheckBox:

    Ole3.png

  • 8. Now OK this window, click on "Next" and then "Finish".
  • 9. Now open the TeeChart Editor, add in a BarSeries and navigate to the DataSource tab of the Series tab.
  • 10. Select "Database" from the ComboBox and oleDbDataAdapter1 [System.Data.OleDb.OleDbDataAdapter] from the DataSet ComboBox.
  • 11. Set Labels to "LASTNAME" and Y to "SALARY" as in the image below:

    Ole4.png

  • 12. Now click on the Apply button and run the form; you should now have something like the following:

    Ole5.png

Single Record

Single Record Charting permits the display of all or some records from a single database record on a TeeChart. It may be useful for database views or tables where, for example, sales figures for each year are stored as a separate month column, 'JAN','FEB','MAR'..etc. and stepping through the records displays monthly sales from consecutive years.
Single Record Charting supports Datasets, Tables and DataViews. Use the Chart Editor to select Single Record as a Datasource type for a Chart Series.

NetSingleRecord.png
Most of the steps required to connect to a Single Record data are achieved via the Chart Editor. To navigate the Datasource and update the Chart accordingly you need to write a few lines of code using Windows.Form's CurrencyManager. Please see the section on coded steps for a demonstration of use.

Connecting to ADO.NET at run-time

[VERSION .NET 2003]

DataAdapter

The above design-time steps can be reproduced programmatically with a few lines of code. This gives you more freedom and control over database operations.

Every Series has the "DataSource" property, which determines the origin of database values (the Table or Query), and the "YValues.DataMember" and "LabelMember" properties to specify which fields we want to plot.

Drag a new TeeChart for .Net object onto a new project and a new Form.

[C#] 
using System.Data;
using System.Data.OleDb;
using System.Security;
using System.Security.Permissions;

private void Form1_Load(object sender, System.EventArgs e) {
            DataSet masterDataSet = new DataSet();
            Bar bar1 = new Bar(tChart1.Chart);
            try
            {
                OpenConnWithJet40(ref masterDataSet);
                DataTable employeeTable = masterDataSet.Tables["EMPLOYEE"];
                bar1.YValues.DataMember = employeeTable.Columns["SALARY"].ToString();
                bar1.LabelMember = employeeTable.Columns["LASTNAME"].ToString();
                bar1.DataSource = employeeTable;
            }
            catch (SecurityException)
            {
                MessageBox.Show("The calling method has not been granted sufficient permission to access the Microsoft Jet 4.0 OLE DB Provider.");
            }
}
        

[OleDbPermission(SecurityAction.Demand, Provider = "Microsoft.Jet.OLEDB.4.0")]
  
private void OpenConnWithJet40(ref DataSet masterDataSet) {
            
            OleDbConnection testConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" @"Data Source=C:\Program Files\Steema Software\TeeChart for .NET v3\Sample data\TeeChart.mdb;");          
            testConn.Open();
            string strCom = "SELECT * FROM Employee";
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom,testConn);
            myCommand.Fill(masterDataSet, "Employee");
            testConn.Close();

        }

[VB.Net]
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim MasterDataSet As New DataSet()
            Dim Bar1 As New Steema.TeeChart.Styles.Bar(TChart1.Chart)
            Try
                OpenConnWithJet40(MasterDataSet)
                Dim employeeTable As DataTable = MasterDataSet.Tables("Employee")
                Bar1.YValues.DataMember = employeeTable.Columns("SALARY").ToString()
                Bar1.LabelMember = employeeTable.Columns("LASTNAME").ToString()
                Bar1.DataSource = employeeTable
            Catch ex As Exception
                MessageBox.Show("Microsoft Jet 4.0 OLE DB Provider problem: " & ex.Message)
            End Try
End Sub

Private Sub OpenConnWithJet40(ByRef MasterDataSet As DataSet)
            Dim TestConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Program Files\Steema Software\TeeChart for .NET v3\Sample data\TeeChart.mdb;")
            TestConn.Open()

            Dim StrCom As String = "SELECT * FROM Employee"
            Dim MyCommand As New OleDbDataAdapter(StrCom, TestConn)
            MyCommand.Fill(MasterDataSet, "Employee")
            TestConn.Close()
        End Sub
End Class

In version .NET 2005 there are little changes for version 2005

[VERSION .NET 2005]
DataAdapter

[C#] 
using System.Data;
using System.Data.OleDb;
using System.Security;
using System.Security.Permissions;
private void Form1_Load(object sender, EventArgs e)
{
            //string ConnString = SqlDataSource1.ConnectionString;      
            string sQuery = "select * from Employee";
          
            //SqlConnection myConnection = new SqlConnection(ConnString);            
            string myConnString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\\Program Files\\Steema Software\\TeeChart for .NET v3\\Sample data\\TeeChart.mdb;Uid=Admin;Pwd=;";
            System.Data.Odbc.OdbcConnection myConnection = new System.Data.Odbc.OdbcConnection(myConnString);
          
            //SqlCommand myCommand = new SqlCommand(sQuery, myConnection);
            System.Data.Odbc.OdbcCommand myCommand = new System.Data.Odbc.OdbcCommand(sQuery, myConnection);
            myConnection.Open();
          
            //SqlDataReader myReader = myCommand.ExecuteReader();
            System.Data.Odbc.OdbcDataReader myReader = myCommand.ExecuteReader();

            int XColumn = myReader.GetOrdinal("ID");
            int YColumn = myReader.GetOrdinal("SALARY");
            int LabelColumn = myReader.GetOrdinal("LASTNAME");

            Steema.TeeChart.Styles.Bar bar1 = new Steema.TeeChart.Styles.Bar(tChart1.Chart);

            while (myReader.Read())
            {
                bar1.Add(Convert.ToInt32(myReader[XColumn]), Convert.ToDouble(myReader[YColumn]), Convert.ToString(myReader[LabelColumn]));
            }

            myReader.Close();
            myConnection.Close();
}


[VB.Net] 
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim MasterDataSet As New DataSet()
        Dim Bar1 As New Steema.TeeChart.Styles.Bar(TChart1.Chart)
        Try
            OpenConnWithJet40(MasterDataSet)
            Dim employeeTable As DataTable = MasterDataSet.Tables("EMPLOYEE")
            Bar1.YValues.DataMember = employeeTable.Columns("SALARY").ToString()
            Bar1.LabelMember = employeeTable.Columns("LASTNAME").ToString()
            Bar1.DataSource = employeeTable
        Catch ex As Exception
            MessageBox.Show("Microsoft Jet 4.0 OLE DB Provider problem: " & ex.Message)
        End Try
End Sub

Private Sub OpenConnWithJet40(ByRef MasterDataSet As DataSet)
        
        Dim TestConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Program Files\Steema Software\TeeChart for .NET v3\Sample data\TeeChart.mdb;")
        TestConn.Open()
        Dim StrCom As String = "SELECT * FROM Employee"
        Dim MyCommand As New OleDb.OleDbDataAdapter(StrCom, TestConn)
        MyCommand.Fill(MasterDataSet, "Employee")
        TestConn.Close()
    End Sub

End Class


In case the database values are changed and you want to "refresh" the connection and retrieve the values again, you should do this:

TChart1.Series(0).CheckDataSource 

The "CheckDataSource" method will force the Series to retrieve all records again. All Series have also the "XValues.ValueSource" property. This property is used for XY charting only (when you have an X coordinate for each point).


Single Record

Connection to the Table
Using the Chart Editor will create the following code in the Form's initialisation. Similar code may be used at runtime to create and connect new SingleRecord datasources.
(assuming a DataSet called sourceTableSet with table: sourceTable)

this.singleRecordSource1 = new Steema.TeeChart.Data.SingleRecordSource(); 
this.sourceTableSet.DataSetName = "NewDataSet";
this.sourceTableSet.Tables.AddRange(new System.Data.DataTable[] {this.sourceTable});
this.singleRecordSource1.DataSource = this.sourceTableSet;
this.singleRecordSource1.ValueMembers = new string[] {
                                                             "JAN",
                                                             "FEB",
                                                             "MAR"};
this.bar1 = new Steema.TeeChart.Styles.Bar();
this.bar1.DataSource = this.singleRecordSource1;


Table Navigation

TeeChart's SingleRecord uses the CurrencyManager component to navigate the database table and update the Chart. Use CheckDatasource to update the Chart after changing the Table record.

Example

private CurrencyManager myCurrencyManager; 

public void ConnectChartToTable()
{
     myCurrencyManager = (CurrencyManager)this.BindingContext[sourceTable];
     singleRecordSource1.RecordCurrency=myCurrencyManager;
     tChart1[0].CheckDataSource();
}


The table (and Chart) may be navigated by using the CurrencyManager

    private void MovePrevious() 
    {
      if (myCurrencyManager.Position>0)
      {
        myCurrencyManager.Position=myCurrencyManager.Position 1;
        tChart1[0].CheckDataSource();
      }
    }

    private void MoveNext()
    {
      if (myCurrencyManager.Position<myCurrencyManager.Count)
      {
        myCurrencyManager.Position=myCurrencyManager.Position 1;
        tChart1[0].CheckDataSource();
      }
    }



Using ADO.NET with ASP.NET

You may connect Datasources via ASP.NET using a TeeChart WebChart on an ASP.NET WebForm in a manner virtually identical to that in which datasources can be connection to a TeeChart Component on a WinForm. See the Internet Applications tutorial for more about ASP.NET.


PREVIOUS

NEXT


© 1998-2019 Steema Software SL. All rights reserved.