ADO.NET Slackers All concepts covered

Testing for No Records in DB

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

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

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

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

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

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

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

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

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

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

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

Table 1. TableNameDataTable methods

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

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

Table 2. TableNameRow class properties and methods

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

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

Table 3. TableNameRowChangeEvent properties

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

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

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

The disadvantages of a strongly typed DataSet object include:

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

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

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

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

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

Using the TypedDataSetGenerator class to generate a typed DataSet

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

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

The arguments of the method are:

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

Using an XSD schema file to generate a typed DataSet

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

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

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

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

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

xsd mySchemaFile.xsd /d /l:CS

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

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

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

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

Building a DataSet Programmatically

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

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

The C# code is shown below.

File: BuildDataSetProgramaticallyForm.cs


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

//  . . . 

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

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

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

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

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

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

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

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

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

Discussion

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

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

Create a new DataSet, naming it in the constructor.

Create a new DataTable, naming it in the constructor.

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

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

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

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

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

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

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

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

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

Retrieving Hierarchical Data into a DataSet

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

The sample code contains three event handlers:

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

The C# code is shown in the below example.

Example- File: HierarchicalDataSetForm.cs

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

private DataSet ds;

//  . . . 

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

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

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

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

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

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

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

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

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

ds.EnforceConstraints = true;
}

Discussion

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

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

Connecting to a Text File

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

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

Example-. File: Categories.txt

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

The C# code is shown here.

Example- File: ConnectTextFileForm.cs

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

//  . . . 

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

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

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

Discussion

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

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

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

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

[Categories.txt]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=0
Character=OEM
Col1=CategoryID Long Width 4 
Col2=CategoryName Text Width 15
Col3=Description Text Width 100

The schema.ini file provides the following schema information about the data in the text file:

Filename
File format
Field names, widths, and data types
Character set
Special data type conversions

The first entry in the schema.ini file is the text filename enclosed in square brackets. For example:

[Categories.txt]

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

Table 1. Schema.ini format options

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

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

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

The MaxScanRows option indicates how many rows should be scanned to automatically determine column type. A value of 0 indicates that all rows should be scanned.

The ColN entries specify the name, width, and data type for each column. This entry is required for fixed-length formats and optional for character-delimited formats. The syntax of the ColN entry is:

ColN=columnName dataType [Width n]

The parameters in the entry are:

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

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

Technorati : Connecting Text File in ADO.NET

Changing the Database for an Open Connection

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

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

The C# code is shown here.

Example 1-12. File: ChangeDatabaseForm.cs


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

//  . . . 

StringBuilder result = new StringBuilder( );

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

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

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

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

resultTextBox.Text = result.ToString( );

Discussion

The ChangeDatabase( ) method is defined in the IDbConnection interface that represents a connection to a data source and is implemented by .NET data providers for relational databases including those for SQL Server, Oracle, and OLE DB. The ChangeDatabase( ) method is used to change the current database for an open connection. It takes a single parameter that specifies the name of the database to use in place of the current database. The name of the database must be valid or an ArgumentException will be raised. If the connection is not open when the method is called, an InvalidOperationException is raised. A provider-specific exception is raised if the database cannot be changed for any reason.

The Database property of the Connection object is updated dynamically and returns the current database for an open connection or the name of a database that will be used by a closed connection when it is opened.

When the Connection is closed after ChangeDatabase( ) is called, the database is reset to that specified in the original connection string.

Technorati : Changing DB in ADO.NET

Using Transactions with Pooled Connections

Connections participating in transactions are drawn from the connection pool and assigned based on an exact match with the transaction context of the requesting thread and with the connection string.

Each connection pool is divided into a subdivision for connections without a transaction context and zero or more subdivisions for connections associated with a particular transaction context. Each of these subdivisions, whether associated with a transaction context or not, uses connection pooling based on exact matching of the connection string as described here.

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

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

Technorati : Using Transactions

Setting Connection Pooling Options

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

The sample code contains a method and four event handlers:

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

The C# code is shown here.

Example-File: ConnectionPoolingOptionsForm.cs

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

private SqlConnection conn;

//  . . . 

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

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

UpdateConnectionString( );
}

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

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

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

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

Discussion

The following subsections describe how to control connection pooling for SQL Server, Oracle, OLE DB, and ODBC .NET data providers.

SQL Server

The connection string attributes that control connection pooling for the SQL Server .NET data provider are described below.

Table1 . SQL Server connection string pooling attributes

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

Oracle

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

Table2. Oracle connection string pooling attributes

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

OLE DB

The OLE DB .NET data provider uses resource pooling support provided by the OLE DB Service component. You can override the default OLE DB provider services by specifying a value for the OLE DB Services attribute in the connection string. OLE DB Resource pooling configuration is controlled using registry entries. There is no user interface to configure these entries-the registry must be edited directly. The registry entries are identified by the <Provider's CLSID>. CLSID values for some Microsoft OLE DB providers are:

SQLOLEDB (SQL Server):

HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}

Microsoft.Jet.OLEDB.4.0 (Jet):

HKEY_CLASSES_ROOT\CLSID\{dee35070-506b-11cf-b1aa-00aa00b8de95}

MSDAORA (Oracle):

HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}

MSDASQL (OLE DB Provider for ODBC):

HKEY_CLASSES_ROOT\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}

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

HKEY_CLASSES_ROOT\CLSID\<Provider's CLSID>\SPTimeout

The session pooling timeout is the number of seconds that an unused session remains in the pool before timing out and being closed. This is a DWORD value with a default of 60 if the registry entry is not specified.

The following registry entries are global to all providers:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Session Pooling\Retry Wait: The amount of time that the service component will wait until attempting to contact the server again in the event of a failed connection attempt. This is a DWORD value with a default of 64 if no registry value is present.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Session Pooling\ExpBackOff: Determines the factor by which the service components will wait between reconnect attempts in the event of a failed connection attempt. This is a DWORD value with a default of 2 if no registry value is present.
HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}: A DWORD value that specifies the maximum lifetime in seconds of a pooled connection. The default is 600. The CLSID is for the MSDAINITIALIZE component, which is the OLE DB service component manager that is used to parse OLE DB connection strings and initialize the appropriate provider.

ODBC

The ODBC .NET data provider uses the connection pooling support provided by the ODBC Driver Manager (DM). Connection pooling is supported by Version 3.0 or later of the ODBC DM; the version of the ODBC driver does not matter.

The following two registry settings control ODBC connection pooling:

Wait Retry: The time in seconds that that the pool is blocked when the server is not responding. This setting affects all applications using the ODBC driver. The registry key specifies a REG_SZ value:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\<Driver_Name>\CPTimeout

CPTimeout: The time in seconds that unused connections remain in the pool. This setting affects all ODBC drivers on the system. The registry key specifies a REG_SZ value:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Connection Pooling

You can control ODBC connection pooling in three ways:

Using the ODBC Data Source Administrator to enable or disable pooling for the entire driver, and to control the CPTimeout and Wait Retry settings
Editing the registry settings described above.
Using the ODBC API to control pooling options from an ODBC application. For more information about the ODBC API, see the ODBC Programmer's Reference in the MSDN Library.

Technorati : Collection Pooling in ADO.NET

Taking Advantage of Connection Pooling in ADO.NET

Connection pooling allows an application to reuse connections from a pool instead of repeatedly creating and destroying new connections. Connection pooling can significantly improve the performance and scalability of applications by allowing a smaller number of connections to service the connection requirements of an application and because the overhead of establishing a new connection is eliminated.

A connection pool is created for each unique connection string. An algorithm associates items in the pool based on an exact match with the connection string; this includes capitalization, order of name value pairs, and even spaces between name/value pairs. Dynamically generated connection strings must be identical so that connection pooling is used. If delegation is used, there will be one pool per delegate user. When transactions are used, one pool is created per transaction context. When the connection pool is created, connection objects are created and added to the pool to satisfy the minimum pool size specified.

When a connection is requested by an application and the maximum pool size has been reached, the request is queued. The request is satisfied by reallocating a connection that is released back to the pool when the Connection is closed or disposed. The connection pool manager removes expired connections and connections that have had their connection with the server severed from the pool.

The Connection object should be closed as soon as it is no longer needed so that it is added to or returned to the connection pool. This is done by calling either the Close( ) or Dispose( ) method of the Connection. Connections that are not explicitly closed might not be added to or returned to the connection pool.

SQL Server and Oracle

The .NET data providers for SQL Server and Oracle provide efficient, transaction-aware support for connection pooling. Pools are created for each process and not destroyed until the process ends. Connection pooling is enabled by default.

Controlling SQL Server and Oracle .NET data provider connection pooling with connection string attribute/value pairs.

OLE DB

The OLE DB .NET data provider pools connections by using resource pooling provided by the OLE DB core components.

The default OLE DB services that are enabled for a provider are specified by the value for the registry HKEY_CLASSES_ROOT\CLSID\<Provider's CLSID>\OLE_DBSERVICES DWORD value. Table1 describes the alternatives.

Table 1. OLE DB services enabled values

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

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

Table 2. OLE DB services connection string values

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

The following three configurable settings control OLE DB connection pooling:

SPTimeout: The length of time in seconds that an unused connection remains in the pool before it is released. This can be configured for each provider and defaults to 60 seconds.
Retry Wait: The length of time in seconds before an attempt to acquire a connection is reattempted when the server is not responding. This is global to all providers and defaults to 64 seconds.
ExpBackOff: The factor by which the retry wait time is increased when a connection attempt fails before reattempting the connection. This is global to all providers and defaults to a factor of 2.

OLE DB connection pooling is enabled by default; you can control it in three different ways:

Specify a value for the OLE DB Services attribute in the connection string.
Edit the registry to enable or disable pooling for an individual provider or globally by changing registry values.
Use the OLE DB API (Application Programming Interface) from an application to enable or disable connection pooling. The SPTimeout and Retry Wait can be configured programmatically only by manipulating the registry entries. For more information about the OLE DB API, see the OLE DB Programmer's Reference in MSDN Library.

ODBC

The ODBC .NET data provider pools connections by using the connection pooling provided by the ODBC Driver Manager (DM). Pooling parameters for an ODBC driver affect all applications that use that driver, unless changed from within a native ODBC application.

The following two configurable settings control ODBC connection pooling:

CPTimeout: The length of time in seconds that an unused connection remains in the pool before it is released.
Wait Retry: The length of time before an attempt to acquire a connection is reattempted when the server is not responding.

Connection pooling is enabled by default. You can enable, disable, and configure it in three ways:

Use the ODBC Data Source Administrator, introduced with ODBC 3.5 (MDAC 1.5), to enable or disable pooling for the entire driver and to control the CPTimeout and Wait Retry settings.
Edit the registry.
Use the ODBC API from an ODBC application to limit the scope of pooling to the environment handler or to the driver, and to configure other pooling options. For more information about the ODBC API, see the ODBC Programmer's Reference in the MSDN Library.

Technorati : Connection Pooling in ADO.NET

Subscribe

Example: File: NoRecordTestForm.cs

Example: Stored procedure: SP0210_Raiserror

Example: Stored procedure: SP0209_OutputValueWithDataReader

Example: File: SpOutputValueDataReaderForm.cs

Example. File: DataReaderRowCountForm.cs

Discussion

Discussion

Table 1. DataRowState enumeration

Table 2. DataRowVersion enumeration

Table 3. DataViewRowState enumeration

Discussion

DataSet

DataReader

Table 1. TableNameDataTable methods

Table 2. TableNameRow class properties and methods

Table 3. TableNameRowChangeEvent properties

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

Using the TypedDataSetGenerator class to generate a typed DataSet

Using an XSD schema file to generate a typed DataSet

File: BuildDataSetProgramaticallyForm.cs

Discussion

Example- File: HierarchicalDataSetForm.cs

Discussion

Example-. File: Categories.txt

Example- File: ConnectTextFileForm.cs

Discussion

Table 1. Schema.ini format options

Example 1-12. File: ChangeDatabaseForm.cs

Discussion

Example-File: ConnectionPoolingOptionsForm.cs

Discussion

SQL Server

Table1 . SQL Server connection string pooling attributes

Oracle

Table2. Oracle connection string pooling attributes

OLE DB

ODBC

SQL Server and Oracle

OLE DB

Table 1. OLE DB services enabled values

Table 2. OLE DB services connection string values

ODBC

Archives

Label Cloud