VB.NET DataTable SqlDataReader Example – SqlDataReader DataTable Example VB.NET

Posted by on Wednesday, December 23, 2009 · Leave a Comment 

VB.NET DataTable SqlDataReader Example – SqlDataReader DataTable Example VB.NET

Purpose: – Illustrates using loaded from .
Prerequistes:

  1. Install Visual Basic (Express or Standard Edition)
  2. Install SQL Server Express
  3. Download Northwind and pubs Database
  4. Attach Northwind Database to Databases in Sql Express
  5. Attach pubs Database to Databases in Sql Express

Notes:

  • Console Application is used to simplify things, but Windows Forms or Web Forms could also be used
  • You can build a library of syntax examples by using same project over and over and just commenting out what you do not want to execute in Module1.vb

Instructions:

  1. Use Visual Basic 2008 Express or Standard Edition
  2. Create new project;
    • Click File/New Project
    • Select Console Application Template
    • Select Visual Basic for Language
    • name of project could be VBNET_Syntax.
  3. Add New folder named “ADONET”
    • Right-click project name in solution explorer;
    • add new folder;
    • name of folder could be: ADONET
  4. Add Class Named clsDataTableSqlDataReader to ADONET folder
    • Right-click ADONET folder;
    • add new item;
    • Select class
    • Class name could be clsDataTableSqlDataReader
  5. Click on copy code in code below to copy code into clsDataTableSqlDataReader.vb
  6. Click on copy code in second set of code below to copy code into Module1.vb
  7. Click green arrow or press F5 to run program

Step 1: Use Copy Code to Cut-n-paste code into clsDataTableSqlDataReader.vb

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

Step 2: Use View Plain to Cut-n-paste code into Module1.vb

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

Filed under Code Samples · Tagged with ,