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"); }