ADO.NET Slackers All concepts covered

Testing for No Records in DB

2008-10-16T13:43:00.001+05:30

To determine whether any records were returned from a query that you just executed.

Use the DataRowCollection.Count property, the DataReader.HasRows property, or the DataReader.Read( ) method.

The sample code creates and fills a DataTable and uses the Count property of the DataRowCollection to determine if the query used to create the table returned any rows. Next, a DataReader is created and both the HasRows property and the Read( ) method are used to determine whether the query used to create the DataReader returned any rows.

The C# code is shown here.

Example: File: NoRecordTestForm.cs


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

// Table name constants
private const String ORDERS_TABLE = "Orders";

//  . . . 

StringBuilder result = new StringBuilder( );

// Fill the Orders DataTable.
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders",
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
DataTable orderTable = new DataTable(ORDERS_TABLE);
da.Fill(orderTable);

// Test Orders DataTable for records.
bool tableHasRecords = orderTable.Rows.Count > 0;
result.Append("DataTable " + ORDERS_TABLE + ": Has records = " +
    tableHasRecords + Environment.NewLine);

// Create the Orders DataReader.
SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand("SELECT * FROM ORDERS", conn);
conn.Open( );
SqlDataReader orderReader = cmd.ExecuteReader( );

// Test Orders DataReader for records.
result.Append("DataReader " + ORDERS_TABLE + ": Has records = " +
    orderReader.HasRows + Environment.NewLine);

// Test Orders DataReader for records.
bool readerHasRecords = orderReader.Read( );
result.Append("DataReader " + ORDERS_TABLE + ": Has records = " +
    readerHasRecords + Environment.NewLine);

orderReader.Close( );
conn.Close( );

resultTextBox.Text = result.ToString( );

The `DataTable` contains a `DataRowCollection` object that contains all `DataRow` objects in the table. The `DataRowCollection` has a `Count` property that returns the number of rows in the table. The `Count` property for an empty table has a value of 0.

The `HasRows` property of the `DataReader` returns a Boolean value indicating whether the `DataReader` has any records.

Another way is to use the `Read( )` method to advance the `DataReader` to the next record. This returns a value of `true` if a record is available and `false` otherwise. The first call to the `Read( )` method will indicate whether any records were returned by the `DataReader`. This was the only way to determine whether the `DataReader` contained any records prior to the introduction of the `HasRows` property in .NET Framework 1.1.

Raising and Handling Stored Procedure Errors

2008-10-16T13:40:00.001+05:30

To catch and handle an error raised from a stored procedure.

Use a try . . . catch block to catch serious errors. Use the SqlConnection.InfoMessage event handler to catch informational and warning messages.

The sample code, as shown below, uses a single stored procedure and two event handlers:

SP0210_Raiserror: Accepts two input parameters specifying the severity and the state of an error and raises an error with the specified severity and state.
Raise Error Button.Click: Creates a connection and attaches a handler for warning and information messages from the SQL Server. A Command is created for the SP0210_Raiserror stored procedure and the input parameters are defined. The user-specified severity and state are assigned to the input parameters and the stored procedure command is executed within a try statement.
SqlConnection.InfoMessage: Called when a warning or informational message is raised by the SQL Server.

Example: Stored procedure: SP0210_Raiserror


CREATE PROCEDURE SP0210_Raiserror
    @Severity int,
    @State int = 1
AS
    if @Severity>=0 and @Severity <=18
        RAISERROR ('Error of severity %d raised from SP 0210_Raiserror.', @Severity, 
             @State, @Severity)
    
    if @Severity>=19 and @Severity<=25
        RAISERROR ('Fatal error of severity %d raised from SP 0210_Raiserror.',
             @Severity, @State, @Severity) WITH LOG
    
    RETURN

The C# code is shown below.

Example: File: RaiserrorForm.cs


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

//  . . . 

private void raiseErrorButton_Click(object sender, System.EventArgs e)
{
    resultTextBox.Text =
        "Severity: " + severityTextBox.Text + Environment.NewLine +
        "State: " + stateTextBox.Text + Environment.NewLine +
        Environment.NewLine;

    // Create the connection.
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    // Attach handler for SqlInfoMessage events.
    conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
    
    // Define a stored procedure command and the parameters.
    SqlCommand cmd = new SqlCommand("SP0210_Raiserror", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@Severity", SqlDbType.Int);
    cmd.Parameters.Add("@State", SqlDbType.Int);
    // Set the value for the stored procedure parameters.
    cmd.Parameters["@Severity"].Value = severityTextBox.Text;
    cmd.Parameters["@State"].Value = stateTextBox.Text;

    // Open the connection.
    conn.Open( );
    try
    {
        // Try to execute the stored procedure.
        cmd.ExecuteNonQuery( );
    }
    catch(System.Data.SqlClient.SqlException ex)
    {
        // Catch SqlException errors.
        resultTextBox.Text += "ERROR: " + ex.Message;
    }
    catch(Exception ex)
    {
        // Catch other errors.
        resultTextBox.Text += "OTHER ERROR: " + ex.Message;
    }
    finally
    {
        // Close the connection.
        conn.Close( );
    }
}

private void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    resultTextBox.Text += "MESSAGE: " + e.Message;
}

Returning an Output Parameter Using DataReader

2008-10-16T13:37:00.000+05:30

To access an output parameter returned by a stored procedure that you have used to create a DataReader.

Add a parameter to a Command's ParameterCollection and specify the ParameterDirection as either Output or InputOutput.

The sample code uses a single stored procedure:

SP0209_OutputValueWithDataReader: Returns a result set containing all records from the Orders table in Northwind. The stored procedure takes one input and one output parameter and sets the value of the output parameter to the value of the input parameter.

The sample code creates a DataReader from a stored procedure command as shown in below example. The stored procedure returns a single output parameter, and then the stored procedure sets this value to the value of the input parameter specified by the user. The code displays the value of the output parameter at four different stages of working with the result set in the DataReader:

Before the DataReader is created

Immediately after the DataReader is created

After all rows in the DataReader have been read

After the DataReader is closed

Example: Stored procedure: SP0209_OutputValueWithDataReader


CREATE PROCEDURE SP0209_OutputValueWithDataReader
    @ValueIn int,
    @ValueOut int output
AS
    set nocount on

    set @ValueOut = @ValueIn

    select * from Orders
        
    RETURN

The C# code is shown below

Example: File: SpOutputValueDataReaderForm.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.
SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);

// Create the command.
SqlCommand cmd = new SqlCommand("SP0209_OutputValueWithDataReader", conn);
cmd.CommandType = CommandType.StoredProcedure;

// Define the input parameter for the command.
cmd.Parameters.Add("@ValueIn", SqlDbType.Int);
// Set the input parameter value.
cmd.Parameters[0].Value = Convert.ToInt32(outputValueTextBox.Text);

// Define the output parameter for the command.
SqlParameter outParam = cmd.Parameters.Add("@ValueOut", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;

result.Append("Before execution, output value = " + outParam.Value +
    Environment.NewLine);

// Open the connection and create the DataReader.
conn.Open( );
SqlDataReader dr = cmd.ExecuteReader( );

result.Append("After execution, output value = " + outParam.Value +
    Environment.NewLine);

// Iterate over the records for the DataReader.
int rowCount = 0;
while (dr.Read( ))
{
    rowCount++;

    //  . . .  Code to process result set in DataReader
}

result.Append("After reading all " + rowCount + " rows, output value = " +
    outParam.Value + Environment.NewLine);

// Close the DataReader.
dr.Close( );
result.Append("After DataReader.Close( ), output value = " +
    outParam.Value + Environment.NewLine);

// Close the connection.
conn.Close( );

resultTextBox.Text = result.ToString( );

Output parameters allow a stored procedure to pass a data value or cursor variable back to the caller. To use an output parameter with a DataReader, add the output parameter to the ParameterCollection for the Command object used to create the DataReader. Specify the ParameterDirection property of the Parameter as Output or InputOutput.

Mapping .NET Data Provider Data Types to .NET Framework Data Types

2008-10-16T13:35:00.001+05:30

To convert between .NET provider data types and .NET Framework data types.

The ADO.NET DataSet and contained objects are data source independent. The DataAdapter is used to retrieve data into the DataSet and to reconcile modifications made to the data to the data source at some later time. The implication is that data in the DataTable objects contained in the DataSet are .NET Framework data types rather than data types specific to the underlying data source or the .NET data provider used to connect to that data source.

While the DataReader object for a data source is specific to the .NET data provider used to retrieve the data, the values in the DataReader are stored in variables with .NET Framework data types.

The .NET Framework data type is inferred from the .NET data provider used to fill the DataSet or build the DataReader. The DataReader has typed accessor methods that improve performance by returning a value as a specific .NET Framework data type when the data type is known, thereby eliminating the need for additional type conversion.

Some DataReader classes expose data source specific accessor methods as well. For example, the SqlDataReader exposes accessor methods that return SQL Server data types as objects of System.Data.SqlType.

The following example shows how to cast a value from a DataReader to a .NET Framework data type and how to use the .NET Framework typed accessor and the SQL Server-specific typed accessor:


// Create the connection and the command.
SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand(
    "SELECT CategoryID, CategoryName FROM Categories", conn);

// Open the connection and build the DataReader.
conn.Open( );
SqlDataReader dr = cmd.ExecuteReader( );

// Get the CategoryID from the DataReader and cast to int.
int categoryId = Convert.ToInt32(dr[0]);

// Get the CategoryID using typed accessor.
int taCategoryId = dr.GetInt32(0);

// Get the CategoryID using the SQL Server-specific accessor.
System.Data.SqlTypes.SqlInt32 sqlCategoryId = dr.GetSqlInt32(0);

In all cases, a null value for a .NET Framework data type is represented by System.DBNull.Value.

Counting Records in DataReader

2008-10-10T00:51:00.002+05:30

To determine how many records there are in a DataReader.

Use one of the following three techniques:

Iterate over the rows in the DataReader.
Issue a COUNT(*) query as part of a batch query. Note that not all data sources support batch queries. If not, execute the statements separately one after the other for a similar result.
Use the @@ROWCOUNT function to return the number or rows in a DataReader after the DataReader has been closed. This technique is SQL Server specific.

The sample code uses a single stored procedure:

SP0207_GetOrders: Returns a result set containing all records in the Orders table in Northwind. Also, the stored procedure returns the @@ROWCOUNT value for the query in an output parameter. The stored procedure is shown in the below example here.; Example: Stored procedure: SP0207_GetOrders

ALTER PROCEDURE SP0207_GetOrders
@RowCount int output
AS
set nocount on

select * from Orders

set @RowCount = @@ROWCOUNT

RETURN

The C# code is shown below:

Example. File: DataReaderRowCountForm.cs

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

//  . . . 

// Batch query to retrieve the COUNT of records and
// all of the records in the Orders table as two result sets.
String sqlText = "SELECT COUNT(*) FROM Orders; " +
"SELECT * FROM Orders;";

// Create the connection.
SqlConnection conn = new SqlConnection(
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand(sqlText, conn);
conn.Open( );

// Create a DataReader on the first result set.
SqlDataReader dr = cmd.ExecuteReader( );
// Get the count of records from the select count(*) statement.
dr.Read( );
resultTextBox.Text = "Orders table record count, using COUNT(*)= " +
dr.GetInt32(0) + Environment.NewLine;

// Move to the data result set.
dr.NextResult( );
int count = 0;
// Iterate over the records in the DataReader.
while(dr.Read( ))
{
count++;

//  . . .  Do something interesting with the data here.
}

// Close the DataReader and the connection.
dr.Close( );

resultTextBox.Text += "Orders table record count, " +
"iterating over result set = " + count +
Environment.NewLine;

// Create the stored procedure to use in the DataReader.
cmd = new SqlCommand("SP0207_GetOrders", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Create the output paramter to return @@ROWCOUNT.
cmd.Parameters.Add("@RowCount", SqlDbType.Int).Direction =
ParameterDirection.Output;

// Create a DataReader for the result set returned by
// the stored procedure.
dr = cmd.ExecuteReader( );

//  . . .  Process the data in the DataReader.

// Close the DataReader.
dr.Close( );
// The output parameter containing the row count is now available.

resultTextBox.Text += "Orders table record count, " +
"returning @@ROWCOUNT from stored procedure = " + cmd.Parameters["@RowCount"].Value;

conn.Close( );

Discussion

The DataReader provides forward-only, read-only access to a stream of rows from a data source. It is optimized for performance by reading data directly from a connection to a data source. As a result, there is no way to determine the number of records in the result set for the DataReader without iterating through all of the records. Additionally, because the DataReader is forward-only, you cannot move backwards in DataReader so when iterating, you must process data at the same time. This technique provides the only accurate count of records in the DataReader.

A second technique demonstrated in the solution counts the records using the COUNT aggregate function for the command text used to build the DataReader. This technique can have discrepancies with the number of records actually in the DataReader because of the timing lag between issuing the COUNT function and creating the DataReader.

The solution also demonstrates using the SQL Server @@ROWCOUNT variable, which returns the number of rows affected by the previous statement, to return the number of records in the result set used to create the DataReader. The count is returned as an output parameter and is therefore not available until the DataReader is closed. Although this does not improve the availability of the record count, centralizing the count in the stored procedure is less prone to coding errors than the counting approach.

The HasRows( ) method of the DataReader was introduced in Version 1.1 of the .NET Framework. It returns a Boolean value indicating whether the DataReader contains at least one row.

There is also no way to determine the number of result sets in a DataReader built using a batch query without iterating over the result sets using the NextResult( ) method.

Accessing Deleted Rows in DataTable

2008-10-10T00:28:00.002+05:30

Use either a DataView or the Select( ) method of the DataTable to access deleted rows.

The sample code contains three event handlers:

Form.Load: Sets up the sample by creating a DataTable containing Orders data from Northwind. A view containing the Current rows is bound to a data grid on the form.
Current Rows RadioButton.CheckedChanged: Sets the view of the Orders data to display only Current rows. The text box displaying information about deleted rows is cleared.
Deleted Rows RadioButton.CheckedChanged: Sets the view of the Orders data to display only Deleted rows. The DataTable for the DataView is retrieved and the Select( ) method is used to get the Deleted rows. The overloaded indexer in C#, or Item( ) property in VB.NET, is used to retrieve the OrderID from the Original version of these deleted rows. This information is displayed in the text box on the form.

The C# code is shown in the following example.

Example: File: AccessDeletedRowsForm.cs

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

private DataView dv;

//  . . . 

private void AccessDataSetDeletedRowsForm_Load(object sender,
System.EventArgs e)
{
// Fill the Orders table.
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders",
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
DataTable dt = new DataTable("Orders");
da.Fill(dt);

// Define a view of just the Current rows.
dv = new DataView(dt, null, null, DataViewRowState.CurrentRows);
dataGrid.DataSource = dv;

currentRadioButton.Checked = true;
}

private void currentRadioButton_CheckedChanged(object sender,
System.EventArgs e)
{
// Filter to include only current rows.
dv.RowStateFilter = DataViewRowState.CurrentRows;
dataGrid.ReadOnly = false;

deletedTextBox.Clear( );
}

private void deletedRadioButton_CheckedChanged(object sender,
System.EventArgs e)
{
// Filter the view to include only deleted rows.
dv.RowStateFilter = DataViewRowState.Deleted;
dataGrid.ReadOnly = true;

// Get the DataTable from the DataView.
DataTable dt = dv.Table;
// Filter using the DataTable RowState.
DataRow[] delRows = dt.Select(null, null, DataViewRowState.Deleted);

StringBuilder sb = new StringBuilder("Deleted Records:" +
Environment.NewLine);
// Iterate over the collection of deleted rows.
foreach(DataRow row in delRows)
sb.Append("Order ID: " + row["OrderID",
DataRowVersion.Original] + Environment.NewLine);

deletedTextBox.Text = sb.ToString( );
}

Discussion

ADO.NET manages the state of the rows while they are being modified. Rows are assigned a state from the DataRowState enumeration described in below table 1.

Table 1. DataRowState enumeration

Value	Description
`Added`	The row has been added to the collection of rows in the table but `AcceptChanges( )` has not been called.
`Deleted`	The row has been deleted from the collection of rows in the table but `AcceptChanges( )` has not been called.
`Detached`	The row does not belong to the collection of rows in a `DataTable`.
`Modified`	The data in the row has been changed but `AcceptChanges( )` has not been called.
`Unchanged`	The data in the row has not been changed since it was loaded or since `AcceptChanges( )` was last called.

When AcceptChanges( ) is called on the DataSet, DataTable, or DataRow, either explicitly or implicitly by calling the Update( ) method of the DataAdapter, the following occurs:

All rows with a row state of Deleted are removed.
All other rows are assigned a row state of Unchanged and the Original row version values are overwritten with the Current version values.

When RejectChanges( ) is called on the DataSet, DataTable, or DataRow, the following occurs:

All rows with a row state of Added are removed.
All other rows are assigned a row state of Unchanged and the Current row version values are overwritten with the Original row version values.

Each DataRow has a RowState property that returns the current state of the row.

ADO.NET maintains several versions of the data in each row while it is being modified to allow the disconnected to be later reconciled with the data source. The following table2 describes the DataRowVersion enumeration values.

Table 2. DataRowVersion enumeration

Value	Description
`Current`	Current value. This version does not exist for rows with a state of `Deleted`.
`Default`	Default value as determined by the `DataRowState`: The `Current` version for rows with `Added`, `Modified`, or `Unchanged` state The `Original` version for rows with `Deleted` state The `Proposed` value for rows with `Detached` state
`Original`	Original value. This version does not exist for rows with a state of `Added`.
`Proposed`	Proposed value. This value exists during a row edit operation started either implicitly or explicitly with the `BeginEdit( )` method and for `Detached` rows.

The HasVersion( ) method of the DataRow object checks whether a particular row version exists.

The DataViewRowState enumeration is used to retrieve a particular version of data or to determine whether a version exists. It is used for this purpose by both the Select( ) method of the DataTable and by the RowStateFilter property of the DataView. You can retrieve more than one version by using a Boolean OR of DataViewRowState values. Table3 describes the DataViewRowState enumeration values.

Table 3. DataViewRowState enumeration

Value	Description
`Added`	The `Current` version of all `Added` rows.
`CurrentRows`	The `Current` version of all `Unchanged`, `Added`, and `Modified` rows. This is the default value.
`Deleted`	The `Original` version of all `Deleted` rows.
`ModifiedCurrent`	The `Current` version of all `Modified` rows.
`ModifiedOriginal`	The `Original` version of all `Modified` rows.
`None`	No rows.
`OriginalRows`	The `Original` version of `Unchanged`, `Modified`, and `Deleted` rows.
`Unchanged`	The `Current` version of all `Unchanged` rows.

The Current version of each row is retrieved by default when accessing rows in a DataTable or in a DataView. The solution demonstrates an approach for getting Deleted rows from both a DataTable and a DataView. Deleted rows include only those marked for deletion using the Delete( ) method of the DataRow or the DataView, not the Remove( ) or RemoveAt( ) method of the DataRowCollection, which instead immediately removes the specified DataRow from the collection.

The solution demonstrates two techniques for retrieving the deleted rows:

To get the Deleted rows from the DataTable, use an overload of the Select( ) method of the DataTable to return an array of deleted DataRow objects. The overload accepts an argument having a DataViewRowState enumeration value. To retrieve deleted rows, pass a value of Deleted as the argument.

To get the Deleted rows from the DataView, set the RowStateFilter property of the DataView to Deleted. Deleted rows are also visible, along with other rows, when you set the RowStateFilter property to ModifiedOriginal and OriginalRows.

Using Web Service as Data Source

2008-10-10T00:12:00.001+05:30

Create a web service that returns a DataSet to a client, and then invoke the web service from the client to retrieve the DataSet.

The XML web service code contains one method:

LoadOrders( ): Creates a DataSet containing all Orders and Order Details data from Northwind. A DataRelation is created relating the tables. The DataSet is returned by the method.

The client-side code instantiates the web service class and calls the LoadOrders( ) method to create a DataSet containing the Orders and Order Details data from Northwind. The default view of the Orders table is bound to a data grid on the form.

The C# code for the XML web service is shown in the following example:

Example 2-4. File: NorthwindServiceCS.asmx.cs


// Namespaces, variables, and constants
using System;
using System.ComponentModel;
using System.Web.Services;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
        
public const String ORDERS_TABLE = "Orders";
public const String ORDERDETAILS_TABLE = "OrderDetails";

public const String ORDERID_FIELD = "OrderID";

public const String ORDERS_ORDERDETAILS_RELATION =
    "Order_OrderDetails_Relation";

//  . . . 

[WebMethod]
public DataSet LoadOrders( )
{
    DataSet ds = new DataSet( );
    
    SqlDataAdapter da;

    // Fill the Order table and add it to the DataSet.
    da = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["DataConnectString"]);
    DataTable orderTable = new DataTable(ORDERS_TABLE);
    da.FillSchema(orderTable, SchemaType.Source);
    da.Fill(orderTable);
    ds.Tables.Add(orderTable);

    // Fill the OrderDetails table and add it to the DataSet.
    da = new SqlDataAdapter("SELECT * FROM [Order Details]",
        ConfigurationSettings.AppSettings["DataConnectString"]);
    DataTable orderDetailTable = new DataTable(ORDERDETAILS_TABLE);
    da.FillSchema(orderDetailTable, SchemaType.Source);
    da.Fill(orderDetailTable);
    ds.Tables.Add(orderDetailTable);

    // Create a relation between the tables.
    ds.Relations.Add(ORDERS_ORDERDETAILS_RELATION,
        ds.Tables[ORDERS_TABLE].Columns[ORDERID_FIELD],
        ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD],
        true);

    return ds;
}

The C# web services client-side code is shown in the following example:

Example: File: WebServiceDataSourceForm.cs


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

// Table name constants
private const String ORDERS_TABLE= "Orders";

//  . . . 

// Create the Web Service object.
NorthwindServiceCS nws = new NorthwindServiceCS( );
// Load the DataSet containing orders and order details.
DataSet ds = nws.LoadOrders( );

// Bind the default view of the orders table to the grid.
dataGrid.DataSource = ds.Tables[ORDERS_TABLE].DefaultView;

Discussion

An XML web service is software that is accessible using Internet standards such as XML and HTTP. Because it is accessible through open-standard interfaces, web services make it easy to allow heterogeneous systems to work together.

.NET makes it very easy to build XML web services. In .NET, web services are implemented as .ASMX files beginning with a @WebService directive. For example, the solution code contains the following directive:


<%@ WebService Language="c#" Codebehind="NorthwindServiceCS.asmx.cs"
    Class="NorthwindServiceCS" %>

Methods in the web service class that are exposed over the Web are tagged with the WebMethod attribute; untagged methods can be used only internally by the web service. To deploy the web service, copy it to a virtual directory that has script execute permissions on an IIS web server that has ASP.NET support.

To use the web service class, use wsdl.exe to create the client-side proxy class. For the solution, the command is:


wsdl.exe http://localhost/NorthwindWebServiceCS/NorthwindServiceCS.asmx

Then, as with a local class, the client is able to instantiate the web service class using the new operator.

For more information about creating and consuming XML web services, see the MSDN Library.

The solution shows that there is very little difference between implementing the LoadOrders( )methods to retrieve a DataSet containing the Orders and Order Details data from Northwind as a local class or as a web services class.

Processing a Batch SQL Statement

2008-10-10T00:07:00.002+05:30

Use the NextResult( ) method to iterate through and process SQL queries that return multiple result sets.

The sample code contains three event handlers:

Go Button.Click: Defines a SQL batch query statement that selects all Orders and Order Details records from Northwind. Depending on the radio button checked by the user, either a DataAdapter is used to fill a DataSet with multiple tables or a Command object is used to create a DataReader containing multiple result sets. In either case the results are displayed in a data grid for the DataSet and in a text box for the DataReader.
DataSet RadioButton.CheckedChanged: Displays a data grid to show the results of a batch query when loaded into a DataSet. Hides the text box for the DataReader results.
DataReader RadioButton.CheckedChanged: Displays a text box to show the results of a batch query when loaded into a DataReader. Hides the data grid for the DataSet results.

The following example shows it.

Example: File: BatchSqlStatementForm.cs

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

// Table name constants
private const String ORDERS_TABLE        = "Orders";
private const String ORDERDETAILS_TABLE  = "OrderDetails";

// Field name constants
private const String ORDERID_FIELD       = "OrderID";

// Relation name constants
private const String ORDERS_ORDERDETAILS_RELATION =
"Orders_OrderDetails_Relation";

//  . . . 

private void goButton_Click(object sender, System.EventArgs e)
{
// Batch SQL query returning two result sets
String sqlText = "select OrderID, CustomerID, EmployeeID, OrderDate," +
"RequiredDate, ShippedDate, ShipVia, Freight, ShipName, " +
"ShipAddress, ShipCity, ShipRegion, ShipPostalCode, " +
"ShipCountry " +
"FROM Orders;" +
"SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " +
"FROM [Order Details];";

if (dataSetRadioButton.Checked)
{
SqlDataAdapter da = new SqlDataAdapter(sqlText,
ConfigurationSettings.AppSettings["Sql_ConnectString"]);

// Map the automatically generated table names Table and Table1.
da.TableMappings.Add("Table", ORDERS_TABLE);
da.TableMappings.Add("Table1", ORDERDETAILS_TABLE);

// Fill the DataSet with the results of the batch query.
DataSet ds = new DataSet( );
da.Fill(ds);

// Add a relation between the Order and Order Details tables.
ds.Relations.Add(new DataRelation(ORDERS_ORDERDETAILS_RELATION,
ds.Tables[ORDERS_TABLE].Columns[ORDERID_FIELD],
ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD],
true));

// Bind the default view of the Orders table to the grid.
resultDataGrid.DataSource = ds.Tables[ORDERS_TABLE];
}
else
{
StringBuilder sb = new StringBuilder( );

// Create a new connection and command to fill the DataReader.
SqlConnection conn = new SqlConnection(
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand(sqlText, conn);
conn.Open( );

// Execute the batch query.
SqlDataReader dr = cmd.ExecuteReader( );

// Process each result set in the DataReader.
int nResultSet = 0;
do
{
sb.Append("RESULT SET: " + (++nResultSet) +
Environment.NewLine);

// Iterate over the rows in the DataReader.
while(dr.Read( ))
{
// Output each field in the DataReader row.
for(int i = 0; i < dr.FieldCount; i++)
sb.Append(dr[i] + "; ");

sb.Append(Environment.NewLine);
}

sb.Append(Environment.NewLine);
} while(dr.NextResult( ));
dr.Close( );
conn.Close( );

// Display the results.
resultTextBox.Text = sb.ToString( );
}
}

private void dataSetRadioButton_CheckedChanged(object sender,
System.EventArgs e)
{
// Display the data grid for DataSet results.
resultDataGrid.Visible = true;
resultTextBox.Visible = false;
}

private void dataReaderRadioButton_CheckedChanged(object sender,
System.EventArgs e)
{
// Display the text box for DataReader results.
resultDataGrid.Visible = false;
resultTextBox.Visible = true;        
}

A batch command is defined as a collection of SQL statements separated by semicolons. The batch command can fill a DataSet or build a DataReader. Working with the results is different for each of these scenarios as described in the following sections.

The batch statement can also be contained in a stored procedure. Everything is the same as for the example where the SQL batch command is defined in the code once the Command is executed.

DataSet

The Fill( ) method of the DataAdapter adds multiple result sets from a batch query to a DataSet. One table is created in the DataSet for each result set. By default, these tables will be named Table, Table1, Table2, and so on. You can make these names more meaningful by specifying table mappings in the TableMappings collection of the DataAdapter. Data relationships between the tables added with a batch query must be created programmatically. As with non-batch queries, you can define the relations and foreign key constraints for the tables prior to filling them with the results of the batch query.

When using the Fill( ) method of the DataAdapter with a batch fill operation, if one of the result sets contains an error, all subsequent processing is skipped and result sets are not added to the DataSet.

When using the FillSchema( ) method of the DataAdapter with a batch query and the OLE DB data provider, the schema is returned for only the first query. To retrieve the schema for all result sets, use the Fill( ) method with the MissingSchemaAction argument set to AddWithKey.

DataReader

As with a single statement command, a batch command is used to build a DataReader by calling the ExecuteReader( ) method of the Command object. The NextResult( ) method of the DataReader is used to advance to the next result set where the method returns true if there is another result set. Iterating over the DataReader is demonstrated in the sample code using the following technique:

do {

 . .  process the result set        

} while(dr.NextResult( ));

Initially, the DataReader is positioned on the first result set. Once NextResult( ) is called there is no way to return to the previous result set.

Creating a Strongly Typed DataSet

2008-09-27T16:44:00.002+05:30

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

2008-09-27T16:40:00.001+05:30

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

2008-09-27T16:38:00.002+05:30

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

2008-09-25T13:06:00.005+05:30

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

2008-09-25T13:02:00.003+05:30

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

2008-09-24T12:32:00.001+05:30

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

2008-09-24T12:29:00.003+05:30

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

2008-09-22T23:11:00.002+05:30

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

2008-09-22T23:09:00.004+05:30

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

2008-09-22T23:08:00.002+05:30

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

2008-09-22T22:52:00.001+05:30

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

2008-09-22T22:51:00.002+05:30

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

2008-09-20T20:44:00.003+05:30

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

2008-09-20T20:42:00.003+05:30

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

Connecting to an Oracle Database

2008-09-20T20:40:00.002+05:30

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

The sample code contains two event handlers:

Oracle Button.Click: Creates and opens a connection to an Oracle database using the Oracle .NET data provider. Information about the database is displayed from the properties of the OracleConnection object.
OLE DB Button.Click: Creates and opens a connection to an Oracle database using the OLE DB .NET data provider. Information about the database is displayed from the properties of the OleDbConnection object.

Example- File: ConnectOracleForm.cs

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

//  . . . 

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

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

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

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

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

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

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=myOracleDb;Integrated Security=yes;

Without integrated security, the connection string is:

Data Source=myOracleDb;User Id=scott;Password=tiger;

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.

Here are desciptions of available managed providers:

Oracle has released a .NET data provider. It is available for free download from http://otn.oracle.com/software/tech/windows/odpnet/content.html.
Data Direct Technologies licenses a fully managed provider that does not require client libraries for Oracle8i Release 2 (8.1.6) or later databases. More information is available at http://www.datadirect-technologies.com/products/dotnet/dotnetindex.asp.

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 not supported by a .NET Oracle provider. An example of the connection string is shown here:

Provider=MSDAORA;Data Source=myOracleDb;User Id=scott;Password=tiger;

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 is shown here:

Driver={Microsoft ODBC for Oracle};Server=myOracleDb;
Trusted_Connection=yes;

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

TNSNAMES.ORA

Oracle uses a configuration file named TNSNAMES.ORA to locate the Oracle database and determine how to connect to it based on the Data Source or Database attribute in the connection string.

An example of an entry in the TNSNAMES.ORA file for the alias MYORCLDB follows:

MYORCLDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = myserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.myurl.com)
)
)

In this simple example, the connection to the alias MYORLCDB uses TCP/IP on port 1521 (the default) and is running on the computer called myserver. The name of the Oracle service is ORCL.MYURL.COM.

Technorati : Connecting to Oracle DB

Connecting to SQL Server Using Integrated Security from ASP.NET

2008-09-20T20:36:00.001+05:30

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.

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.

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

Connecting to a Named Instance of SQL Server or Microsoft Data Engine (MSDE)

2008-09-18T00:47:00.002+05:30

You need to understand what a SQL Server or MSDE named instance is and how to connect to one. The sample code contains a single event handler:

Connect Button.Click: Creates and opens a connection to a named instance of a SQL Server. Information about the SQL Server is displayed from the properties of the SqlConnection object.

The C# code is shown here.

Example- File: ConnectNamedInstanceForm.cs

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

//  . . . 

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

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

try
{
conn.Open( );

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

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

resultTextBox.Text = result.ToString( );
}

SQL Server 2000 introduced the ability to install multiple copies of SQL Server on a single computer. Only one copy can function as the default instance at any time; it is identified by the network name of the computer on which it is running. All other copies are named instances and are identified by the network name of the computer plus an instance name. The format is <computerName>\<instanceName>. This format is used in the connection string to specify the Data Source attribute for a named instance.

Each instance operates independently of the other instances installed on the same computer. Each instance has its own set of system and user databases that are not shared between instances and it runs within its own security context. The maximum number of instances supported on SQL Server 2000 is 16. The Microsoft Distributed Transaction Coordinator (DTC) and the Microsoft Search services are installed and used simultaneously by every installed instance of SQL Server. Client tools such as Enterprise Manager and Query Analyzer are also shared.

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, if the named instance msde01 was set up to listen on port 1450, the following connection string might be used:

Data Source=(local)\msde01,1450;Integrated security=SSPI;
Initial Catalog=Northwind

Technorati : Conneting to Named Instance

Using an IP Address to Connect to SQL Server

2008-09-17T20:45:00.002+05:30

Use the Network Address and Network Library attributes of the connection string.

The sample code contains a single event handler:

Connect Button.Click: Creates and opens a connection to a SQL Server using its IP address. Information about the SQL Server is displayed from the properties of the SqlConnection object.

The C# code is shown here.

Example- File: ConnectSqlServerIpAddressForm.cs

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

//  . . . 

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

SqlConnection conn = new SqlConnection(connString);
conn.Open( );

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

conn.Close( );

resultTextBox.Text += "ConnectionState = " + conn.State;
}

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

AppleTalk ADSP: Allows Apple Macintosh to communicate with SQL Server using native AppleTalk protocol.
Banyan VINES: Supports Banyan VINES Sequenced Packet Protocol (SPP) across Banyan VINES IP network protocol.
Multiprotocol: Automatically chooses the first available network protocol to establish a connection generally with performance comparable to using a native network library. TCP/IP Sockets, NWLink IPX/SPX, and Named Pipes are supported.
Named Pipes: Interprocess communication (IPC) mechanism provided by SQL Server for communication between clients and servers.
NWLink IPX/SPX: The native protocol of Novell Netware networks.
TCP/IP Sockets: Uses standard Windows sockets to communicate across the TCP/IP protocol.

Clustered installations of SQL Server support only Named Pipes and TCP/IP protocols. AppleTalk, Banyan Vines, and Multiprotocol protocols are unavailable if named instances are installed on the server.

For more information about network libraries and configuring network libraries, 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.

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=Northwind

In the example, the IP address is 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 of the SQL Server. 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

Connecting to an Access Database from ASP.NET

2008-09-17T20:36:00.002+05:30

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. Other ways to impersonate a user from an ASP page are described in the Microsoft Knowledge Base article Q248187.

The Microsoft Jet engine uses the TEMP folder on the IIS computer that is accessing the Access database. The user identity requires NTFS (Windows NT File System) full-control permissions on the TEMP folder. Ensure that the TEMP and TMP environment variables are properly configured.

Configure the Access server

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.

Connecting to a Secured Access Database

2008-09-17T12:56:00.001+05:30

Use the Jet OLEDB:System Database attribute in the connection string to specify the path and filename of the workgroup information file or system database.
The sample code contains a single event handler:
Connect Button.Click
Creates and opens a connection to a Microsoft Access database secured with user-level security and a workgroup file using the OLE DB .NET data provider. Information about the database is displayed from the properties of the OleDbConnection object.

The C# code is shown here.

Example- File: AccessSecureForm.cs// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data.OleDb;
// . . .
private void connectButton_Click(object sender, System.EventArgs e)
{
StringBuilder result = new StringBuilder( );
// Build the connection string with security information.
String connectionString =
ConfigurationSettings.AppSettings["MsAccess_ConnectString"] +
@"Jet OLEDB:System database=" +
ConfigurationSettings.AppSettings["MsAccess_SecureMdw_Filename"] +
";" + "User ID=" + userIdTextBox.Text + ";" +
"Password=" + passwordTextBox.Text + ";" +
Environment.NewLine + Environment.NewLine;
result.Append(connectionString);
// Create the connection.
OleDbConnection conn = new OleDbConnection(connectionString);
try
{
// Attempt to open the connection.
conn.Open( );
result.Append(
"Connection State: " + conn.State + Environment.NewLine +
"OLE DB Provider: " + conn.Provider +
Environment.NewLine +
"Server Version: " + conn.ServerVersion +
Environment.NewLine);
conn.Close( );
result.Append("Connection State: " + conn.State +
Environment.NewLine);
}
catch(System.Data.OleDb.OleDbException ex)
{
result.Append("ERROR: " + ex.Message);
}
resultTextBox.Text = result.ToString( );
}

Microsoft Access user-level security requires an additional file—the workgroup information or MDW file—in addition to the database or MDB file. This file contains the user and group information for the secured database while the actual permissions are stored in the database file.
When you connect to a secured Jet database, the user ID and password are validated against the values in the MDW file. The permissions are obtained from the MDB file. To connect, the location of both the database file and the workgroup file must be supplied.

The OLE DB provider for Microsoft Jet has several provider-specific connection string attributes in addition to those defined by ADO.NET. To open a database secured by Microsoft Access user-level security, use the Jet OLEDB:System Database attribute in the connection string to specify the path and filename of the workgroup information file or system database. This corresponds to the OLE DB property DBPROP_JETOLEDB_SYSDBPATH.

Connecting to a Password-Protected Access Database

2008-09-17T12:42:00.001+05:30

Use the Jet OLEDB:Database Password attribute in the connection string to specify the password.

The sample code contains a single event handler:
Connect Button.Click
Creates and opens a connection to a password-secured Microsoft Access database using the OLE DB .NET data provider. Information about the database is displayed from the properties of the OleDbConnection object.
The C# code is shown here.

Example- File: AccessPasswordForm.cs// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.OleDb;
// . . .
private void connectButton_Click(object sender, System.EventArgs e)
{
StringBuilder result = new StringBuilder( );
// Build the connections string incorporating the password.
String connectionString =
ConfigurationSettings.AppSettings["MsAccess_Secure_ConnectString"]+
"Jet OLEDB:Database Password=" + passwordTextBox.Text + ";";
result.Append("ConnectionString: " + Environment.NewLine +
connectionString + Environment.NewLine + Environment.NewLine);
OleDbConnection conn = new OleDbConnection(connectionString);
try
{
conn.Open( );
// Retrieve some database information.
result.Append(
"Connection State: " + conn.State + Environment.NewLine +
"OLE DB Provider: " + conn.Provider +
Environment.NewLine +
"Server Version: " + conn.ServerVersion +
Environment.NewLine);
conn.Close( );
result.Append("Connection State: " + conn.State);
}
catch(System.Data.OleDb.OleDbException ex)
{
conn.Close( );
result.Append("ERROR: " + ex.Message);
}
resultTextBox.Text = result.ToString( );
}

A Microsoft Access database password requires that users enter a password to obtain access to the database and database objects. This is also known as share-level security. A password does not allow groups or users to have distinct levels of access or permissions. Anyone with the password has unrestricted access to the database.
The Set Database command from the Tools Security menu is used to set up a database password.

The OLE DB provider for Microsoft Jet 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.

Connecting to a Microsoft Excel Workbook

2008-09-17T12:34:00.001+05:30

Use the OLE DB Jet provider to create, access, and modify data stored in an Excel workbook.
The sample code contains two event handlers:
Form.Load
Creates an OleDbDataAdapter that uses the Jet OLE DB provider to access an Excel workbook. Custom insert and update logic is created for the DataAdapter. A DataTable is filled from the first worksheet, Sheet1, in the Excel workbook and the default view of the table is bound to a data grid on the form.
Update Button.Click
Uses the DataAdapter created in the Form.Load event handler to update the Excel workbook with the programmatic changes.

The C# code is shown here.
Example- File: ExcelForm.cs// Namespaces, Variables, and Constants
using System;
using System.Configuration;
using System.Data;
private OleDbDataAdapter da;
private DataTable dt;
// . . .
private void ExcelForm_Load(object sender, System.EventArgs e)
{
// Create the DataAdapter.
da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",
ConfigurationSettings.AppSettings["Excel_0115_ConnectString"]);
// Create the insert command.
String insertSql = "INSERT INTO [Sheet1$] " +
"(CategoryID, CategoryName, Description) " +
"VALUES (?, ?, ?)";
da.InsertCommand =
new OleDbCommand(insertSql, da.SelectCommand.Connection);
da.InsertCommand.Parameters.Add("@CategoryID", OleDbType.Integer, 0,
"CategoryID");
da.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15,
"CategoryName");
da.InsertCommand.Parameters.Add("@Description", OleDbType.VarChar, 100,
"Description");
// Create the update command.
String updateSql = "UPDATE [Sheet1$] " +
"SET CategoryName=?, Description=? " +
"WHERE CategoryID=?";
da.UpdateCommand =
new OleDbCommand(updateSql, da.SelectCommand.Connection);
da.UpdateCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15,
"CategoryName");
da.UpdateCommand.Parameters.Add("@Description", OleDbType.VarChar, 100,
"Description");
da.UpdateCommand.Parameters.Add("@CategoryID", OleDbType.Integer, 0,
"CategoryID");
// Fill the table from the Excel spreadsheet.
dt = new DataTable( );
da.Fill(dt);
// Define the primary key.
dt.PrimaryKey = new DataColumn[] {dt.Columns[0]};
// Records can only be inserted using this technique.
dt.DefaultView.AllowDelete = false;
dt.DefaultView.AllowEdit = true;
dt.DefaultView.AllowNew = true;
// Bind the default view of the table to the grid.
dataGrid.DataSource = dt.DefaultView;
}
private void updateButton_Click(object sender, System.EventArgs e)
{
da.Update(dt);
}

You can use the Jet OLE DB provider to access Microsoft Excel as a 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. Excel 2000 and 2002 are supported with the Excel 8.0 source database type as shown in the following example:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myBook.xls;
Extended Properties="Excel 8.0;HDR=YES";

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.

There are three ways in which you can reference Excel workbook data within a SQL statement:
Specify the worksheet name followed by a dollar sign to access the entire range used in the worksheet:SELECT * FROM [MySheet$]
Specify a range explicitly using cells:SELECT * FROM [MySheet$A1:E5]
Specify a range with a defined name, as shown in the solution:SELECT * FROM MyRange
The following subsections discuss how to use Excel as an ADO.NET data source.

Create table
The CREATE TABLE command will create a table in an Excel workbook. The workbook for the connection will be created if it does not exist. For example:CREATE TABLE MySheet (Field1 char(10), Field2 float, Field3 date)

Create data
You can use the INSERT command, either static or parameterized, to insert data into a worksheet or range:INSERT INTO [MySheet$] (Field1, Field2, Field3)
VALUES ('testdata', 1.234, '09/28/1979');

Retrieve data
Use either a DataAdapter or a DataReader to retrieve data from an Excel workbook. Create a SQL SELECT statement referencing a worksheet or a range in an Excel workbook and execute the statement to fill a DataSet using a DataAdapter or to create a DataReader. For example:SELECT * FROM [MySheet$]

Update data
The UPDATE command, either static or parameterized, can update data in a worksheet or range. For example:UPDATE [MySheet$]
SET Field2 = '2.345',
Field3 = '10/18/1964'
WHERE
Field1 = 'testdata'

Delete data
The Jet OLE DB provider does not allow DELETE operations. An error will be raised if an attempt is made to execute a DELETE statement affecting one or more records.

Connecting to an ODBC Data Source

2008-09-17T12:29:00.001+05:30

Use the ODBC .NET data provider to access data exposed through an ODBC driver.
The sample code contains a single event handler:
Connect Button.Click
Creates an OdbcDataAdapter and uses it to fill a DataTable with the Category table from the Northwind sample database. The default view of the table is bound to a data grid on the form.

The C# code is shown here.
Example File: OdbcConnectForm.cs// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.Odbc;
// . . .
private void connectButton_Click(object sender, System.EventArgs e)
{
// Create the DataAdapter.
String sqlSelect = "SELECT CategoryID, CategoryName, Description " +
"FROM Categories";
OdbcDataAdapter da = new OdbcDataAdapter(sqlSelect,
ConfigurationSettings.AppSettings["Odbc_ConnectString"]);
// Create the table, fill it, and bind the default view to the grid.
DataTable dt = new DataTable( );
da.Fill(dt);
dataGrid.DataSource = dt.DefaultView;
}

The ODBC .NET data provider communicates with native ODBC drivers through COM interop (.NET's interoperability layer for COM). The following ODBC providers are guaranteed to be compatible with the ODBC.NET data provider:
Microsoft SQL Server ODBC Driver
Microsoft ODBC Driver for Oracle
Microsoft Access (Jet) ODBC Driver

The .NET data provider for ODBC connects to ODBC data sources through the OdbcConnection object. The ODBC driver connection string is specified using the ConnectionString property. It includes settings needed to establish the connection to the data source. The connection string format closely matches the ODBC connection string format. Additionally, you can specify an ODBC data source name (DSN) or file DSN by setting the ConnectionString attribute to "DSN=myDSN" or "FileDSN=myFileDSN". For more information about specifying ODBC connection strings, see the topic "SQLDriverConnect" in the ODBC Programmer's Reference within MSDN Library.
Visual Studio also supports creating ODBC data source connections visually:
Create a data connection in Server Explorer and drag it onto a form or design surface. Configure the OdbcConnection object that appears in the component tray.
Drag an OdbcConnection from the Data tab of the Toolbox onto a form or design surface. Configure the ConnectionString property in the Properties window of the OdbcConnection object that appears.

The .NET ODBC data provider requires a reference to the System.Data.Odbc namespace in .NET Framework Version 1.1. In Version 1.0, the namespace is Microsoft.Data.Odbc. Add a .NET Reference to Microsoft.Data.Odbc.dll for a .NET Framework Version 1.0 project.
The .NET ODBC .NET data provider ships with .NET Framework Version 1.1. The data provider can be downloaded from http://msdn.microsoft.com/downloads for .NET Framework Version 1.0.