Progress Monitor for IdealProgrammer.com

Take Swift, Intelligent, Massive, Planned, Loving, Effective (S.I.M.P.L.E.) Action to Transform Self into Ideal

Skip Repetitive Navigational Links

Home

News

Presenters

Contact

Please login

VB.NET DataTable SqlDataReader Example

Description: Illustrates using DataTable loaded from SqlDataReader.

Example Class:

Imports System
Imports System.Data
Imports System.Data.SqlClient
 
 
Public Class clsDataTableSqlDataReader
    Shared WithEvents con As SqlConnection
 
    Public Sub Main()
        con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI")
 
        Dim cmd As New SqlCommand()
 
        cmd.CommandText = "SELECT EmployeeID, FirstName + ' ' + LastName as FullName FROM Employees"
        cmd.Connection = con
 
        Dim Table1 As DataTable
        Table1 = New DataTable("Employees")
        'creating a table named Employees
        Dim Row1 As DataRow
        'declaring row for the table
 
        Dim EmployeeID As DataColumn = New DataColumn("EmployeeID")
        'declaring a column named EmployeeID
        EmployeeID.DataType = System.Type.GetType("System.Int32")
        'setting the datatype for the column
        Table1.Columns.Add(EmployeeID)
        'adding the column to table
        Dim FullName As DataColumn = New DataColumn("FullName")
        FullName.DataType = System.Type.GetType("System.String")
        Table1.Columns.Add(FullName)
 
 
        Try
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader '(CommandBehavior.SingleRow)
            While reader.Read()
                Row1 = Table1.NewRow()
                'declaring a new row
                Row1.Item("EmployeeID") = reader.GetInt32(0)
                'filling the row with values. Item property is used to set the field value.
                Row1.Item("FullName") = reader.GetString(1)
                'filling the row with values. adding FullName
                Table1.Rows.Add(Row1)
 
            End While
            reader.Close()
        Finally
            con.Close()
        End Try
 
        For Each row In Table1.Rows
            Console.WriteLine(row.Item("EmployeeID").ToString().PadRight(10) + row.Item("FullName").ToString())
 
        Next
        Console.ReadLine()
    End Sub
End Class

Example Module:

Module Module1
 
    Sub Main()
        '***** ADONET *************
 
        Dim myDataTableSqlDataReader As New clsDataTableSqlDataReader
        myDataTableSqlDataReader.Main()
 
 
    End Sub
 
End Module