September 2008 in ADO.NET

Creating a Strongly Typed DataSet

A strongly typed DataSet is a collection of classes that inherit from and extend the DataSet, DataTable, and DataRow classes, and provide additional properties, methods, and events based on the DataSet schema. You can use all of the functionality in classes from which the strongly typed classes inherit in the same way as with untyped classes.

A strongly typed DataSet class contains, in addition to a single class extending the DataSet class, three classes for each table in the DataSet extending each of the DataTable, DataRow, and DataRowChangeEvent classes. This recipe describes these classes and discusses their commonly used methods and properties.

There is a class named TableNameDataTable for each table in the strongly typed DataSet. It has the base class DataTable. Below Table lists commonly used methods of this class specific to the strongly typed DataSet.

Table 1. TableNameDataTable methods

Method	Description
`AddTableNameRow( )`	Adds a row to the table. The method has two overloads: one takes a `TableNameRow` object as the argument, while the other takes a set of arguments containing the column values.
`FindByPrimaryKeyField1` ... `PrimaryKeyFieldN( )`	Takes `N` arguments which are the values of the primary key fields of the row to find. Returns a `TableNameRow` object, if found.
`NewTableNameRow( )`	Takes no arguments and returns a new `TableNameRow` object with the same schema as the table to be used for adding new rows to the table in the strongly typed `DataSet`.

There is a class named TableNameRow for each table in the strongly typed DataSet. It has the base class DataRow and represents a row of data in the below table which lists commonly used properties and methods of this class specific to the strongly typed DataSet.

Table 2. TableNameRow class properties and methods

Property/method	Description
Typed Accessor	Sets and get the value of a column. The typed accessor is exposed as a property having the same name as the underlying data column.
`IsColumnNameNull( )`	Returns a Boolean value indicating whether the field contains a null value.
`SetColumnNameNull( )`	Sets the value of the underlying field to a null value.
`GetChildTableNameRows( )`	Returns the rows for the table as an array of `ChildTableNameRow` objects.
`ParentTableNameRow( )`	Returns the parent row as an object of type `ParentTableNameRow`.

There is a class named TableNameRowChangeEvent for each table in the strongly typed DataSet. It has the base class EventArgs. Table 3 describes the properties of this class.

Table 3. TableNameRowChangeEvent properties

Property	Description
`Action`	A value from the `System.Data.DataRowAction` enumeration that describes the action performed on a row that caused the event to be raised.
`Row`	The `TableNameRow` object for which the event was raised.

A strongly typed DataSet has some advantages over using an untyped DataSet:

The schema information is contained within the strongly typed DataSet resulting in a performance over retrieving schema information at runtime. The schema of an untyped DataSet can also be defined programmatically, as discussed here, resulting in similar performance.
Programming is more intuitive and code is easier to maintain. Table, column, and other object names are accessed through properties having names based on the underlying data source object names rather than by using index or delimited string arguments. The Visual Studio .NET IDE provides autocomplete functionality for strongly typed DataSet names.
Type mismatch errors and errors resulting from misspelled or out of bounds arguments used with DataSet objects can be detected during compilation, rather than at runtime.

The disadvantages of a strongly typed DataSet object include:

Additional overhead when executing. If strongly typed functionality is not required, performance is better with an untyped DataSet rather than with a typed DataSet.
A strongly typed DataSet must be regenerated when the structure of the underlying data source changes. Applications using these strongly typed DataSet objects will need to be rebuilt with a reference to the new strongly typed DataSet. With an untyped DataSet, as long as the new schema is a superset of the old schema, existing clients can simply ignore any new data and do not need to be recompiled.

Four ways to generate a typed DataSet class are described in the following subsections.

Using the Visual Studio .NET IDE to generate a typed DataSet

The first and easiest method uses Visual Studio .NET following these steps:

Drop a DataAdapter object from the Data tab in the Visual Studio .NET Toolbox onto a design surface such as a form or a component. The Data Adapter Configuration Wizard will appear.
Press Next to continue to the Choose Your Data Connection dialog.
Select an existing connection or create a new one as required. Press Next to continue to the Choose a Query Type dialog.
Select the Use SQL statements radio button and press Next to continue to the Generate the SQL Statements dialog.
Press the Advanced Options... button.
Uncheck all three check boxes on the Advanced SQL Generation Options dialog and press OK.
Press the Query Builder... button.
Select only one table on the Add Table dialog and press Add. Press Close.
Check the columns from the table to include or (All Columns) and press OK.
Press Next to continue to the View Wizard Results dialog.
Press Finish to complete the wizard.
Repeat steps 1-11 for the other tables that you want to have included in the strongly typed DataSet.
Right-click on the design surface and select Generate DataSet.
Provide a name for the strongly typed DataSet, select the tables to be included, and press OK to generate the new strongly typed DataSet.
To relate tables in the new strongly typed DataSet, open the XSD file for the new DataSet in the Solution Explorer window.
Right-click on the child table in XSD schema designer, select Add New Relation . . . from the shortcut menu, and complete the dialog. Repeat this step to create all required relationships.
Instances of the strongly typed DataSet can now be created programmatically either by using the new keyword in C# or the New keyword in Visual Basic .NET or by dragging the DataSet object from the Data tab in the Visual Studio .NET Toolbox onto a design surface such as a component or form.

Using the TypedDataSetGenerator class to generate a typed DataSet

The second technique is to derive a class from the TypedDataSetGenerator class. The static Generate( ) method of the TypedDataSetGenerator class is used to create a strongly typed DataSet. The prototype of the method is:

public static void Generate(DataSet dataSet, CodeNamespace codeNamespace,
ICodeGenerator codeGenerator);

The arguments of the method are:

DataSet: The DataSet used to specify the schema for the typed DataSet.
codeNamespace: The target namespace for the typed DataSet.
codeGenerator: A class capable of dynamically rendering source code in a specific language and used to create the typed DataSet.

Using an XSD schema file to generate a typed DataSet

The other two methods require an XSD schema file. You can generate this file in a number of ways: using the Visual Studio .NET tools, third-party tools, or the DataSet WriteXmlSchema( ) method. You can create a strongly typed DataSet from the XSD schema file using Visual Studio .NET or using the XML Schema Definition Tool.

To create a strongly typed DataSet from the XSD schema using Visual Studio .NET, follow these steps:

Right-click on the project in the Solution Explorer window, choose Add/Existing Item... from the shortcut menu, and select the XSD file to add it to the project.
Select the XSD schema file from the Solution Explorer window and open it in the designer window.
Right-click on the designer window and select Generate DataSet.
Instances of the strongly typed DataSet can now be created programmatically by using the new keyword in C# or the New keyword in Visual Basic .NET or by dragging the DataSet object from the Data tab in the Visual Studio .NET Toolbox onto a design surface such as a component or form.

The second way to create a strongly typed DataSet from an XSD schema is to use the XML Schema Definition Tool (XSD.EXE) found in the .NET Framework SDK bin directory. Follow these steps:

Generate the strongly typed DataSet class file from the XSD schema file by issuing the following command from the command prompt:

xsd mySchemaFile.xsd /d /l:CS

The /d switch specifies that source code for a strongly typed DataSet should be created.

The /l:CS switch specifies that the utility should use the C# language, which is the default if not specified. For VB.NET, use the switch /l:VB.

The XML Schema Definition Tool offers other options. For more information, see the .NET Framework SDK documentation or the MSDN Library.

The class file that is generated for the strongly typed DataSet is named using the DataSet name in the XSD schema and has an extension corresponding to the programming language: .cs for C# and .vb for VB.NET. The strongly typed DataSet class can now be added to a project.

Building a DataSet Programmatically

The following example shows how to build a complex DataSet programmatically, including how to build and add tables, columns, primary key constraints, relations, and column mappings. Use this as a template for building your own DataSet.

The sample code creates a DataSet. A DataTable object is created representing the Orders table in Northwind. Columns are added, including the auto-increment primary key, to the table. The table is added to the DataSet. The process is repeated for a DataTable representing the Order Details table in Northwind. A DataRelation is created relating the two tables. Finally, the tables are filled with data from Northwind.

The C# code is shown below.

File: BuildDataSetProgramaticallyForm.cs


// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

//  . . . 

// Create the DataSet.
DataSet ds = new DataSet("MyDataSet");

// Build the Orders (parent) table.
DataTable parentTable = new DataTable("Orders");

DataColumnCollection cols = parentTable.Columns;
// Add the identity field.
DataColumn column = cols.Add("OrderID", typeof(System.Int32));            
column.AutoIncrement = true;
column.AutoIncrementSeed = -1;
column.AutoIncrementStep = -1;
// Add the other fields.
cols.Add("CustomerID", typeof(System.String)).MaxLength = 5;  
cols.Add("EmployeeID", typeof(System.Int32));  
cols.Add("OrderDate", typeof(System.DateTime));  
cols.Add("RequiredDate", typeof(System.DateTime));  
cols.Add("ShippedDate", typeof(System.DateTime));  
cols.Add("ShipVia", typeof(System.Int32));  
cols.Add("Freight", typeof(System.Decimal));  
cols.Add("ShipName", typeof(System.String)).MaxLength = 40;  
cols.Add("ShipAddress", typeof(System.String)).MaxLength = 60;  
cols.Add("ShipCity", typeof(System.String)).MaxLength = 15;  
cols.Add("ShipRegion", typeof(System.String)).MaxLength = 15;  
cols.Add("ShipPostalCode", typeof(System.String)).MaxLength = 10;  
cols.Add("ShipCountry", typeof(System.String)).MaxLength = 15;  
// Set the primary key.
parentTable.PrimaryKey = new DataColumn[] {cols["OrderID"]};
// Add the Orders table to the DataSet.
ds.Tables.Add(parentTable);

// Build the Order Details (child) table.
DataTable childTable = new DataTable("Order Details");

cols = childTable.Columns;
// Add the PK fields.
cols.Add("OrderID", typeof(System.Int32)).AllowDBNull = false;  
cols.Add("ProductID", typeof(System.Int32)).AllowDBNull = false;  
// Add the other fields.
cols.Add("UnitPrice", typeof(System.Decimal)).AllowDBNull = false;  
cols.Add("Quantity", typeof(System.Int16)).AllowDBNull = false;  
cols.Add("Discount", typeof(System.Single)).AllowDBNull = false;  
// Set the primary key.
childTable.PrimaryKey = new DataColumn[]
    {
        cols["OrderID"],
        cols["ProductID"]
    };
// Add the Order Details table to the DataSet.
ds.Tables.Add(childTable);

// Add the relationship between parent and child tables.
ds.Relations.Add("Order_OrderDetails_Relation",
    parentTable.Columns["OrderID"], childTable.Columns["OrderID"], true);

// Fill the tables from the data source.
SqlDataAdapter da;
String sqlText;

sqlText = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, " +
    "RequiredDate, ShippedDate, ShipVia, Freight, ShipName, " +
    "ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry " +
    "FROM Orders";
da = new SqlDataAdapter(sqlText,
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.Fill(parentTable);

sqlText = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " +
    "FROM [Order Details]";
da = new SqlDataAdapter(sqlText,
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.Fill(childTable);

Discussion

The steps to build a complex DataSet programmatically, as shown in the code for the solution, are:

Design the DataSet identifying the tables, columns, indexes, constraints, and data relations that need to be created.

Create a new DataSet, naming it in the constructor.

Create a new DataTable, naming it in the constructor.

Add a column to the ColumnCollection of the table using the Add( ) method exposed by the Columns property of the DataTable specifying the name and data type of the column. If the column is a character-type column, define its maximum length. If the column is an auto-increment column, set the AutoIncrement property to true and set both the AutoIncrementSeed and AutoIncrementStep properties of the column to -1. Repeat step 4 for each column in the table.

Define the primary key for the table by setting the PrimaryKey property of the DataTable to the array of primary key DataColumn objects.

Add the new table to the DataSet using the Add( ) method of the DataTableCollection exposed by the Tables property of the DataSet.

Repeat steps 3-6 for each table in the DataSet.

Create a data relationship between two related tables in the DataSet by using the Add( ) method of the DataRelationCollection exposed by the Relations property of the DataSet. Specify the relationship name, the related columns, and whether constraints are to be created when calling the Add( ) method. Repeat step 8 for each data relationship in the DataSet.

The steps continue, demonstrating how to fill the new DataSet:

To fill the DataSet with data from the data source, create a DataAdapter defining the SQL select statement and the connection string in the constructor.

Use the Fill( ) method of the DataSet to fill the table. Specify the table name to be filled in the second argument of the Fill( ) method.

Repeat steps 9 and 10 for each table to be filled.

Retrieving Hierarchical Data into a DataSet

There are several techniques you can use to load parent and child data into a DataSet.

The sample code contains three event handlers:

Form.Load: Sets up the sample by creating a DataSet with table schemas for both the Orders table and the Order Details table from Northwind and a DataRelation object relating these two tables. The default view of the parent table, Orders, is bound to a data grid on the form.
Load DataSet Button.Click: Starts by clearing the data from the DataSet and refreshing the data grid. DataAdapter objects are created for both the parent and the child table. The Orders and Order Details are then filled using data adapters in the sequence specified and enforcing constraints during the load as specified by the user.

The C# code is shown in the below example.

Example- File: HierarchicalDataSetForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

private DataSet ds;

//  . . . 

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

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

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

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

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

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

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

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

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

ds.EnforceConstraints = true;
}

Discussion

By default, a DataRelation is created with constraints as in the example; however, an overloaded constructor can override this behavior if necessary. If constraints are created, it is important that each record in the child table refers to a valid parent record, otherwise a ConstraintException is raised. Two techniques can be used to load parent and related child data without error into a DataSet with a schema that includes data relations defined:

Load data from the parent tables before loading data from the child table. This ensures that each record in the child table refers to a valid parent record.
The EnforceConstraints property of the DataSet indicates whether constraint rules are followed when data is added to or modified in the DataSet. Turn constraints off by setting the EnforceConstraints property to false prior to loading the data and back to true once the data is loaded. With this approach the order in which the data is loaded is not important. If one or more constraints cannot be enforced when EnforceConstraints is set back to true, a ConstraintException will be raised and EnforceConstraints stay set to false.

Connecting to a Text File

Use the OLE DB Jet provider to access data in a text file.

The sample code creates an OleDbDataAdapter that uses the Jet OLE DB provider to load the contents of the text file Categories.txt, shown here, into a DataTable and displays the contents in a data grid on the form.

Example-. File: Categories.txt

"CategoryID","CategoryName","Description"
1,"Beverages","Soft drinks, coffees, teas, beers, and ales"
2,"Condiments","Sweet and savory sauces, relishes, spreads, and seasonings"
3,"Confections","Desserts, candies, and sweet breads"
4,"Dairy Products","Cheeses"
5,"Grains/Cereals","Breads, crackers, pasta, and cereal"
6,"Meat/Poultry","Prepared meats"
7,"Produce","Dried fruit and bean curd"
8,"Seafood","Seaweed and fish"

The C# code is shown here.

Example- File: ConnectTextFileForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

//  . . . 

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

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

// Bind the default view of the table to the grid.
categoriesDataGrid.DataSource = dt.DefaultView;

Discussion

The Jet OLE DB provider can read records from and insert records into a text file data source. The Jet database engine can access other database file formats through Indexed Sequential Access Method (ISAM) drivers specified in the Extended Properties attribute of the connection. Text files are supported with the text source database type as shown in the following example:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyTextFileDirectory;
Extended Properties="text;HDR=yes;FMT=Delimited";

The Extended Properties attribute can, in addition to the ISAM version property, specify whether or not tables include headers as field names in the first row of a range using an HDR attribute.

It is not possible to define all characteristics of a text file through the connection string. You can access files that use non-standard text delimiters and fixed-width text files by creating a schema.ini file in the same directory as the text file. As an example, a possible schema.ini file for the Categories.txt file used in this solution is:

[Categories.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 the following 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:

[Categories.txt]

The Format option specifies the text file format.Below Table describes the different options.

Table 1. Schema.ini format options

Format	Description
CSV Delimited	Fields are delimited with commas: Format=CSVDelimited This is the default value.
Custom Delimited	Fields are delimited with a custom character. You can use any single character except the double quotation mark (") as a delimiter: Format=Delimited(customCharacter)
Fixed Length	Fields are fixed length: Format=FixedLength If the `ColumnNameHeader` option is `True`, the first line containing the column names must be comma-delimited.
Tab Delimited	Fields are delimited with tabs: Format=TabDelimited

You can specify the fields in the text file in two ways:

Include the field names in the first row of the text file and set the ColNameHeader option to True.
Identify each column using the format ColN (where N is the one-based column number) and specify the name, width, and data type for each column.

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 [Width n]

The parameters in the entry are:

columnName: The name of the column. If the column name contains spaces, it must be enclosed in double quotation marks.
dataType: The data type of the column. This value can be Bit, Byte, Currency, DateTime, Double, Long, Memo, Short, Single, or Text.
DateTime values must be in one of the following formats: dd-mmm-yy, mm-dd-yy, mmm-dd-yy, yyyy-mm-dd, or yyyy-mmm-dd, where mm is the month number and mmm are the characters specifying the month.
Width n: The literal value Width followed by the integer value specifying the column width.

The Character option specifies the character set; you can set it to either ANSI or OEM.

Technorati : Connecting Text File in ADO.NET

Changing the Database for an Open Connection

Use the ChangeDatabase( ) method to change the database for a connection.

The sample code creates a Connection to the Northwind database using the SQL Server .NET data provider. The connection is changed to use the pubs database. Finally the connection is closed. The Database property of the SqlConnection object is displayed throughout the sample for the different connection states.

The C# code is shown here.

Example 1-12. File: ChangeDatabaseForm.cs


// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;

//  . . . 

StringBuilder result = new StringBuilder( );

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

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

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

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

resultTextBox.Text = result.ToString( );

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 for relational databases including those for SQL Server, Oracle, and OLE DB. The ChangeDatabase( ) method is used to change the current database for an open connection. It takes a single parameter that specifies the name of the database to use in place of the current database. The name of the database must be valid or an ArgumentException will be raised. If the connection is not open when the method is called, an InvalidOperationException is raised. A provider-specific exception 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.

Technorati : Changing DB in ADO.NET

Using Transactions with Pooled Connections

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 here.

When a thread associated with a particular transaction context requests a connection, one from the appropriate pool enlisted with that transaction is automatically returned.

When a connection is closed it is returned to the appropriate subdivision in the connection pool based on the transaction context. This allows a connection to be closed without generating an error even if a distributed transaction is still pending. The transaction can committed or aborted later.

Technorati : Using Transactions

Setting Connection Pooling Options

Use the connection string to control connection pooling for the SQL Server, OLE DB .NET, Oracle, or ODBC.NET data provider.

The sample code contains a method and four event handlers:

Form.Load: Creates a Connection, attaches an event handler to its StateChange event, and sets default properties for controls on the form that are used to specify connection properties. The UpdateConnection( ) method is called to dynamically construct a connection string from the specified properties.
UpdateConnectionString( ): This method dynamically constructs a connection string from the connection string properties specified by the user in text boxes on the form. This method is called to update the connection string when the user changes the value of any of the controls used to specify connection string properties.
Open Button.Click: Opens the Connection that is based on the connection string constructed in the UpdateConnectionString( ) method.
Close Button.Click: Closes the connection string.
Connection.StateChange: Displays original and current state information about the connection when its state changes.

The C# code is shown here.

Example-File: ConnectionPoolingOptionsForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

private SqlConnection conn;

//  . . . 

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

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

UpdateConnectionString( );
}

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

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

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

private void conn_StateChange(object sender, StateChangeEventArgs e)
{
connectionStateTextBox.Text =
"Connection.StateChange event occurred" +
Environment.NewLine +
"OriginalState = " + e.OriginalState.ToString( ) +
Environment.NewLine +
"CurrentState = " + e.CurrentState.ToString( );
}

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 below.

Table1 . SQL Server connection string pooling attributes

Attribute	Description
`Connection Lifetime`	Length of time in seconds after creation after which a connection is destroyed. The default is 0 indicating that connection will have the maximum time-out.
`Connection Reset`	Specifies whether the connection is reset when removed from the pool. The default is `true`.
`Enlist`	Specifies whether the connection is automatically enlisted in the current transaction context of the creation thread if that transaction context exists. The default is `true`.
`Max Pool Size`	Maximum number of connections allowed in the pool. The default is 100.
`Min Pool Size`	Minimum number of connections maintained in the pool. The default is 0.
`Pooling`	Specifies whether the connection is drawn from a pool or when necessary created and added to a pool. The default is `true`.

Oracle

The connection string attributes that control connection pooling for the Oracle .NET data provider are described in Table2.

Table2. Oracle connection string pooling attributes

Attribute	Description
`Connection Lifetime`	Length of time in seconds after creation after which a connection is destroyed. The default is 0 indicating that connection will have the maximum time-out.
`Enlist`	Specifies whether the connection is automatically enlisted in the current transaction context of the creation thread if that transaction context exists. The default is `true`.
`Max Pool Size`	Maximum number of connections allowed in the pool. The default is 100.
`Min Pool Size`	Minimum number of connections maintained in the pool. The default is 0.
`Pooling`	Specifies whether the connection is drawn from a pool or when necessary created and added to a pool. The default is `true`.

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. 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's CLSID>. 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}

Some OLE DB provider configuration options set by registry entries are:

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 Wait: The 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 DWORD value with a default of 64 if no registry value is present.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Session Pooling\ExpBackOff: Determines the factor by which the service components will wait between reconnect attempts in the event of a failed connection attempt. This is a DWORD value with a default of 2 if no registry value is present.
HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}: A DWORD value that specifies the maximum lifetime in seconds of a pooled connection. The default is 600. 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 Retry: The 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

CPTimeout: The 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 Administrator to enable or disable pooling for the entire driver, and to control the CPTimeout and Wait Retry settings
Editing the registry settings described above.
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.

Technorati : Collection Pooling in ADO.NET

Taking Advantage of Connection Pooling in ADO.NET

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. 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.

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.

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's CLSID>\OLE_DBSERVICES DWORD value. Table1 describes the alternatives.

Table 1. OLE DB services enabled values

OLE_DBSERVICES value	Description
`0xffffffff`	All services (default).
`0xfffffffe`	All services except Pooling and AutoEnlistment.
`0xfffffffb`	All services except Client Cursor.
`0xfffffff0`	All services except Pooling, AutoEnlistment, and Client Cursor.
`0x00000000`	No services.
`missing value`	No aggregation. All services are disabled.

You can override the default OLE DB provider services by specifying a value for the OLE DB Services attribute in the connection string. Below Table describes possible values.

Table 2. OLE DB services connection string values

OLE DB Services attribute value	Default services enabled
`-1`	All services (default)
`-2`	All services except Pooling and AutoEnlistment
`-5`	All services except Client Cursor
`-7`	All services except Pooling, AutoEnlistment, and Client Cursor
`0`	No services

The following three configurable settings control OLE DB connection pooling:

SPTimeout: The 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 Wait: The 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.
ExpBackOff: The 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 Services attribute in the connection string.
Edit the registry to enable or disable pooling for an individual provider or globally by changing registry values.
Use the OLE DB API (Application Programming Interface) from an application to enable or disable connection pooling. The SPTimeout and Retry Wait can 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:

CPTimeout: The length of time in seconds that an unused connection remains in the pool before it is released.
Wait Retry: The 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 the CPTimeout and Wait Retry settings.
Edit the registry.
Use the ODBC API from an ODBC 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.

Technorati : Connection Pooling in ADO.NET

Monitoring Connections in ADO.NET

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.

1. 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 --] Profiler.
- From SQL Enterprise Manager: Tools --] SQL Profiler.
When the SQL Server Profiler appears, select File --] New --] Trace.
Supply connection details and click OK. The Trace Properties dialog box will appear.
Select the Events tab of the Trace Properties dialog box.
In the Selected Events list box, ensure that the Audit Login and Audit Logout events appear beneath the Security Audit node. Remove all other events from the list. Click the Run button to start the trace.
The new Profiler window will display a table containing Audit Login events when connections are established and Audit Logout events when connections are closed.

2. Windows Performance Monitor

To use the Windows Performance Monitor to monitor connection pooling:

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 popup menu.
- Click the add button above the graph.
In the Performance object drop down list, select ".NET CLR Data."
The SQL Server .NET data provider adds performance counters that can tune connection pooling and troubleshoot pooling problems. Below table describes the counters.

Table . SQL Server .NET provider performance counters

Counter	Description
`SqlClient: Current # of pooled and nonpooled connections`	Current number of connections, both pooled and non-pooled
`SqlClient: Current # pooled connections`	Current number of pooled connections
`SqlClient: Current # connection pools`	Current number of connection pools
`SqlClient: Peak # pooled connections`	The largest number of connections in all pools since the process started
`SqlClient: Total # failed connects`	The total number of attempts to open a connection that have failed for any reason

Select the counters to monitor from the list box and click the Add button. Click the Close button.

ODBC

To enable ODBC performance monitoring:

Open the ODBC Data Source Administrator in Control Panel --] Administrative Tools.
Select the Connection Pooling tab.
Ensure that the PerfMon Enable checkbox is checked.
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 popup menu.
- Click the add button above the graph.
In the Performance object drop down list, select ODBC Connection Pooling. Table2 describes the ODBC Connection Pooling counters.

Table2. ODBC connection pooling counters

Counter	Description
`Connections Currently Active`	Number of connections currently used by applications
`Connections Currently Free`	Number of connections in the pool available for requests
`Connections/Sec Hard`	Number of real connections per second
`Connections/Sec Soft`	Number of connections server from the pool per second
`Disconnections/Sec Hard`	Number of real disconnects per second
`Disconnections/Sec Soft`	Number of disconnects from the pool per second

Using the Data Link Properties Dialog Box

Use COM interop with the OLE DB Service Component to display the Data Link Properties dialog box.

You'll need a reference to the Primary Interop Assembly (PIA) for ADO provided in the file ADODB.DLL; select adodb from the .NET tab in Visual Studio .NET's Add Reference Dialog. You'll also need a reference to the Microsoft OLE DB Service Component 1.0 Type Library from the COM tab in Visual Studio .NET's Add Reference Dialog.

The sample code contains a single event handler:

Data Link Dialog Button.Click: Creates and displays a Data Link Properties dialog box using the Microsoft OLE DB Service Component through COM interop.

The C# code is shown here.

Example- File: DataLinkDialogForm.cs

// Namespaces, variables, and constants
using System;

//  . . . 

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

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

connectionStringTextBox.Text = conn.ConnectionString;
}

Connecting to SQL Server Using Integrated Security from ASP.NET in ADO.NET

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

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

Integrated security requires that the SQL Server is running on the same computer as IIS and that all application users are on the same domain so that their credentials are available to IIS. The following areas of the application need to be configured:

Configure the ASP.NET application so that Integrated Windows Authentication is enabled and Anonymous Access is disabled.

The web.config file establishes the authentication mode that the application uses and that the application will run as or impersonate the user. Add the following elements to the web.config file:
```
<authentication mode="Windows" />
<identity impersonate="true" />
```

The connection string must contain attributes that tell the SQL Server that integrated security is used. Use the Integrated Security=SSPI attribute-and-value pair instead of the User ID and Password attributes in the connection string. The older attribute-and-value pair Trusted_Connection=Yes is also supported.

Add users and groups from the domain and set their access permissions as required.

By default, ASP.NET applications run in the context of a local user ASPNET on IIS. The account has limited permissions and is local to the IIS computer and therefore not recognized as a user on remote computers. To overcome this limitation when SQL Server is not on the same computer as IIS, run the web application in the context of a domain user recognized on both IIS and SQL Server computers.

In addition to the areas identified where IIS and SQL Server are on the same computer, the following additional items must be configured if the SQL Server is on a different computer:

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" />
```

Storing Connection Strings in ADO.NET

A connection string is made up of a semi-colon delimited collection of attribute/value pairs that define how to connect a data source. Although connection strings tend to look similar, the available and required attributes are different depending on the data provider and on the underlying data source. There are a variety of options providing differing degrees of flexibility and security.

Persist Security Info

The Persist Security Info connection string attribute specifies whether the data source can hang on to, or persist, sensitive information such as user authentication credentials. Its value should be kept at the default false. If its value is true, the connection information-including the password-can be obtained by querying the connection, allowing an untrusted party to have access to sensitive information when a Connection is passed or persisted to a disk. This is an issue only when passing connected objects such as Connection or DataAdapter; disconnected objects such as DataSet and DataTable do not store information about the original source of their data.

Before a data source object is initialized for the first time, sensitive information can be retrieved from it regardless of the setting of the Persist Security Info property. Avoid passing uninitialized data source objects.

The Persist Security Info connection string attribute is supported by the SQL Server, OLE DB, and Oracle .NET Framework data providers. Although not supported by the ODBC .NET Framework data provider, its behavior is as if Persist Security Info is false and cannot be changed. Check the documentation for other data providers to determine specific implementation details.

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 two approaches for obtaining these credentials:

Prompting for connection credentials at runtime.
Storing predetermined connection credentials on the server and using them at runtime to connect to the database server.

Integrated Security

Integrated security is the most secure way to connect to a SQL Server and should be used unless it is impractical to do so. Integrated security uses the identity of the current active user rather than an explicit user ID and password in the connection string to authorize access to the database. Integrated security avoids storing usernames and passwords in connection strings and its use is recommended where possible instead of SQL Server Authentication.

To use integrated security in the connection string, specify the value SSPI for the Integrated Security attribute and do not specify User ID and Password connection string attributes:

Integrated Security=SSPI

See this post for information about connecting to SQL Server using integrated security from ASP.NET.

Often, it is not practical to prompt for connection credentials because of disadvantages including:

Security: Transferring connection information from the browser to the server can expose connection credentials if they are not encrypted.
Connection pooling: Each user must be recognized separately by the server. This does not allow effective connection pooling and can limit the scalability of the application.
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 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.

Always configure predetermined accounts with the minimum permissions required.

Never use sa or any other administrative account.

Never use blank passwords.

Hardcode in the application

An obvious technique for storing connection strings is hardcoding them into the application. Although this approach results in the best 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.

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.

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 defined in an <appSettings> element 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 <appSettings> element of the application file is used to store custom application settings as a collection of key-value pairs. You can store a connection string as shown:

<configuration>
<appSettings>
<add key="ConnectionString"
value="Data Source=(local);Initial Catalog=Northwind;User ID=sa;password=;"
/>
</appSettings>
</configuration>

The AppSettings property of the System.Configuration.ConfigurationSettings class is used to retrieve the value for a specific key within the appSettings element; the ConfigurationSettings 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 file system. 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.

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.config within 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.

Universal data link (UDL) file

The OLE DB .NET data providers supports 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.

NTFS Encryption

NTFS was enhanced in Windows 2000 with the Encrypted File System (EFS) that provides file- and directory-level encryption. Actually, EFS encrypts only files-directories are simply marked so that new files in the directory are encrypted. Encryption and decryption of files is both automatic and transparent for the user who set the encryption.

Encrypted files are visible to any user who can access the system but the contents of the encrypted files can only be viewed by the user who set the encryption. If necessary, standard NT security methods can hide directories and files from view of specific users and user groups.

EFS is a separate mechanism that is used together with the standard security subsystem.

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.

Writing Database-Independent Code

The solution shows how to use interfaces that are inherited by .NET connected classes (such as Connection and DataReader) to create provider-independent code that can be used with provider-specific code to access unique functionality.

The sample code contains a method and two event handlers:

GetData( )

This method is a .NET data provider-independent method that accepts .NET data provider-specific Connection and DataAdapter arguments as generic IDbConnection and IDbDataAdapter interface types. The interfaces are used to fill a DataSet from the Customers table in Northwind. The default view of the Customers DataTable is bound to the data grid on the form.

Finally, the provider-specific Connection for the IDbConnection is identified and provider-specific logic executed.

SQL Button.Click

This event handler is provider-specific code that creates a SqlConnection and a SqlDataAdapter object and passes them as arguments into the provider-independent GetData( ) method.

OLE DB Button.Click

This event handler is provider-specific code that creates an OleDbConnection and an OleDbDataAdapter object and passes them as arguments into the provider-independent GetData( ) method.

The C# code is shown here.

Example 1-9. File: DatabaseIndependentCodeForm.cs


// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;

//  . . . 

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

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

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

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

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

    dataGrid.CaptionText = "SQL .NET Provider";

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

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

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

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

Discussion

The IDbConnection, IDbCommand, IDataAdapter, and IDataReader interfaces are implemented by Connection, Command, DataAdapter, and DataReader classes in .NET data providers. You can pass these provider-independent base classes as interface arguments instead of the provider-specific inherited classes. This allows applications that support multiple data providers to reuse common provider-independent code.

The provider-specific functionality of the classes is not available when the base interfaces are used. The is operator is used to identify the provider-specific class of the provider-independent interface. Branching logic is then used execute code specific to that class.

Technorati : DB Independent code

Connecting to Exchange or Outlook

Use the OLE DB Jet provider to access Exchange and Outlook data.

The sample code contains two event handlers:

Form.Load: Displays a form that allows the user to specify the mailbox name and mail profile to connect to.
Connect Button.Click: Creates and opens a connection to Outlook or Exchange data using the OLE DB .NET data provider. A DataAdapter is used to fill a table with the Subject and Content of each message in the Inbox. The default view of the table is bound to a data grid on the form.

The C# code is shown here.

Example- File: ConnectExchangeDataForm.cs

// Namespaces, variables, and constants
using System;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

//  . . . 

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

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

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

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

// Create and fill the table.
DataTable dt = new DataTable("Inbox");
try
{
da.Fill(dt);
dataGrid.DataSource = dt.DefaultView;
}
catch(Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
return;
}
}

Discussion

The .NET data provider for OLE DB does not support OLE DB Version 2.5 interfaces including the Microsoft OLE DB Provider for Exchange. The Jet OLE DB provider can access an Outlook or Exchange store. An example of the connection string:

Microsoft.Jet.OLEDB.4.0;Outlook 9.0;MAPILEVEL=Personal Folders|;
PROFILE=Outlook;TABLETYPE=0;DATABASE=c:\temp;

The connection string attributes-and-value pairs are described in below table.

Table1. Outlook or Exchange connection string attributes

Element	Description
`Database name`	`DATABASE=path` With an Identifier of `Outlook` `9.0`, the path to store temporary system tables. With an Identifier of `Exchange` `4.0`, the path and filename to a Microsoft Access database in which to store temporary system tables.
`Identifier`	`Outlook` `9.0` to connect to Outlook 2000 and later. `Exchange` `4.0` to connect to Exchange 4.x and 5.x.
`Password`	`PASSWORD=password` Outlook or Exchange password. This parameter is not required if your network logon password is passed to the Outlook or Exchange server. This parameter is optional.
`Profile name`	`PROFILE=profile` The name of the Outlook or Exchange profile to use. If this not specified, the default profile is used.
`Table path`	`MAPILEVEL=<storage>\|<folderPath>` `<storage>` is the exact mailbox name on the server, a personal folder, or public folder as it appears in the Outlook Folder list. `<folderPath>` is the path to the folder immediately above the folder to access using the SQL statement. The folder path is required only when accessing folders below the top level of folders within the store; the pipe (`\|`) character is always required. When listing nested folders, separate each folder name with a backslash (`\`).
`Table type`	`TABLETYPE=0` for folders (default value). `TABLETYPE=1` for address books.

Technorati : Connecting to Exc or Outlook

Subscribe

Table 1. TableNameDataTable methods

Table 2. TableNameRow class properties and methods

Table 3. TableNameRowChangeEvent properties

Using the Visual Studio .NET IDE to generate a typed DataSet

Using the TypedDataSetGenerator class to generate a typed DataSet

Using an XSD schema file to generate a typed DataSet

File: BuildDataSetProgramaticallyForm.cs

Discussion

Example- File: HierarchicalDataSetForm.cs

Discussion

Example-. File: Categories.txt

Example- File: ConnectTextFileForm.cs

Discussion

Table 1. Schema.ini format options

Example 1-12. File: ChangeDatabaseForm.cs

Discussion

Example-File: ConnectionPoolingOptionsForm.cs

Discussion

SQL Server

Table1 . SQL Server connection string pooling attributes

Oracle

Table2. Oracle connection string pooling attributes

OLE DB

ODBC

SQL Server and Oracle

OLE DB

Table 1. OLE DB services enabled values

Table 2. OLE DB services connection string values

ODBC

SQL Server

1. SQL Server Profiler

2. Windows Performance Monitor

Table . SQL Server .NET provider performance counters

ODBC

Table2. ODBC connection pooling counters

Example- File: DataLinkDialogForm.cs

Persist Security Info

Integrated Security

Hardcode in the application

Application configuration file

Universal data link (UDL) file

NTFS Encryption

Windows registry

Custom file

Example 1-9. File: DatabaseIndependentCodeForm.cs

Discussion

Example- File: ConnectExchangeDataForm.cs

Discussion

Table1. Outlook or Exchange connection string attributes

Archives

Label Cloud