ONDotNet.com    
 Published on ONDotNet.com (http://www.ondotnet.com/)
 See this if you're having trouble printing code examples


O'Reilly Book Excerpts: Programming Visual Basic .NET

ADO.NET, Part 1

Related Reading

Programming Visual Basic .NET
By Dave Grundgeiger

by Dave Grundgeiger

This excerpt is Chapter 8 from Programming Visual Basic .NET, published in December 2001 by O'Reilly.

A Brief History of Universal Data Access

Database management systems provide APIs that allow application programmers to create and access databases. The set of APIs that each manufacturer's system supplies is unique to that manufacturer. Microsoft has long recognized that it is inefficient and error prone for an applications programmer to attempt to master and use all the APIs for the various available database management systems. What's more, if a new database management system is released, an existing application can't make use of it without being rewritten to understand the new APIs. What is needed is a common database API.

Microsoft's previous steps in this direction included Open Database Connectivity (ODBC), OLE DB, and ADO (not to be confused with ADO.NET). Microsoft has made improvements with each new technology.

With .NET, Microsoft has released a new mechanism for accessing data: ADO.NET. The name is a carryover from Microsoft's ADO (ActiveX Data Objects) technology, but it no longer stands for ActiveX Data Objects--it's just ADO.NET. To avoid confusion, I will refer to ADO.NET as ADO.NET and to ADO as classic ADO.

If you're familiar with classic ADO, be careful--ADO.NET is not a descendant, it's a new technology. In order to support the Internet evolution, ADO.NET is highly focused on disconnected data and on the ability for anything to be a source of data. While you will find many concepts in ADO.NET to be similar to concepts in classic ADO, it is not the same.

Managed Providers

When speaking of data access, it's useful to distinguish between providers of data and consumers of data. A data provider encapsulates data and provides access to it in a generic way. The data itself can be in any form or location. For example, the data may be in a typical database management system such as SQL Server, or it may be distributed around the world and accessed via web services. The data provider shields the data consumer from having to know how to reach the data. In ADO.NET, data providers are referred to as managed providers.

A data consumer is an application that uses the services of a data provider for the purposes of storing, retrieving, and manipulating data. A customer-service application that manipulates a customer database is a typical example of a data consumer. To consume data, the application must know how to access one or more data providers.

ADO.NET is comprised of many classes, but five take center stage:

Connection
Represents a connection to a data source.

Command
Represents a query or a command that is to be executed by a data source.

DataSet
Represents data. The DataSet can be filled either from a data source (using a DataAdapter object) or dynamically.

DataAdapter
Used for filling a DataSet from a data source.

DataReader
Used for fast, efficient, forward-only reading of a data source.

With the exception of DataSet, these five names are not the actual classes used for accessing data sources. Each managed provider exposes classes specific to that provider. For example, the SQL Server managed provider exposes the SqlConnection, SqlCommand, SqlDataAdapter, and SqlDataReader classes. The DataSet class is used with all managed providers.

Any data-source vendor can write a managed provider to make that data source available to ADO.NET data consumers. Microsoft has supplied two managed providers in the .NET Framework: SQL Server and OLE DB.

The examples in this chapter are coded against the SQL Server managed provider, for two reasons. The first is that I believe that most programmers writing data access code in Visual Basic .NET will be doing so against a SQL Server database. Second, the information about the SQL Server managed provider is easily transferable to any other managed provider.

Connecting to a SQL Server Database

To read and write information to and from a SQL Server database, it is necessary first to establish a connection to the database. This is done with the SqlConnection object, found in the System.Data.SqlClient namespace. Here's an example:

' Open a database connection.
Dim strConnection As String = _
   "Data Source=localhost;Initial Catalog=Northwind;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(strConnection)
cn.Open(  )

This code fragment instantiates an object of type SqlConnection, passing its constructor a connection string. Calling the SqlConnection object's Open method opens the connection. A connection must be open for data to be read or written, or for commands to be executed. When you're finished accessing the database, use the Close method to close the connection:

' Close the database connection.
cn.Close(  )

The connection string argument to the SqlConnection class's constructor provides information that allows the SqlConnection object to find the SQL Server database. The connection string shown in the earlier code fragment indicates that the database is located on the same machine that is running the code snippet (Data Source=localhost), that the database name is Northwind (Initial Catalog=Northwind), and that the user ID that should be used for logging in to SQL Server is the current Windows login account (Integrated Security=True). Table 8-1 shows the valid SQL Server connection string settings.

Table 8-1: SQL Server connection string settings

Setting

Default Value

Description

Addr

 

Synonym for Data Source.

Address

 

Synonym for Data Source.

Application Name

 

The name of the client application. If provided, SQL Server uses this name in its sysprocesses table to help identify the process serving this connection.

AttachDBFilename

 

Synonym for Initial File Name.

Connect Timeout

15

Synonym for Connection Timeout.

Connection Timeout

15

The number of seconds to wait for a login response from SQL Server. If no response is received during this period, an SqlException exception is thrown.

This setting corresponds to the SqlConnection object's ConnectionTimeout property.

Current Language

 

The language to use for this session with SQL Server. The value of this setting must match one of the entries in either the "name" column or the "alias" column of the "master.dbo.syslanguages" system table. If this setting is not specified, SQL Server uses either its system default language or a user-specific default language, depending on its configuration.

The language setting affects the way dates are displayed and may affect the way SQL Server messages are displayed.

Search for "SQL Server Language Support" in SQL Server Books Online for more information.

Data Source

 

The name or network address of the computer on which SQL Server is located.

This setting corresponds to the SqlConnection object's DataSource property.

extended properties

 

Synonym for Initial File Name.

Initial Catalog

 

The name of the database to use within SQL Server.

This setting corresponds to the SqlConnection object's Database property.

Initial File Name

 

The full pathname of the primary file of an attachable database.

If this setting is specified, the Initial Catalog setting must also be specified.

Search for "Attaching and Detaching Databases" in SQL Server Books Online for more information.

AttachDBFilename and extended properties are synonyms for Initial File Name.

Integrated Security

`false'

Indicates whether to use NT security for authentication. A value of `true' or `sspi' (Security Support Provider Interface) indicates that NT security should be used. A value of `false' indicates that SQL Server security should be used.

Search for "How SQL Server Implements Security" in SQL Server Books Online for more information.

Net

`dbmssocn'

Synonym for Network Library.

Network Address

 

Synonym for Data Source.

Network Library

`dbmssocn'

The name of the .dll that manages network communications with SQL Server. The default value, `dbmssocn', is appropriate for clients that communicate with SQL Server over TCP/IP.

Search for "Communication Components" and "Net-Libraries and Network Protocols" in SQL Server Books Online for more information.

Password

 

The SQL Server login password for the user specified in the User ID setting.

Persist Security Info

`false'

Specifies whether SqlConnection object properties can return security-sensitive information while a connection is open.

Before a connection is opened, its security-sensitive properties return whatever was placed in them. After a connection is opened, properties return security-sensitive information only if the Persist Security Info setting was specified as `true'.

For example, if Persist Security Info is `false' and the connection has been opened, the value returned by the SqlConnection object's ConnectionString property does not show the Password setting, even if the Password setting was specified.

Pwd

 

Synonym for Password.

Server

 

Synonym for Data Source.

Trusted_Connection

`false'

Synonym for Integrated Security.

User ID

 

The SQL Server login account to use for authentication.

Workstation ID

the client computer name

The name of the computer that is connecting to SQL Server.

SQL Server Authentication

Before a process can access data that is located in a SQL Server database, it must log in to SQL Server. The SqlConnection object communicates with SQL Server and performs this login based on information provided in the connection string. Logging in requires authentication. Authentication means proving to SQL Server that the process is acting on behalf of a user who is authorized to access SQL Server data. SQL Server recognizes two methods of authentication:

  • SQL Server Authentication, which requires the process to supply a username and password that have been set up in SQL Server by an administrator. Beginning with SQL Server 2000, this method of authentication is no longer recommended (and is disabled by default).
  • Integrated Windows Authentication, in which no username and password are provided. Instead, the Windows NT or Windows 2000 system on which the process is running communicates the user's Windows login name to SQL Server. The Windows user must be set up in SQL Server by an administrator in order for this to work.

To use SQL Server Authentication:

  1. (SQL Server 2000 only) Enable SQL Server Authentication. In Enterprise Manager, right-click on the desired server, click Properties, and then click the Security tab. Select "SQL Server and Windows" and click OK.
  2. The network administrator sets up a login account using Enterprise Manager, specifying that the account will use SQL Server Authentication and supplying a password. Programming books (including this one) typically assume the presence of a user named "sa" with an empty password, because this is the default system administrator account set up on every SQL Server installation (good administrators change the password, however).
  3. The network administrator assigns rights to this login account as appropriate.
  4. The data access code specifies the account and password in the connection string passed to the SqlConnection object. For example, the following connection string specifies the "sa" account with a blank password:

"Data Source=SomeMachine; Initial Catalog=Northwind; User ID=sa; Password="

To use Integrated Windows Authentication:

  1. The network administrator sets up the login account using Enterprise Manager, specifying that the account will use Windows Authentication and supplying the Windows user or group that is to be given access.
  2. The network administrator assigns rights to this login account as appropriate.
  3. The data access code indicates in the connection string that Integrated Windows Security should be used, as shown here:

"Data Source= SomeMachine; Initial Catalog=Northwind; Integrated Security=True"

When using Integrated Windows Authentication, it is necessary to know what Windows login account a process will run under and to set up appropriate rights for that login account in SQL Server Enterprise Manager. A program running on a local machine generally runs under the login account of the user that started the program. A component running in Microsoft Transaction Server (MTS) or COM+ runs under a login account specified in the MTS or COM+ Explorer. Code that is embedded in an ASP.NET web page runs under a login account specified in Internet Information Server (IIS). Consult the documentation for these products for information on specifying the login account under which components run. Consult the SQL Server Books Online for information on setting up SQL Server login accounts and on specifying account privileges.

Next week, check out the next excerpt installment that shows you how to connect to an OLE DB data source using ADO.NET.


View catalog information for Programming Visual Basic .NET

Return to the .NET DevCenter.

Copyright © 2009 O'Reilly Media, Inc.