VB.Net/Database ADO.net/Data Provider

Available ADO.NET Data Providers

<source lang="vbnet">

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
  
   
 </source>

Generic Data Access

<source lang="vbnet"> 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

 </source>

Obtain the DbProviderFactory for SQL Server.

<source lang="vbnet">

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
  
   
 </source>

OleDb Data Provider

<source lang="vbnet"> 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

 </source>

SQL Server Data Provider

<source lang="vbnet"> 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

 </source>