The DataSet consists of a collection of DataTable objects and it is major component of of the ADO.NET architecture in .net.
In this article we will discuss how to use dataset in vb.net. This article covers these key points:

Establish sql connection

So first we cover how to establish connection to sql table, we use sqlclient.sqlconnection class for establishing connection with sql database with valid connection string. Now I am describing about connection string little bit. We can use different –different connection string according to our requirement such as if we use secure connection then we can use the following connection string:

Data Source=Servername; Initial Catalog=DataBase name;User Id=myUsername;Password=myPassword;

Use serverName\instanceName as Data Source to connect to a specific SQL Server instance.

Note: if we are using sql server express instance then we need to write server name as ServerName\SqlExpress, if we have some specific instance name then you need to write server name as ServerName\SqlExpress Instance Name.

If we use trusted connection then we can use this connection string:

Data Source= Servername; Initial Catalog= DataBase name;Integrated Security=true

Retrieve data into dataset from SQl Server table

Following example use Sql server 2005 with database name ‘EmployeeDatabase’
We have a ‘tbl_employee’ table in EmployeeDatabase database that has this structure:
Table name: tbl_Employee

Now we want to make dataset of all records of this table, use this code snippet:

    Private Sub MakeDataset()
        Dim ConnectionString = "Data Source=Author_machine; Initial Catalog=EmployeeDatabase;Integrated Security=true"
        Dim SqlCon As New SqlClient.SqlConnection
        SqlCon.ConnectionString = ConnectionString
        SqlCon.Open()
        Dim cmd As New SqlClient.SqlCommand
        cmd.Connection = SqlCon
        cmd.CommandText = "Select * from tbl_employee"
        cmd.CommandTimeout = 6000
        Dim oDataAdapter As New SqlClient.SqlDataAdapter(cmd)
        oDataAdapter.Fill(ds)
    End Sub

Use dataset in application for getting data or read dataset

Now you want to use this dataset ‘ds’ in your window application, suppose you need to bind ComboBox control with all employee name

Private Sub BindComboBox()
        For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
            ComboBox1.Items.Add(ds.Tables(0).Rows(i)("EmpName"))
        Next
    End Sub

Something more on DataSet

Create Datatable and add into dataset at run time

The following example show how we can create a datatable at run time and then add to data set.
 

Private Sub CreateDataset()
        Dim table1 As DataTable = New DataTable("Employee")
        table1.Columns.Add("name")
        table1.Columns.Add("id")
        table1.Rows.Add("Ram", 1)
        ' Create DataSet and add datatable
        Dim ds As DataSet = New DataSet("Department")
        ds .Tables.Add(table1)  
    End Sub

Create XML from Dataset

You can create a xml file from dataset( Read This) at run time. In the following example we are using WriteXml method of the dataset class.

Private Sub CreateDataset()
        Dim table1 As DataTable = New DataTable("Employee")
        table1.Columns.Add("name")
        table1.Columns.Add("id")
        table1.Rows.Add("Ram", 1)
        ' Create DataSet and add datatable
        Dim ds As DataSet = New DataSet("Department")
        ds .Tables.Add(table1)  
       'Write xml file on specified path
        ds.WriteXml("put xml file path with file name")
    End Sub