VB.Net/Database ADO.net/DataSet Read
Multiple Tables in DataSet
<source lang="vbnet"> 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
</source>
Populate Data from data table
<source lang="vbnet"> 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
</source>