VB.Net/Database ADO.net/DataSet Read

Multiple Tables in DataSet

  
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.Data
Public Class Form1
    Inherits System.Windows.Forms.Form
    Public Sub New()
        MyBase.New()
        InitializeComponent()
    End Sub
    Friend WithEvents Button1 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button()
        Me.SuspendLayout()
        "
        Me.Button1.Location = New System.Drawing.Point(96, 48)
        Me.Button1.Size = New System.Drawing.Size(272, 40)
        Me.Button1.Text = "Do"
        "
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(456, 142)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Button1})
        Me.ResumeLayout(False)
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyDataSet As New DataSet()
        Dim Tables(2) As String
        Tables(0) = "authors" : Tables(1) = "sales" : Tables(2) = "titles"
        MyDataSet = GetDataSet("data source=localhost;initial catalog=pubs;user id=sa;pwd=", Tables)
    End Sub
    Public Function GetDataSet(ByVal ConnectionString As String, ByRef Tables() As String) As System.Data.DataSet
        Dim objConn As New System.Data.SqlClient.SqlConnection(ConnectionString)
        Dim objCmd As New System.Data.SqlClient.SqlCommand()
        objCmd.Connection = objConn
        objCmd.rumandType = System.Data.rumandType.Text
        Dim objDS As New System.Data.DataSet()
        Dim objDA As New System.Data.SqlClient.SqlDataAdapter(objCmd)
        objDA.SelectCommand = objCmd
        objConn.Open()
        Dim intCount As Integer
        For intCount = 0 To Tables.GetUpperBound(0)
            objCmd.rumandText = "SELECT * FROM " & Tables(intCount)
            objDA.Fill(objDS, Tables(intCount))
        Next
        objConn.Close()
        Return objDS
    End Function
End Class

Populate Data from data table

 
Imports System
Imports System.Data
Imports System.Data.SqlClient
public class MainClass
   Shared Sub Main()
      Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
          "integrated security=sspi;database=MyDatabase")

      "Sql Query
      Dim sql As String = "SELECT * From Employee"
      Try
         " Open Connection
         thisConnection.Open()
         Console.WriteLine("Connection Opened")
         " Create Data Adapter
         Dim da As New SqlDataAdapter(sql, thisConnection)
         " Create and fill Dataset
         Dim ds As New DataSet
         da.Fill(ds, "Employee")
         " Get Data Table
         Dim dt As DataTable = ds.Tables("Employee")
         "Display Data
         For Each row As DataRow In dt.Rows
            For Each col As DataColumn In dt.Columns
               Console.WriteLine(row(col))
            Next
            Console.WriteLine("".PadLeft(20, "="))
         Next
      Catch ex As SqlException
         " Display error
         Console.WriteLine("Error: " & ex.ToString())
      Finally
         " Close Connection
         thisConnection.Close()
         Console.WriteLine("Connection Closed")
      End Try
   End Sub
End Class