The DataSet contains copy of the data we requested through the SQL statement. The SqlDataAdapter object allows us to populate DataTable in a DataSet. We can use Fill method in the SqlDataAdapter for populating data in a Dataset. We can populate Dataset with more than one table at a time using SqlDataAdapter Object. The DataTableCollection contains zero or more DataTable objects.
In some situation we want to combine the result of multiple SQL query as a single result set. In that case we can use the Dataset's Merge method for doing this. The tables involved in the merge should be identical, that is the columns are similar data types .
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dt As DataTable
Dim firstSql As String
Dim secondSql As String
Dim i As Integer
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
firstSql = "Your First SQL Statement Here"
secondSql = "Your Second SQL Statement Here"
connection = New SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(firstSql, connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Table(0)")
adapter.SelectCommand.CommandText = secondSql
adapter.Fill(ds, "Table(1)")
adapter.Dispose()
command.Dispose()
connection.Close()
ds.Tables(0).Merge(ds.Tables(1))
dt = ds.Tables(0)
For i = 0 To dt.Rows.Count - 1
MsgBox(dt.Rows(i).Item(0) & " -- " & dt.Rows(i).Item(1))
Next
Catch ex As Exception
MsgBox("Can not open connection ! ")
End Try
End Sub
End Class