ADO.NET 3.5 Cookbook | O'Reilly Media

BUY THIS BOOK

Print Book $54.99

PDF $43.99

Safari Books Online

What is this?

Print Book £34.50

What is this?

Looking to Reprint or License this content?

Tell a friend

ADO.NET 3.5 Cookbook, Second Edition

By Bill Hamilton
Book Price: $54.99 USD
£34.50 GBP
PDF Price: $43.99

Cover | Table of Contents | Colophon

Chapter 1: Connecting to Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter shows 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 the part of the .NET Framework that connects applications to data sources and lets you retrieve and update the contained data. ADO.NET supports 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 Framework data provider is used to connect to a data source, execute commands, and retrieve results. The .NET Framework ships with the data providers shown in .

Table : Data providers included in the .NET Framework
.NET Framework data provider	Data source access
SQL Server	Microsoft SQL Server version 7.0 or later
OLE DB	Data sources using OLE DB
ODBC	Data sources using ODBC
Oracle	Oracle client software version 8.1.7 or later

Other providers are also available; for example, Oracle has developed its own .NET data provider. 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 core 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 with tight XML integration and is designed specifically to facilitate development of loosely coupled solutions.

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 PDF of this chapter|Buy reprint rights for this chapter

A .NET Framework data provider is used to connect to a data source, execute commands, and retrieve results. The .NET Framework ships with the data providers shown in .

Table : Data providers included in the .NET Framework
.NET Framework data provider	Data source access
SQL Server	Microsoft SQL Server version 7.0 or later
OLE DB	Data sources using OLE DB
ODBC	Data sources using ODBC
Oracle	Oracle client software version 8.1.7 or 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!

Storing Connection Strings

Content preview·Buy PDF of this chapter|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.

Solutions include storing the connection string in an application configuration file or the Windows registry, hardcoding the connection string in your application, representing it using a Universal Data Link (UDL) file, and storing it in a custom file. Some of these techniques are better than others, and some are completely inadvisable. This solution explores and discusses the alternatives.

The .NET Framework 2.0 introduced protected configuration as a mechanism for encrypting stored connection strings used by ASP.NET applications. It also introduced the connectionStrings configuration element to specify a collection of database strings. In prior versions, connection strings were stored in the appSettings element.

A connection string is made up of a semicolon 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

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 Connection String

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You need to programmatically construct a connection string.

Use a connection string builder class.

The solution uses SqlConnectionStringBuilder to programmatically construct a connection string for SQL Server that connects to the AdventureWorks database using integrated security.

The C# code in Program.cs in the project BuildConnectionString is shown in .

Example . Program.cs for BuildConnectionString solution

using System;
using System.Data.SqlClient;

namespace BuildConnectionString
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a connection string builder
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(  );

            // Define connection string attributes using three techniques
            csb.DataSource = "(local)";
            csb.Add("Initial Catalog", "AdventureWorks");
            csb["Integrated Security"] = true;

            // Output the connection string from the connection string builder
            Console.WriteLine("Connection string:\n{0}", csb.ConnectionString);

            // Create a connection string from the connection string builder
            SqlConnection connection = new SqlConnection(csb.ConnectionString);
            // Open and close the connection
            connection.Open(  );
            Console.WriteLine("\nConnectionState = {0}", connection.State);
            connection.Close(  );
            Console.WriteLine("ConnectionState = {0}", connection.State);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in .

Figure : Output for BuildConnectionString solution

.NET Framework 2.0 introduced connection string builders—helper classes used to construct provider-specific connection strings. You supply the connection string name-value pairs using properties of the connection string builder, the Add() method, or the indexer. You retrieve and use the connection string using the ConnectionString property.

Connection string builders are included for SQL Server, Oracle, OLE DB, and ODBC. These strongly typed connection string builders inherit from 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!

Connecting to SQL Server

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to connect to a SQL Server database.

You can connect to an SQL Server database using the SQL Server .NET data provider, the OLE DB .NET data provider, or the ODBC .NET data provider.

The solution creates and opens a connection to an SQL Server database using the SQL Server .NET data provider, OLE DB .NET data provider, and ODBC.NET data provider in turn. In each case, information about the connection is displayed from the properties of the connection object.

The C# code in Program.cs in the project ConnectSqlServer is shown in .

Example . File: Program.cs for ConnectSqlServer solution

using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;

namespace ConnectSqlServer
{
    class Program
    {
        static void Main(string[] args)
        {

            // Connect using .NET data provider for SQL Server and integrated
            // security
            string sqlConnectString1 = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString1))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("---.NET data provider for SQL Server " +
                    "with Windows Authentication mode---");
                Console.WriteLine("ConnectionString = {0}\n", sqlConnectString1);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

            // Connect using .NET data provider for SQL Server and SQL Server
            // authentication
            string sqlConnectString2 = "Data Source=(local);" +
                "User Id=sa;Password=password;Initial Catalog=AdventureWorks;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString2))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("\n---.NET data provider for SQL Server " +
                    "with SQL Server Authentication mode---");
                Console.WriteLine("ConnectionString = {0}\n", sqlConnectString2);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

            // Connect using .NET data provider for OLE DB.
            string oledbConnectString = "Provider=SQLOLEDB;Data Source=(local);" +
                "Initial Catalog=AdventureWorks;User Id=sa;Password=password;";

            using (OleDbConnection connection =
                new OleDbConnection(oledbConnectString))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("\n---.NET data provider for OLE DB---");
                Console.WriteLine("ConnectionString = {0}\n", oledbConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

            // Connect using .NET data provider for ODBC.
            string odbcConnectString = "Driver={SQL Native Client};" +
                "Server=(local);Database=AdventureWorks;uid=sa;pwd=password;";

            using (OdbcConnection connection =
                new OdbcConnection(odbcConnectString))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("\n---.NET data provider for ODBC---");
                Console.WriteLine("ConnectionString = {0}\n", odbcConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

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

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to connect to a named instance of a SQL Server or SQL Server Express.

You need to understand what a SQL Server or SQL Server Express named instance is and how to connect to one.

The solution creates and opens a connection to a named instance of a SQL Server Express. Information about the SQL Server is displayed from the properties of the SqlConnection object.

The C# code in Program.cs in the project ConnectSqlServerNamedInstance is shown in .

Example . File: Program.cs for ConnectSqlServerNamedInstance solution

using System;
using System.Data.SqlClient;

namespace ConnectSqlServerNamedInstance
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = @"Data Source=(local)\SQLExpress;" +
                "Integrated security=SSPI;Initial Catalog=master;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("ConnectionString = {0}\n", sqlConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in .

Figure : Output for ConnectSqlServerNamedInstance solution

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 of the Enterprise Edition of SQL Server 2005 and later is 50. The maximum number of instances of other versions of SQL Server 2005 and later is 16. 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 SQL Server Management Studio 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 an IP Address

Content preview·Buy PDF of this chapter|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 solution 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 in Program.cs in the project ConnectIPAddressSqlServer is shown in .

Example . File: Program.cs for ConnectIPAddressSqlServer solution

using System;
using System.Data.SqlClient;

namespace ConnectIPAddressSqlServer
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectString =
                "Network Library=dbmssocn;Network Address=127.0.0.1;" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks";

            using (SqlConnection connection = new SqlConnection(connectString))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine(
                    "ConnectionState = {0}\nDataSource = {1}\nServerVersion = {2}", 
                    connection.State, connection.DataSource,
                    connection.ServerVersion);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in .

Figure : Output for ConnectIPAddressSqlServer solution

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 protocols for SQL Server include:

Shared Memory: Connects to SQL Server instances running on the same computer as the client. Shared memory protocol is used primarily for troubleshooting. This protocol cannot be used on clients that use MDAC 2.8 or earlier—these clients are automatically switched to the named pipes protocol.

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 PDF of this chapter|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:

Begin by creating a new ASP.NET Web Application project.
Add the following elements to the Web.config file within the <system.web> element:
```
	<authentication mode="Windows" />
	<identity impersonate="true" />
```

Add a connection string to AdventureWorks on the local machine to the configuration file Web.config by updating the <connectionStrings> element within the <configuration> element as follows:

	<connectionStrings>
	    <add name="AdventureWorks" providerName="System.Data.SqlClient"
	        connectionString="Data Source=(local);
	        Integrated security=SSPI;Initial Catalog=AdventureWorks;"/>
	</connectionStrings>

The C# code in Default.aspx.cs in the project IntegratedSecurityFromAspNet is shown in .

Example . File: Default.aspx.cs for IntegratedSecurityFromAspNet solution

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace IntegratedSecurityFromAspNet
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string sqlText = "SELECT TOP 10 * FROM Person.Contact";
            string connectString = 
                ConfigurationManager.ConnectionStrings[
                "AdventureWorks"].ConnectionString;
            DataTable dt = new DataTable(  );
            SqlDataAdapter da = new SqlDataAdapter(sqlText, connectString);
            da.Fill(dt);

            foreach (DataRow row in dt.Rows)
                Response.Write(row["ContactID"] + " - " + row["LastName"] +
                    ", " + row["FirstName"] + "<br/>");
        }
    }
}

The output is shown in .

Connecting to a SQL Server database provides two different authentication modes:

Windows Authentication: Uses the current security identity from the Windows user account to provide authentication information. It does not expose the user ID and password and is the recommended method for authenticating a connection.

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 PDF of this chapter|Buy reprint rights for this chapter

You want to connect to an Oracle database.

You can connect to an Oracle database using the Oracle .NET data provider, the OLE DB .NET data provider, or the ODBC .NET data provider.

The solution creates and opens a connection to an Oracle database using the Oracle .NET data provider, OLE DB .NET data provider, and ODBC .NET data provider in turn. In each case, information about the connection is displayed from the properties of the connection object.

The solution requires a reference to the System.Data.OracleClient assembly.

The C# code in Program.cs in the project ConnectOracle is shown in .

Example . File: Program.cs for ConnectOracle solution

using System;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Data.Odbc;

namespace ConnectOracle
{
    class Program Interprocess communication (IPC)
    {
        static void Main(string[] args)
        {
            // Connect using .NET data provider for Oracle
            string oracleConnectString =
                "Data Source=ORCL;User Id=hr;Password=password;";

            using (OracleConnection connection =
                new OracleConnection(oracleConnectString))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("---Microsoft .NET Provider for Oracle---");
                Console.WriteLine("ConnectionString = {0}\n", oracleConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

            // Connect using .NET data provider for OLE DB.
            string oledbConnectString =
                "Provider=MSDAORA;Data Source=ORCL;User Id=hr;Password=password;";
            
            using (OleDbConnection connection =
                new OleDbConnection(oledbConnectString))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("\n---Microsoft .NET Provider for OLE DB---");
                Console.WriteLine("ConnectionString = {0}\n", oledbConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }
 
            // Connect using .NET data provider for ODBC
            string odbcConnectString = "Driver={Oracle in OraDb10g_home1};" +
                "Server=ORCL;uid=hr;pwd=password;";

            using (OdbcConnection connection =
                new OdbcConnection(odbcConnectString))
            {
                connection.Open(  );

                // Return some information about the server.
                Console.WriteLine("\n---Microsoft .NET Provider for ODBC---");
                Console.WriteLine("ConnectionString = {0}\n", odbcConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

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 OLE DB Data Source

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to access your data source using an OLE DB provider from your .NET application.

Use the OLE DB .NET data provider to access data exposed through an OLE DB driver.

The solution creates an OleDbDataReader and uses it to output the FirstName and LastName columns from the first five rows in the Person.Contact table in the AdventureWorks sample database.

The C# code in Program.cs in the project ConnectOleDbDataSource is shown in .

Example . File: Program.cs for ConnectOleDbDataSource solution

using System;
using System.Data.OleDb;

namespace ConnectOleDbDataSource
{
    class Program
    {
        static void Main(string[] args)
        {
            string oledbConnectString = "Provider=SQLOLEDB;Data Source=(local);" +
                "Initial Catalog=AdventureWorks;Integrated Security=SSPI";

            string sqlSelect = "SELECT TOP 5 Title, FirstName, LastName " +
                "FROM Person.Contact";

            // Create an ODBC Connection
            using (OleDbConnection connection =
                new OleDbConnection(oledbConnectString))
            {
                OleDbCommand command = new OleDbCommand(sqlSelect, connection);

                // Execute the DataReader
                connection.Open(  );
                OleDbDataReader reader = command.ExecuteReader(  );

                // Output the data from the DataReader to the console

                while (reader.Read(  ))
                    Console.WriteLine("{0} {1}", reader[0], reader[1]);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in .

Figure : Output for ConnectOleDbDataSource solution

An OLE DB provider is a set of COM objects that serves tabular data from a data source to the client in response to interface calls. The .NET Framework data provider for OLE DB is a collection of managed classes that lets you access an OLE DB data source. OLE DB providers exist for virtually every database as well as for many other data sources including Active Directory Service, Microsoft Exchange, and Microsoft Indexing Service.

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 PDF of this chapter|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 solution creates an OdbcDataReader and uses it to output the FirstName and LastName columns from the first five rows in the Person.Contact table in the AdventureWorks sample database.

The C# code in Program.cs in the project ConnectOdbcDataSource is shown in .

Example . File: Program.cs for ConnectOdbcDataSource solution

using System;
using System.Data.Odbc;

namespace ConnectOdbcDataSource
{
    class Program
    {
        static void Main(string[] args)
        {
            string odbcConnectString = "DRIVER={SQL Server};SERVER=(local);" +
                "DATABASE=AdventureWorks;Trusted_Connection=yes;";

            string sqlSelect = "SELECT TOP 5 Title, FirstName, LastName " +
                "FROM Person.Contact";

            // Create an ODBC Connection
            using (OdbcConnection connection =
                new OdbcConnection(odbcConnectString))
            {
                OdbcCommand command = new OdbcCommand(sqlSelect, connection);

                // Execute the DataReader
                connection.Open(  );
                OdbcDataReader reader = command.ExecuteReader(  );

                // Output the data from the DataReader to the console

                while (reader.Read(  ))
                    Console.WriteLine("{0} {1}", reader[0], reader[1]);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in .

Figure : Output for ConnectOdbcDataSource solution

The ODBC .NET data provider uses the native ODBC Driver Manager to access data. 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

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 Access Database

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to connect to a Microsoft Access database.

Use the OLE DB .NET data provider.

The solution creates and opens a connection to a Microsoft Access database using the OLE DB .NET data provider. Information about the connection is displayed.

The C# code in Program.cs in the project ConnectAccessDatabase is shown in .

Example . File: Program.cs for ConnectAccessDatabase solution

using System;
using System.Data;
using System.Data.OleDb;

namespace ConnectAccessDatabase
{
    class Program
    {
        static void Main(string[] args)
        {
            string oledbConnectString =
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                @"C:\Documents and Settings\bill\My Documents\" +
                "Northwind 2007.accdb;";

            using (OleDbConnection connection =
                new OleDbConnection(oledbConnectString))
            {
                connection.Open(  );

                // Output some connection and database information.
                Console.WriteLine("Connection State: {0}", connection.State);
                Console.WriteLine("OLE DB Provider: {0}", connection.Provider);
                Console.WriteLine("Server Version: {0}", connection.ServerVersion);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in .

Figure : Output for ConnectAccessDatabase solution

You can connect to a Microsoft Access database using the OLE DB .NET data provider. The OLE DB connection uses the Microsoft.ACE.OLEDB.12.0, which is the new Access database engine OLE DB driver that can also read previous formats. The Jet OLE DB driver cannot access Microsoft Access 2007 databases.

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 Microsoft Access Database

Content preview·Buy PDF of this chapter|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 solution creates and opens a connection to a password-secured Microsoft Access database using the OLE DB .NET data provider. Information about the connection is displayed from the properties of the OleDbConnection object.

The C# code in Program.cs in the project ConnectPasswordAccessDatabase is shown in .

Example . File: Program.cs for ConnectPasswordAccessDatabase solution

using System;
using System.Data;
using System.Data.OleDb;

namespace ConnectAccessDatabase
{
    class Program
    {
        static void Main(string[] args)
        {
            string oledbConnectString =
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                @"C:\Documents and Settings\bill\My Documents\" +
                "Northwind 2007.accdb;" +
                "Jet OLEDB:Database Password=password;";

            using (OleDbConnection connection =
                new OleDbConnection(oledbConnectString))
            {
                connection.Open(  );

                // Output some connection and database information.
                Console.WriteLine("Connection State: {0}", connection.State);
                Console.WriteLine("OLE DB Provider: {0}", connection.Provider);
                Console.WriteLine("Server Version: {0}", connection.ServerVersion);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in .

Figure : Output for ConnectPasswordAccessDatabase solution

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.

The Set Database command from the Tools → Security menu is used to set up a database password.

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 Access Database from ASP.NET

Content preview·Buy PDF of this chapter|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 Microsoft Access database engine to the default user account used by ASP.NET.

Begin by creating a new ASP.NET Web Application project named ConnectMSAccessDataAspNet.
Add the following elements to the Web.config file within the <system.web> element:
```
	<authentication mode="Windows" />
	<identity impersonate="true" />
```

Add a connection string to Northwind 2007.accdbs on the local machine to the configuration file Web.config by adding the following <connectionStrings> element within the <configuration> element:

	<connectionStrings>
	    <add name="Northwind2007" providerName="System.Data.OleDb"
	       connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
	           Data Source=C:\Documents and Settings\bill\My Documents\
	           Northwind 2007.accdb;"/>
	</connectionStrings>

The C# code Default.aspx.cs in the project ConnectMSAccessDataAspNet is shown in .

Example . File: Default.aspx.cs for ConnectMSAccessDataAspNet solution

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

namespace ConnectMSAccessDataAspNet
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string sqlText = "SELECT TOP 10 * FROM Customers";
            string connectString =
                ConfigurationManager.ConnectionStrings[
                "Northwind2007"].ConnectionString;
            DataTable dt = new DataTable(  );
            OleDbDataAdapter da =
                new OleDbDataAdapter(sqlText, connectString);
            da.Fill(dt);

            foreach (DataRow row in dt.Rows)
                Response.Write(row["ID"] + " - " + row["Last Name"] +
                    ", " + row["First Name"] + "<br/>");
        }
    }
}

The output is shown in .

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 PDF of this chapter|Buy reprint rights for this chapter

You want to connect to a Microsoft Excel workbook.

Use the OLE DB .NET data provider.

The solution creates and opens a connection to a Microsoft Excel workbook using the OLE DB .NET data provider. Information about the connection is displayed.

The Excel 2007 workbook in this solution is shown in .

Figure : Excel workbook Category.xlsx

The Excel 2003 workbook used in this solution is identical.

The C# code in Program.cs in the project ConnectExcel is shown in .

Example . File: Program.cs for ConnectExcel solution

using System;
using System.Data;
using System.Data.OleDb;

namespace ConnectExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            // Define connection strings for both default
            // Excel .xlsx format and Excel 97-2003 .xls format
            string[] oledbConnectString = new string[]
            {
                "Provider=Microsoft.ACE.OLEDB.12.0;" +
                @"Data Source=..\..\..\Category.xlsx;" +
                "Extended Properties=\"Excel 12.0;HDR=YES\";",

                "Provider=Microsoft.ACE.OLEDB.12.0;" +
                @"Data Source=..\..\..\Category.xls;" +
                "Extended Properties=\"Excel 8.0;HDR=YES\";"
             };

            foreach (string connectString in oledbConnectString)
            {
                // Define and open the connection
                OleDbConnection connection =
                    new OleDbConnection(connectString);
                connection.Open(  );

                // Output some connection properties to the console
                Console.WriteLine("---CONNECTION---");
                Console.WriteLine("Connection.String = {0}\n",
                    connectString);
                Console.WriteLine("Connection.State = {0}",
                    connection.State);
                Console.WriteLine("Connection.Provider = {0}",
                    connection.Provider);
                Console.WriteLine("Connection.ServerVersion = {0}",
                    connection.ServerVersion);

                connection.Close(  );

                Console.WriteLine(  );
            }

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey(  );
        }
    }
}

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 PDF of this chapter|Buy reprint rights for this chapter

You want to use ADO.NET to access data stored in a text file.

Use the OLE DB .NET data provider.

The solution creates and opens a connection to a text file using the OLE DB .NET data provider. Information about the connection is displayed.

The C# code in Program.cs in the project ConnectTextFile is shown in .

Example . File: Program.cs for ConnectTextFile solution

using System;
using System.Data;
using System.Data.OleDb;

namespace ConnectTextFile
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                @"Data Source=..\..\..\;" +
                "Extended Properties=\"text;HDR=yes;FMT=Delimited\";";

            OleDb Connection connection = new OleDbConnection(connectString);
            connection.Open(  );

            // Output some connection properties to the console
            Console.WriteLine("Connection.String = {0}\n",
                connectString);
            Console.WriteLine("Connection.State = {0}",
                connection.State);
            Console.WriteLine("Connection.Provider = {0}",
                connection.Provider);
            Console.WriteLine("Connection.ServerVersion = {0}",
                connection.ServerVersion);

            connection.Close(  );

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey(  );
        }
    }
}

The output is shown in .

Figure : Output for ConnectTextFile solution

The .NET OLE DB provider can read records from and insert records into a text file data source using the Microsoft Access database engine (ACE) driver. The ACE driver 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.ACE.OLEDB.12.0;Data Source=..\..\..\;
	Extended Properties="text;HDR=yes;FMT=Delimited";

Notice that only the directory for the text file is specified in the connection string. The filename of the text file is specified in the T-SQL commands that access data in the text file, similar to a table name in a 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!

Changing the Database for an Open Connection

Content preview·Buy PDF of this chapter|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 solution creates a Connection to the AdventureWorks database using the SQL Server .NET data provider. The database for the connection is then changed to use the ReportServer database. Finally, the connection is explicitly closed.

The C# code in Program.cs in the project ChangeConnectionDatabase is shown in .

Example . File: Program.cs for ChangeConnectionDatabase solution

using System;
using System.Data.SqlClient;

namespace ChangeConnectionDatabase
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                Console.WriteLine("ConnectionString = {0}\n",
                    connection.ConnectionString);

                // Open the connection
                connection.Open();
                Console.WriteLine("=> Connection opened.\n");

                Console.WriteLine("Connection.State = {0}", connection.State);
                Console.WriteLine("Database = {0}\n", connection.Database);

                // Change the database.
                connection.ChangeDatabase("ReportServer");
                Console.WriteLine("=> Database changed to ReportServer.\n");

                Console.WriteLine("Connection.State = {0}", connection.State);
                Console.WriteLine("Database = {0}\n", connection.Database);

                // Close the connection
                connection.Close();
                Console.WriteLine("=> Connection closed.\n");

                Console.WriteLine("Connection.State = {0}", connection.State);
                Console.WriteLine("Database = {0}", connection.Database);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

The output is shown in .

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 PDF of this chapter|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. Note that connection pooling does not work in the debugger regardless of whether build configuration is set to Debug or Release.

The solution creates two connection strings, each with different connection pooling options. Each connection is opened and closed. In each case, an event handler is attached to the StateChange event to monitor changes in connection state.

The C# code in Program.cs in the project SetConnectionPoolingOptions is shown in .

Example . File: Program.cs for SetConnectionPoolingOptions solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace SetConnectionPoolingOptions
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            SqlConnection connection = new SqlConnection(  );

            // Set up the event handler to detect connection state change
            connection.StateChange +=
                new StateChangeEventHandler(connection_StateChange);

            // Set the connection string with pooling options
            connection.ConnectionString = sqlConnectString +
                "Connection Timeout=15;Connection Lifetime=0;" +
                "Min Pool Size=0;Max Pool Size=100;Pooling=true;";

            // Output the connection string and open/close the connection
            Console.WriteLine("Connection string = {0}",
                connection.ConnectionString);
            Console.WriteLine("-> Open connection.");
            connection.Open();
            Console.WriteLine("-> Close connection.");
            connection.Close();

            // Set the connection string with new pooling options
            connection.ConnectionString = sqlConnectString +
                "Connection Timeout=30;Connection Lifetime=0;" +
                "Min Pool Size=0;Max Pool Size=200;Pooling=true;";

            // Output the connection string and open/close the connection
            Console.WriteLine("\nConnection string = {0}",
                connection.ConnectionString);
            Console.WriteLine("-> Open connection.");
            connection.Open();
            Console.WriteLine("-> Close connection.");
            connection.Close();

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }

        static void connection_StateChange(object sender, StateChangeEventArgs e)
        {
            Console.WriteLine("\tConnection.StateChange event occurred.");
            Console.WriteLine("\tOriginalState = {0}", e.OriginalState.ToString());
            Console.WriteLine("\tCurrentState = {0}", 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!

Taking Advantage of Connection Pooling

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You need to understand connection 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 , next.) 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.

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 Transactions with Pooled Connections

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to use connection pooling with transactions in your .NET application to maximize performance.

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 .

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 be 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!

Displaying a Connection Property Dialog Box

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You want to display a dialog box from an application that lets users create database connections using a Properties dialog box similar to that in the Visual Studio IDE.

Use a PropertyGrid control.

Follow these steps to create the solution:

Create a C# Windows Forms application, DisplayConnectionPropertyDialog.
Add a PropertyGrid control. Accept all default properties including the name propertyGrid1. Add an event handler named onPropertyValueChanged() for the PropertyValueChanged event.
Add a Label control below the PropertyGrid control. Set its Text property to Connection String:.
Add a TextBox control below the Label control. Name it connectString. Set the ReadOnly property to true. Set the Multiline property to true. Size the control to accommodate about four lines of text.
The C# code in Form1.cs in the project DisplayConnectionPropertyDialog is shown in . You need to add the highlighted code.

Example . File: Program.cs for DisplayConnectionPropertyDialog solution

using System;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DisplayConnectionPropertyDialog
{
    public partial class Form1 : Form
    {
        SqlConnectionStringBuilder scsb =
            new SqlConnectionStringBuilder();

        public Form1()
        {
            InitializeComponent();

            propertyGrid1.SelectedObject = scsb;
        }

        private void onPropertyValueChanged(
            object s, PropertyValueChangedEventArgs e)
        {
            connectString.Text = scsb.ConnectionString;
        }
    }
}

Run the solution and set the DataSource, Initial Catalog, and Integrated Security properties in the property grid. The output is shown in .

The PropertyGrid control provides a user interface for browsing the properties of an object. Set the SelectedObject property of the PropertyGrid class to the object to display properties for—a SqlConnectionStringBuilder, in this case. The PropertyValueChanged event occurs when a property is changed. In the solution, the event handler displays the updated connection string when a property is changed.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Displaying the Data Link Properties Dialog Box

Content preview·Buy PDF of this chapter|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.

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 solution creates and displays a Data Link Properties dialog box using the Microsoft OLE DB Service Component through COM Interop. Follow these steps:

Create a C# Windows Forms application, DisplayDataLinkPropertiesDialog.
Add a TextBox control. Name it connectString. Set the MultiLine property = true.
Add a Button control. Name it openDialog.
Double-click the button to add a Click event handler named openDialog_Click.

The C# code in Form1.cs shown in the project DisplayDataLinkPropertiesDialog is shown in .

Example . File: Form1.cs for DisplayDataLinkPropertiesDialog solution

using System;
using System.Windows.Forms;

namespace DisplayDataLinkPropertiesDialog
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void openDialog_Click(object sender, EventArgs e)
        {
            ADODB.Connection adodbConnection = new ADODB.Connection(  );
            object connection = (object)adodbConnection;

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

            connectString.Text = adodbConnection.ConnectionString;
        }
    }
}

Execute the application.

Click the Display Dialog button. In the Data Link Properties dialog, select SQL Native Client on the Provider tab. Select the Connection tab. Set the Data Source to (local), select the Use Windows NT Integrated security radio button, and click the OK button to create the connection string. The completed Data Link Properties dialog is shown in .

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·