private void BulkCopyToServer (DataTable table, DataRowState state)
{
if (connection == null || connection.State == ConnectionState.Closed)
throw new InvalidOperationException ("This method should not be called on a closed connection");
if (_destinationTableName == null)
throw new ArgumentNullException ("DestinationTableName");
if (isLocalConnection && connection.State != ConnectionState.Open)
connection.Open();
if ((copyOptions & SqlBulkCopyOptions.KeepIdentity) == SqlBulkCopyOptions.KeepIdentity) {
SqlCommand cmd = new SqlCommand ("set identity_insert " +
table.TableName + " on",
connection);
cmd.ExecuteScalar ();
}
DataTable [] columnMetaDataTables = GetColumnMetaData ();
DataTable colMetaData = columnMetaDataTables [0];
DataTable tableCollations = columnMetaDataTables [1];
if (_columnMappingCollection.Count > 0) {
if (_columnMappingCollection [0].SourceOrdinal != -1)
ordinalMapping = true;
ValidateColumnMapping (table, tableCollations);
}
SqlCommand tmpCmd = new SqlCommand ();
TdsBulkCopy blkCopy = new TdsBulkCopy ((Tds)connection.Tds);
if (((Tds)connection.Tds).TdsVersion >= TdsVersion.tds70) {
string statement = "insert bulk " + DestinationTableName + " (";
statement += GenerateColumnMetaData (tmpCmd, colMetaData, tableCollations);
statement += ")";
#region Check requested options and add corresponding modifiers to the statement
if ((copyOptions & insertModifiers) != SqlBulkCopyOptions.Default) {
statement += " WITH (";
bool commaRequired = false;
if ((copyOptions & SqlBulkCopyOptions.CheckConstraints) == SqlBulkCopyOptions.CheckConstraints) {
if (commaRequired)
statement += ", ";
statement += "CHECK_CONSTRAINTS";
commaRequired = true;
}
if ((copyOptions & SqlBulkCopyOptions.TableLock) == SqlBulkCopyOptions.TableLock) {
if (commaRequired)
statement += ", ";
statement += "TABLOCK";
commaRequired = true;
}
if ((copyOptions & SqlBulkCopyOptions.KeepNulls) == SqlBulkCopyOptions.KeepNulls) {
if (commaRequired)
statement += ", ";
statement += "KEEP_NULLS";
commaRequired = true;
}
if ((copyOptions & SqlBulkCopyOptions.FireTriggers) == SqlBulkCopyOptions.FireTriggers) {
if (commaRequired)
statement += ", ";
statement += "FIRE_TRIGGERS";
commaRequired = true;
}
statement += ")";
}
#endregion Check requested options and add corresponding modifiers to the statement
blkCopy.SendColumnMetaData (statement);
}
blkCopy.BulkCopyStart (tmpCmd.Parameters.MetaParameters);
long noRowsCopied = 0;
foreach (DataRow row in table.Rows) {
if (row.RowState == DataRowState.Deleted)
continue; // Don't copy the row that's in deleted state
if (state != 0 && row.RowState != state)
continue;
bool isNewRow = true;
int i = 0;
foreach (SqlParameter param in tmpCmd.Parameters) {
int size = 0;
object rowToCopy = null;
if (_columnMappingCollection.Count > 0) {
if (ordinalMapping) {
foreach (SqlBulkCopyColumnMapping mapping
in _columnMappingCollection) {
if (mapping.DestinationOrdinal == i && param.Value == null) {
rowToCopy = row [mapping.SourceOrdinal];
SqlParameter parameter = new SqlParameter (mapping.SourceOrdinal.ToString (),
rowToCopy);
if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
parameter.SqlDbType = param.SqlDbType;
rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
}
string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
if (row [i] != null && row [i] != DBNull.Value) {
size = ((string) parameter.Value).Length;
size <<= 1;
}
} else if (colType == "varchar" || colType == "text" || colType == "char") {
if (row [i] != null && row [i] != DBNull.Value)
size = ((string) parameter.Value).Length;
} else {
size = parameter.Size;
}
break;
}
}
} else {
foreach (SqlBulkCopyColumnMapping mapping
in _columnMappingCollection) {
if (mapping.DestinationColumn == param.ParameterName) {
rowToCopy = row [mapping.SourceColumn];
SqlParameter parameter = new SqlParameter (mapping.SourceColumn, rowToCopy);
if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
parameter.SqlDbType = param.SqlDbType;
rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
}
string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
if (row [mapping.SourceColumn] != null && row [mapping.SourceColumn] != DBNull.Value) {
size = ((string) rowToCopy).Length;
size <<= 1;
}
} else if (colType == "varchar" || colType == "text" || colType == "char") {
if (row [mapping.SourceColumn] != null && row [mapping.SourceColumn] != DBNull.Value)
size = ((string) rowToCopy).Length;
} else {
size = parameter.Size;
}
break;
}
}
}
i++;
} else {
rowToCopy = row [param.ParameterName];
string colType = param.MetaParameter.TypeName;
/*
If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
FIXME: Need to check for other types
*/
if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
size = ((string) row [param.ParameterName]).Length;
size <<= 1;
} else if (colType == "varchar" || colType == "text" || colType == "char") {
size = ((string) row [param.ParameterName]).Length;
} else {
size = param.Size;
}
}
if (rowToCopy == null)
continue;
blkCopy.BulkCopyData (rowToCopy, isNewRow, size, param.MetaParameter);
if (isNewRow)
isNewRow = false;
} // foreach (SqlParameter)
if (_notifyAfter > 0) {
noRowsCopied ++;
if (noRowsCopied >= _notifyAfter) {
RowsCopied (noRowsCopied);
noRowsCopied = 0;
}
}
} // foreach (DataRow)
blkCopy.BulkCopyEnd ();
}
private void BulkCopyToServer (DataTable table, DataRowState state)
{
if (connection == null || connection.State == ConnectionState.Closed)
throw new InvalidOperationException ("This method should not be called on a closed connection");
if (_destinationTableName == null)
throw new ArgumentNullException ("DestinationTableName");
if (identityInsert) {
SqlCommand cmd = new SqlCommand ("set identity_insert " +
table.TableName + " on",
connection);
cmd.ExecuteScalar ();
}
DataTable [] columnMetaDataTables = GetColumnMetaData ();
DataTable colMetaData = columnMetaDataTables [0];
DataTable tableCollations = columnMetaDataTables [1];
if (_columnMappingCollection.Count > 0) {
if (_columnMappingCollection [0].SourceOrdinal != -1)
ordinalMapping = true;
ValidateColumnMapping (table, tableCollations);
}
SqlCommand tmpCmd = new SqlCommand ();
TdsBulkCopy blkCopy = new TdsBulkCopy ((Tds)connection.Tds);
if (((Tds)connection.Tds).TdsVersion >= TdsVersion.tds70) {
string statement = "insert bulk " + DestinationTableName + " (";
statement += GenerateColumnMetaData (tmpCmd, colMetaData, tableCollations);
statement += ")";
blkCopy.SendColumnMetaData (statement);
}
blkCopy.BulkCopyStart (tmpCmd.Parameters.MetaParameters);
long noRowsCopied = 0;
foreach (DataRow row in table.Rows) {
if (row.RowState == DataRowState.Deleted)
continue; // Don't copy the row that's in deleted state
if (state != 0 && row.RowState != state)
continue;
bool isNewRow = true;
int i = 0;
foreach (SqlParameter param in tmpCmd.Parameters) {
int size = 0;
object rowToCopy = null;
if (_columnMappingCollection.Count > 0) {
if (ordinalMapping) {
foreach (SqlBulkCopyColumnMapping mapping
in _columnMappingCollection) {
if (mapping.DestinationOrdinal == i && param.Value == null) {
rowToCopy = row [mapping.SourceOrdinal];
SqlParameter parameter = new SqlParameter (mapping.SourceOrdinal.ToString (),
rowToCopy);
if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
parameter.SqlDbType = param.SqlDbType;
rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
}
string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
if (colType == "nvarchar") {
if (row [i] != null) {
size = ((string) parameter.Value).Length;
size <<= 1;
}
} else {
size = parameter.Size;
}
break;
}
}
} else {
foreach (SqlBulkCopyColumnMapping mapping
in _columnMappingCollection) {
if (mapping.DestinationColumn == param.ParameterName) {
rowToCopy = row [mapping.SourceColumn];
SqlParameter parameter = new SqlParameter (mapping.SourceColumn, rowToCopy);
if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
parameter.SqlDbType = param.SqlDbType;
rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
}
string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
if (colType == "nvarchar") {
if (row [mapping.SourceColumn] != null) {
size = ((string) rowToCopy).Length;
size <<= 1;
}
} else {
size = parameter.Size;
}
break;
}
}
}
i++;
} else {
rowToCopy = row [param.ParameterName];
string colType = param.MetaParameter.TypeName;
/*
If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
FIXME: Need to check for other types
*/
if (colType == "nvarchar") {
size = ((string) row [param.ParameterName]).Length;
size <<= 1;
} else {
size = param.Size;
}
}
if (rowToCopy == null)
continue;
blkCopy.BulkCopyData (rowToCopy, size, isNewRow);
if (isNewRow)
isNewRow = false;
} // foreach (SqlParameter)
if (_notifyAfter > 0) {
noRowsCopied ++;
if (noRowsCopied >= _notifyAfter) {
RowsCopied (noRowsCopied);
noRowsCopied = 0;
}
}
} // foreach (DataRow)
blkCopy.BulkCopyEnd ();
}