Net-informations.com
SiteMap  | About    

How to merge tables in a Dataset - Sql Server

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

connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
firstSql = "Your First SQL Statement Here"
secondSql = "Your Second SQL Statement Here"

You have to replace the string with your real time variables.


 ADO.NET Dataset - Related Contents


 ADO.NET Dataset - Related Programs