Using Data Sets ado.net tutorials

Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013

This lesson describes how to use Data Sets

A DataSet object is an abstract representation of a set of data that provides a consistent relational programming model regardless of the data source. Thus it enables you to store and modify large amounts of data in an in-memory cache, view the data as tables, and process the data either connected to or disconnected from data sources.

It includes related tables, constraints, and relationships among the tables which is like having a small relational database residing in memory so be careful about how much data you put into it, as it consumes memory.

You can use one of several methods for working with a DataSet or you can use them in combination:

For instance you can programmatically create DataTables, DataRelations, and constraints within the DataSet and populate these objects with data.

    ' instantiate a data table #1
    Dim table1 As New DataTable
    ' instantiate a data table #2
    Dim table2 As New DataTable

    ' declare and create a sql command #1
    Dim command1 As SqlCommand = connection.CreateCommand
    ' declare and create a sql command #2
    Dim command2 As SqlCommand = connection.CreateCommand

    ' set the sql statements
    command1.CommandText = "SELECT UserID, Username FROM aspnet_Users"
    command2.CommandText = "SELECT UserID, RoleId FROM aspnet_UsersInRoles"

    ' fill the data tables with values
    table1.Load(command1.ExecuteReader)
    table2.Load(command2.ExecuteReader)

    ' instantiate a data set
    Dim dataset As New DataSet

    ' add the tables
    dataset.Tables.Add(table1)
    dataset.Tables.Add(table2)

    ' create data relation
    Dim relations As New DataRelation("Users", _
    dataset.Tables(0).Columns("UserID"), _
    dataset.Tables(1).Columns("UserID"))
    
    ' add the data relation
    dataset.Relations.Add(relations)

    ' ToDo: something with the dataset
  

Also you can populate the DataSet using a DataAdapter.

    
    ' declare and initialize the SqlDataAdapter
    Dim adapter As New SqlDataAdapter( _
    "SELECT Username FROM aspnet_Users", connection)

    Dim dataset As New DataSet
    adapter.Fill(dataset)
    ' ToDo: something with the dataset e.g.
    DataGrid1.DataSource = dataset.Tables(0)
  

In addition you can load and persist a DataSet or DataTable using XML or load a DataSet from an XSD schema file.

    ' instantiate a data set
    Dim dataset As New DataSet
    ' read xml data
    dataset.ReadXml("myxmlfile.xml")
  

Finally you can use DataReader object to load a DataSet.

    ' declare and create a sql command object
    Dim command As SqlCommand = connection.CreateCommand

    ' set the sql statement to execute at the data source
    command.CommandText = "SELECT Username FROM aspnet_Users"

    ' instantiate a data table
    Dim table As New DataTable

    ' fill the table with data
    table.Load(command.ExecuteReader)
    ' release the resources
    command.Dispose()

    ' declare new data set (in-memory cache of data)
    Dim dataset As New DataSet
    ' add the table to the data set
    dataset.Tables.Add(table)

    ' ToDo: something with the dataset
  


Share This

Home | About | Contact | Privacy Policy

Copyright visual-basic-tutorials.com 2017 - All Rights Reserved.