Tutorial 8 - ADO Database access
Contents

Introduction

Connecting to ADO.NET sources at design-time
     DataAdapter [VS2003]
     Single Record
     DataAdapter[VS2005,VS2008,VS2010,VS2012,VS2013]
     Single Record


Connecting to ADO.NET sources at run-time
     DataAdapter
     Single Record

Using ADO.NET with ASP.NET

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 [VS2003]
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:




  • 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:




  • 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:




  • 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:

    •   


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







    • BindingSource [VS2005,VS2008,VS2010,VS2012,VS2013]
    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 BindingSource onto a WinForm with a TeeChart for .Net object on it.




  • 2. Go to Data tab and select "Show Data Source"




  • 3. Right-Click on the Data Source toolbox and select "Add New Data Source".

  • 4. Select Database, select/create the connection string in our example we use Microsoft Access DataBase File (OLE DB), click continue:






    • Note: Click to Test Connection button to check the connection of your datasource.
  • 5. Finally select the tables you want to connect in Browse:




  • 6. Select the table you want to use as data source from the Data Sources toolbox and drag it to your form.




  • 7. Now open the TeeChart Editor, add in a BarSeries and navigate to the DataSource tab of the Series tab.

  • 8. Select "Database" from the ComboBox and  bindingSource1 from the DataSet ComboBox.
    •   
  • 9.  Set Labels to "LASTNAME" and Y to "SALARY" as in the image below:




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




    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.



    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 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.