Now you'll see an example of how to access data from a data source using the ODBC data provider. In this example, you'll use the Access 2000 Northwind database as the data source.
Before creating a connection, the first thing you need to set is the connection string. The connection string for OdbcConnection contains a data source driver and the data source path with an optional user ID and password. Optionally, you can also use an ODBC Data Source Name (DSN) as the connection string. You create a DSN from the ODBC Administration applet in the Windows Control Panel.
The connection string for an Oracle database looks like the following:
Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;UID=odbcuser;PWD=odbc$5xr
The connection string for a Microsoft Access database looks like the following:
Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\Northwind.mdb
The connection string for an Excel database looks like the following:
Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls
The connection string for a text database looks like the following:
Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\\
You can use any DSN by using the following connection string:
DSN=dsnname
The connection string for a SQL Server database looks like following:
"DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=Qvr&77xk;DATABASE=northwind;";
Listing 10-1 reads data from the Northwind database and shows the results on the console. For this example, create a console application to test the code. As you can see from Listing 10-1, first you import the Microsoft.Data.Odbc namespace. After that you create an OdbcConnection object with the Microsoft Access ODBC driver and the Northwind database. The next step is to create an OdbcCommand object and call the ExecuteReader method, which returns an OdbcDataReader object. Finally, you read data from the DataReader and display the results on the console.
Listing 10-1: Reading Data from Northwind Using the ODBC Data Provider
Imports Microsoft.Data.Odbc Module Module1 Sub Main() ' Build a connection and SQL strings Dim connectionString As String = _ "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\Northwind.mdb" Dim SQL As String _ = "SELECT EmployeeID, FirstName, LastName FROM Employees" ' Create connection object Dim conn As OdbcConnection = New OdbcConnection(connectionString) ' Create command object Dim cmd As OdbcCommand = New OdbcCommand(SQL) cmd.Connection = conn ' Open connection conn.Open() ' Call command's ExecuteReader Dim reader As OdbcDataReader = cmd.ExecuteReader() ' Read the reader and display results on the console Console.WriteLine("Employeed ID, First Name, Last Name ") While reader.Read() Console.Write(reader.GetInt32(0).ToString()) Console.Write(", ") Console.Write(reader.GetString(1).ToString()) Console.Write(", ") Console.WriteLine(reader.GetString(2).ToString()) End While ' close reader and connection reader.Close() conn.Close() conn.Dispose() End Sub End Module
The output of Listing 10-1 looks like Figure 10-1. As you can see, the program reads the EmployeeID, FirstName, and LastName fields from Employees table.
Figure 10-1: The output of Listing 10-1