UVc.SqlMirror Class: Copy rows from all DataTables within a DataSet using SqlBulkCopy
UVcSQLMirror Class Documentation
UVc.SqlMirror Class Reference
Provides functionality for mirroring data from a DataSet into a SQL server table using the SqlBulkCopy class. The Mirror method performs an iterative copy over the items contained in the DataSet's Tables collection. The List IncludeTables property specifies the names of DataTable objects to include from the DataSet's Tables collection. The List IgnoreTables specifies the names of DataTable objects to exclude. If you have already specified the IncludeTables list, then an exception is thrown if you try to access the IgnoreTables list when IncludeTables.Count > 0. Similarly, an exception is thrown when you try to access IncludeTables when IgnoreTables.Count > 0.
Public Member Functions
· delegate bool PreCopyDataTableHandler (SqlMirror sqlMirror, DataTable table)
Delegate signature for the OnPreCopyDataTable event.
· void Mirror ()
Go through each data table and copy it to the target server.
Properties
· DataSet DataSource
The DataSet to copy contents of DataTables from.
· SqlBulkCopy BulkCopy
The SqlBulkCopy instance to use to perform the copy operations.
· List< string > IgnoreTables
Specifies a list of tables to ignore from DataSource when copying.
· List< string > IncludeTables
Specifies a list of tables to include from DataSource when copying.
· event PreCopyDataTableHandler PreCopyDataTable
Event that is raised when a DataTable is about to be copied to the destination server.
Member Function Documentation
delegate bool UVc.SqlMirror.PreCopyDataTableHandler (SqlMirror sqlMirror, DataTable table)
Delegate signature for the OnPreCopyDataTable event.
Parameters:
sqlMirror The SqlMirror object that is about to copy data
table The target DataTable that is about to be copied
Returns:
Boolean. True indicates that the copy was handled or ignored by the delegate. False means that default processing should occur.
Here is a sample of how to use the code:
// Use a bulk copy command to ipmort data into the target system
SqlBulkCopyOptions opts = SqlBulkCopyOptions.KeepIdentity;
SqlBulkCopy bcp = new SqlBulkCopy(destConnString, opts);
// Use a SqlMirror class to iterate each item of a DataSet
// for the SqlBulkCopy instance to copy.
SqlMirror mirror = new SqlMirror(destDS, bcp);
// Include only this table:
//mirror.IncludeTables.Add("Report");
// Note that Trying to ignore this will generate an exception
// since the IncludeTables has already been added to
//mirror.IgnoreTables.Add("ReportHistory");
// If you comment out below, then this will generate an
// exception if the comments above are removed
//mirror.IncludeTables.Add("ReportProperties");
// Specify a call back to use to transform values of
// the tblReport table through code
mirror.PreCopyDataTable += delegate(SqlMirror sqlMirror, DataTable table)
{
if (table.TableName == "Report")
{
// Modify a row
Reports.ReportRow row =
table.Rows[0] as Reports.ReportRow;
// Set the ID to me instead of J.W.
row.contributor_id = 44;
// Return false to indicate that
// the default copy behavior should now occur
return false;
}
// If it's not Reports, don't handle it
return false;
};
mirror.Mirror();
And here is the code itself:
/**
* Permission to use or modify this code is granted for any purpose.
* Author: Josh Gough
*/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Xml;
using System.Text;
namespace UVc
{
///
/// Provides functionality for mirroring data from a DataSet into a
/// SQL server table using the SqlBulkCopy class. The Mirror method
/// performs an iterative copy over the items contained in the
/// DataSet's Tables collection. The List IncludeTables property specifies
/// the names of DataTable objects to include from the DataSet's Tables
/// collection. The List IgnoreTables specifies the names of DataTable
/// objects to exclude. If you have already specified the IncludeTables
/// list, then an exception is thrown if you try to access the
/// IgnoreTables list when IncludeTables.Count > 0. Similarly, an
/// exception is thrown when you try to access IncludeTables when
/// IgnoreTables.Count > 0.
///
public class SqlMirror
{
public SqlMirror()
{
this.Init();
}
public SqlMirror(DataSet ds, SqlBulkCopy bcp)
{
this.DataSource = ds;
this.BulkCopy = bcp;
this.Init();
}
private void Init()
{
this.IgnoreTables = new List<string>();
this.IncludeTables = new List<string>();
}
private DataSet _dataSource;
///
/// The DataSet to copy contents of DataTables from.
///
public DataSet DataSource
{
get { return _dataSource; }
set { _dataSource = value; }
}
private SqlBulkCopy _bulkCopy;
///
/// The SqlBulkCopy instance to use to perform the copy operations
///
public SqlBulkCopy BulkCopy
{
get { return _bulkCopy; }
set { _bulkCopy = value; }
}
private List<string> _ignoreTables;
///
/// Specifies a list of tables to ignore from DataSource when copying
///
public List<string> IgnoreTables
{
get
{
// Throw an exception if the IncludeTables count is
// non zero
if (this._includeTables.Count > 0)
{
throw new SqlMirrorCountNonZeroException(
"Could not access IgnoreTables property because " +
"the IncludeTables property already has a nonzero " +
"count");
}
else
{
return _ignoreTables;
}
}
set { _ignoreTables = value; }
}
private List<string> _includeTables;
///
/// Specifies a list of tables to include from DataSource when
/// copying
///
public List<string> IncludeTables
{
get
{
// Throw an exception if the IgnoreTables count is
// non zero
if (this._ignoreTables.Count > 0)
{
throw new SqlMirrorCountNonZeroException(
"Could not access IncludeTables property because " +
"the IgnoreTables property already has a nonzero " +
"count");
}
return _includeTables;
}
set { _includeTables = value; }
}
///
/// Delegate signature for the OnPreCopyDataTable event.
///
/// The SqlBulkCopy object that is
/// about to copy data
/// The target table name that is
/// about to be copied
/// Boolean. True indicates that the copy was handled or
/// ignored by the delegate. False means that default processing
/// should occur.
public delegate bool PreCopyDataTableHandler(SqlMirror sqlMirror,
DataTable table);
///
/// Event that is raised when a DataTable is about to be copied to
/// the destination server.
///
public event PreCopyDataTableHandler PreCopyDataTable;
private bool OnPreCopyDataTable(DataTable table)
{
if (PreCopyDataTable != null)
return PreCopyDataTable(this, table);
return false;
}
///
/// Go through each data table and copy it to the target server
///
public void Mirror()
{
if (this.IncludeTables.Count > 0)
{
foreach (string tableName in this.IncludeTables)
{
DataTable dt = _dataSource.Tables[tableName];
if (
dt != null
&&
dt.Rows.Count > 0
)
{
&