ONDotNet.com    
 Published on ONDotNet.com (http://www.ondotnet.com/)
 See this if you're having trouble printing code examples


O'Reilly Book Excerpts: ADO.NET Cookbook

Cooking with ADO.NET

by Bill Hamilton

Related Reading

ADO.NET Cookbook
By Bill Hamilton

Editor's note: O'Reilly's recently released ADO.NET Cookbook contains more than 150 solutions and best practices for everyday dilemmas. This week, we're excerpting three sample recipes from the book that show how to asynchronously update data in a database, how to protect login credentials during network transmissions, and how to enforce a business rule with column expressions.

Recipe 4.13: Updating Data Asynchronously Using Message Queuing

Problem

You need to asynchronously update data on a database on system that is not always connected.

Solution

You must use message queuing and an XML DiffGram to:

The sample code contains four event handlers:

Form.Load

Sets up the sample by loading a DataSet with the Customers table from the Northwind database. The default view of the table is bound to a data grid on the form.

Send Update Button.Click

Checks if the update message queue exists and creates it if necessary. A MessageQueue object is created to access the queue. A message is created containing a DiffGram of the changes made to the DataSet containing the Customers table. This message is sent to the update queue.

Process Update Button.Click

Checks if the update message queue exists and creates it if necessary. A MessageQueue object is created to access the queue. An attempt is made to receive a message from the queue, waiting one second before giving up. If a message is received, a DataAdapter that uses a CommandBuilder to generate updating logic is created and used to update the database with the changes in the DataSet contained in the body of the message. The latest version of the Customers table is retrieved into a new DataSet. A result queue is created if necessary and a message containing the latest DataSet is sent to the queue.

Receive Update Button.Click

Checks if the result message queue exists and creates it if necessary. A MessageQueue object is created to access the queue and the formatter set to deserialize the DataSet in the message bodies. An attempt is made to receive a message from the queue, waiting one second before giving up. If a message is received, the DataSet in the body is deserialized and the default view of the Categories DataTable is bound to the data grid on the form.

The C# code is shown in Example 4-32.

Example 4-32. File: MessageQueueUpdateForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Messaging;
using System.Data;
using System.Data.SqlClient;

private const String CUSTOMERS_TABLE = "Customers";

private const String QUEUENAMEUPDATE = @".\Private$\adodotnetcb0413update";
private const String QUEUENAMERESULT = @".\Private$\adodotnetcb0413result";

private DataSet ds;

//  . . . 

private void MessageQueueUpdateForm_Load(object sender, System.EventArgs e)
{
    // As a starting point, load the data directly.
    // Create the DataAdapter to load customers data.
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    // Get the schema and data for the customers table.
    ds = new DataSet( );
    da.FillSchema(ds, SchemaType.Source, CUSTOMERS_TABLE);
    da.Fill(ds, CUSTOMERS_TABLE);

    // Bind the default view of the customers table to the grid.
    dataGrid.DataSource = ds.Tables[CUSTOMERS_TABLE].DefaultView;
}

private void sendUpdateButton_Click(object sender, System.EventArgs e)
{
    // Create the result queue if it does not exist.
    if(!MessageQueue.Exists(QUEUENAMEUPDATE))
        MessageQueue.Create(QUEUENAMEUPDATE);

    // Create an object to access the result queue.
    MessageQueue mq = new MessageQueue(QUEUENAMEUPDATE);
    // Set the formatter for serialization of message bodies.
    mq.Formatter = new XmlMessageFormatter(new Type[] {typeof(DataSet)});

    // Create a message containing the changes.
    mq.Send(ds.GetChanges( ));

    MessageBox.Show("Update message sent.","MessageQueue Update",
        MessageBoxButtons.OK, MessageBoxIcon.Information);
}

private void processUpdateButton_Click(object sender, System.EventArgs e)
{
    // Create the result queue if it does not exist.
    if(!MessageQueue.Exists(QUEUENAMEUPDATE))
        MessageQueue.Create(QUEUENAMEUPDATE);

    // Create an object to access the result queue.
    MessageQueue mq = new MessageQueue(QUEUENAMEUPDATE);
    // Set the formatter for deserialization of message bodies.
    mq.Formatter = new XmlMessageFormatter(new Type[] {typeof(DataSet)});

    // Receive a message from the query queue.
    System.Messaging.Message msg;
    try
    {
        msg = mq.Receive(new TimeSpan(0,0,1));
    }
    catch(MessageQueueException ex)
    {
        MessageBox.Show(ex.Message, "MessageQueue Receive Error",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
        return;
    }

    // Create the DataAdapter and CommandBuilder to update.
    SqlDataAdapter da =
        new SqlDataAdapter("SELECT * FROM " + CUSTOMERS_TABLE,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    SqlCommandBuilder cb = new SqlCommandBuilder(da);

    if (msg.BodyStream.Length > 0)
    {
        // Get the DataSet of changes from the message body.
        DataSet dsChanges = (DataSet)msg.Body;
        
        // Process the updates.
        da.Update(dsChanges, CUSTOMERS_TABLE);
    }

    // Get the updated DataSet.
    DataSet dsUpdate = new DataSet( );
    da.Fill(dsUpdate, CUSTOMERS_TABLE);

    // Create the result queue if it does not exist.
    if(!MessageQueue.Exists(QUEUENAMERESULT))
        MessageQueue.Create(QUEUENAMERESULT);

    // Create an object to access the result queue.
    mq = new MessageQueue(QUEUENAMERESULT);
    // Send a message with the update DataSet to the queue.
    mq.Send(dsUpdate);

    MessageBox.Show("Update processed. Refreshed DataSet sent.",
        "MessageQueue Process", MessageBoxButtons.OK,
        MessageBoxIcon.Information);
}

private void receiveUpdateButton_Click(object sender, System.EventArgs e)
{
    // Create the result queue if it does not exist.
    if(!MessageQueue.Exists(QUEUENAMERESULT))
        MessageQueue.Create(QUEUENAMERESULT);

    // Create an object to access the result queue.
    MessageQueue mq = new MessageQueue(QUEUENAMERESULT);
    // Set the formatter for deserialization of message bodies.
    mq.Formatter = new XmlMessageFormatter(new Type[] {typeof(DataSet)});

    // Receive a message from the result queue.
    System.Messaging.Message msg;
    try
    {
        msg = mq.Receive(new TimeSpan(0,0,1));
    }
    catch(MessageQueueException ex)
    {
        MessageBox.Show("ERROR: " + ex.Message, "MessageQueue Receive",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
        return;
    }

    // Refresh the DataSet underlying the DataGrid.
    ds = (DataSet)msg.Body;
    dataGrid.DataSource = ds.Tables[CUSTOMERS_TABLE].DefaultView;

    MessageBox.Show("Retrieved and loaded refreshed data.",
        "MessageQueue Receive", MessageBoxButtons.OK,
        MessageBoxIcon.Information);
}

Discussion

The discussion in Recipe 2.22 provides an overview of Message Queuing (MSMQ).

For more information about DiffGrams, see Recipe 8.8 in ADO.NET Cookbook.

Recipe 5.8: Transferring Login Credentials Securely

Problem

You need to protect login credentials during transmission over the network and when they are stored within a database.

Solution

Use password hashing and salting with the .NET FormsAuthentication class to control user authentication and access to the application.

The schema of table TBL0508 used in this solution is shown in Table 5-5.

Table 5-5. TBL0508 schema

Column name

Data type

Length

Allow nulls?

UserName

nvarchar

50

No

PasswordHash

nvarchar

50

No

PasswordSalt

nvarchar

50

No

The sample code contains two event handlers:

Create Button.Click

Creates a GUID-based salt and generates a hash of the password concatenated with the salt for a user-specified password. The username, password hash, and salt are inserted into a database.

Login Button.Click

Retrieves the salt and the hash of the password and salt from the database for the specified username. The user-entered password is concatenated with the retrieved salt and the hash is generated. If the hash matches the hash retrieved from the database, the user is authenticated.

The C# code is shown in Example 5-8.

Example 5.8. File: ADOCookbookCS0508.aspx.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Web.Security;
using System.Data;
using System.Data.SqlClient;
        
private const String TABLENAME = "TBL0508";

//  . . . 

private void createButton_Click(object sender, System.EventArgs e)
{
    // Create and display the password salt.
    String passwordSalt = Guid.NewGuid().ToString( );
    passwordSaltLabel.Text = passwordSalt;

    // Create and display the password hash.
    String passwordHash =
        FormsAuthentication.HashPasswordForStoringInConfigFile(
        passwordTextBox.Text + passwordSalt, "md5");
    passwordHashLabel.Text = passwordHashDBLabel.Text = passwordHash;

    // Insert UserName with the password hash and salt into the database.
    String sqlText = "INSERT " + TABLENAME +
        "(UserName, PasswordHash, PasswordSalt) " +
        "VALUES ('" + userNameTextBox.Text + "', '" + passwordHash +
        "', '" + passwordSalt + "')";
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["DataConnectString"]);
    SqlCommand cmd = new SqlCommand(sqlText, conn);
    conn.Open( );

    try
    {
        if(cmd.ExecuteNonQuery( ) == 1)
            statusLabel.Text = "User created.";
        else
            statusLabel.Text = "Could not create user.";
    }
    catch(SqlException)
    {
        statusLabel.Text = "Could not create user.";
    }
    finally
    {
        conn.Close( );
    }
}

private void loginButton_Click(object sender, System.EventArgs e)
{
    bool isAuthenticated = false;

    // Get the password hash and salt for the user.
    String sqlText = "SELECT PasswordHash, PasswordSalt FROM " +
        TABLENAME + " WHERE UserName = '" + userNameTextBox.Text + "'";
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["DataConnectString"]);
    SqlCommand cmd = new SqlCommand(sqlText, conn);

    conn.Open( );
    SqlDataReader dr = cmd.ExecuteReader( );

    // Get the DataReader first row containing user's password and salt.
        if(dr.Read( ))
    {
        // Get and display password hash and salt from the DataReader.
        String passwordHashDB = passwordHashDBLabel.Text =
            dr.GetString(0);
        String passwordSalt = passwordSaltLabel.Text = dr.GetString(1);

        // Calculate password hash based on the password entered and
        // the password salt retrieved from the database.
        String passwordHash = passwordHashLabel.Text =
            FormsAuthentication.HashPasswordForStoringInConfigFile(
            passwordTextBox.Text + passwordSalt, "md5");

        // Check whether the calculated hash matches the hash retrieved
        // from the database.
        isAuthenticated = (passwordHash == passwordHashDB);
    }
    conn.Close( );

    if(isAuthenticated)
        statusLabel.Text = "Authentication succeeded.";
    else
        statusLabel.Text = "Authentication failed.";
}

Discussion

Persisting a user's password can be made more secure by first hashing the password. This means that an algorithm is applied to the password to generate a one-way transformation—or hash—of the password making it statistically infeasible to recreate the password from the hash.

A hash algorithm creates a small binary value of fixed length from a larger binary value of an arbitrary length. The hash value is a statistically unique compact representation of the original data. A hash value can be created for and transmitted together with data. The hash can be recreated at a later time and compared to the original hash to ensure that the data has not been altered. To prevent the message from being intercepted and replaced along with a new hash, the hash is encrypted using the private key of an asymmetric key algorithm. This allows the hash to be authenticated as having come from the sender. For more information about symmetric and asymmetric key algorithms, see the discussion in Recipe 5.7. The .NET Framework classes that implement hash algorithms are:

In the sample, the user enters his password, the password is hashed, and then the combination of user ID and password hash are compared to values stored persistently such as in a database table. If the pairs match, the user is authenticated, without comparing the actual password. Because the hash algorithm is a one-way algorithm, the user's password cannot be recreated even if unauthorized access is gained to the persistent store where the user's password hash is stored.

The .NET Framework, as part of the FormsAuthentication class, provides the method HashPasswordForStoringInConfigFile( ) that can hash a password using either SHA1 or MD5 algorithms. The method is easy to call. It takes two arguments, the password and the hash algorithm, and returns a string containing the password hash.

Security is never perfect and this technique is no exception. It can be compromised by a dictionary attack where hash values for most commonly used passwords are generated. When these values are compared with the hash of the password and a match is found, the password is then known. To thwart the dictionary attack, a random string referred to as salt is concatenated with the original password before generating the hash value. The salt is stored together with the hash of the password and salt. This makes a dictionary attack much more difficult to perform.

This web page should be used in conjunction with forms-based authentication. Additionally, this page should be accessed securely (i.e., https to protect the plaintext password from client to server).

The most secure technique is useless if the password policy does not prevent users from choosing easy to guess passwords, or if security is compromised by users who write passwords down on notes attached to their computer monitors, for example.

Recipe 6.7: Enforcing Business Rules with Column Expressions

Problem

You need to enforce a business rule based on multiple columns in a table at the user interface tier.

Solution

Use expression-based columns to enforce business rules at the user interface tier. The business rule for this solution is that the sum of Field1 and Field2 for a row in the table must be 10.

The schema of table TBL0607 used in this solution is shown in Table 6-5.

Table 6-5. TBL0607 schema

Column name

Data type

Length

Allow nulls?

Id

int

4

No

Field1

nvarchar

4

No

Field2

nvarchar

4

No

The sample uses four stored procedures, which are shown in Example 6-21 through Example 6-24:

SP0607_Delete

Used to delete a record from the table TBL0607 for a specified Id

SP0607_Get

Used to retrieve a record for a specified Id or all records from the table TBL0607

SP0607_Insert

Used to insert a record into the table TBL0607

SP0607_Update

Used to update a record in the table TBL0607

Example 6-21. Stored procedure: SP0607_Delete
CREATE PROCEDURE SP0607_Delete
    @Id int
AS
    SET NOCOUNT ON
    
    delete
    from
        TBL0607
    where
        Id=@Id
        
    return 0
Example 6-22. Stored procedure: SP0607_Get
CREATE PROCEDURE SP0607_Get
    @Id int=null
AS
    SET NOCOUNT ON
    
    if @Id is not null
    begin
        select
            Id,
            Field1,
            Field2
        from
            TBL0607
        where
            Id=@Id
            
        return 0
    end
    
    select
        Id,
        Field1,
        Field2
    from
        TBL0607
        
    return 0
Example 6-23. Stored procedure: SP0607_Insert
CREATE PROCEDURE SP0607_Insert
    @Id int,
    @Field1 int,
    @Field2 int
AS
    SET NOCOUNT ON
    
    insert TBL0607(
        Id,
        Field1,
        Field2)
    values (
        @Id,
        @Field1,
        @Field2)
    
    if @@rowcount=0
        return 1
            
    return 0
Example 6-24. Stored procedure: SP0607_Update
CREATE PROCEDURE SP0607_Update
    @Id int,
    @Field1 int,
    @Field2 int
AS
    SET NOCOUNT ON
    
    update
        TBL0607
    set
        Field1=@Field1,
        Field2=@Field2
    where
        Id=@Id
    
    if @@rowcount=0
        return 1
    
    return 0

The sample code contains three event handlers:

Form.Load

Sets up the sample by creating a DataTable and creating a schema for it matching TBL0607 in the database. An expression column is added to the table. The calculation returns a Boolean value indicating whether the sum of Field1 and Field2 is equal to 10. A DataAdapter is created and event handler is attached to handle its RowUpdating event. Delete, insert, and update commands using the stored procedures in this solution are created for the DataAdapter. The DataAdapter is used to fill the table and its default view is bound to the data grid on the form.

DataAdapter.RowUpdating

Checks whether a row is being updated or inserted and whether the value of the expression column is false indicating that the data is invalid according to the business rule defined by the expression in the column. If the business rule has not been met, an error is set on the row and the update for the row is skipped.

Update Button.Click

Uses the DataAdapter to update changes made to the DataTable back to table TBL0607 in the database.

The C# code is shown in Example 6-25.

Example 6-25. File: EnforceBusinessRulesWithColumnExpressionsForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

private DataTable dt;
private SqlDataAdapter da;

private const String TABLENAME             = "TBL0607";

// Table column name constants 
private const String ID_FIELD              = "Id";
private const String FIELD1_FIELD          = "Field1";
private const String FIELD2_FIELD          = "Field2";
private const String CONSTRAINT_EXPRESSION = "ConstraintExpression";

// Stored procedure name constants
private const String DELETE_SP             = "SP0607_Delete";
private const String GET_SP                = "SP0607_Get";
private const String INSERT_SP             = "SP0607_Insert";
private const String UPDATE_SP             = "SP0607_Update";

// Stored procedure parameter name constants for table
private const String ID_PARM               = "@Id";
private const String FIELD1_PARM           = "@Field1";
private const String FIELD2_PARM           = "@Field2";

//  . . . 

private void EnforceBusinessRulesWithColumnExpressionsForm_Load(
    object sender, System.EventArgs e)
{
    DataColumnCollection cols;

    // Build the table.
    dt = new DataTable(TABLENAME);
    cols = dt.Columns;
    cols.Add(ID_FIELD, typeof(Int32));
    cols.Add(FIELD1_FIELD, typeof(Int32));
    cols.Add(FIELD2_FIELD, typeof(Int32));
    // add the primary key
    dt.PrimaryKey = new DataColumn[] {cols[ID_FIELD]};
    // Expression to evaluate whether the sum of Field1 and Field2
    // equals 10
    cols.Add(CONSTRAINT_EXPRESSION, typeof(Boolean), FIELD1_FIELD +
        "+" + FIELD2_FIELD + " = 10");

    // Create the DataAdapter, handling the RowUpdating event.
    da = new SqlDataAdapter( );
    da.RowUpdating += new SqlRowUpdatingEventHandler(da_RowUpdating);
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);

    // Build the select command.
    SqlCommand selectCommand = new SqlCommand(GET_SP, conn);
    selectCommand.CommandType = CommandType.StoredProcedure;
    da.SelectCommand = selectCommand;

    // Build the delete command.
    SqlCommand deleteCommand = new SqlCommand(DELETE_SP, conn);
    deleteCommand.CommandType = CommandType.StoredProcedure;
    deleteCommand.Parameters.Add(ID_PARM, SqlDbType.Int, 0, ID_FIELD);
    da.DeleteCommand = deleteCommand;

    // Build the insert command.
    SqlCommand insertCommand = new SqlCommand(INSERT_SP, conn);
    insertCommand.CommandType = CommandType.StoredProcedure;
    insertCommand.Parameters.Add(ID_PARM, SqlDbType.Int, 0, ID_FIELD);
    insertCommand.Parameters.Add(FIELD1_PARM, SqlDbType.Int, 0,
        FIELD1_FIELD);
    insertCommand.Parameters.Add(FIELD2_PARM, SqlDbType.Int, 0,
        FIELD2_FIELD);
    da.InsertCommand = insertCommand;

    // Build the update command.
    SqlCommand updateCommand = new SqlCommand(UPDATE_SP, conn);
    updateCommand.CommandType = CommandType.StoredProcedure;
    updateCommand.Parameters.Add(ID_PARM, SqlDbType.Int, 0, ID_FIELD);
    updateCommand.Parameters.Add(FIELD1_PARM, SqlDbType.Int, 0,
        FIELD1_FIELD);
    updateCommand.Parameters.Add(FIELD2_PARM, SqlDbType.Int, 0,
        FIELD2_FIELD);
    da.UpdateCommand = updateCommand;

    // Fill the table.
    da.Fill(dt);

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

private void da_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
    // For insert or update statements, check that the
    // calculated constraint column is true.
    if((e.StatementType == StatementType.Insert ||
        e.StatementType == StatementType.Update) &&
        !(bool)e.Row[CONSTRAINT_EXPRESSION])
    {
        // Constraint has not been met.
        // Set an error on the row and skip it.
        e.Row.RowError = "Constraint error.";
        e.Status = UpdateStatus.SkipCurrentRow;
    }
}

private void updateButton_Click(object sender, System.EventArgs e)
{
    try
    {
        da.Update(dt);
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Discussion

The RowUpdating event of the DataAdapter occurs during the Update( ) method before the command to update a row is executed against the data source. The event fires with each row update attempt.

The RowUpdating event handler receives an argument of type RowUpdatingEventArgs that provides information specifically related to the event as described in Table 6-6.

Table 6-6. RowUpdatingEventArgs properties

Property

Description

Command

Gets the Command to execute during the Update( ) method.

Errors

Gets errors generated by the .NET data provider when the Command was executed.

Row

Gets the DataRow to send through the Update( ) method.

StatementType

Gets the type of SQL statement to execute. This is one of the following values from the StatementType enumeration: Select, Insert, Update, or Delete.

Status

Gets or sets the action to take with the current and remaining rows during the Update( ) method. This is a value from the UpdateStatus enumeration (described in Table 6-7).

TableMapping

Gets the DataTableMapping to send through the Update( ) method.

Table 6-7 describes the values in the UpdateStatus enumeration used by the Status property of the RowUpdatingEventArgs object.

Table 6-7. UpdateStatus enumeration

Value

Description

Continue

Continue processing the rows. This is the default value.

ErrorsOccurred

The event handler reports that the update should be treated as an error.

SkipAllRemainingRows

Do not update the current row and skip updating the remaining rows.

SkipCurrentRow

Do not update the current row and continue updating with the subsequent row.

The Update( ) method of the DataAdapter raises two events for every row in the data source that is updated. The order of the events is:

  1. The values in the DataRow are moved to parameter values.

  2. The OnRowUpdating event is raised.

  3. The update command executes against the row in the data source.

  4. If the UpdatedRowSource property of the Command is set to FirstReturnedRecord or Both, the first returned result is placed in the DataRow.

  5. If the UpdateRowSource property of the Command is set to OutputParameters or Both, the output parameters are placed in the DataRow.

  6. The OnDataRowUpdated event is raised.

  7. AcceptChanges( ) is called.

If zero rows are affected, the DBConcurrencyException is raised during the update operation on a row. This usually indicates a concurrency violation.

The solution uses the RowUpdating event of the DataAdapter to check whether the expression column in the DataTable is true, indicating that the business rule has been satisfied, before a database record is updated. If the expression if false, an error is set on the row and the Status is set to SkipCurrentRow preventing the record in the database from being updated and continuing the processing with the next row.

Stay tuned for more recipes from ADO.NET Cookbook in early January 2004.

Bill Hamilton is a technology and management consultant who specializes in assessing business objectives and company processes and designing and restructuring technology strategy and enterprise architecture.


Return to ONDotnet.com.

Copyright © 2009 O'Reilly Media, Inc.