private static List <string> GetSourceColumns(ISqlCeBulkCopyInsertAdapter adapter)
{
var sourceColumns = new List <string>();
for (int fieldCount = 0; fieldCount < adapter.FieldCount; fieldCount++)
{
sourceColumns.Add(adapter.FieldName(fieldCount).ToUpper());
}
return(sourceColumns);
}
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);
}
internal List <KeyValuePair <int, int> > ValidateCollection(SqlCeConnection conn, ISqlCeBulkCopyInsertAdapter adapter, SqlCeBulkCopyOptions copyOptions, string tableName)
{
if (Count > 0)
{
var retVal = new List <KeyValuePair <int, int> >();
var sourceColumns = GetSourceColumns(adapter);
var destColumns = ToColumnNames(DestinationTableDefaultMetadata.GetDataForTable(conn, tableName));
foreach (SqlCeBulkCopyColumnMapping mapping in this.Items)
{
var sourceColumnName = (mapping.SourceColumn ?? string.Empty).ToUpper();
var destColumnName = (mapping.DestinationColumn ?? string.Empty).ToUpper();
int sourceIndex = -1;
int destIndex = -1;
//verify if we have a source column name that it exists
if (!string.IsNullOrEmpty(sourceColumnName))
{
if (!sourceColumns.Contains(sourceColumnName))
{
throw new ApplicationException("No column exists with the name of " + mapping.SourceColumn + " in source."); //use collection name for error since it has original casing.
}
sourceIndex = sourceColumns.IndexOf(sourceColumnName);
}
else
{
if (mapping.SourceOrdinal < 0 || mapping.SourceOrdinal >= destColumns.Count)
{
throw new ApplicationException("No column exists at index " + mapping.SourceOrdinal + " in source."); //use collection name for error since it has original casing.
}
sourceIndex = mapping.SourceOrdinal;
}
if (!string.IsNullOrEmpty(destColumnName))
{
if (destColumnName.StartsWith("[") && destColumnName.EndsWith("]"))
{
destColumnName = destColumnName.Substring(1, destColumnName.Length - 2);
}
if (!sourceColumns.Contains(destColumnName))
{
string bestFit = null;
foreach (var existingColumn in destColumns)
{
if (String.Equals(existingColumn, destColumnName, StringComparison.InvariantCultureIgnoreCase))
{
bestFit = existingColumn;
}
}
if (bestFit == null)
{
throw new ApplicationException("Destination column " + mapping.DestinationColumn + " does not exist in destination table " +
tableName + " in database " + conn.Database + "."); //use collection name for error since it has original casing.
}
else
{
throw new ApplicationException(
"Destination column " + mapping.DestinationColumn + " does not exist in destination table " + tableName
+ " in database " + conn.Database + "." +
" Column name mappings are case specific and best found match is " + bestFit + "."); //use collection name for error since it has original casing.
}
}
else
{
destIndex = destColumns.IndexOf(destColumnName);
}
}
else
{
if (mapping.DestinationOrdinal < 0 || mapping.DestinationOrdinal >= destColumns.Count)
{
throw new ApplicationException(
"No column exists at index " + mapping.DestinationOrdinal + " in destination table " + tableName +
"in database " + conn.Database + "."); //use collection name for error since it has original casing.
}
destIndex = mapping.DestinationOrdinal;
}
retVal.Add(new KeyValuePair <int, int>(sourceIndex, destIndex));
}
retVal.Sort((a, b) =>
{
return(a.Key.CompareTo(b.Key));
});
return(retVal);
}
else
{
return(Create(conn, adapter, copyOptions, tableName));
}
}
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);
}
}
