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

Note: From May 2009 onwards SqlEx was incorporated into the main source code tree and is available through the standard EntLibContrib releases.

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 SqlExDatbase class.

<dataConfiguration defaultDatabase="Service_Dflt">
	<providerMappings>
		<add databaseType="EnterpriseLibraryContrib.Data.SqlEx.SqlExDatabase, EntLibContrib.Data.SqlEx, Version=3.1.1.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 15, 2009 at 1:38 PM by ewdev, version 6

Comments

XML Jul 17, 2008 at 11:20 AM 
are we going to use the Enterprise Library Configuration in our application. Does it have step by step tutorial on how to use it.

Ecosmose Mar 29, 2008 at 10:18 PM 
I explain more my problem on the Ent Lib 4.0 which appears to be more active...

http://www.codeplex.com/entlib/Wiki/View.aspx?title=EntLib4%20Backlog

Ecosmose Mar 28, 2008 at 10:30 PM 
Oh forget !

Thanks your Futurs answers (sorry)..

Ecosmose Mar 28, 2008 at 10:29 PM 
For the used Adapter into DAAB this default value are

AcceptChangesDuringFill = true;
AcceptChangesDuringUpdate = true;

I get some problems to keep my Changes in a DataSEt whan I use UpdateDataset whit a single DataTable..

This datatable is included in a dataset..
When I launch the SqlExDatabase.Updatedataset Methods with the Datatable I lose all the Rowstate of The OTHER Datatables in this DataSet..

How can I reslove that ? (keep the changes after launch SqlExDatabase.Updatedataset(MyDatatable, Commands, Transaction)?)