In this Article, we will learn how to store multiple tables in a single DataSet
using SqlServer + VB.NET.
Step 1:
- Create One Database File named " DemoFile" in Microsoft SqlServer Management Studio Express
- Create 3 Tables as follows
- Employee_Info (EmpID, Name, Department, Designation)
- Payment_Info (EmpID, BasicSalary, Allowance, GrossSalary)
- Work_Info (EmpID, WorkAssigned)
Now come to Front End Window Forms Application in VB.NET
Step 2:
- Take One ListBox (named List1), One Button (named cmdUpdate), Four Labels and One DataGridView (named GridView)
Step 3: Code behind Form
Imports System.Data.SqlClient
Imports
System.Data.SqlClient
Public Class DataSetDemo
Dim Conn As New SqlConnection("Data
Source=Studio\SqlExpress; Initial Catalog=DemoFile;Integrated Security=True")
'Create new SqlConnection
String
Dim Dset As New DataSet()
'Create new Instance of DataSet Class
Dim adapt As
SqlDataAdapter 'Create new Instance of
SqlDataAdapter Class
Public Sub
Clear() 'Clear the Previous
Contents on Labels
lblRow.Text = ""
lblCol.Text = ""
End Sub
Private Sub
cmdUpdate_Click(ByVal sender
As System.Object, _
ByVal e
As System.EventArgs)
Handles cmdUpdate.Click
Dset.Clear() 'Clear the
DataSet Contents
adapt =
New SqlDataAdapter("select
* from Employee_Info", Conn)
adapt.Fill(Dset, "Employee_Info")
'Add Employee_Info Table to DataSet
adapt =
New SqlDataAdapter("select
* from Payment_Info", Conn)
adapt.Fill(Dset, "Payment_Info")
'Add Payment_Info Table to DataSet
adapt = New SqlDataAdapter("select
* from Work_Info", Conn)
adapt.Fill(Dset, "Work_Info")
'Add Work_Info Table to DataSet
'Now
Select the TableName From ListBox And Load the Same in DataGridView
For Each item As Integer In
List1.SelectedIndices
GridView.DataSource = Dset.Tables(item)
Clear()
lblRow.Text = Dset.Tables(item).Rows.Count.ToString
'Count
the no. of Rows in the Table
lblCol.Text = Dset.Tables(item).Columns.Count.ToString 'Count
the no. of Columns in the Table
Next
End Sub
End Class
Step 4: Execute the Code, Select Table Name and Click the Update Button
