Extended SQL Data Provider - SqlEx

Project Description

This provider extends the SqlDatabase provider included in the Enterprise Library Data Access Application Block. It provides additional overloads for the UpdateDataSet method so that a collection of rows or a table can be passed to the DataAdapter for update. These methods were developed to support updating datasets that have multiple tables and cascading hierarchies.

This was contributed by Chris Dufour, ASP.NET MVP https://mvp.support.microsoft.com/profile=DC965C02-3359-4F4A-A61E-EAF9FA2E2E61

Installation & Configuration

To use this module you will need to add a reference to the EntLibContrib.Data.SqlEx.dll assembly and modify your App.config or Web.config like the following to utilize the extended features that this module provides. All existing data access code can run 'as is' and you can access the new features by casting to the SqlExDatabase class.

<dataConfiguration defaultDatabase="Service_Dflt">
	<providerMappings>
		<add databaseType="EntLibContrib.Data.SqlEx.SqlExDatabase, EntLibContrib.Data.SqlEx, Version=4.1.0.0, Culture=neutral, PublicKeyToken=null"
			name="System.Data.SqlClient" />
	</providerMappings>
</dataConfiguration>

Using UpdateDataSet to persist a DataTable

The following code shows how to use the UpdateDataSet method.

[C#]
Database db = DatabaseFactory.CreateDatabase();

DataSet productsDataSet = new DataSet();

string sqlCommand = "Select Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate From Products";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

string productsTable = "Products";

// Retrieve the initial data.
db.LoadDataSet(dbCommand, productsDataSet, productsTable);

// Get the table that will be modified.
DataTable table = productsDataSet.Tables[productsTable];

// Add a new product to existing DataSet.
DataRow addedRow = table.Rows.Add(new object[] {DBNull.Value, "New product", 11, 25});

// Modify an existing product.
table.Rows[0]["ProductName"] = "Modified product";

// Establish the Insert, Delete, and Update commands.
DbCommand insertCommand = db.GetStoredProcCommand("AddProduct");
db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);
db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);

DbCommand deleteCommand = db.GetStoredProcCommand("DeleteProduct");
db.AddInParameter(deleteCommand , "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);

DbCommand updateCommand = db.GetStoredProcCommand("UpdateProduct");
db.AddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);
db.AddInParameter(updateCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
db.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current);

// Submit the DataTable, capturing the number of rows that were affected.
int rowsAffected = ((SqlExDatabase)db).UpdateDataSet(productsDataSet.Tables["Products"], insertCommand, updateCommand, deleteCommand, 
    Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard);


Using UpdateDataSet to persist an array of DataRows

The following code shows how to use the UpdateDataSet method.

[C#]
Database db = DatabaseFactory.CreateDatabase();

DataSet productsDataSet = new DataSet();

string sqlCommand = "Select Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate From Products";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

string productsTable = "Products";

// Retrieve the initial data.
db.LoadDataSet(dbCommand, productsDataSet, productsTable);

// Get the table that will be modified.
DataTable table = productsDataSet.Tables[productsTable];

// Add a new product to existing DataSet.
DataRow addedRow = table.Rows.Add(new object[] {DBNull.Value, "New product", 11, 25});

// Modify an existing product.
table.Rows[0]["ProductName"] = "Modified product";

// Establish the Insert, Delete, and Update commands.
DbCommand insertCommand = db.GetStoredProcCommand("AddProduct");
db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);
db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);

DbCommand deleteCommand = db.GetStoredProcCommand("DeleteProduct");
db.AddInParameter(deleteCommand , "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);

DbCommand updateCommand = db.GetStoredProcCommand("UpdateProduct");
db.AddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);
db.AddInParameter(updateCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
db.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current);

// Submit the DataTable, capturing the number of rows that were affected.
int rowsAffected = ((SqlExDatabase)db).UpdateDataSet(productsDataSet.Tables["Products"].Rows, insertCommand, updateCommand, deleteCommand, 
    Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard);


Update a DataSet with relationships

The following code shows how you can use the enhanced features to update a dataset that has multiple tables and cascading hierarchies.

[C#]
private DataSet Update(DataSet dataSet)
{
    SqlExDatabase dbx = (SqlExDatabase)db;

    //make a copy of the dataset to preserve state information in the dataset
    DataSet dataSetChanges = dataSet.Copy();

    using (DbConnection cn = dbx.CreateConnection())
    {
        cn.Open();
        DbTransaction tx = cn.BeginTransaction();

        try
        {
            // perform deletes on child tables - start with tables at the end of the relationship tree
            dbx.UpdateDataSet(dataSetChanges.Tables["Order Details"].Select("", "", DataViewRowState.Deleted),
                insertOrderDetailCommand, updateOrderDetailCommand, deleteOrderDetailCommand,
                tx);
            dbx.UpdateDataSet(dataSetChanges.Tables["Orders"].Select("", "", DataViewRowState.Deleted),
                insertOrderCommand, updateOrderCommand, deleteOrderCommand,
                tx);

            // update root table
            dbx.UpdateDataSet(dataSetChanges, "Customers",
                insertCustomerCommand, updateCustomerCommand, deleteCustomerCommand,
                tx);

            // perform all other updates on child tables - end with tables at the end of the relationship tree
            dbx.UpdateDataSet(dataSetChanges, "Orders",
                insertOrderCommand, updateOrderCommand, deleteOrderCommand,
                tx);
            dbx.UpdateDataSet(dataSetChanges, "Order Details",
                insertOrderDetailCommand, updateOrderDetailCommand, deleteOrderDetailCommand,
                tx);

            tx.Commit();
        }
        catch 
        {
            tx.Rollback();

            throw;
        }
        cn.Close();

        return dataSetChanges;
    }
}


Last edited Dec 22, 2009 at 8:14 PM by ewdev, version 2

Comments

No comments yet.