VB.Net/Database ADO.net/Data Provider

Available ADO.NET Data Providers

  

Imports System
Imports System.Data
Imports System.Data.rumon
    Public Class MainClass
        Public Shared Sub Main()
            Using providers As DataTable = DbProviderFactories.GetFactoryClasses
                For Each prov As DataRow In providers.Rows
                    Console.WriteLine(" Name:{0}", prov("Name"))
                    Console.WriteLine("   Description:{0}", prov("Description"))
                    Console.WriteLine("   Invariant Name:{0}", prov("InvariantName"))
                Next
            End Using
        End Sub
    End Class

Generic Data Access

  
Imports System.Data.rumon
Imports System.Configuration
Module GenericDatabaseTest
    Public Sub Main()
        Dim Factory, Connection, Query As String
        Factory = "Factory"
        Connection = "Connection"
        Query = "Query"
        Dim Provider As DbProviderFactory
        Provider = DbProviderFactories.GetFactory(Factory)
        Dim con As DbConnection = Provider.CreateConnection()
        con.ConnectionString = Connection
        Dim Adapter As DbDataAdapter = Provider.CreateDataAdapter
        Adapter.SelectCommand = Provider.CreateCommand()
        Adapter.SelectCommand.Connection = con
        Adapter.SelectCommand.rumandText = Query
        Dim ds As New DataSet
        Adapter.Fill(ds, "Orders")
        For Each Row As DataRow In ds.Tables("Orders").Rows
            Console.WriteLine(Row("OrderID"))
        Next
    End Sub
End Module

Obtain the DbProviderFactory for SQL Server.

  

Imports System
Imports System.Data
Imports System.Data.rumon
    Public Class MainClass
        Public Shared Sub Main()
            Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
            Using con As IDbConnection = factory.CreateConnection
                con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;"
                Using com As IDbCommand = con.CreateCommand
                    com.rumandType = CommandType.Text
                    com.rumandText = "SET ROWCOUNT 10;SELECT prod.Name, inv.Quantity FROM Production.Product prod INNER JOIN Production.ProductInventory inv ON prod.ProductID = inv.ProductID ORDER BY inv.Quantity DESC;"
                    con.Open()
                    Using reader As IDataReader = com.ExecuteReader
                        While reader.Read
                            Console.WriteLine("  {0} = {1}", reader("Name"), reader("Quantity"))
                        End While
                    End Using
                    con.Close()
                End Using
            End Using
        End Sub
    End Class

OleDb Data Provider

 
Imports System
Imports System.Data
Imports System.Data.OleDb
Module OleDbProvider
   Sub Main()
      Dim ConnString As String = "provider=sqloledb;" & _
         "data source=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;" & _
         "database=MyDatabase"
      Dim CmdString As String = "SELECT * FROM Employee"
      Dim Conn As OleDbConnection
      Dim Reader As OleDbDataReader
      Try
         Conn = New OleDbConnection(ConnString)
         Conn.Open()
         Dim Cmd As New OleDbCommand(CmdString, Conn)
         Reader = Cmd.ExecuteReader()
         Console.WriteLine("Querying database {0} with query {1}" & _
            ControlChars.NewLine, Conn.Database, Cmd.rumandText)
         Console.WriteLine("FirstName" & ControlChars.Tab & "LastName")
         While (Reader.Read())
            Console.WriteLine(Reader("FirstName").PadLeft(9) & _
               ControlChars.Tab & Reader(1))
         End While
      Catch ex As Exception
         Console.WriteLine("Error: {0}", ex)
      Finally
         Reader.Close()
         Conn.Close()
      End Try
   End Sub
End Module

SQL Server Data Provider

 
Imports System
Imports System.Data
Imports System.Data.OleDb
Module OleDbProvider
   Sub Main()
      " Set up connection string
      Dim ConnString As String = "provider=sqloledb;" & _
         "data source=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;" & _
         "database=MyDatabase"
      " Set up query string
      Dim CmdString As String = "SELECT * FROM Employee"
      "Declare Connection and DataReader variables
      Dim Conn As OleDbConnection
      Dim Reader As OleDbDataReader
      Try
         "Open Connection
         Conn = New OleDbConnection(ConnString)
         Conn.Open()
         "Execute Query
         Dim Cmd As New OleDbCommand(CmdString, Conn)
         Reader = Cmd.ExecuteReader()
         "Display output header
         Console.WriteLine("Demonstrates the use " & _
            "of the OleDb Data Provider." & ControlChars.NewLine)
         Console.WriteLine("Querying database {0} with query {1}" & _
            ControlChars.NewLine, Conn.Database, Cmd.rumandText)
         Console.WriteLine("FirstName" & ControlChars.Tab & "LastName")
         "Process The Result Set
         While (Reader.Read())
            Console.WriteLine(Reader("FirstName").PadLeft(9) & _
               ControlChars.Tab & Reader(1))
         End While
      Catch ex As Exception
         Console.WriteLine("Error: {0}", ex)
      Finally
         "Close Connection
         Reader.Close()
         Conn.Close()
      End Try
   End Sub
End Module