WindowsDevCenter.com
oreilly.comSafari Books Online.Conferences.

advertisement


AddThis Social Bookmark Button O'Reilly Book Excerpts: Programming Visual Basic .NET

ADO.NET, Part 2

Related Reading

Programming Visual Basic .NET
By Dave Grundgeiger

by Dave Grundgeiger

This is the second installment from the Programming Visual Basic .NET chapter on ADO.NET, focusing on connecting to an OLE DB data source and reading data into a data set.

Connecting to an OLE DB Data Source

OLE DB is a specification for wrapping data sources in a COM-based API so that data sources can be accessed in a polymorphic way. The concept is the same as ADO.NET's concept of managed providers. OLE DB predates ADO.NET and will eventually be superseded by it. However, over the years, OLE DB providers have been written for many data sources, including Oracle, Microsoft Access, Microsoft Exchange, and others, whereas currently only one product--SQL Server--is natively supported by an ADO.NET managed provider. To provide immediate support in ADO.NET for a wide range of data sources, Microsoft has supplied an ADO.NET managed provider for OLE DB. That means that ADO.NET can work with any data source for which there is an OLE DB data provider. Furthermore, because there is an OLE DB provider that wraps ODBC (an even older data-access technology), ADO.NET can work with virtually all legacy data, regardless of the source.

Connecting to an OLE DB data source is similar to connecting to SQL Server, with a few differences: the OleDbConnection class (from the System.Data.OleDb namespace) is used instead of the SqlConnection class, and the connection string is slightly different. When using the OleDbConnection class, the connection string must specify the OLE DB provider that is to be used as well as additional information that tells the OLE DB provider where the actual data is. For example, the following code opens a connection to the Northwind sample database in Microsoft Access:

' Open a connection to the database.
Dim strConnection As String = _
   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
   & "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim cn As OleDbConnection = New OleDbConnection(strConnection)
cn.Open(  )

Similarly, this code opens a connection to an Oracle database:

' Open a connection to the database.
Dim strConnection As String = _
   "Provider=MSDAORA.1;User ID=MyID;Password=MyPassword;" _
   & "Data Source=MyDatabaseService.MyDomain.com"
Dim cn As OleDbConnection = New OleDbConnection(strConnection)
cn.Open(  )

The values of each setting in the connection string, and even the set of settings that are allowed in the connection string, are dependent on the specific OLE DB provider being used. Refer to the documentation for the specific OLE DB provider for more information.

Table 8-2 shows the provider names for several of the most common OLE DB providers.

Table 8-2: Common OLE DB provider names

Data source

OLE DB provider name

Microsoft Access

Microsoft.Jet.OLEDB.4.0

Microsoft Indexing Service

MSIDXS.1

Microsoft SQL Server

SQLOLEDB.1

Oracle

MSDAORA.1

Reading Data into a DataSet

The DataSet class is ADO.NET's highly flexible, general-purpose mechanism for reading and updating data. Example 8-1 shows how to issue a SQL SELECT statement against the SQL Server Northwind sample database to retrieve and display the names of companies located in London. The resulting display is shown in Figure 8-1.

Figure 8-1. The output generated by the code in Example 8-1
Figure 1

 

Example 8-1: Retrieving data from SQL Server using a SQL SELECT statement

' Open a connection to the database.
Dim strConnection As String = _
   "Data Source=localhost; Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )
 
' Set up a data set command object.
Dim strSelect As String = "SELECT * FROM Customers WHERE City = 'London'"
Dim dscmd As New SqlDataAdapter(strSelect, cn)
 
' Load a data set.
Dim ds As New DataSet(  )
dscmd.Fill(ds, "LondonCustomers")
 
' Close the connection.
cn.Close(  )
 
' Do something with the data set.
Dim dt As DataTable = ds.Tables.Item("LondonCustomers")
Dim rowCustomer As DataRow
For Each rowCustomer In dt.Rows
   Console.WriteLine(rowCustomer.Item("CompanyName"))
Next

The code in Example 8-1 performs the following steps to obtain data from the database:

  1. Opens a connection to the database using a SqlConnection object.
  2. Instantiates an object of type SqlDataAdapter in preparation for filling a DataSet object. In Example 8-1, a SQL SELECT command string and a Connection object are passed to the SqlDataAdapter object's constructor.
  3. Instantiates an object of type DataSet and fills it by calling the SqlDataAdapter object's Fill method.

Pages: 1, 2, 3, 4

Next Pagearrow