Accessing Access Databases

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:


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

start example
 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 
end example

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.

click to expand
Figure 10-1: The output of Listing 10-1

Applied ADO. NET(c) Building Data-Driven Solutions
Applied ADO.NET: Building Data-Driven Solutions
ISBN: 1590590732
EAN: 2147483647
Year: 2006
Pages: 214 © 2008-2017.
If you may any questions please contact us: