Using Dataset

The following is the heirarchy of the DataSet.

DataSet
 |-- DataTable
       |--  DataRow
                |-- DataColumn

An important feature of Dataset is its disconnected nature. It connects to database to retrieve data and once data is retrieved then the connection is closed. So DataSet doesn't keep the connection opened throughout and instead it connects when it is required. You are allowed to make changes to Dataset once the data is loaded into it. It is possible to add rows, delete rows and update rows of data tables of Dataset. When the changes to DataSet are to be updated then it connects to data source and makes changes based on the status of the rows in data tables.

OLEDBDataProvider contains a set of data commands and a connect to the database and uses the connection and command objects to retrieve the data and make changes to the data.

The following code creates a Dataset with one DataTable.

' import required namespaces

Imports system
Imports System.data
Imports system.data.oledb


Module DataSetExample1
    
    Public Sub Main()
        
        Dim con As New OLEDBConnection("provider=Microsoft.jet.oledb.4.0;Data Source=c:\srikanth\phones.mdb")
        Dim da As New OLEDBDataAdapter("select * from cities", con)
        Dim ds As New dataset()

            ' create a datatable with the name CITIES
            da.Fill(ds, "cities")

           'display number of rows in the table
            console.writeline(ds.Tables(0).Rows.Count())
            
           ' load another table into dataset
      
           da.selectcommand = new OLEDBCommand("select * from phones",con)
           da.fill(ds,"phones")

           'display number of rows in the table
            console.writeline(ds.Tables("phones").Rows.Count())
        
    End Sub
    
End Module

Then we used Fill method of the DataAdapter class to retrieve the data from the database and create a DataTable in Dataset with the name CITIES.

Rows collection of DataTable provides access to rows of the table and Count method returns the number of rows in rows collection.

The following are the important steps related to Dataset.

• Build and fill each DataTable in a DataSet with data from a data source
• Change the data in DataTable objects by adding, updating, or deleting DataRow objects.
• Apply the changes of the DataTable objects to data source.

' import required namespaces

Imports system
Imports System.data
Imports system.data.oledb


Module UpdateSetExample
    
    Public Sub Main()
        
        Dim con As New OLEDBConnection("provider=Microsoft.jet.oledb.4.0;Data Source=c:\srikanth\phones.mdb")
        Dim da as new OLEDBDataAdapter()
        dim ds as new dataset

           ' load another table into dataset
      
           da.selectcommand = new OLEDBCommand("select * from phones",con)
           da.fill(ds,"phones")

          ' update rows 
          dim  r as datarow
          dim phno as string

          for  each  r in  ds.tables(0).rows
               ' change starting digit of phone number from 5 to 7
                phno =  r.item("phoneno") 
                if  phno.substring(0,1) = "5" then
                       phno = "7" & phno.substring(1)
                       ' update current row
                       r.BeginEdit()
                       r.item("Phoneno") = phno
                       r.EndEdit() 

               end if
         next

        '  OLEDBCommandBuilder will automatically create required commands 
        dim  cmdbuilder  as new OLEDBCommandBuilder(da)
        da.update(ds,"phones") 
        Console.writeline("Change is successful")
        
    End Sub
    
End Module

• Connects to PHONES.MDB database using OLEDBConnection object
• Creates an object OLEDBDataAdapter
• Creates a new object of DataSet
• Sets SelectCommand property of OLEDbAdapter to the required command
• Loads data into DataTable with the name PHONES
• Uses FOR EACH loop to take one row at a time from data table
• If PHONENO starts with 5 then the starting digit is replaced with 7.
• Before the change is made, it executed BeginEdit and EditEdit methods of DataRow object before and after changing the data in the row.
• It creates an object of OLEDBCommandBuilder class, which is used to create required DML command automatically. This object takes an object of DataAdapter as parameter to its constructor.
• Finally it called Update method of DataAdapter.

Setting relationship between data tables

Relationship is set using DataRelation object.

The following example shows how to set relation ship between CITITES and PHONES table using DataRelation object.

' import required namespaces

Imports system
Imports System.data
Imports system.data.oledb


Module RelationExample
    
    Public Sub Main()
        
        Dim con As New OLEDBConnection("provider=Microsoft.jet.oledb.4.0;Data Source=c:\srikanth\phones.mdb")
        Dim da as new OLEDBDataAdapter()
        dim ds as new dataset

           ' load data into tables from data source
      
           da.selectcommand = new OLEDBCommand("select * from cities",con)
           da.fill(ds,"cities")

           da.selectcommand = new OLEDBCommand("select * from phones",con)
           da.fill(ds,"phones")


          Dim pcol As DataColumn
          Dim ccol As DataColumn

         pcol = ds.Tables("cities").columns("citycode")
         ccol = ds.Tables("phones").Columns("citycode")

        ' Create DataRelation.
         Dim rel As DataRelation
         rel = New DataRelation("CityPhones", pcol, ccol) 

        ' Add the relation to the DataSet.
         ds.Relations.Add(rel) 

         ' use relation object to retrieve child rows for each parent row
         dim parentrow,childrow as datarow
   
         for each  parentrow in  ds.tables(0).rows
               Console.writeline("City : " &  parentrow("cityname") )
                for each childrow  in  parentrow.getchildrows("cityphones") 
                         Console.writeline( childrow.item("person") & " - " &  childrow.item("phoneno"))
                 next
        next

    
    End Sub
    
End Module

The method GetChildRows takes the name of the data relation object and return all child rows of the given parent row.

The program take one parent row at a time using For Each loop. And then it takes all child rows of each parent row using the relation object.

In this article I have concentrated on major features of DataSet. However, there are some other applications of Dataset. For example, datasets are very widely used to display data using server side controls in ASP.NET and even in windows based controls.

For more information about DataSet, DataAdapter and other related object, please go through documentation as it contains several examples and details of each property and method.

P.Srikanth