October 2008 in ADO.NET

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.

ADO.NET Slackers All concepts covered

Subscribe

Testing for No Records in DB

Example: File: NoRecordTestForm.cs

Raising and Handling Stored Procedure Errors

Example: Stored procedure: SP0210_Raiserror

Returning an Output Parameter Using DataReader

Example: Stored procedure: SP0209_OutputValueWithDataReader

Example: File: SpOutputValueDataReaderForm.cs

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

Counting Records in DataReader

Example. File: DataReaderRowCountForm.cs

Discussion

Accessing Deleted Rows in DataTable

Discussion

Table 1. DataRowState enumeration

Table 2. DataRowVersion enumeration

Table 3. DataViewRowState enumeration

Using Web Service as Data Source

Discussion

Processing a Batch SQL Statement

DataSet

DataReader

Archives

Label Cloud