private void CopyTable(string sourceTableName, string targetTableName, NameValueCollection columnsMapping, string selPrefix, string selColumns, string selCondition, bool copyBinary, DBHelper source, DBHelper target, IList<StringCollection> nameLists, IList<Hashtable> valueHashTables, bool insertIdentity, Hashtable identities)
{
SendProgress("Copying data from table {0} to table {1}", sourceTableName, targetTableName);
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
DataTable dt;
Type dataType;
bool isIdentity, hasIdentity = false;
string selectCommand;
string insertCommand;
ArrayList columnTypes = new ArrayList();
ArrayList sourceColumns = new ArrayList();
ArrayList targetColumns = new ArrayList();
ArrayList sourceBinColumns = new ArrayList();
ArrayList targetBinColumns = new ArrayList();
Hashtable binIdentities = new Hashtable();
string sourceIdentityColumn = null, targetIdentityColumn = null;
int identityIndex = -1;
string sourceColumnName, targetColumnName;
// Get column names to be inserted
selectCommand = string.Format(CultureInfo.InvariantCulture, "SELECT {0}{1} FROM [{2}] {3}", selPrefix, selColumns, sourceTableName, selCondition);
using (IDataReader reader = source.RunTextDataReaderSchemaOnly(selectCommand))
{
dt = reader.GetSchemaTable();
foreach (DataRow row in dt.Rows)
{
sourceColumnName = (string)row["ColumnName"];
targetColumnName = GetTargetColumn(sourceColumnName, columnsMapping);
dataType = (Type)row["DataType"];
// Check if column exists and is identity
if (CheckColumn(target, cmd, targetTableName, targetColumnName, out isIdentity))
{
if (isIdentity)
{
hasIdentity = true;
sourceIdentityColumn = sourceColumnName;
targetIdentityColumn = targetColumnName;
}
if (dataType == typeof(byte[]))
{
sourceBinColumns.Add(sourceColumnName);
targetBinColumns.Add(targetColumnName);
}
else
{
columnTypes.Add(dataType);
sourceColumns.Add(sourceColumnName);
targetColumns.Add(targetColumnName);
}
}
}
}
// Build select command
sb.Append("SELECT ");
int n = 0;
foreach (string colName in sourceColumns)
{
if (n > 0)
sb.Append(", ");
sb.Append(selPrefix);
sb.Append("[");
sb.Append(colName);
sb.Append("]");
n++;
}
sb.Append(" FROM [");
sb.Append(sourceTableName);
sb.Append("] ");
sb.Append(selCondition);
// Build insert command
sb2.Append("INSERT INTO [");
sb2.Append(targetTableName);
sb2.Append("] (");
int j = n = 0;
foreach (string colName in targetColumns)
{
if (colName == targetIdentityColumn)
identityIndex = j;
if (insertIdentity || identityIndex != j)
{
if (n > 0)
sb2.Append(", ");
sb2.Append("[");
sb2.Append(colName);
sb2.Append("]");
n++;
}
j++;
}
sb2.Append(") VALUES (");
n = 0;
for (int i = 0; i < targetColumns.Count; i++)
{
if (insertIdentity || identityIndex != i)
{
if (n > 0)
sb2.Append(", ");
sb2.Append("@p");
sb2.Append(i);
n++;
}
}
sb2.Append(")");
if (hasIdentity && !insertIdentity)
sb2.Append(" SELECT @@IDENTITY");
selectCommand = sb.ToString();
insertCommand = sb2.ToString();
// Read data from source and insert into target
using (DBTransaction tran = target.BeginTransaction())
{
object identity;
using (IDataReader reader = source.RunTextDataReader(selectCommand))
{
dt = reader.GetSchemaTable();
if (insertIdentity && hasIdentity)
SetInsertIdentity(target, targetTableName, true);
try
{
cmd.CommandText = insertCommand;
while (reader.Read())
{
if (cmd.Parameters.Count == 0)
{
// Create parameters
for (int i = 0; i < targetColumns.Count; i++)
{
SqlParameter p = cmd.Parameters.AddWithValue(string.Format(CultureInfo.InvariantCulture, "@p{0}", i), GetColumnValue((string)sourceColumns[i], reader[i], nameLists, valueHashTables));
if (columnTypes[i] == typeof(Decimal))
p.DbType = DbType.Decimal;
}
}
else
{
// Set values
for (int i = 0; i < targetColumns.Count; i++)
{
cmd.Parameters[i].Value = GetColumnValue((string)sourceColumns[i], reader[i], nameLists, valueHashTables);
}
}
identity = target.RunCmdScalar(cmd);
if (hasIdentity)
{
object oldIdentity = reader[identityIndex];
if (insertIdentity)
binIdentities[oldIdentity] = oldIdentity;
else
binIdentities[oldIdentity] = identity;
if (!insertIdentity && identities != null)
identities[oldIdentity] = identity;
}
}
}
finally
{
if (insertIdentity && hasIdentity)
SetInsertIdentity(target, targetTableName, false);
}
}
tran.Commit();
}
// Copy binary and image fields
if (copyBinary)
{
if (sourceIdentityColumn != null && sourceBinColumns.Count > 0)
{
SqlParameter paramTextPtr = DBHelper.MP("@ptr", SqlDbType.VarBinary, 16, null);
SqlParameter paramData = new SqlParameter("@data", SqlDbType.VarBinary);
paramData.Value = _buffer;
SqlParameter paramId = new SqlParameter();
paramId.ParameterName = "@id";
foreach (object id in binIdentities.Keys)
{
paramId.Value = binIdentities[id];
for (int i = 0; i < sourceBinColumns.Count; i++)
{
CopyBinaryData(source, sourceTableName, (string)sourceBinColumns[i], sourceIdentityColumn, id,
target, targetTableName, (string)targetBinColumns[i], targetIdentityColumn,
cmd, paramTextPtr, paramData, paramId);
}
}
}
}
SendProgress("Done");
}
private static bool CheckColumn(DBHelper dbh, SqlCommand cmd, string tableName, string columnName, out bool isIdentity)
{
isIdentity = false;
cmd.CommandText = string.Format(CultureInfo.InvariantCulture, "SELECT COLUMNPROPERTY(OBJECT_ID('{0}'), '{1}', 'IsIdentity')", tableName, columnName);
object o = dbh.RunCmdScalar(cmd);
if (o != DBNull.Value)
isIdentity = (int)o != 0;
return o != DBNull.Value;
}