VB.net learning: A Complete Data Provider Factory Example

Step 1:

insert an App.config file to the current project and define an
empty <appSettings> element. Add a new key-named provider that maps to the namespace name of the
data provider you wish to obtain (System.Data.SqlClient). Also, define a connection string that
represents a connection to the xx database.


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<!-- Which provider? -->
<add key="provider" value="System.Data.SqlClient" />
<!-- Which connection string? -->
<add key="cnStr" value= "Data Source=(local)\SQLEXPRESS;
Initial Catalog=AutoLot;Integrated Security=True"/>
</appSettings>
...
</configuration>

Step 2: Read the property file

Sub Main()
Console.WriteLine("***** Fun with Data Provider Factories *****" & vbLf)
' Get Connection string/provider from *.config.
Dim dp As String = ConfigurationManager.AppSettings("provider")
Dim cnStr As String = ConfigurationManager.AppSettings("cnStr")
' Get the factory provider.
Dim df As DbProviderFactory = DbProviderFactories.GetFactory(dp)

step 3: ` Now get the connection object.
Using cn As DbConnection = df.CreateConnection()
Console.WriteLine("Your connection object is a: {0}", cn.GetType().Name)
cn.ConnectionString = cnStr
cn.Open()
' Make command object.
Dim cmd As DbCommand = df.CreateCommand()
Console.WriteLine("Your command object is a: {0}", cmd.GetType().Name)
cmd.Connection = cn
cmd.CommandText = "Select * From Inventory"
' Print out data with data reader.
Using dr As DbDataReader = cmd.ExecuteReader()
Console.WriteLine("Your data reader object is a: {0}",
dr.GetType().Name)
Console.WriteLine(vbLf & "***** Current Inventory *****")
Do While dr.Read()
Console.WriteLine("-> Car #{0} is a {1}.", dr("CarID"),
dr("Make").ToString())
Loop
End Using
End Using
Console.ReadLine()
End Sub