private void IdInsertOn(SqlCeTransaction localTrans, int idOrdinal)
{
if (_keepIdentity && idOrdinal >= 0)
{
using (var idCmd = AdoNetUtils.CreateCommand(_conn, localTrans, string.Format(CultureInfo.InvariantCulture, "SET IDENTITY_INSERT [{0}] ON", DestinationTableName)))
{
idCmd.ExecuteNonQuery();
}
if (_trans == null)
{
_autoIncNext = SqlCeBulkCopyTableHelpers.GetAutoIncNext(_conn, DestinationTableName);
}
}
}
internal static List <KeyValuePair <int, int> > Create(SqlCeConnection conn, ISqlCeBulkCopyInsertAdapter adapter, SqlCeBulkCopyOptions copyOptions, string tableName)
{
var keepNulls = SqlCeBulkCopyTableHelpers.IsCopyOption(SqlCeBulkCopyOptions.KeepNulls, copyOptions);
var retVal = new List <KeyValuePair <int, int> >();
//we use this to determine if we throw an error while building maps.
int idOrdinal = SqlCeBulkCopyTableHelpers.IdentityOrdinalIgnoreOptions(conn, tableName);
var destColumnData = DestinationTableDefaultMetadata.GetDataForTable(conn, tableName);
//we are going to validate all of the columns but if we don't map then we will not set the HasMappings
//A map is defined as
//1. any column that the column order is changed
//2. field exists in the dest but not the source and the dest has a default.
//3. Identity column that is not 0 ?? we may be able to remove this one.
//we only really care about the destination columns being mapped. If too many columns exist do we really care????
var sourceColumns = GetSourceColumns(adapter);
for (int destIndex = 0; destIndex < destColumnData.Count; destIndex++)
{
var destColumn = destColumnData[destIndex];
var sourceIndex = sourceColumns.IndexOf(destColumn.ColumnName);
//see if the source is the same as the destination ordinal
//if (destIndex != sourceIndex) //we have a map if we care later
//If the source index is -1 and the dest does not allow nulls or has a default, it is an error
if (sourceIndex < 0)
{
//either we allow nulls or the ordinal is the index and therefore it is valid
if (!destColumnData[destIndex].HasDefault && ((!destColumnData[destIndex].IsNullable && !keepNulls) && idOrdinal != destIndex))
{
//var error = destColumnData[destIndex].HasDefault + " " + destColumnData[destIndex].IsNullable + " " + keepNulls + " " + idOrdinal + " " + destIndex;
throw new ApplicationException(string.Format("Source column '{0}' does not exist and destination does not allow nulls.", destColumn.ColumnName));
}
}
retVal.Add(new KeyValuePair <int, int>(sourceIndex, destIndex));
}
return(retVal);
}
private void ResetSeed(int totalRows)
{
if (totalRows == 0)
{
return;
}
if (!_keepIdentity)
{
return;
}
//Cannot run re-seed when using user supplied transaction, so fail silently
if (_keepIdentity && _trans != null)
{
return;
}
var newAutoIncNext = SqlCeBulkCopyTableHelpers.GetAutoIncNext(_conn, DestinationTableName);
if (_autoIncNext != newAutoIncNext)
{
return;
}
using (var transact = _conn.BeginTransaction())
{
// Get Identity column
string idCol = null;
using (var ainCmd = AdoNetUtils.CreateCommand(_conn, transact, string.Format(CultureInfo.InvariantCulture,
"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}' AND AUTOINC_INCREMENT IS NOT NULL", DestinationTableName)))
{
object res = ainCmd.ExecuteScalar();
if (res != null)
{
idCol = (string)res;
}
}
if (string.IsNullOrEmpty(idCol))
{
return;
}
// Get Max value if the column
long?maxVal = null;
using (var ainCmd = AdoNetUtils.CreateCommand(_conn, transact, string.Format(CultureInfo.InvariantCulture,
"SELECT CAST(MAX([{0}]) AS bigint) FROM [{1}]", idCol, DestinationTableName)))
{
object res = ainCmd.ExecuteScalar();
if (res != null)
{
maxVal = (long)res;
}
}
if (!maxVal.HasValue)
{
return;
}
//Reseed
using (var ainCmd = AdoNetUtils.CreateCommand(_conn, transact, string.Format(CultureInfo.InvariantCulture,
"ALTER TABLE [{0}] ALTER COLUMN [{1}] IDENTITY ({2},1);", DestinationTableName, idCol, maxVal + 1)))
{
ainCmd.ExecuteNonQuery();
}
transact.Commit();
}
}
private void WriteToServer(ISqlCeBulkCopyInsertAdapter adapter)
{
CheckDestination();
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
GetAndDropConstraints();
List <KeyValuePair <int, int> > map;
int totalRows = 0;
SqlCeTransaction localTrans = _trans ?? _conn.BeginTransaction();
if (ColumnMappings.Count > 0)
{
//mapping are set, and should be validated
map = ColumnMappings.ValidateCollection(_conn, localTrans, adapter, _keepNulls, _destination);
}
else
{
//create default column mappings
map = SqlCeBulkCopyColumnMappingCollection.Create(_conn, localTrans, adapter, _keepNulls, _destination);
}
using (var cmd = new SqlCeCommand(_destination, _conn, localTrans))
{
cmd.CommandType = CommandType.TableDirect;
using (var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
{
var idOrdinal = SqlCeBulkCopyTableHelpers.IdentityOrdinal(_conn, localTrans, _destination);
var rec = rs.CreateRecord();
var rowCounter = 0;
IdInsertOn(localTrans, idOrdinal);
//Converting to an array removed the perf issue of a list and foreach statement.
var cm = map.ToArray();
while (adapter.Read())
{
if (adapter.SkipRow())
{
continue;
}
for (var i = 0; i < cm.Length; i++)
{
//caching the values this way do not cause a perf issue.
var sourceIndex = cm[i].Key;
var destIndex = cm[i].Value;
// Let the destination assign identity values
if (!_keepIdentity && destIndex == idOrdinal)
{
continue;
}
//determine if we should ever allow this in the map.
if (sourceIndex < 0)
{
continue;
}
var value = sourceIndex > -1 ? adapter.Get(sourceIndex) : null;
if (value != null && value.GetType() != DbNullType)
{
rec.SetValue(destIndex, value);
}
else
{
//we can't write to an auto number column so continue
if (_keepNulls && destIndex == idOrdinal)
{
continue;
}
if (_keepNulls)
{
rec.SetValue(destIndex, DBNull.Value);
}
else
{
rec.SetDefault(destIndex);
}
}
}
rowCounter++;
totalRows++;
try
{
rs.Insert(rec);
}
catch (SqlCeException ex)
{
if (ex.NativeError == 25016 && _ignoreDuplicateErrors) //A duplicate value cannot be inserted into a unique index.
{
System.Diagnostics.Trace.TraceWarning("SqlCeBulkCopy: Duplicate value error was ignored");
continue;
}
else
{
throw;
}
}
// Fire event if needed
if (RowsCopied != null && _notifyAfter > 0 && rowCounter == _notifyAfter)
{
FireRowsCopiedEvent(totalRows);
rowCounter = 0;
}
}
IdInsertOff(localTrans, idOrdinal);
if (RowsCopied != null)
{
FireRowsCopiedEvent(totalRows);
}
}
}
//if we have our own transaction, we will commit it
if (_trans == null)
{
localTrans.Commit(CommitMode.Immediate);
localTrans.Dispose();
}
ResetSeed(totalRows);
RestoreConstraints();
}
private void WriteToServer(ISqlCeBulkCopyInsertAdapter adapter)
{
CheckDestination();
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
List <KeyValuePair <int, int> > mappings = null;
if (Mappings.Count > 0)
{
//mapping are set, and should be validated
mappings = Mappings.ValidateCollection(conn, adapter, options, destination);
}
else
{
//create default column mappings
mappings = SqlCeBulkCopyColumnMappingCollection.Create(conn, adapter, options, destination);
}
SqlCeTransaction localTrans = trans ?? conn.BeginTransaction();
using (SqlCeCommand cmd = new SqlCeCommand(destination, conn, localTrans))
{
cmd.CommandType = CommandType.TableDirect;
using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
{
int idOrdinal = SqlCeBulkCopyTableHelpers.IdentityOrdinal(conn, options, destination);
SqlCeUpdatableRecord rec = rs.CreateRecord();
int rowCounter = 0;
int totalRows = 0;
IdInsertOn();
//Converting to an array removed the perf issue of a list and foreach statement.
var cm = mappings.ToArray();
while (adapter.Read())
{
if (adapter.SkipRow())
{
continue;
}
for (int i = 0; i < cm.Length; i++)
{
//caching the values this way do not cause a perf issue.
var sourceIndex = cm[i].Key;
var destIndex = cm[i].Value;
// Let the destination assign identity values
if (!keepIdentity && destIndex == idOrdinal)
{
continue;
}
//determine if we should ever allow this in the map.
if (sourceIndex < 0)
{
continue;
}
var value = sourceIndex > -1 ? adapter.Get(sourceIndex) : null;
if (value != null && value.GetType() != DbNullType)
{
rec.SetValue(destIndex, value);
}
else
{
//we can't write to an auto number column so continue
if (keepNulls && destIndex == idOrdinal)
{
continue;
}
if (keepNulls)
{
rec.SetValue(destIndex, DBNull.Value);
}
else
{
rec.SetDefault(destIndex);
}
}
// Fire event if needed
if (notifyAfter > 0 && rowCounter == notifyAfter)
{
FireRowsCopiedEvent(totalRows);
rowCounter = 0;
}
}
rowCounter++;
totalRows++;
rs.Insert(rec);
}
IdInsertOff();
}
}
//if we have our own transaction, we will commit it
if (trans == null)
{
localTrans.Commit(CommitMode.Immediate);
}
}