You access the .NET Framework Data Providers through their .NET namespaces. (Table 5-1 in the preceding section lists the namespaces for each of the .NET Framework Data Providers.) For example, to access the features of the SQL Server provider, type the following namespace in the Code Editor:

System.Data.SqlClient

Because of how ADO.NET is constructed, you can access data without choosing a specific data provider. For example, you can write code that retrieves data from a data source without knowing in advance whether you want to use the SQL Server provider or the Oracle provider. Not choosing a data provider is useful when you need code to be flexible enough to choose your data source at runtime.

When you access data without using a data provider, you write provider-independent code. When you write it, you use the System.Data.Common namespace rather than one of the data provider namespaces listed in Table 5-1.

Writing provider-independent data access code isn't for the faint of heart. To read more about what's involved in writing provider-independent code, look for the topic provider independent code in the index of the Visual Studio 2010 documentation. You can find a good resource for the topic at http://msdn2.microsoft.com/en-us/library/t9f29wbk(VS.80).aspx.

Each.NET Framework Data Provider provides access to a common set of data access features and services. You use the following features and services of the providers to connect to your data source and retrieve data:

Each .NET Framework Data Provider has its own flavor of the features and services in the preceding list. For example, the DataAdapter for the SQL Server data provider is the SqlDataAdapter.

Figure 5-2 illustrates the relationship among the data providers' features and services.

Figure 5-2. Each data provider has a set of services for accessing data.

You access the features and services of the data providers through the provider's namespace. For example, you access the Oracle data provider's DataReader, OracleDataReader, at System.Data.OracleClient. Table 5-1 lists the namespaces for each of the data providers.

The features and services in the preceding list are objects of the .NET Framework Data Providers. Before you can use any of these features, you must create a new instance of the object you want to use. To read more about creating object instances, see Book III, Chapter 3.

You use the connection object for your data provider to connect to your data source. As you can imagine, connecting to a data source is a prerequisite for retrieving data from the data source. You use a connection object to

ADO.NET uses a feature called connection pooling that groups multiple database requests into a single connection. Connections that are exactly the same — same server, same database, same user credentials — are pooled by default with ADO.NET. Pooling connections together so that they can be reused reduces the overhead required to open and close connections.

You follow the same sequence of events each time you want to access a data source. For example, to use a connection object to access an SQL Server 2008 database, follow these steps:

Here's the entire code listing for the preceding code example:

VB

Dim MyConnection As New System.Data.SqlClient.SqlConnection
MyConnection.ConnectionString = ("Data Source=(local)sqlexpress;Initial Catalog=
    AdventureWorks;Integrated Security=True")
MyConnection.Open()
'send commands
MyConnection.Close()

C#.

System.Data.SqlClient.SqlConnection MyConnection =
   new System.Data.SqlClient.SqlConnection();
MyConnection.ConnectionString = "Data Source=(local)"+    "sqlexpress;Initial
   Catalog=AdventureWorks;"+ "Integrated Security=True";
MyConnection.Open();
// Send Commands
MyConnection.Close();

To use the connection object for any .NET Framework Data Providers, substitute the name of the provider's connection object listed in Table 5-2 where you see SqlConnection in the preceding code example.

For example, to work with an ODBC connection, type this line:

VB:

Dim MyConnection As New System.Data.Odbc.OdbcConnection

C#:

System.Data.Odbc.OdbcConnection MyConnection =
     new System.Data.Odbc.OdbcConnection();

You should always explicitly close your connection to your data source. Closing the connection releases resources.

VB

Using resource as New resourceType
.
.
.
End Using

C#.

using( resource = new resourcetype )
{
}

You place, between the Using ... End Using statements, statements that access the resource. A database connection is an example of a resource you can use with a Using block, as shown in the following code sample:

VB

Using MySqlConnection As New System.Data.SqlClient.
   SqlConnection()
  MySqlConnection.ConnectionString = ("Data Source=(local)
   sqlexpress;Initial Catalog=
   AdventureWorks;Integrated Security=True")
  MySqlConnection.Open()
  'send commands
End Using

C#.

using( MySqlConnection = new System.Data.SqlClient.SqlConnection() )
{
    MySqlConnection.ConnectionString =
    "Data Source=(local)sqlexpress;Initial Catalog="+
            "AdventureWorks;Integrated Security=True";
    MySqlConnection.Open();
    // Send Commands
}

There's no need to explicitly call the connection object's Close method with the Using block. The End Using statement automatically closes the connection for you.

Nothing stops you in your tracks faster than an incorrect connection string. Without a valid connection string, your code can't establish a connection to the data source. If you're lucky enough to connect to the same data sources over and over, you have to build a working connection string only once. As long as the data source doesn't change, you can reuse your connection string.

Whether you work with the same data sources day in and day out, or are always using different data sources, Visual Studio and .NET provide many tools to help you build and manage your connection strings. See the next section for more information.

A data provider's connection object uses the connection string to establish a connection to a data source when the connection object's Open method is called. A connection string is a set of name/value pairs (keywords) separated by semicolons, as shown in the following example:

Data Source=(local)sqlexpress;Initial Catalog=AdventureWorks;Integrated
    Security=True

The set of keywords used to create a connection string are determined by the data source. Common name/value pairs used to connect to the SQL Server database are described in Table 5-3.

See the topic Securing connection strings in the Visual Studio 2010 documentation for more information. See the Impersonation topic in the Visual Studio 2010 documentation to read more about using integrated security with ASP.NET.

You set a connection string by using the ConnectionString property of the connection object.

Visual Studio has many ways to help you build connection strings, including

The .NET Framework makes extensive use of configuration setting files, such as application settings in Windows applications and configuration settings in ASP.NET, to store all kinds of information related to your application. Visual Studio provides tools for adding settings, such as connection strings, to .NET configuration files. For example, you can use the ASP.NET Web Site Administration Tool to add several predefined and custom settings to your Web site. See Book IV, Chapter 6 for more information on using the Web Site Administration Tool.

Adding connection strings to Web projects

Web projects use the web.config file to store configuration settings, such as connection strings. The easiest way to add new connection strings to the web.config file is with the Data Source Configuration Wizard. (Book V, Chapter 1 walks you through using the wizard in the section about adding data to Web Forms).

You can use the Data Source Configuration Wizard to configure many different kinds of data sources. Configuring a database with the wizard prompts you to select an existing data connection or configure a new connection. After creating the configuration string, the wizard saves the connection string in the web.config file, as shown in Figure 5-4.

Figure 5-4. Use the Data Source Configuration Wizard to save connection strings in the web.config file.

The wizard writes the connection string to the web.config file. Alternatively, you can add the connection string manually to the web.config file. For example, a connection string for the Adventure Works database might look like this:

<connectionStrings>
   <add name="AdventureWorksConnectionString" connectionString="Data
    Source=(local)sqlexpress;Initial Catalog=AdventureWorks;Integrated
    Security=True"
           providerName="System.Data.SqlClient" />
</connectionStrings>

Add your connection string between the <connectionStrings></connectionStrings> tags.

Building connection strings manually

A common theme in building connection strings is the use of the Add Connection dialog box. It's used in both the application settings for Windows and the configuration settings for Web applications.

You access the Add Connection dialog box by choosing Tools

You can grab the connection string from Server Explorer and reuse it elsewhere, in either code or a configuration-settings file. To copy a connection string from a data connection in Server Explorer, follow these steps:

1. Create a new data connection by choosing Tools

   

   The Add Connection dialog box appears.

2. Use the Add Connection dialog box to create a connection string.

   A new data connection appears in Server Explorer, as shown in Figure 5-5.

3. Right-click the data connection in Server Explorer.

   You can open Server Explorer by choosing View

   

4. Choose Properties from the shortcut menu.

   The Properties window appears.

5. Highlight the ConnectionString property.

6. Copy and paste the property.

Using the ADO.NET connection string builders

Each of the .NET Framework Data Providers includes a service for building and managing connection strings. The connection string builders provide the properties needed to build a connection string. The builder outputs a properly formatted connection string that you pass to the data provider's connection object. Table 5-4 lists the connection string builders for each data provider.

Figure 5-5. Copy and paste the ConnectionString property from Server Explorer.

Table 5-4. Connection String Builders

Data Provider	Object
SQL Server	SqlConnectionStringBuilder
OLE DB	OleDbConnectionStringBuilder
ODBC	OdbcConnectionStringBuilder
Oracle	OracleConnectionStringBuilder

The following code uses the SQL Server data provider's connection string builder, SqlConnectionStringBuilder, to build a connection string for the Adventure Works sample database:

VB

Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder
builder.DataSource = "(local)sqlexpress"
builder.InitialCatalog = "AdventureWorks"
builder.IntegratedSecurity = True

C#.

System.Data.SqlClient.SqlConnectionStringBuilder builder =
    new System.Data.SqlClient.SqlConnectionStringBuilder();
builder.DataSource = "(local)sqlexpress";
builder.InitialCatalog = "AdventureWorks";
  builder.IntegratedSecurity = True;

You pass the connection string from SqlConnectionStringBuilder to the connection object's ConnectionString property like this:

VB

Dim MyConnection As New System.Data.SqlClient.SqlConnection
MyConnection.ConnectionString = builder.ConnectionString

C#.

System.Data.SqlClient.SqlConnection MyConnection =
     new System.Data.SqlClient.SqlConnection();
MyConnection.ConnectionString = builder.ConnectionString;

The DataReader Read method advances the DataReader to the next row in the result set. As long as more rows are present, the Read method returns the value True. The Read method is typically used to test the Read method's return value in a while loop.

A while loop loops through a set of statements as long as a test condition remains True. You can use a while loop to iterate through each row in a DataReader's result set and perform the same action on each row. For example, to use a while loop to step through a SqlDataReader object named MySqlDataReader, type the following lines:

VB

While (MySqlDataReader.Read() = True)
   Me.lstDepartments.Items.Add(MySqlDataReader("Name"))
End While

C#.

while( MySqlDataReader.Read() )
{
     lstDepartments.Items.Add(
       Convert.ToString(MySqlDataReader["Name"]));
}

The preceding example gets the value in column Name of the MySqlDataReader result set and adds the value to the items collection of a lstDepartments list box. The while loop performs the statement inside the loop as long as the Read method returns True. You end up with a list box full of items from the SqlDataReader. See the next section, about how to get data from a row.

The DataReader object provides access to a forward-only result set which presents one row at a time. When you're deciding how to work with the data in a row, you have to consider whether you want to work with the data

DataReader provides several Get accessors for retrieving data. You should retrieve the data by using a typed accessor, such as GetDateTime.

DataReader provides several options for retrieving data by using typed accessors. Each DataReader provides a set of common typed Get accessors, such as

SqlDataReader provides special types that work exclusively with SQL Server database types. Examples include

Use GetSql typed accessors when you're using the SqlDataReader. GetSql typed accessors are more precise than the .NET data types.

Of course, you can also retrieve data in its native format. DataReader provides several methods, such as

When you access the data in its native format, you must ensure that the data is converted to the appropriate .NET Framework data type. For example, the following statement retrieves a column by using the Item method and uses the ToString method to convert it to a string:

VB

myString = MySqlDataReader.Item("Name").ToString()

C#.

myString = MySqlDataReader.Item["Name"].ToString();

To read more about converting data types, see Book III, Chapter 2. Additionally, see the topic mapping data types in the Visual Studio 2010 help documentation to see how native data types are mapped to .NET Framework data types.

Most Get accessors use a zero-based column index to retrieve a column. A zero-based index starts counting elements at zero instead of at one. For example, in a table with the columns CustID, FirstName, and LastName, the FirstName column might have the column index of one. To access the FirstName column by using the GetSqlString accessor, you type the following line:

VB

MySqlDataReader.GetSqlString(1)

C#.

MySqlDataReader.GetSqlString(1);

Using column indexes to retrieve values may be fast for the DataReader, but it's slow and confusing to a programmer. Fortunately, DataReaders have two methods for accessing columns by name:

Each of these approaches has its drawbacks. If you use the Item method, you must explicitly convert the column to a .NET data type. Using GetOrdinal hits the data source twice — once to get the ordinal and again to retrieve the data by using the ordinal.

Here's an example of using GetOrdinal to access data:

VB

Dim nameCol, groupNameCol As Integer
nameCol = MySqlDataReader.GetOrdinal("Name")

groupNameCol = MySqlDataReader.GetOrdinal("GroupName")
  While (MySqlDataReader.Read() = True)
    myString = String.Format("{0} {1}", MySqlDataReader.
     GetString(nameCol), MySqlDataReader.
     GetString(groupNameCol))
    Me.lstDepartments.Items.Add(myString)
  End While

C#.

int nameCol, groupNameCol;
nameCol = MySqlDataReader.GetOrdinal("Name");
groupNameCol = MySqlDataReader.GetOrdinal("GroupName");
while( MySqlDataReader.Read())
{
  myString = String.Format("{0} {1}", MySqlDataReader.
   GetString(nameCol), MySqlDataReader.
   GetString(groupNameCol));
  lstDepartments.Items.Add(myString)
}

Here's an example of using the GetValues method:

VB

Dim MyArray(MySqlDataReader.FieldCount − 1) As Object
MySqlDataReader.GetValues(MyArray)

C#.

Object[] MyArray = new Object[MySqlDataReader.FieldCount-1];
MySqlDataReader.GetValues(MyArray);

In the first line of this example, you use the DataReader FieldCount property to set the size of the array. In the second line, you pass the array to the GetValues method. The array is filled with the values for the entire row.

The GetValues method retrieves values in their native data formats. The values must be converted to .NET data types.

With the values in the array, you can access them by using the properties and methods of arrays. To read more about using arrays, see Book III, Chapter 2.

The GetValues method, which is a fast way to grab an entire row, is typically used by GetValues to pass the array to the ItemArray method of the DataRow object.

DataRows are rows in a DataTable. You specify ahead of time which columns and data types exist in the DataTable. When you add a new row by using ItemArray, the data is plugged into the columns and converted to the column's data type. The following code fills an array and passes the array to a new DataRow in the myTable DataTable:

VB

MySqlDataReader.GetValues(array)
row = myTable.NewRow()
row.ItemArray = array

C#.

MySqlDataReader.GetValues(array);
row = myTable.NewRow();
  row.ItemArray = array;

The values in the array are converted to the appropriate data type for each column in the DataRow.

The DataReader includes a GetSchemaTable method that you can use to retrieve the schema information about the result set. The GetSchemaTable method returns a DataTable, as shown in the following code:

VB

Dim table As New DataTable
table = MySqlDataReader.GetSchemaTable()

C#.

DataTable table = new DataTable():
  table = MySqlDataReader.GetSchemaTable();

You can use the DataRow's ItemArray method, as described in the preceding section, to add new rows to a table built with GetSchemaTable.

A DataAdapter acts as a bridge between the DataSet and the data source. Filling a DataSet involves these tasks:

As is the case with all the data access features of ADO.NET, filling a DataSet isn't as simple as creating a few objects and then calling the Fill method. You have to complete a number of prerequisite steps, such as creating a connection object.

The following example walks you through using a DataAdapter to fill a DataSet:

Behind the scenes, the Fill method does the following:

Figure 5-6 shows the relationship among the objects of the SQL Server data provider used in the preceding example. The relationships are similar for other .NET Framework data providers.

Figure 5-6. The SQL Data Adapter is the bridge between the data source and the DataSet.

You call the DataAdapter Update method to send data updates from the DataSet to the data source. The Update method uses InsertCommand, UpdateCommand, and DeleteCommand to update the data source.

The Update method requires you to pass in a DataSet, a DataTable, or an array of DataRows, as shown in the following example:

VB

MySqlDataAdapter.Update(MyDataSet)

C#.

MySqlDataAdapter.Update(MyDataSet);

The DataAdapter uses the Command objects specified in its properties to execute SQL statements against the data source.

Each of the .NET Framework Data Providers has a CommandBuilder object that you can use to automatically generate commands against a single-table data source. CommandBuilder automatically builds the commands for a DataAdapter to use.

To use SqlCommandBuilder with the example from the preceding section, follow these steps:

TableAdapter, a new data access feature in ADO.NET, encapsulates all the retrieve and update commands and a connection object for a single table. Visual Studio provides extensive support for creating TableAdapters by using the TableAdapter Configuration Wizard in DataSet Designer.

You can call the commands of a generated TableAdapter by using the standard "dot" notation of IntelliSense in the Code Editor. To access the properties and methods of a generated TableAdapter, follow these steps:

SQL statements are executed by databases in transactions. A transaction is a group of statements executed against a database. You use statements such as BEGIN TRANSACTION and COMMIT WORK to mark the start and end of a transaction. Between the start and end are SQL statements that retrieve and modify data. The ROLLBACK WORK statement rolls back, or undoes, the statements executed in the transaction.

The .NET Framework provides a new model for transaction processing with the System.Transactions namespace. You use it to create two kinds of database transactions: