Chapter 1. Connecting to Data
1.0. Introduction
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 Overview
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 1-1.
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.
ADO.NET code is forward-compatible—ADO.NET code written using .NET Framework 1.1 or later will run on later versions of the .NET Framework.
ADO.NET has both connected and disconnected classes. The connected classes let you retrieve and update data in underlying data sources. The disconnected classes let you access and manipulate offline the data you retrieved using the connected classes and later synchronize it with the underlying data source using the connected class.
Each data provider is responsible for implementing the connected classes. A brief description of each follows:
ConnectionA unique session with the data source. A
Connectionspecifies necessary authentication information needed to connect to a data source. TheConnectionobject is specific to the type of data source—for example, the .NET Framework data provider for SQL Server includes theSqlConnectionclass.CommandIssues database commands against the data source using an established
Connection. TheCommandTextproperty of theCommandclass contains the SQL statement, stored procedure name, or table name executed at the data source. TheCommandobject is specific to the type of data source—for example, the .NET Framework data provider for SQL Server includes theSqlCommandobject.DataReaderRetrieves a forward-only, read-only data stream from a data source. The
DataReaderobject is specific to the type of data source—for example, the .NET Framework data provider for SQL Server includes theSqlDataReaderobject.DataAdapterBridges the connected classes with the disconnected classes by retrieving data from a data source and filling a (disconnected)
DataSet. TheDataAdapteralso updates the data source with changes made to a disconnectedDataSet. TheDataAdapteruses theConnectionobject to connect the data source and up to fourCommandobjects to retrieve data from and resolve changes (i.e., update, insert, and delete rows) to the data source. TheDataAdapterobject is specific to the type of data source—for example, the .NET Framework data provider for SQL Server includes theSqlDataAdapterobject.
The disconnected classes are part of the ADO.NET classes in the .NET Framework. They provide a consistent programming model regardless of the data source or data provider. The disconnected classes include:
DataSetAn in-memory cache of data retrieved from the data source. The
DataSetexhibits similar properties to an in-memory relational database—for example, data is organized into multiple tables usingDataTableobjects, tables can be related usingDataRelationobjects, and data integrity can be enforced using the constraint objectsUniqueConstraintandForeignKeyConstraint.The
DataSetretains no information about the source of the data used to fill it with data. It maintains both current and original versions of data allowing the data source to be updated with changes at some future time. Disconnected data classes (DataSetandDataTable) are serializable. This supports transport-independent marshaling between application tiers and across a distributed application. You can also use these classes to persist data independently of a database.DataTableA single table of in-memory data that can exist independently or as part of a col-lection of
DataTableobjects in aDataSet.DataColumnDataRowDataViewA data-bindable view of a
DataTableused for custom sorting, filtering, searching, editing, and navigation.DataRelationA parent/child relationship between two
DataTableobjects in aDataSet.ConstraintA constraint on one or more columns in a
DataTableused to maintain data integrity. A constraint is either aUniqueConstraintthat ensures that a column or collection ofColumnobjects are unique within aDataTableor aForeignKeyConstraintthat represents an action restriction on one or more columns in a relationship in aDataTablewhen a value or row is either updated or deleted.
ADO.NET and XML converge in .NET. You can save the DataSet as an XML document, or fill it from an XML document. You can access and modify data simultaneously using both the DataSet classes and XML classes.
Connections, Connection Strings, and Connection Pooling
Database connections are a critical and limited resource. Connections must be managed to ensure that an application performs well and is scalable. SQL Server and Oracle data providers provide connection pooling, while the OLE DB and ODBC providers use the pooling provided by OLE DB or ODBC, respectively.
Connections should be opened as late as possible and closed as soon as possible using the Close()method. Alternatively, you can create the connection in a using block to ensure that the system disposes of the connection when the code exits the block. The connection should be used as briefly as possible, meaning that connections should not last longer than a method call. Connections should not be passed between methods—in addition to creating performance problems and limiting scalability, this can lead to security vulnerabilities.
Data providers use a connection string containing a collection of attribute/value pairs to establish the connection with the database. You specify connection strings using ConnectionString property of a Connection object. The DBConnectionStringBuilder class or the strongly typed version for each specific .NET data provider is used to build connection strings programmatically.
1.1. Storing Connection Strings
Problem
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.
Solution
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.
Discussion
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.
Connecting to a database server requires passing credentials—username and password—to the server in a connection string. These credentials, together with the data source name, need to be kept private to protect unauthorized access to the data source. There are three approaches for obtaining these credentials:
Often, it is not practical to prompt for connection credentials because of disadvantages including:
- Security
Transferring connection information from the client to the server can expose connection credentials if they are not encrypted.
- Connection pooling
The server must recognize each user separately. This results in different connection strings for each user and prevents using connection pooling, which in turn limits application scalability. For more on connection pooling, see Recipe 1.17.
- Single sign-on
It is difficult to integrate with single sign-on strategies, which are becoming increasingly important in enterprise environments (for example, where numerous applications are aggregated into portals).
- Server-based applications
Cannot be used by applications that otherwise have no user interface, such as an XML web service.
There are a number of techniques that you can use to store predetermined connection credentials. These, together with their advantages and drawbacks, are discussed in the following subsections.
Tip
Always configure predetermined accounts with the minimum permissions required.
Use integrated security whenever possible.
Always encrypt stored credentials and carefully control access to the associated encryption keys.
Never use sa or any other administrative account.
Never use blank or weak passwords.
Application configuration file
An application configuration file is an XML-based text file that is used to store application-specific settings used at runtime by the application. The naming convention for and deployment location of the file depend on the type of application:
- Executable application
The name of the configuration file is the name of the application executable with a .config extension—for example, myApplication.exe.config. It is located in the same directory as the executable file.
- ASP.NET application
A web application can have multiple configuration files all named web.config. Each configuration file supplies configuration settings for its directory and all of its child directories; it also overrides any configuration settings inherited from parent directories.
Tip
The machine configuration file—machine.config, located in the CONFIG subdirectory of the .NET runtime installation—contains configuration information that applies to the computer. The machine.config file is checked for configuration settings before the application configuration file is checked.
It is best to put application settings in the application configuration file both to facilitate deployment and to keep the machine configuration file manageable and secure.
The <connectionStrings> element of the application configuration file is used to store a collection of connection strings as name/value pairs. You can store a connection string as shown:
<configuration> <connectionStrings> <add key="ConnectionString" value="Data Source=(local);Initial Catalog=AdventureWorks; User ID=sa;password=;" /> </connectionStrings> </configuration>
The ConnectionStrings property of the System.Configuration class is used to retrieve the value for a specific key within the connectionStrings element; the System.Configuration class cannot be used to write settings to a configuration file.
Application configuration files facilitate deployment because the files are simply installed alongside other application files. One drawback is that application configuration files are not inherently secure since they store information as clear text in a file that is accessible through the filesystem. Encrypt the connection and other sensitive information within the configuration file and ensure that NTFS file permissions are set to restrict access to the file. Recipe 6.11 shows techniques to encrypt data.
Tip
Make sure you name the application configuration file for a Windows Forms application App.config—this is the default. At build time, this file is automatically copied into the startup directory by Visual Studio .NET with the name applicationName.exe.config.
If you name the application configuration file applicationName.exe.configwithin your solution, you will have to copy it to the startup directory each time you modify it and each time you build the solution; the build process deletes it from the startup directory.
Create a new C# console application named
StoredConnectionStringConfig.In the Solution Explorer pane, right-click the project and select Add → New Item from the context menu to open the Add New Item dialog. Select the Application Configuration File template, accept the default name App.config, and click the Add button to add the configuration file.
Add a SQL Server connection string within a
connectionStringselement in the file App.config as shown in Example 1-1.Example 1-1. File: App.config<?xml version="1.0" encoding="utf-8" ?> <configuration><connectionStrings> <add name="AdventureWorks" providerName="System.Data.SqlClient" connectionString="Data Source=(local); Integrated security=SSPI;Initial Catalog=AdventureWorks;" /> </connectionStrings></configuration>Add a reference to the
System.Configurationassembly.
The C# code in Program.cs in the project StoreConnectionStringConfig is shown in Example 1-2.
using System; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace StoreConnectionStringConfig { class Program { static void Main(string[] args) { // Enumerate connection strings Console.WriteLine("---Connection string enumeration---"); foreach (ConnectionStringSettings css in ConfigurationManager.ConnectionStrings) { Console.WriteLine(css.Name); Console.WriteLine(css.ProviderName); Console.WriteLine(css.ConnectionString); } // Retrieve a connection string and open/close it Console.WriteLine("\n---Using a connection string---"); Console.WriteLine("-> Retrieving connection string AdventureWorks"); string sqlConnectString = ConfigurationManager.ConnectionStrings[ "AdventureWorks"].ConnectionString; SqlConnection connection = new SqlConnection(sqlConnectString); Console.WriteLine("-> Opening connection string."); connection.Open( ); Console.WriteLine("Connection string state = {0}", connection.State); connection.Close( ); Console.WriteLine("-> Closing connection string."); Console.WriteLine("Connection string state = {0}", connection.State); Console.WriteLine("\nPress any key to continue."); Console.ReadKey( ); } } }
The output is shown in Figure 1-1.
Protected configuration
You can use protected configuration to encrypt sensitive information, including database connection strings in a web application configuration file. Values are saved in encrypted form rather than as clear text. The .NET Framework decrypts the information when the configuration file is processed and makes it available to your application. Protected configuration requires IIS 6.0 or later. For more information about using protected configuration, search for “Protected Configuration” in MSDN.
Hardcode in the application
An obvious, but poor, technique for storing connection strings is hardcoding them into the application. Although this approach results in the good performance, it has poor flexibility; the application needs to be recompiled if the connection string needs to be changed for any reason. Security is poor. The code can be disassembled to expose connection string information. Caching techniques together with external storage techniques eliminate nearly all performance benefits of hardcoding over external storage techniques.
Hardcoding connection string information is not advised; external server-side storage is preferred in nearly all cases because of the increased flexibility, security, and configuration ease. A discussion of available external storage options follows.
Universal data link (UDL) file
The OLE DB .NET data providers support UDL filenames in its connection string. The UDL file is a resource external to the application that encapsulates connection properties in a separate file. It must be protected using NTFS security to prevent connection information from being exposed or altered. The SQL Server .NET data provider does not support UDL files in its connection string. UDL files are not encrypted; cryptography cannot be used to increase security. NTFS directory and file encryption can secure a UDL file so that even if unauthorized access is gained to the file or the physical disk is stolen, the user ID and password of the user who encrypted the file would still be required to access its contents.
Windows registry
You can store connection strings in the Windows registry as a subkey of HKEY_LOCAL_MACHINE\SOFTWARE. You can encrypt these settings within the registry subkey and restrict access to the subkey to increase the security of this technique. This technique is easy to use because of programmatic support for registry access in .NET classes Registry and RegistryKey in the Microsoft.Win32 namespace.
Storing connection strings in the registry is usually discouraged because of deployment issues; the registry settings must be deployed with the application, defeating benefits of xcopy deployment. Application code can also be restricted in its access to the registry, further complicating deployment.
Custom file
A custom file is any file that is used to for proprietary storage of application settings that are typically used at runtime. There is generally no particular advantage to using a custom file to store connection information so the technique is not recommended. The approach requires extra coding and forces concurrency and other issues to be explicitly addressed.
1.2. Building a Connection String
Solution
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 1-3.
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 1-2.
Discussion
.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 DbConnectionStringBuilder-based class that can be used to create applications that are portable across databases. You have to ensure when using DbConnectionStringBuilder that the name/value pairs you create are supported by the target database. Unlike the strongly typed connection string builders, DbConnectionString builder allows you to create invalid connection strings.
1.3. Connecting to SQL Server
Solution
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 1-4.
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( );
}
}
}The output is shown in Figure 1-3.
Discussion
You can access a SQL Server database using three different provider types: native SQL Server, OLE DB, and ODBC. These alternatives are discussed in the following subsections.
Native SQL Server
The Microsoft SQL Server .NET data provider accesses SQL Server databases beginning with version 7.0 using an internal protocol. The classes are located in the System.Data.SqlClient namespace. An example of a connection string using integrated security is shown in the following snippet:
Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;
Without integrated security, the connection string is:
Data Source=(local);User Id=sa;Password=password;Initial Catalog=AdventureWorks;
Native providers generally perform better than OLE DB or ODBC providers because they are built specifically for the database and because they remove a layer of indirection from the application to the database.
OLE DB
You can use the OLE DB .NET data provider with the SQL Server OLE DB provider (SQL Server or SQL Native Client) to access SQL Server. An example of the connection string is shown here:
Provider=SQLOLEDB;Data Source=(local);Initial Catalog=AdventureWorks; User Id=sa;Password=password;
The OLE DB provider should be used primarily as a bridge from applications that already use OLE DB. Use a native SQL Server .NET data provider where practical.
ODBC
Finally, the ODBC .NET data provider can connect to an SQL Server database. An example of the connection string is shown here:
Driver={SQL Native Client};Server=(local);Database=AdventureWorks;
uid=sa;pwd=password;The ODBC .NET data provider should be used primarily as a bridge from applications that already use ODBC. Use a native SQL Server .NET data provider where possible.
1.4. Connecting to a Named Instance of SQL Server
Solution
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 1-5.
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 1-4.
Discussion
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.
The System.Data.SqlClient class cannot automatically discover the port number of a named instance of SQL Server listening on a port other than the default 1433. To connect to a named instance of SQL Server listening on a custom port, specify the port number following the instance name in the connection string separated by a comma. For example, if the named instance SQLExpress was set up to listen on port 1450, the following connection string might be used:
Data Source=(local)\SQLExpress,1450;Integrated security=SSPI;
Initial Catalog=AdventureWorks1.5. Connecting to SQL Server Using an IP Address
Solution
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 1-6.
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 1-5.
Discussion
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.
- TCP/IP
- Named Pipes
Interprocess communication (IPC) mechanism provided by SQL Server for communication between clients and servers.
- VIA
Virtual Interface Adapter (VIA) protocol is used with VIA hardware.
As of SQL Server 2005, the following network protocols are no longer supported:
- AppleTalk ADSP
Allows Apple Macintosh to communicate with SQL Server using native Apple-Talk 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.
- NWLink IPX/SPX
The native protocol of Novell Netware networks.
For more information about configuring network protocols, see Microsoft SQL Server Books Online.
The use of the SQL Server TCP/IP Sockets improves performance and scalability with high volumes of data. It avoids some security issues associated with named pipes. As with any protocol, the client and the server must be configured to use TCP/IP.
Do this through SQL Server Configuration Manager by expanding the SQL Server Network Configuration node and enabling TCP/IP in the Protocols subnode. You will need to restart SQL Server for the change to take effect.
To connect to SQL Server using an IP address, the TCP/IP network library must be used to connect to the SQL Server. This is done by specifying the library in the connection string as either the attribute Net or Network Library with a value of dbmssocn. Specify the IP address using the Data Source, Server, Address, Addr, or Network Address parameter. The following connection string demonstrates using an IP address to specify the data source:
"Network Library=dbmssocn;Network Address=127.0.0.1;" + "Integrated security=SSPI;Initial Catalog=AdventureWorks";
In the example, the IP address 127.0.0.1 is the IP address for the local machine. This could also be specified as (local). To specify a SQL Server other than a local instance, specify the IP address of the computer on which SQL Server is installed.
Default instances of SQL Server listen on port 1433. Named instances of SQL Server dynamically assign a port number when they are first started. The example above does not specify the port number and therefore uses the default port 1433. If the SQL Server is configured to listen on another port, specify the port number following the IP address specified by the Network Address attribute separated by a comma as shown in the following snippet, which connects to a local SQL Server listening on port 1450:
Network Address=(local),1450
1.6. Connecting to SQL Server Using Integrated Security from ASP.NET
Problem
You want to coordinate Windows security accounts between an ASP.NET application and SQL Server.
Solution
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
AdventureWorkson 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 1-7.
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 Figure 1-6.
Discussion
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.
- SQL Server Authentication
Uses a SQL Server login account providing a user ID and password.
Integrated security requires 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 Authenticationis enabled andAnonymous Accessis 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 within the
<system.web>element:<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=SSPIattribute-and-value pair instead of theUser IDandPasswordattributes in the connection string. The older attribute-and-value pairTrusted_Connection=Yesis 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 use SQL Server on a different computer than 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 domain:
Ensure that the mapped domain user has required privileges to run the web application.
Configure the web application to impersonate the domain user. Add the following elements to the web.config file for the web application:
<authentication mode="Windows" /> <identity impersonate="true" userName="domain\username" password="myPassword" />
1.7. Connecting to an Oracle Database
Solution
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 1-8.
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( ); } } }
The output is shown in Figure 1-7.
Discussion
You can access an Oracle database using three different provider types: native Oracle, OLE DB, and ODBC. These alternatives are discussed in the following subsections.
Native Oracle
The Microsoft Oracle .NET data provider accesses an Oracle database using the Oracle Call Interface (OCI) through Oracle client connectivity software. The provider can access Oracle 7.3.4 or later and requires Oracle 8i Release 3 (8.1.7) or later client software. The classes are located in the System.Data.OracleClient namespace. An example of a connection string using integrated security is shown in the following snippet:
Data Source=ORCL;Integrated Security=yes;
Without integrated security, the connection string is:
Data Source=ORCL;User Id=hr;Password=password;
The Microsoft Oracle .NET data provider is included with .NET Framework version 1.1. It is not included with the .NET Framework version 1.0, but you can download it from http://msdn.microsoft.com/downloads. The Oracle .NET data provider can access Oracle8 Release 8.0 or later and requires the Oracle9i Client Release 2 (9.2) or later.
Native providers generally perform better than OLE DB or ODBC providers because they are built specifically for the database and because they remove a layer of indirection from the application to the database.
OLE DB
You can use the OLE DB .NET data provider with the Oracle OLE DB provider (MSDAORA) to access Oracle data. An example of the connection string is shown here:
Provider=MSDAORA;Data Source=myOracleDb;User Id=hr;Password=password;
The OLE DB provider should be used primarily as a bridge from applications that already use OLE DB. Use a native Oracle .NET data provider where practical.
ODBC
Finally, the ODBC .NET data provider can connect to an Oracle database. An example of the connection string using the Oracle 10g driver from Oracle is shown here:
"Driver={Oracle in OraDb10g_home1};Server=ORCL;uid=hr;pwd=password;";The ODBC .NET data provider should be used primarily as a bridge from applications that already use ODBC. Use a native Oracle .NET data provider where possible.
1.8. Connecting to an OLE DB Data Source
Solution
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 1-9.
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 1-8.
Discussion
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. System.Data.OleDb is the namespace for the .NET Framework data provider for OLE DB.
The .NET Framework data provider for OLE DB connects to an OLE DB data sources through the OleDbConnection object. The OLE DB provider connection string is specified using the ConnectionString property of the OleDbConnection object. This property specifies all settings needed to establish the connection to the data source and matches the OLE DB connection string format with an added Provider key-value pair specifying the OLE DB provider is required.
1.9. Connecting to an ODBC Data Source
Solution
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 1-10.
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 1-9.
Discussion
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:
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 of the OdbcConnection object. This property specifies all settings needed to establish the connection to the data source and matches the ODBC connection string format. You can also specify an ODBC data source name (DSN) or file DSN by setting the ConnectionString attribute "DSN=myDSN" or "FileDSN=myFileDSN“.
The .NET ODBC data provider requires MDAC 2.6 or later with MDAC 2.8 SP1 recommended.
The .NET ODBC data provider requires a reference to the System.Data.Odbc namespace in .NET Framework version 1.1 and later. In version 1.0, the namespace is Microsoft.Data.Odbc. Add a reference to the Microsoft.Data.Odbc assembly for a .NET Framework version 1.0 project.
The .NET ODBC .NET data provider ships with .NET Framework version 1.1 and later. The data provider can be downloaded from http://msdn.microsoft.com/downloads for .NET Framework version 1.0.
1.10. Connecting to a Microsoft Access Database
Solution
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 1-11.
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 1-10.
Discussion
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.
1.11. Connecting to a Password-Protected Microsoft Access Database
Solution
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 1-12.
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 1-11.
Discussion
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.
The OLE DB provider for the Microsoft Access database engine has several provider-specific connection string attributes in addition to those defined by ADO.NET. To open a database secured by a Microsoft Access database password, use the Jet OLEDB:Database Password attribute in the connection string to specify the password. This corresponds to the OLE DB property DBPROP_JETOLEDB_DATABASEPASSWORD.
1.12. Connecting to a Microsoft Access Database from ASP.NET
Problem
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?
Solution
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 1-13.
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 Figure 1-12.
Discussion
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.
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.
The Microsoft Access database 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 TEMP folder. Ensure that the TEMP and TMP environment variables are properly configured.
Configure the Access computer
On the Access computer, the user account that is used to access the database requires Read, Write, Execute, and Change permissions on the database file. The user identity needs Read, Write, Execute, Delete, and Change permissions on the folder containing the database files. The user account requires permissions to access the share that contains the database file and folders.
The user account must be recognized by the Access computer. For a domain user account, add it to the permissions list on both computers. For a user account local to the IIS computer, create a duplicate account on the Access computer with the same name and password.
Grant the user account “Log on Locally and Access this Computer from the Network” permission to access the computer in the local security policy. These permissions are assigned within the Security Settings\Local Policies\User Rights Assignment node in the Local Security Policy tool.
1.13. Connecting to a Microsoft Excel Workbook
Solution
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 1-13.
The Excel 2003 workbook used in this solution is identical.
The C# code in Program.cs in the project ConnectExcel is shown in Example 1-14.
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( ); } } }
The output is shown in Figure 1-14.
Discussion
You can connect to a Microsoft Excel workbook 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 versions of Microsoft Excel workbooks. The Jet OLE DB driver cannot access Microsoft Excel 2007 workbooks.
1.14. Connecting to a Text File
Solution
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 1-15.
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 1-15.
Discussion
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.
The Extended Properties attribute can, in addition to the ISAM version property, specify whether tables include headers as field names in the first row of a range using an HDR attribute.
It is not possible to define all characteristics of a text file through the connection string. You can access files that use nonstandard text delimiters and fixed-width text files by creating a schema.ini file in the same directory as the text file. A text file, Category.txt, is shown in Figure 1-16.
A possible schema.ini file for the Category.txt file is:
[Category.txt] Format=CSVDelimited ColNameHeader=True MaxScanRows=0 Character=OEM Col1=CategoryID Long Width 4 Col2=CategoryName Text Width 15 Col3=Description Text Width 100
The schema.ini file provides this schema information about the data in the text file:
Filename
File format
Field names, widths, and data types
Character set
Special data type conversions
The first entry in the schema.ini file is the text filename enclosed in square brackets. For example:
[Category.txt]
The Format option specifies the text file format. Table 1-2 describes the options.
You can specify the fields in the text file in two ways:
The MaxScanRows option indicates how many rows should be scanned to automatically determine column type. A value of 0 indicates that all rows should be scanned.
The ColN entries specify the name, width, and data type for each column. This entry is required for fixed-length formats and optional for character-delimited formats. The syntax of the ColN entry is:
ColN=columnName dataType[Widthn]
The parameters in the entry are:
columnNameThe name of the column. If the column name contains spaces, it must be enclosed in double quotation marks.
dataTypeThe data type of the column. This value can be
Bit, Byte, Currency, DateTime, Double, Long, Memo, Short, Single,orText.DateTimevalues must be in one of the following formats:dd-mmm-yy, mm-dd-yy, mmm-dd-yy, yyyy-mm-dd,oryyyy-mmm-dd,wheremmis the month number andmmmare the characters specifying the month.WidthnThe literal value
Widthfollowed by the integer value specifying the column width.
The Character option specifies the character set; you can set it to either ANSI or OEM.
1.15. Changing the Database for an Open Connection
Solution
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 1-16.
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 Figure 1-17.
Discussion
The ChangeDatabase() method is defined in the IDbConnection interface that represents a connection to a data source and is implemented by .NET data providers. The ChangeDatabase() method changes 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 InvalidOperationException is raised. A provider-specific exception (e.g., SqlException for SQL Server data provider) is raised if the database cannot be changed for any reason.
The Database property of the Connection object is updated dynamically and returns the current database for an open connection or the name of a database that will be used by a closed connection when it is opened.
When the Connection is closed after ChangeDatabase() is called, the database is reset to that specified in the original connection string.
1.16. Setting Connection Pooling Options
Solution
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 1-17.
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());
}
}
}The output is shown in Figure 1-18.
Discussion
The following subsections describe how to control connection pooling for SQL Server, Oracle, OLE DB, and ODBC .NET data providers.
SQL Server
The connection string attributes that control connection pooling for the SQL Server .NET data provider are described in Table 1-3.
Description | |
| Length of time in seconds after creation after which a connection is destroyed. The default is 0, indicating that connection will have the maximum timeout. |
| Specifies whether the connection is reset when removed from the pool. The default is |
| Specifies whether the connection is automatically enlisted in the current transaction context of the creation thread if that transaction context exists. The default is |
| Length of time in seconds that a connection can remain idle in a connection pool before being removed. |
| Maximum number of connections allowed in the pool. The default is |
| Minimum number of connections maintained in the pool. The default is |
| Specifies whether the connection is drawn from a pool or, when necessary, created and added to a pool. The default is |
Oracle
The connection string attributes that control connection pooling for the Oracle .NET data provider are described in Table 1-4.
OLE DB
The OLE DB .NET data provider uses resource-pooling support provided by the OLE DB Service component. You can override the default OLE DB provider services by specifying a value for the OLE DB Services attribute in the connection string. For more information, see Recipe 1.17, next.
OLE DB Resource pooling configuration is controlled using registry entries. There is no user interface to configure these entries—the registry must be edited directly. The registry entries are identified by the <Provider'sCLSID>. CLSID values for some Microsoft OLE DB providers are:
SQLOLEDB (SQL Server):
HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}Microsoft.Jet.OLEDB.4.0 (Jet):
HKEY_CLASSES_ROOT\CLSID\{dee35070-506b-11cf-b1aa-00aa00b8de95}MSDAORA (Oracle):
HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}MSDASQL (OLE DB Provider for ODBC):
HKEY_CLASSES_ROOT\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}
An OLE DB provider configuration option set by registry entries is:
HKEY_CLASSES_ROOT\CLSID\<Provider's CLSID>\SPTimeout
The session pooling timeout is the number of seconds that an unused session remains in the pool before timing out and being closed. This is a DWORD value with a default of 60 if the registry entry is not specified.
The following registry entries are global to all providers:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Session Pooling\Retry WaitThe amount of time that the service component will wait until attempting to contact the server again in the event of a failed connection attempt. This is a
DWORDvalue with a default of64if no registry value is present.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Session Pooling\ExpBackOffDetermines the factor by which the service components will wait between reconnect attempts in the event of a failed connection attempt. This is a
DWORDvalue with a default of2if no registry value is present.HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}A
DWORDvalue that specifies the maximum lifetime in seconds of a pooled connection. The default is600. The CLSID is for the MSDAINITIALIZE component, which is the OLE DB service component manager that is used to parse OLE DB connection strings and initialize the appropriate provider.
ODBC
The ODBC .NET data provider uses the connection pooling support provided by the ODBC Driver Manager (DM). Connection pooling is supported by version 3.0 or later of the ODBC DM; the version of the ODBC driver does not matter.
The following two registry settings control ODBC connection pooling:
Wait RetryThe time in seconds that that the pool is blocked when the server is not responding. This setting affects all applications using the ODBC driver. The registry key specifies a REG_SZ value:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\<Driver_Name>\CPTimeout
CPTimeoutThe time in seconds that unused connections remain in the pool. This setting affects all ODBC drivers on the system. The registry key specifies a REG_SZ value:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Connection Pooling
You can control ODBC connection pooling in three ways:
Using the
ODBC Data Source Administratorto enable or disable pooling for the entire driver, and to control theCPTimeoutandWait Retrysettings.Using the ODBC API to control pooling options from an ODBC application. For more information about the ODBC API, see the ODBC Programmer’s Reference in the MSDN Library.
Editing the registry settings described previously.
1.17. Taking Advantage of Connection Pooling
Problem
You need to understand connection pooling and make sure that your applications use it.
Solution
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.
Discussion
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.18, 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.
Warning
The DataAdapter automatically opens and closes a Connection as required if it is not already open when a method such as Fill(), FillSchema(),or Update() is called. The Connection must be explicitly closed if it is already open prior to the DataAdapter operation.
The following subsections detail connection pooling for specific .NET Framework data providers.
SQL Server and Oracle
The .NET data providers for SQL Server and Oracle provide efficient, transaction-aware support for connection pooling. Pools are created for each process and not destroyed until the process ends. Connection pooling is enabled by default.
Controlling SQL Server and Oracle .NET data provider connection pooling with connection string attribute/value pairs is discussed in Recipe 1.16.
OLE DB
The OLE DB .NET data provider pools connections by using resource pooling provided by the OLE DB core components.
The default OLE DB services that are enabled for a provider are specified by the value for the registry HKEY_CLASSES_ROOT\CLSID\<Provider'sCLSID>\OLE_DBSERVICES DWORD value. Table 1-5 describes the alternatives.
OLE_DBSERVICES value | Description |
| All services (default). |
| All services except pooling. |
| All services except pooling and automatic transaction enlistment. |
| All services except Client Cursor Engine. |
| All services except pooling, automatic transaction enlistment, and Client Cursor Engine. |
| Pooling and automatic transaction enlistment, session-level aggregation only. |
| No services. |
| No aggregation. All services are disabled. |
You can override the default OLE DB provider services by specifying a value for the OLE DBServices attribute in the connection string. Table 1-6 describes possible values.
Default services enabled | |
– | All services (default) |
– | All services except pooling and automatic transaction enlistment. |
– | All services except Client Cursor Engine. |
– | All services except pooling, automatic transaction enlistment, and Client Cursor Engine. |
| Pooling and automatic transaction enlistment, session-level aggregation only. |
| No services. |
The following three configurable settings control OLE DB connection pooling:
SPTimeoutThe length of time in seconds that an unused connection remains in the pool before it is released. This can be configured for each provider and defaults to 60 seconds.
Retry WaitThe length of time in seconds before an attempt to acquire a connection is reattempted when the server is not responding. This is global to all providers and defaults to 64 seconds.
ExpBackOffThe factor by which the retry wait time is increased when a connection attempt fails before reattempting the connection. This is global to all providers and defaults to a factor of 2.
OLE DB connection pooling is enabled by default; you can control it in three different ways:
Specify a value for the
OLE DB Servicesattribute in the connection string.Edit the registry to enable or disable pooling for an individual provider or globally by changing registry values. For more information, see Recipe 1.16.
Use the OLE DB API from an application to enable or disable connection pooling. The
SPTimeoutandRetry Waitcan be configured programmatically only by manipulating the registry entries. For more information about the OLE DB API, see the OLE DB Programmer’s Reference in MSDN Library.
ODBC
The ODBC .NET data provider pools connections by using the connection pooling provided by the ODBC Driver Manager (DM). Pooling parameters for an ODBC driver affect all applications that use that driver, unless changed from within a native ODBC application.
The following two configurable settings control ODBC connection pooling:
CPTimeoutThe length of time in seconds that an unused connection remains in the pool before it is released.
Wait RetryThe length of time before an attempt to acquire a connection is reattempted when the server is not responding.
Connection pooling is enabled by default. You can enable, disable, and configure it in three ways:
Use the
ODBC Data Source Administrator, introduced with ODBC 3.5 (MDAC 1.5), to enable or disable pooling for the entire driver and to control theCPTimeoutandWait Retrysettings.Use the ODBC API from an application to limit the scope of pooling to the environment handler or to the driver, and to configure other pooling options. For more information about the ODBC API, see the ODBC Programmer’s Reference in the MSDN Library.
Edit the registry. For more information, see Recipe 1.16.
1.18. Using Transactions with Pooled Connections
Problem
You want to use connection pooling with transactions in your .NET application to maximize performance.
Solution
Use connection pooling with transactions.
Discussion
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.17.
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.
1.19. Displaying a Connection Property Dialog Box
Problem
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.
Solution
Follow these steps to create the solution:
Create a C# Windows Forms application,
DisplayConnectionPropertyDialog.Add a
PropertyGridcontrol. Accept all default properties including the namepropertyGrid1. Add an event handler namedonPropertyValueChanged()for thePropertyValueChangedevent.Add a
Labelcontrol below thePropertyGridcontrol. Set itsTextproperty toConnection String:.Add a
TextBoxcontrol below theLabelcontrol. Name itconnectString. Set theReadOnlyproperty totrue. Set theMultilineproperty totrue. Size the control to accommodate about four lines of text.The C# code in Form1.cs in the project
DisplayConnectionPropertyDialogis shown in Example 1-18. You need to add the highlighted code.
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 Figure 1-19.
Discussion
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.
1.20. Displaying the Data Link Properties Dialog Box
Problem
You want to display the Data Link Properties dialog box from an application so that users can create their own database connections.
Solution
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:
The C# code in Form1.cs shown in the project DisplayDataLinkPropertiesDialog is shown in Example 1-19.
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 Figure 1-20.
The output is shown in Figure 1-21.
1.21. Monitoring Connections
Problem
You want to monitor the opening and closing of connections and the number of connections in the connection pool while an application is running.
Solution
Use the Windows Performance Monitor and the SQL Profiler to monitor connections and connection pooling. See Recipe 1.17 for more information on connection pooling.
Discussion
The following subsections discuss monitoring connection pooling for SQL Server and ODBC .NET Framework data providers.
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.
SQL Server Profiler. To use the SQL Server Profiler to monitor connection pooling:
Start the Profiler using one of the following methods:
From Windows desktop: Start → All Programs → Microsoft SQL Server → Performance Tools → SQL Server Profiler.
From SQL Server Management Studio: Tools → SQL Server Profiler.
When the SQL Server Profiler appears, select File → New Trace.
Supply connection details and click Connect. The Trace Properties dialog box will appear.
Select the Events Selection tab of the Trace Properties dialog box.
In the Selected Events list box, ensure that the
Audit LoginandAudit Logoutevents appear beneath the Security Audit node. Remove all other events from the list. Click the Run button to start the trace.The new Profiler window will display a table containing
Audit Loginevents when connections are established andAudit Logoutevents when connections are closed.Figure 1-22 shows the result of the trace after a database connection is opened and closed.
Windows Performance Monitor. To use the Windows Performance Monitor to monitor connection pooling:
Start Performance Monitor by selecting Start → All Programs → Administrative Tools → Performance.
Right-click the graph and select Properties from the context menu to open the System Monitor Properties dialog. Select the Graph tab and set the “Vertical scale maximum” to 2. This will make the results easier to see. Click OK to close the dialog.
Add performance counters to monitor connection pooling with one of the following methods:
Right-click the graph and select Add Counters from the pop-up menu.
Click the Add button above the graph.
Both of these actions open the Add Counters dialog shown in Figure 1-23.
In the “Performance object” drop-down list, select
SQLServer:General Statistics.Performance counters can help tune connection pooling and troubleshoot pooling problems. To get an explanation for a counter, select the counter and click the Explain button.Select the
User Connectionscounter from the list and click the Add button. Click the Close button.Figure 1-24 shows the Performance Monitor as a user connects to and disconnects from SQL Server.
ODBC
To enable ODBC performance monitoring:
Open the ODBC Data Source Administrator by selecting Start → All Programs → Administrative Tools → Data Sources (ODBC) as shown in Figure 1-25.
Select the Connection Pooling tab.
Ensure that the PerfMon Enable checkbox is checked. Click OK to close the dialog.
Start Performance Monitor by selecting Start → All Programs → Administrative Tools → Performance.
Add performance counters to monitor connection pooling with one of the following methods:
Right-click the graph and select Add Counters from the pop-up menu.
Click the Add button above the graph.
Both of these actions open the Add Counters dialog shown in Figure 1-26.
In the “Performance object” drop-down list, select ODBC Connection Pooling. Table 1-7 describes the available ODBC connection pooling counters.
Counter | Description |
| Number of connections currently used by applications |
| Number of connections in the pool available for requests |
| Number of real connections per second |
| Number of connections from the pool per second |
| Hard Number of real disconnects per second |
|
Get ADO.NET 3.5 Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
