BUY THIS BOOK

Safari Books Online

What is this?

Looking to Reprint this content?

Envelope Tell a friend

ADO.NET Cookbook
ADO.NET Cookbook By Bill Hamilton
September 2003
Pages: 624

Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: Connecting to Data
Content preview·Buy reprint rights for this chapter
This chapter describes how to connect to a variety of data sources from ADO.NET; how to handle security-related issues including storing connection strings and using different authentication methods; and how to set up, monitor, and optimize connection pooling.
ADO.NET is designed to support data access requirements of loosely coupled n-tier application architectures including web services. ADO.NET can access a variety of different data sources, including relational databases such as Microsoft SQL Server, Oracle, and Microsoft Access, as well as other data sources such as Microsoft Excel, Outlook, and text files.
A .NET data provider is used to connect to a data source, execute commands, and retrieve results. The .NET Framework ships with four .NET-managed data providers: Microsoft SQL Server, Oracle, OLE DB, and ODBC.
Other providers are also available—for example, Oracle has developed its own .NET data provider for Oracle. Data providers also exist for databases such as Sybase and MySQL. Database-specific providers usually access the underlying data store directly and offer the best performance, broadest functionality, and support for database-specific features. Since a data provider needs only to implement a set of standard interfaces, the capabilities and performance of data providers for the same data source can differ significantly.
In addition to database-specific providers, the OLE DB .NET data provider allows access to most OLE DB data sources through OLE DB providers. Similarly, the ODBC .NET data provider uses the ODBC drivers to access most ODBC data sources. You can also develop your own data provider to access proprietary data sources or to meet special requirements.
ADO.NET is fundamentally different from ADO despite sharing a similar name. ADO.NET is based on a disconnected architecture and has both connected and disconnected classes. Each data provider is responsible for providing the connected classes:
Connection
A unique session with the data source.
Command
Executes SQL statements and stored procedures against the data source.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Introduction
Content preview·Buy reprint rights for this chapter
This chapter describes how to connect to a variety of data sources from ADO.NET; how to handle security-related issues including storing connection strings and using different authentication methods; and how to set up, monitor, and optimize connection pooling.
ADO.NET is designed to support data access requirements of loosely coupled n-tier application architectures including web services. ADO.NET can access a variety of different data sources, including relational databases such as Microsoft SQL Server, Oracle, and Microsoft Access, as well as other data sources such as Microsoft Excel, Outlook, and text files.
A .NET data provider is used to connect to a data source, execute commands, and retrieve results. The .NET Framework ships with four .NET-managed data providers: Microsoft SQL Server, Oracle, OLE DB, and ODBC.
Other providers are also available—for example, Oracle has developed its own .NET data provider for Oracle. Data providers also exist for databases such as Sybase and MySQL. Database-specific providers usually access the underlying data store directly and offer the best performance, broadest functionality, and support for database-specific features. Since a data provider needs only to implement a set of standard interfaces, the capabilities and performance of data providers for the same data source can differ significantly.
In addition to database-specific providers, the OLE DB .NET data provider allows access to most OLE DB data sources through OLE DB providers. Similarly, the ODBC .NET data provider uses the ODBC drivers to access most ODBC data sources. You can also develop your own data provider to access proprietary data sources or to meet special requirements.
ADO.NET is fundamentally different from ADO despite sharing a similar name. ADO.NET is based on a disconnected architecture and has both connected and disconnected classes. Each data provider is responsible for providing the connected classes:
Connection
A unique session with the data source.
Command
Executes SQL statements and stored procedures against the data source.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Connecting to an ODBC Data Source
Content preview·Buy reprint rights for this chapter
You want to access your data source using an ODBC provider from your .NET application.
Use the ODBC .NET data provider to access data exposed through an ODBC driver.
The sample code contains a single event handler:
Connect Button.Click
Creates an OdbcDataAdapter and uses it to fill a DataTable with the Category table from the Northwind sample database. The default view of the table is bound to a data grid on the form.
The C# code is shown in Example 1-1.
Example 1-1. File: OdbcConnectForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.Odbc;

//  . . . 

private void connectButton_Click(object sender, System.EventArgs e)
{
    // Create the DataAdapter.
    String sqlSelect = "SELECT CategoryID, CategoryName, Description " +
        "FROM Categories";
    OdbcDataAdapter da = new OdbcDataAdapter(sqlSelect,
        ConfigurationSettings.AppSettings["Odbc_ConnectString"]);

    // Create the table, fill it, and bind the default view to the grid.
    DataTable dt = new DataTable( );
    da.Fill(dt);
    dataGrid.DataSource = dt.DefaultView;
}
The ODBC .NET data provider communicates with native ODBC drivers through COM interop (.NET's interoperability layer for COM). The following ODBC providers are guaranteed to be compatible with the ODBC.NET data provider:
  • Microsoft SQL Server ODBC Driver
  • Microsoft ODBC Driver for Oracle
  • Microsoft Access (Jet) ODBC Driver
The .NET data provider for ODBC connects to ODBC data sources through the OdbcConnection object. The ODBC driver connection string is specified using the ConnectionString property. It includes settings needed to establish the connection to the data source. The connection string format closely matches the ODBC connection string format. Additionally, you can specify an ODBC data source name (DSN) or file DSN by setting the ConnectionString attribute to "DSN=myDSN" or "FileDSN=myFileDSN". For more information about specifying ODBC connection strings, see the topic "SQLDriverConnect" in the ODBC Programmer's Reference
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Connecting to a Microsoft Excel Workbook
Content preview·Buy reprint rights for this chapter
You want to access data stored in a Microsoft Excel workbook.
Use the OLE DB Jet provider to create, access, and modify data stored in an Excel workbook.
The sample code contains two event handlers:
Form.Load
Creates an OleDbDataAdapter that uses the Jet OLE DB provider to access an Excel workbook. Custom insert and update logic is created for the DataAdapter. A DataTable is filled from the first worksheet, Sheet1, in the Excel workbook and the default view of the table is bound to a data grid on the form.
Update Button.Click
Uses the DataAdapter created in the Form.Load event handler to update the Excel workbook with the programmatic changes.
The C# code is shown in Example 1-2.
Example 1-2. File: ExcelForm.cs
// Namespaces, Variables, and Constants
using System;
using System.Configuration;
using System.Data;

private OleDbDataAdapter da;
private DataTable dt;

//  . . . 

private void ExcelForm_Load(object sender, System.EventArgs e)
{
    // Create the DataAdapter.
    da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",
        ConfigurationSettings.AppSettings["Excel_0115_ConnectString"]);

    // Create the insert command.
    String insertSql = "INSERT INTO [Sheet1$] " +
        "(CategoryID, CategoryName, Description) " +
        "VALUES (?, ?, ?)";
    da.InsertCommand =
        new OleDbCommand(insertSql, da.SelectCommand.Connection);
    da.InsertCommand.Parameters.Add("@CategoryID", OleDbType.Integer, 0,
        "CategoryID");
    da.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15,
        "CategoryName");
    da.InsertCommand.Parameters.Add("@Description", OleDbType.VarChar, 100,
        "Description");

    // Create the update command.
    String updateSql = "UPDATE [Sheet1$] " +
        "SET CategoryName=?, Description=? " +
        "WHERE CategoryID=?";
    da.UpdateCommand =
        new OleDbCommand(updateSql, da.SelectCommand.Connection);
    da.UpdateCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15,
        "CategoryName");
    da.UpdateCommand.Parameters.Add("@Description", OleDbType.VarChar, 100,
        "Description");
    da.UpdateCommand.Parameters.Add("@CategoryID", OleDbType.Integer, 0,
        "CategoryID");

    // Fill the table from the Excel spreadsheet.
    dt = new DataTable( );
    da.Fill(dt);
    // Define the primary key.
    dt.PrimaryKey = new DataColumn[] {dt.Columns[0]};

    // Records can only be inserted using this technique.
    dt.DefaultView.AllowDelete = false;
    dt.DefaultView.AllowEdit = true;
    dt.DefaultView.AllowNew = true;
    // Bind the default view of the table to the grid.
    dataGrid.DataSource = dt.DefaultView;
}
private void updateButton_Click(object sender, System.EventArgs e)
{
    da.Update(dt);
}
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Connecting to a Password-Protected Access Database
Content preview·Buy reprint rights for this chapter
You want to connect to a Microsoft Access database that has a database password.
Use the Jet OLEDB:Database Password attribute in the connection string to specify the password.
The sample code contains a single event handler:
Connect Button.Click
Creates and opens a connection to a password-secured Microsoft Access database using the OLE DB .NET data provider. Information about the database is displayed from the properties of the OleDbConnection object.
The C# code is shown in Example 1-3.
Example 1-3. File: AccessPasswordForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.OleDb;

//  . . . 

private void connectButton_Click(object sender, System.EventArgs e)
{
    StringBuilder result = new StringBuilder( );

    // Build the connections string incorporating the password.
    String connectionString =
        ConfigurationSettings.AppSettings["MsAccess_Secure_ConnectString"]+
        "Jet OLEDB:Database Password=" + passwordTextBox.Text + ";";

    result.Append("ConnectionString: " + Environment.NewLine +
        connectionString + Environment.NewLine + Environment.NewLine);

    OleDbConnection conn = new OleDbConnection(connectionString);
    try
    {
        conn.Open( );

        // Retrieve some database information.
        result.Append(
            "Connection State: " + conn.State + Environment.NewLine +
            "OLE DB Provider: " + conn.Provider +
            Environment.NewLine +
            "Server Version: " + conn.ServerVersion +
            Environment.NewLine);

        conn.Close( );
        result.Append("Connection State: " + conn.State);
    }
    catch(System.Data.OleDb.OleDbException ex)
    {
        conn.Close( );
        result.Append("ERROR: " + ex.Message);
    }

    resultTextBox.Text = result.ToString( );
}
A Microsoft Access database password requires that users enter a password to obtain access to the database and database objects. This is also known as share-level security. A password does not allow groups or users to have distinct levels of access or permissions. Anyone with the password has unrestricted access to the database.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Connecting to a Secured Access Database
Content preview·Buy reprint rights for this chapter
You want to connect to a Microsoft Access database that has been secured with user-level security and a workgroup file.
Use the Jet OLEDB:System Database attribute in the connection string to specify the path and filename of the workgroup information file or system database.
The sample code contains a single event handler:
Connect Button.Click
Creates and opens a connection to a Microsoft Access database secured with user-level security and a workgroup file using the OLE DB .NET data provider. Information about the database is displayed from the properties of the OleDbConnection object.
The C# code is shown in Example 1-4.
Example 1-4. File: AccessSecureForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data.OleDb;

//  . . . 

private void connectButton_Click(object sender, System.EventArgs e)
{
    StringBuilder result = new StringBuilder( );

    // Build the connection string with security information.
    String connectionString =
        ConfigurationSettings.AppSettings["MsAccess_ConnectString"] +
        @"Jet OLEDB:System database=" +
        ConfigurationSettings.AppSettings["MsAccess_SecureMdw_Filename"] +
        ";" + "User ID=" + userIdTextBox.Text + ";" +
        "Password=" + passwordTextBox.Text + ";" +
        Environment.NewLine + Environment.NewLine;

    result.Append(connectionString);

    // Create the connection.
    OleDbConnection conn = new OleDbConnection(connectionString);

    try
    {
        // Attempt to open the connection.
        conn.Open( );

        result.Append(
            "Connection State: " + conn.State + Environment.NewLine +
            "OLE DB Provider: " + conn.Provider +
            Environment.NewLine +
            "Server Version: " + conn.ServerVersion +
            Environment.NewLine);

        conn.Close( );

        result.Append("Connection State: " + conn.State +
            Environment.NewLine);
    }
    catch(System.Data.OleDb.OleDbException ex)
    {
        result.Append("ERROR: " + ex.Message);
    }

    resultTextBox.Text = result.ToString( );
}
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Connecting to an Access Databasefrom ASP.NET
Content preview·Buy reprint rights for this chapter
You know your connection string is correct, but still can't connect to your Microsoft Access database from your ASP.NET application. What are the differences between connecting from a Windows Forms .NET application and an ASP.NET application?
You must grant the necessary file permissions for accessing a Jet database (Microsoft's transparent data access engine) to the default user account used by ASP.NET.
When a user retrieves a page from an ASP.NET web site, code runs on the server to generate and deliver the page. By default, IIS (Internet Information Server) uses the system account to provide the security context for all processes. This account can access the IIS computer, but is not allowed to access network shares on other computers.
To allow an ASP.NET application to connect to a Microsoft Access database, IIS must be configured to use an account other than the system account. The new account must be configured to have permission to access all files and folders needed to use the Access database. If the Access database is on a remote computer, the account also requires access to that computer.
The following sections describe how to configure the IIS Server and the Access computer to allow ASP.NET to connect to an Access database.

Section : Configure IIS

The system account cannot authenticate across a network. Enable impersonation in the web.config file for a given ASP.NET application so that ASP.NET impersonates an account on the Microsoft Access computer with the required access permissions to the Access database. For example:
<identity impersonate="true" userName="domain\username"
    password="myPassword" />
This method stores the username and password in clear text on the server. Ensure that IIS is configured to prevent users of the web site from viewing the contents of the web.config file—this is the default configuration. Other ways to impersonate a user from an ASP page are described in the Microsoft Knowledge Base article Q248187.
The Microsoft Jet engine uses the TEMP folder on the IIS computer that is accessing the Access database. The user identity requires NTFS (Windows NT File System) full-control permissions on the
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Using an IP Address to Connect to SQL Server
Content preview·Buy reprint rights for this chapter
You want to connect to a SQL Server using its IP address instead of its server name.
Use the Network Address and Network Library attributes of the connection string.
The sample code contains a single event handler:
Connect Button.Click
Creates and opens a connection to a SQL Server using its IP address. Information about the SQL Server is displayed from the properties of the SqlConnection object.
The C# code is shown in Example 1-5.
Example 1-5. File: ConnectSqlServerIpAddressForm.cs
// Namespaces, variables, and constants
using System;
using System.Data.SqlClient;

//  . . . 

private void connectButton_Click(object sender, System.EventArgs e)
{
    String connString =
        "Network Library=dbmssocn;Network Address=127.0.0.1
;" +
        "Integrated security=SSPI;Initial Catalog=Northwind";
    
    SqlConnection conn = new SqlConnection(connString);
    conn.Open( );

    // Return some information about the server.
    resultTextBox.Text =
        "ConnectionState = " + conn.State + Environment.NewLine +
        "DataSource = " + conn.DataSource + Environment.NewLine +
        "ServerVersion = " + conn.ServerVersion + Environment.NewLine;

    conn.Close( );

    resultTextBox.Text += "ConnectionState = " + conn.State;
}
SQL Server network libraries are dynamic-link libraries (DLLs) that perform network operations required for client computers and SQL Server computers to communicate. A server can monitor multiple libraries simultaneously; the only requirement is that each network library to be monitored is installed and configured.
Available network libraries for SQL Server 2000 include:
AppleTalk ADSP
Allows Apple Macintosh to communicate with SQL Server using native AppleTalk protocol.
Banyan VINES
Supports Banyan VINES Sequenced Packet Protocol (SPP) across Banyan VINES IP network protocol.
Multiprotocol
Automatically chooses the first available network protocol to establish a connection generally with performance comparable to using a native network library. TCP/IP Sockets, NWLink IPX/SPX, and Named Pipes are supported.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Connecting to a Named Instance of SQL Server or Microsoft Data Engine (MSDE)
Content preview·Buy reprint rights for this chapter
You want to connect to a named instance of a SQL Server or Microsoft Data Engine (MSDE).
You need to understand what a SQL Server or MSDE named instance is and how to connect to one. The sample code contains a single event handler:
Connect Button.Click
Creates and opens a connection to a named instance of a SQL Server. Information about the SQL Server is displayed from the properties of the SqlConnection object.
The C# code is shown in Example 1-6.
Example 1-6. File: ConnectNamedInstanceForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Text;
using System.Data.SqlClient;

//  . . . 

private void connectButton_Click(object sender, System.EventArgs e)
{
    StringBuilder result = new StringBuilder( );

    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_Msde_ConnectString"]);

    try
    {
        conn.Open( );

        // Return some information about the server.
        result.Append(
            "ConnectionState = " + conn.State + Environment.NewLine +
            "DataSource = " + conn.DataSource + Environment.NewLine +
            "ConnectionState = " + conn.State + Environment.NewLine +
            "ServerVersion=" + conn.ServerVersion +
            Environment.NewLine);
    } 
    catch(Exception ex) 
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        conn.Close( );
    }

    result.Append("ConnectionState = " + conn.State);

    resultTextBox.Text = result.ToString( );
}
SQL Server 2000 introduced the ability to install multiple copies of SQL Server on a single computer. Only one copy can function as the default instance at any time; it is identified by the network name of the computer on which it is running. All other copies are named instances and are identified by the network name of the computer plus an instance name. The format is <computerName>\<instanceName>. This format is used in the connection string to specify the Data Source attribute for a named instance.
Each instance operates independently of the other instances installed on the same computer. Each instance has its own set of system and user databases that are not shared between instances and it runs within its own security context. The maximum number of instances supported on SQL Server 2000 is 16. The Microsoft Distributed Transaction Coordinator (DTC) and the Microsoft Search services are installed and used simultaneously by every installed instance of SQL Server. Client tools such as Enterprise Manager and Query Analyzer are also shared.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Connecting to SQL Server Using Integrated Security from ASP.NET
Content preview·Buy reprint rights for this chapter
You want to coordinate Windows security accounts between an ASP.NET application and SQL Server.
Connect to SQL Server from ASP.NET using Windows Authentication in SQL Server.
Connecting to a SQL Server database provides two different authentication modes:
Windows Authentication
Uses the current security identity from the Windows NT or Windows 2000 user account to provide authentication information. It does not expose the user ID and password and is the recommended method for authenticating a connection.
SQL Server Authentication
Uses a SQL Server login account providing a user ID and password.
Integrated security requires that the SQL Server is running on the same computer as IIS and that all application users are on the same domain so that their credentials are available to IIS. The following areas of the application need to be configured:
  • Configure the ASP.NET application so that Integrated Windows Authentication is enabled and Anonymous Access is disabled.
  • The web.config file establishes the authentication mode that the application uses and that the application will run as or impersonate the user. Add the following elements to the web.config file:
    <authentication mode="Windows" />
<identity impersonate="true" />
  • The connection string must contain attributes that tell the SQL Server that integrated security is used. Use the Integrated Security=SSPI attribute-and-value pair instead of the User ID and Password attributes in the connection string. The older attribute-and-value pair Trusted_Connection=Yes is also supported.
  • Add users and groups from the domain and set their access permissions as required.
By default, ASP.NET applications run in the context of a local user ASPNET on IIS. The account has limited permissions and is local to the IIS computer and therefore not recognized as a user on remote computers. To overcome this limitation when SQL Server is not on the same computer as IIS, run the web application in the context of a domain user recognized on both IIS and SQL Server computers.
In addition to the areas identified where IIS and SQL Server are on the same computer, the following additional items must be configured if the SQL Server is on a different computer:
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Connecting to an Oracle Database
Content preview·Buy reprint rights for this chapter
You want to connect to an Oracle database.
You can connect to an Oracle database using either the Oracle .NET data provider or the OLE DB .NET data provider.
The sample code contains two event handlers:
Oracle Button.Click
Creates and opens a connection to an Oracle database using the Oracle .NET data provider. Information about the database is displayed from the properties of the OracleConnection object.
OLE DB Button.Click
Creates and opens a connection to an Oracle database using the OLE DB .NET data provider. Information about the database is displayed from the properties of the OleDbConnection object.
The C# code is shown in Example 1-7.
Example 1-7. File: ConnectOracleForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data.OleDb;
using System.Data.OracleClient;

//  . . . 

private void oracleProviderButton_Click(object sender, System.EventArgs e)
{
    // Connect to Oracle using Microsoft Oracle .NET data provider.
    OracleConnection conn = new OracleConnection(
        ConfigurationSettings.AppSettings["Oracle_Scott_ConnectString"]);

    resultTextBox.Text = "Connection with ORACLE Provider" +
        Environment.NewLine;
    try
    {
        conn.Open( );

        resultTextBox.Text += "ConnectionState = " + conn.State +
            Environment.NewLine +
            "DataSource = " + conn.DataSource +
            Environment.NewLine +
            "ServerVersion = " + conn.ServerVersion +
            Environment.NewLine;
    }
    catch(OracleException ex)
    {
        resultTextBox.Text += "ERROR: " + ex.Message;
    }
    finally
    {
        conn.Close( );
        resultTextBox.Text += "ConnectionState = " + conn.State;
    }
}

private void oleDbButton_Click(object sender, System.EventArgs e)
{
    // Connect to Oracle using OLE DB .NET data provider.
    OleDbConnection conn = new OleDbConnection(
        ConfigurationSettings.AppSettings["OleDb_Oracle_ConnectString"]);

    resultTextBox.Text = "Connection with OLE DB Provider" +
        Environment.NewLine;
    try
    {
        conn.Open( );

        resultTextBox.Text += "ConnectionState = " + conn.State +
            Environment.NewLine +
            "DataSource = " + conn.DataSource +
            Environment.NewLine +
            "ServerVersion = " + conn.ServerVersion +
            Environment.NewLine;
    }
    catch(OleDbException ex)
    {
        resultTextBox.Text += "ERROR: " + ex.Message;
    }
    finally
    {
        conn.Close( );
        resultTextBox.Text += "ConnectionState = " + conn.State +
            Environment.NewLine;
    }        
}
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Connecting to Exchange or Outlook
Content preview·Buy reprint rights for this chapter
You want to use ADO.NET to extract data from Microsoft Outlook or Microsoft Exchange.
Use the OLE DB Jet provider to access Exchange and Outlook data.
The sample code contains two event handlers:
Form.Load
Displays a form that allows the user to specify the mailbox name and mail profile to connect to.
Connect Button.Click
Creates and opens a connection to Outlook or Exchange data using the OLE DB .NET data provider. A DataAdapter is used to fill a table with the Subject and Content of each message in the Inbox. The default view of the table is bound to a data grid on the form.
The C# code is shown in Example 1-8.
Example 1-8. File: ConnectExchangeDataForm.cs
// Namespaces, variables, and constants
using System;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

//  . . . 

private void ConnectExchangeDataForm_Load(object sender,
    System.EventArgs e)
{
    mailboxNameTextBox.Text = "Personal Folders";
    profileTextBox.Text = "Outlook";
}

private void connectButton_Click(object sender, System.EventArgs e)
{
    String sqlText = "SELECT Subject, Contents FROM Inbox";

    // Build the connection string.
    String connectionString="Provider=Microsoft.Jet.OLEDB.4.0;" +
        "Outlook 9.0;" +
        "MAPILEVEL=" + mailboxNameTextBox.Text + "|;" +
        "PROFILE=" + profileTextBox.Text + ";" +
        "TABLETYPE=0;" +
        "DATABASE=" + System.IO.Path.GetTempPath( );

    // Create the DataAdapter.
    OleDbDataAdapter da = new OleDbDataAdapter(sqlText, connectionString);

    // Create and fill the table.
    DataTable dt = new DataTable("Inbox");
    try
    {
        da.Fill(dt);
        dataGrid.DataSource = dt.DefaultView;
    }
    catch(Exception ex)
    {
        MessageBox.Show("ERROR: " + ex.Message);
        return;
    }
}
The .NET data provider for OLE DB does not support OLE DB Version 2.5 interfaces including the Microsoft OLE DB Provider for Exchange. The Jet OLE DB provider can access an Outlook or Exchange store. An example of the connection string:
Microsoft.Jet.OLEDB.4.0;Outlook 9.0;MAPILEVEL=Personal Folders|;
    PROFILE=Outlook;TABLETYPE=0;DATABASE=c:\temp;
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Writing Database-Independent Code
Content preview·Buy reprint rights for this chapter
You need to develop an application that can be used with different data providers, but does not lose functionality that is specific to the different providers. You want to create provider-independent code and use it with the provider-specific code that you might need.
The solution shows how to use interfaces that are inherited by .NET connected classes (such as Connection and DataReader) to create provider-independent code that can be used with provider-specific code to access unique functionality.
The sample code contains a method and two event handlers:
GetData( )
This method is a .NET data provider-independent method that accepts .NET data provider-specific Connection and DataAdapter arguments as generic IDbConnection and IDbDataAdapter interface types. The interfaces are used to fill a DataSet from the Customers table in Northwind. The default view of the Customers DataTable is bound to the data grid on the form.
Finally, the provider-specific Connection for the IDbConnection is identified and provider-specific logic executed.
SQL Button.Click
This event handler is provider-specific code that creates a SqlConnection and a SqlDataAdapter object and passes them as arguments into the provider-independent GetData( ) method.
OLE DB Button.Click
This event handler is provider-specific code that creates an OleDbConnection and an OleDbDataAdapter object and passes them as arguments into the provider-independent GetData( ) method.
The C# code is shown in Example 1-9.
Example 1-9. File: DatabaseIndependentCodeForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;

//  . . . 

private void GetData(IDbConnection conn, IDbDataAdapter da)
{
    // Create the command and assign it to the IDbDataAdapter interface.
    IDbCommand cmd = conn.CreateCommand( );
    cmd.CommandText = "SELECT * FROM Customers";
    da.SelectCommand = cmd;
    // Add a table mapping.
    da.TableMappings.Add("Table", "Customers");

    dataGrid.DataSource = null;
    
    // Fill the DataSet.
    DataSet ds = new DataSet( );
    da.Fill(ds);

    // Bind the default view for the Customer table to the grid.
    dataGrid.DataSource = ds.Tables["Customers"].DefaultView;

    // Identify provider-specific connection type and process appropriately.
    if (conn is SqlConnection)
    {
        MessageBox.Show("Specific processing for SQL data provider.");
    }
    else if(conn is OleDbConnection)
    {
        MessageBox.Show("Specific processing for OLE DB data provider.");
    }
}

private void sqlButton_Click(object sender, System.EventArgs e)
{
    // Create a SQL Connection and DataAdapter.
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    SqlDataAdapter da = new SqlDataAdapter( );

    dataGrid.CaptionText = "SQL .NET Provider";

    // Call provider-independent function to retrieve data.
    GetData(conn, da);
}

private void oleDbButton_Click(object sender, System.EventArgs e)
{
    // Create a OLE DB Connection and DataAdapter.
    OleDbConnection conn = new OleDbConnection(
        ConfigurationSettings.AppSettings["OleDb_ConnectString"]);
    OleDbDataAdapter da = new OleDbDataAdapter( );

    dataGrid.CaptionText = "OLE DB .NET Provider";

    // Call provider-independent function to retrieve data.
    GetData(conn, da);
}
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Storing Connection Strings
Content preview·Buy reprint rights for this chapter
You need to choose the best place to store connection strings that you need in your application to increase maintainability, simplify future modifications, and eliminate the need to recompile the application when it is modified.
There are several alternatives for storing connection strings, including hard-coding the connection string in your application, storing it in an application configuration file or the Windows Registry, representing it using a Universal Data Link (UDL) file, or in a custom file.
A connection string is made up of a semi-colon delimited collection of attribute/value pairs that define how to connect a data source. Although connection strings tend to look similar, the available and required attributes are different depending on the data provider and on the underlying data source. There are a variety of options providing differing degrees of flexibility and security.
The Persist Security Info connection string attribute specifies whether the data source can hang on to, or persist, sensitive information such as user authentication credentials. Its value should be kept at the default false. If its value is true, the connection information—including the password—can be obtained by querying the connection, allowing an untrusted party to have access to sensitive information when a Connection is passed or persisted to a disk. This is an issue only when passing connected objects such as Connection or DataAdapter; disconnected objects such as DataSet and DataTable do not store information about the original source of their data.
Before a data source object is initialized for the first time, sensitive information can be retrieved from it regardless of the setting of the Persist Security Info property. Avoid passing uninitialized data source objects.
The Persist Security Info connection string attribute is supported by the SQL Server, OLE DB, and Oracle .NET Framework data providers. Although not supported by the ODBC .NET Framework data provider, its behavior is as if Persist Security Info is false and cannot be changed. Check the documentation for other data providers to determine specific implementation details.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Using the Data Link Properties Dialog Box
Content preview·Buy reprint rights for this chapter
You want to display the Data Link Properties dialog box from an application so that users can create their own database connections just as they can from the Server Explorer window in the Visual Studio .NET IDE.
Use COM interop with the OLE DB Service Component to display the Data Link Properties dialog box.
You'll need a reference to the Primary Interop Assembly (PIA) for ADO provided in the file ADODB.DLL; select adodb from the .NET tab in Visual Studio .NET's Add Reference Dialog. You'll also need a reference to the Microsoft OLE DB Service Component 1.0 Type Library from the COM tab in Visual Studio .NET's Add Reference Dialog.
The sample code contains a single event handler:
Data Link Dialog Button.Click
Creates and displays a Data Link Properties dialog box using the Microsoft OLE DB Service Component through COM interop.
The C# code is shown in Example 1-10.
Example 1-10. File: DataLinkDialogForm.cs
// Namespaces, variables, and constants
using System;

//  . . . 

private void dataLinkDialogButton_Click(object sender, System.EventArgs e)
{
    ADODB.Connection conn = new ADODB.Connection( );
    object oConn = (object) conn;

    MSDASC.DataLinks dlg = new MSDASC.DataLinks( );
    dlg.PromptEdit(ref oConn);

    connectionStringTextBox.Text = conn.ConnectionString;
}
COM interop can open a Data Link Properties dialog box allowing a user to select an OLE DB provider and set its properties. You can use the results programmatically to construct the connection string for an ADO.NET connection object at runtime with a GUI (graphical user interface).
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Monitoring Connections
Content preview·Buy reprint rights for this chapter
You want to monitor the opening and closing of connections and the number of connections in the connection pool while an application is running.
Use the Windows Performance Monitor and the SQL Profiler to monitor connections and connection pooling. See Recipe 1.15 for more information on connection pooling.
The following subsections discuss monitoring connection pooling for SQL Server and ODBC .NET Framework data providers.

Section : SQL Server

You can monitor SQL Server connections and connection pooling using the SQL Server Profiler or the Windows Performance Monitor as described in the following subsections.

Section 1.14.3.1.1: SQL Server Profiler

To use the SQL Server Profiler to monitor connection pooling:
  1. Start the Profiler using one of the following methods
    • From Windows desktop: Start → All Programs → Microsoft SQL Server → Profiler.
    • From SQL Enterprise Manager: Tools → SQL Profiler.
  2. When the SQL Server Profiler appears, select File → New → Trace.
  3. Supply connection details and click OK. The Trace Properties dialog box will appear.
  4. Select the Events tab of the Trace Properties dialog box.
  5. In the Selected Events list box, ensure that the Audit Login and Audit Logout events appear beneath the Security Audit node. Remove all other events from the list. Click the Run button to start the trace.
  6. The new Profiler window will display a table containing Audit Login events when connections are established and Audit Logout events when connections are closed.

Section 1.14.3.1.2: Windows Performance Monitor

To use the Windows Performance Monitor to monitor connection pooling:
  1. Start Performance Monitor by selecting Start → All Programs → Administrative Tools → Performance.
  2. Add performance counters to monitor connection pooling with one of the following methods:
    • Right-click the graph and select Add Counters from the popup menu.
    • Click the add button above the graph.
  3. In the Performance object drop down list, select ".NET CLR Data."
    The SQL Server .NET data provider adds performance counters that can tune connection pooling and troubleshoot pooling problems. Table 1-2 describes the counters.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Taking Advantage of Connection Pooling
Content preview·Buy reprint rights for this chapter
You need to understand connecting pooling and make sure that your applications use it.
To effectively use connection pooling, you need to understand the concepts underlying connection pooling, how connection pooling is implemented by the major .NET data providers, and how to ensure that connection pooling is used by an application.
Connection pooling allows an application to reuse connections from a pool instead of repeatedly creating and destroying new connections. Connection pooling can significantly improve the performance and scalability of applications by allowing a smaller number of connections to service the connection requirements of an application and because the overhead of establishing a new connection is eliminated.
A connection pool is created for each unique connection string. An algorithm associates items in the pool based on an exact match with the connection string; this includes capitalization, order of name value pairs, and even spaces between name/value pairs. Dynamically generated connection strings must be identical so that connection pooling is used. If delegation is used, there will be one pool per delegate user. When transactions are used, one pool is created per transaction context. (For more information, see Recipe 1.17.) When the connection pool is created, connection objects are created and added to the pool to satisfy the minimum pool size specified.
When a connection is requested by an application and the maximum pool size has been reached, the request is queued. The request is satisfied by reallocating a connection that is released back to the pool when the Connection is closed or disposed. The connection pool manager removes expired connections and connections that have had their connection with the server severed from the pool.
The Connection object should be closed as soon as it is no longer needed so that it is added to or returned to the connection pool. This is done by calling either the Close( ) or Dispose( ) method of the Connection. Connections that are not explicitly closed might not be added to or returned to the connection pool.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Setting Connection Pooling Options
Content preview·Buy reprint rights for this chapter
You need to know the different connection pooling options and how you can control them.
Use the connection string to control connection pooling for the SQL Server, OLE DB .NET, Oracle, or ODBC.NET data provider.
The sample code contains a method and four event handlers:
Form.Load
Creates a Connection, attaches an event handler to its StateChange event, and sets default properties for controls on the form that are used to specify connection properties. The UpdateConnection( ) method is called to dynamically construct a connection string from the specified properties.
UpdateConnectionString( )
This method dynamically constructs a connection string from the connection string properties specified by the user in text boxes on the form. This method is called to update the connection string when the user changes the value of any of the controls used to specify connection string properties.
Open Button.Click
Opens the Connection that is based on the connection string constructed in the UpdateConnectionString( ) method.
Close Button.Click
Closes the connection string.
Connection.StateChange
Displays original and current state information about the connection when its state changes.
The C# code is shown in Example 1-11.
Example 1-11. File: ConnectionPoolingOptionsForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

private SqlConnection conn;

//  . . . 

private void ConnectionPoolingOptionsForm_Load(object sender,
    System.EventArgs e)
{    
    conn = new SqlConnection( );
    conn.StateChange += new StateChangeEventHandler(conn_StateChange);

    connectionStringTextBox.Text =
        ConfigurationSettings.AppSettings["Sql_ConnectString"];
    connectTimeoutTextBox.Text = "15";
    connectLifetimeTextBox.Text = "0";
    minPoolSizeTextBox.Text = "0";
    maxPoolSizeTextBox.Text = "100";
    poolCheckBox.Checked = true;

    UpdateConnectionString( );
}

private void UpdateConnectionString( )
{
    connectionStringTextBox.Text =
        ConfigurationSettings.AppSettings["Sql_ConnectString"] +
        "Connection Timeout = " + connectTimeoutTextBox.Text + ";" +
        "Connection Lifetime = " + connectLifetimeTextBox.Text + ";" +
        "Min Pool Size = " + minPoolSizeTextBox.Text + ";" +
        "Max Pool Size = " + maxPoolSizeTextBox.Text + ";" +
        "Pooling = " + poolCheckBox.Checked.ToString( );
}

private void openButton_Click(object sender, System.EventArgs e)
{
    try
    {
        conn.ConnectionString = connectionStringTextBox.Text;
        conn.Open( );
    }
    catch(SqlException ex)
    {
        MessageBox.Show("ERROR: " + ex.ToString( ), "Open Connection",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    catch(InvalidOperationException ex)
    {
        MessageBox.Show("ERROR: " + ex.ToString( ), "Open Connection",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

private void closeButton_Click(object sender, System.EventArgs e)
{
    conn.Close( );
}

private void conn_StateChange(object sender, StateChangeEventArgs e)
{
    connectionStateTextBox.Text =
        "Connection.StateChange event occurred" +
        Environment.NewLine +
        "OriginalState = " + e.OriginalState.ToString( ) +
        Environment.NewLine +
        "CurrentState = " + e.CurrentState.ToString( );
}
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Using Transactions with Pooled Connections
Content preview·Buy reprint rights for this chapter
You want to use connection pooling with transactions in your .NET application to maximize performance.
The discussion explains how to use connection pooling with transactions.
Connections participating in transactions are drawn from the connection pool and assigned based on an exact match with the transaction context of the requesting thread and with the connection string.
Each connection pool is divided into a subdivision for connections without a transaction context and zero or more subdivisions for connections associated with a particular transaction context. Each of these subdivisions, whether associated with a transaction context or not, uses connection pooling based on exact matching of the connection string as described in Recipe 1.15.
When a thread associated with a particular transaction context requests a connection, one from the appropriate pool enlisted with that transaction is automatically returned.
When a connection is closed it is returned to the appropriate subdivision in the connection pool based on the transaction context. This allows a connection to be closed without generating an error even if a distributed transaction is still pending. The transaction can committed or aborted later.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Changing the Database for an Open Connection
Content preview·Buy reprint rights for this chapter
You want to change the database that a connection uses without recreating the connection.
Use the ChangeDatabase( ) method to change the database for a connection.
The sample code creates a Connection to the Northwind database using the SQL Server .NET data provider. The connection is changed to use the pubs database. Finally the connection is closed. The Database property of the SqlConnection object is displayed throughout the sample for the different connection states.
The C# code is shown in Example 1-12.
Example 1-12. File: ChangeDatabaseForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;

//  . . . 

StringBuilder result = new StringBuilder( );

// Create the connection accessing Northwind database.
SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
result.Append("Connection String:" + Environment.NewLine);
result.Append(conn.ConnectionString + Environment.NewLine + Environment.NewLine);

// Open the connection.
conn.Open( );
result.Append("Connection.State: " + conn.State + Environment.NewLine);
result.Append("Database: " + conn.Database + Environment.NewLine);

// Change the database to pubs.
conn.ChangeDatabase("pubs");
result.Append("Database: " + conn.Database + Environment.NewLine);

// Close the connection.
conn.Close( );
result.Append("Connection.State: " + conn.State + Environment.NewLine);
result.Append("Database: " + conn.Database);

resultTextBox.Text = result.ToString( );
The ChangeDatabase( ) method is defined in the IDbConnection interface that represents a connection to a data source and is implemented by .NET data providers for relational databases including those for SQL Server, Oracle, and OLE DB. The ChangeDatabase( ) method is used to change the current database for an open connection. It takes a single parameter that specifies the name of the database to use in place of the current database. The name of the database must be valid or an ArgumentException will be raised. If the connection is not open when the method is called, an
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Connecting to a Text File
Content preview·Buy reprint rights for this chapter
You want to use ADO.NET to access data stored in a text file.
Use the OLE DB Jet provider to access data in a text file.
The sample code creates an OleDbDataAdapter that uses the Jet OLE DB provider to load the contents of the text file Categories.txt, shown in Example 1-13, into a DataTable and displays the contents in a data grid on the form.
Example 1-13. File: Categories.txt
"CategoryID","CategoryName","Description"
1,"Beverages","Soft drinks, coffees, teas, beers, and ales"
2,"Condiments","Sweet and savory sauces, relishes, spreads, and seasonings"
3,"Confections","Desserts, candies, and sweet breads"
4,"Dairy Products","Cheeses"
5,"Grains/Cereals","Breads, crackers, pasta, and cereal"
6,"Meat/Poultry","Prepared meats"
7,"Produce","Dried fruit and bean curd"
8,"Seafood","Seaweed and fish"
The C# code is shown in Example 1-14.
Example 1-14. File: ConnectTextFileForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

//  . . . 

// Create the data adapter to retrieve all rows from text file.
OleDbDataAdapter da =
    new OleDbDataAdapter("SELECT * FROM [Categories.txt]",
    ConfigurationSettings.AppSettings["TextFile_0119_ConnectString"]);

// Create and fill the table.
DataTable dt = new DataTable("Categories");
da.Fill(dt);

// Bind the default view of the table to the grid.
categoriesDataGrid.DataSource = dt.DefaultView;
The Jet OLE DB provider can read records from and insert records into a text file data source. The Jet database engine can access other database file formats through Indexed Sequential Access Method (ISAM) drivers specified in the Extended Properties attribute of the connection. Text files are supported with the text source database type as shown in the following example:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyTextFileDirectory;
    Extended Properties="text;HDR=yes;FMT=Delimited";
The Extended Properties attribute can, in addition to the ISAM version property, specify whether or not tables include headers as field names in the first row of a range using an
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Chapter 2: Retrieving and Managing Data
Content preview·Buy reprint rights for this chapter
The DataSet is a disconnected, in-memory relational database that provides sophisticated navigational capabilities. It acts as a container for other objects including DataTable, DataColumn, DataRow, and DataRelation. The DataAdapter works with the DataSet to update the data source with changes made offline to the DataSet. You can also data bind a DataSet to a variety of Windows Forms and Web Forms controls, in particular, any control that supports the IList interface. The DataSet maintains both current and original versions of its data. Although data appears to be changed, it is not permanently changed until the AcceptChanges( ) method is called either explicitly or implicitly to commit the changes. Recipe 2.6 shows how to access rows marked for deletion.
The DataReader provides forward-only, read-only access to a result set. The DataReader offers the best performance for accessing data by avoiding the overhead associated with the DataSet. The Connection object underlying a DataReader remains open and cannot be used for any other purpose while data is being accessed. This makes the DataReader unsuitable for communicating data remotely between application tiers, or interacting with the data dynamically. If you want to discard a result set in a DataReader before the entire result set has been read, call the Cancel( ) method of the DataReader before calling the Close( ) method. This discards the results on the server so they are not transmitted to the client. Simply calling Close( ) causes the remaining results to be pulled to the client to empty the stream. Since the DataReader reads a result set stream directly from a connection, there is no way to know the number of records in a DataReader. Recipe 2.7 demonstrates techniques that simulate a record count for a DataReader and discusses limitations of the techniques.
You can define DataSet and DataReader object schemas programmatically or infer them from a database schema. Retrieving schema information from the database has its limitations. For example, data relations cannot be created in a DataSet
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Introduction
Content preview·Buy reprint rights for this chapter
The DataSet is a disconnected, in-memory relational database that provides sophisticated navigational capabilities. It acts as a container for other objects including DataTable, DataColumn, DataRow, and DataRelation. The DataAdapter works with the DataSet to update the data source with changes made offline to the DataSet. You can also data bind a DataSet to a variety of Windows Forms and Web Forms controls, in particular, any control that supports the IList interface. The DataSet maintains both current and original versions of its data. Although data appears to be changed, it is not permanently changed until the AcceptChanges( ) method is called either explicitly or implicitly to commit the changes. Recipe 2.6 shows how to access rows marked for deletion.
The DataReader provides forward-only, read-only access to a result set. The DataReader offers the best performance for accessing data by avoiding the overhead associated with the DataSet. The Connection object underlying a DataReader remains open and cannot be used for any other purpose while data is being accessed. This makes the DataReader unsuitable for communicating data remotely between application tiers, or interacting with the data dynamically. If you want to discard a result set in a DataReader before the entire result set has been read, call the Cancel( ) method of the DataReader before calling the Close( ) method. This discards the results on the server so they are not transmitted to the client. Simply calling Close( ) causes the remaining results to be pulled to the client to empty the stream. Since the DataReader reads a result set stream directly from a connection, there is no way to know the number of records in a DataReader. Recipe 2.7 demonstrates techniques that simulate a record count for a DataReader and discusses limitations of the techniques.
You can define DataSet and DataReader object schemas programmatically or infer them from a database schema. Retrieving schema information from the database has its limitations. For example, data relations cannot be created in a DataSet from the database schema.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Retrieving Hierarchical Data into a DataSet
Content preview·Buy reprint rights for this chapter
You want to fill a DataSet with parent and related child data, even if the DataSet already has a schema that includes the relationship.
There are several techniques you can use to load parent and child data into a DataSet.
The sample code contains three event handlers:
Form.Load
Sets up the sample by creating a DataSet with table schemas for both the Orders table and the Order Details table from Northwind and a DataRelation object relating these two tables. The default view of the parent table, Orders, is bound to a data grid on the form.
Load DataSet Button.Click
Starts by clearing the data from the DataSet and refreshing the data grid. DataAdapter objects are created for both the parent and the child table. The Orders and Order Details are then filled using data adapters in the sequence specified and enforcing constraints during the load as specified by the user.
The C# code is shown in Example 2-1.
Example 2-1. File: HierarchicalDataSetForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

private DataSet ds;

//  . . . 

private void HierarchicalDataSetForm_Load(object sender,
    System.EventArgs e)
{
    ds = new DataSet( );

    // Get the schema for the Orders table.
    DataTable parentTable = new DataTable("Orders");
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    da.FillSchema(parentTable, SchemaType.Source);
    ds.Tables.Add(parentTable);

    // Get the schema for the Order Details table.
    DataTable childTable = new DataTable("Order Details");
    da = new SqlDataAdapter("SELECT * FROM [Order Details]",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    da.FillSchema(childTable, SchemaType.Source);
    ds.Tables.Add(childTable);

    // Add the relation between the tables.
    DataRelation dr = new DataRelation("Order_OrderDetails_Relation",
        parentTable.Columns["OrderID"], childTable.Columns["OrderID"]);
    ds.Relations.Add(dr);

    // Bind the default view of the Orders table with the grid.
    dataGrid.DataSource = parentTable.DefaultView;        
}

private void loadDataSetButton_Click(object sender, System.EventArgs e)
{
    // Remove all data from the DataSet and refresh the grid.
    ds.Clear( );
    dataGrid.Refresh( );

    // Create parent and child data adapters.
    SqlDataAdapter daParent = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    SqlDataAdapter daChild = new SqlDataAdapter(
        "SELECT * FROM [Order Details]",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);

    // Enforce constraints as specified by user.
    ds.EnforceConstraints = (enforceConstraintsCheckBox.Checked);

    try
    {
        if (loadParentFirstRadioButton.Checked)
        {
            // Load parent data first.
            daParent.Fill(ds, "Orders");
            daChild.Fill(ds, "Order Details");
        }
        else
        {
            // Load child data first.
            daChild.Fill(ds, "Order Details");
            daParent.Fill(ds, "Orders");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        return;
    }

    ds.EnforceConstraints = true;
}
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Building a DataSet Programmatically
Content preview·Buy reprint rights for this chapter
You want to build a DataSet programmatically—including adding tables, columns, primary keys, and relations—from a schema that you have designed.
The following example shows how to build a complex DataSet programmatically, including how to build and add tables, columns, primary key constraints, relations, and column mappings. Use this as a template for building your own DataSet.
The sample code create