2 // System.Data.SqlClient.SqlBulkCopy.cs
5 // Nagappan A (anagappan@novell.com)
7 // (C) Novell, Inc 2007
10 // Copyright (C) 2007 Novell, Inc (http://www.novell.com)
12 // Permission is hereby granted, free of charge, to any person obtaining
13 // a copy of this software and associated documentation files (the
14 // "Software"), to deal in the Software without restriction, including
15 // without limitation the rights to use, copy, modify, merge, publish,
16 // distribute, sublicense, and/or sell copies of the Software, and to
17 // permit persons to whom the Software is furnished to do so, subject to
18 // the following conditions:
20 // The above copyright notice and this permission notice shall be
21 // included in all copies or substantial portions of the Software.
23 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
24 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
25 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
26 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
27 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
28 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
29 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
35 using System.Data.Common;
37 using Mono.Data.Tds.Protocol;
39 namespace System.Data.SqlClient {
40 /// <summary>Efficient way to bulk load SQL Server table with several data rows at once</summary>
41 public sealed class SqlBulkCopy : IDisposable
44 private const string transConflictMessage = "Must not specify SqlBulkCopyOptions.UseInternalTransaction " +
45 "and pass an external Transaction at the same time.";
47 private const SqlBulkCopyOptions insertModifiers =
48 SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.TableLock |
49 SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.FireTriggers;
54 private int _batchSize = 0;
55 private int _notifyAfter = 0;
56 private int _bulkCopyTimeout = 0;
57 private SqlBulkCopyColumnMappingCollection _columnMappingCollection = new SqlBulkCopyColumnMappingCollection ();
58 private string _destinationTableName = null;
59 private bool ordinalMapping = false;
60 private bool sqlRowsCopied = false;
61 private bool isLocalConnection = false;
62 private SqlConnection connection;
63 private SqlTransaction externalTransaction;
64 private SqlBulkCopyOptions copyOptions = SqlBulkCopyOptions.Default;
69 public SqlBulkCopy (SqlConnection connection)
71 if (connection == null) {
72 throw new ArgumentNullException("connection");
75 this.connection = connection;
78 public SqlBulkCopy (string connectionString)
80 if (connectionString == null) {
81 throw new ArgumentNullException("connectionString");
84 this.connection = new SqlConnection (connectionString);
85 isLocalConnection = true;
89 public SqlBulkCopy (string connectionString, SqlBulkCopyOptions copyOptions)
91 if (connectionString == null) {
92 throw new ArgumentNullException ("connectionString");
95 this.connection = new SqlConnection (connectionString);
96 isLocalConnection = true;
98 if ((copyOptions & SqlBulkCopyOptions.UseInternalTransaction) == SqlBulkCopyOptions.UseInternalTransaction)
99 throw new NotImplementedException ("We don't know how to process UseInternalTransaction option.");
101 this.copyOptions = copyOptions;
105 public SqlBulkCopy (SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction)
107 if (connection == null) {
108 throw new ArgumentNullException ("connection");
111 this.connection = connection;
112 this.copyOptions = copyOptions;
114 if ((copyOptions & SqlBulkCopyOptions.UseInternalTransaction) == SqlBulkCopyOptions.UseInternalTransaction) {
115 if (externalTransaction != null)
116 throw new ArgumentException (transConflictMessage);
119 this.externalTransaction = externalTransaction;
121 if ((copyOptions & SqlBulkCopyOptions.UseInternalTransaction) == SqlBulkCopyOptions.UseInternalTransaction)
122 throw new NotImplementedException ("We don't know how to process UseInternalTransaction option.");
124 this.copyOptions = copyOptions;
131 public int BatchSize {
132 get { return _batchSize; }
133 set { _batchSize = value; }
136 public int BulkCopyTimeout {
137 get { return _bulkCopyTimeout; }
138 set { _bulkCopyTimeout = value; }
141 public SqlBulkCopyColumnMappingCollection ColumnMappings {
142 get { return _columnMappingCollection; }
145 public string DestinationTableName {
146 get { return _destinationTableName; }
147 set { _destinationTableName = value; }
150 public int NotifyAfter {
151 get { return _notifyAfter; }
154 throw new ArgumentOutOfRangeException ("NotifyAfter should be greater than or equal to 0");
155 _notifyAfter = value;
165 if (sqlRowsCopied == true) {
166 throw new InvalidOperationException ("Close should not be called from SqlRowsCopied event");
168 if (connection == null || connection.State == ConnectionState.Closed) {
174 private DataTable [] GetColumnMetaData ()
176 DataTable [] columnMetaDataTables = new DataTable [2];
177 SqlCommand cmd = new SqlCommand ("select @@trancount; " +
178 "set fmtonly on select * from " +
179 DestinationTableName + " set fmtonly off;" +
180 "exec sp_tablecollations_90 '" +
181 DestinationTableName + "'",
183 SqlDataReader reader = cmd.ExecuteReader ();
184 int i = 0; // Skipping 1st result
187 columnMetaDataTables [i - 1] = reader.GetSchemaTable ();
189 SqlDataAdapter adapter = new SqlDataAdapter ();
190 adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
191 columnMetaDataTables [i - 1] = new DataTable ();
192 adapter.FillInternal (columnMetaDataTables [i - 1], reader);
195 } while (reader.IsClosed == false && reader.NextResult());
197 return columnMetaDataTables;
200 private string GenerateColumnMetaData (SqlCommand tmpCmd, DataTable colMetaData, DataTable tableCollations)
203 string statement = "";
205 foreach (DataRow row in colMetaData.Rows) {
207 foreach (DataColumn col in colMetaData.Columns) { // FIXME: This line not required, remove later
209 if (_columnMappingCollection.Count > 0) {
210 if (ordinalMapping) {
211 foreach (SqlBulkCopyColumnMapping mapping
212 in _columnMappingCollection) {
213 if (mapping.DestinationOrdinal == i) {
219 foreach (SqlBulkCopyColumnMapping mapping
220 in _columnMappingCollection) {
221 if (mapping.DestinationColumn == (string) row ["ColumnName"]) {
230 if ((bool)row ["IsReadOnly"]) {
236 SqlParameter param = new SqlParameter ((string) row ["ColumnName"],
237 ((SqlDbType) row ["ProviderType"]));
239 if ((int)row ["ColumnSize"] != -1) {
240 param.Size = (int) row ["ColumnSize"];
243 short numericPresision = (short)row ["NumericPrecision"];
244 if (numericPresision != 255) {
245 param.Precision = (byte) numericPresision;
248 short numericScale = (short)row ["NumericScale"];
249 if (numericScale != 255) {
250 param.Scale = (byte) numericScale;
253 param.IsNullable = (bool)row ["AllowDBNull"];
254 tmpCmd.Parameters.Add (param);
260 bool insertSt = false;
261 foreach (DataRow row in colMetaData.Rows) {
262 SqlDbType sqlType = (SqlDbType) row ["ProviderType"];
263 if (_columnMappingCollection.Count > 0) {
266 foreach (SqlParameter param in tmpCmd.Parameters) {
267 if (ordinalMapping) {
268 foreach (SqlBulkCopyColumnMapping mapping
269 in _columnMappingCollection) {
270 if (mapping.DestinationOrdinal == i && param.Value == null) {
275 foreach (SqlBulkCopyColumnMapping mapping
276 in _columnMappingCollection) {
277 if (mapping.DestinationColumn == param.ParameterName &&
278 (string)row ["ColumnName"] == param.ParameterName) {
285 if (insertSt == true)
288 if (insertSt == false)
291 if ((bool)row ["IsReadOnly"]) {
295 int columnSize = (int)row ["ColumnSize"];
296 string columnInfo = "";
298 if (columnSize >= TdsMetaParameter.maxVarCharCharacters && sqlType == SqlDbType.Text)
299 columnInfo = "VarChar(max)";
300 else if (columnSize >= TdsMetaParameter.maxNVarCharCharacters && sqlType == SqlDbType.NText)
301 columnInfo = "NVarChar(max)";
302 else if (IsTextType(sqlType) && columnSize != -1) {
303 columnInfo = string.Format ("{0}({1})",
305 columnSize.ToString());
307 columnInfo = string.Format ("{0}", sqlType);
310 if ( sqlType == SqlDbType.Decimal)
311 columnInfo += String.Format("({0},{1})", row ["NumericPrecision"], row ["NumericScale"]);
315 string columnName = (string) row ["ColumnName"];
316 statement += string.Format ("[{0}] {1}", columnName, columnInfo);
319 if (IsTextType(sqlType) && tableCollations != null) {
320 foreach (DataRow collationRow in tableCollations.Rows) {
321 if ((string)collationRow ["name"] == columnName) {
322 statement += string.Format (" COLLATE {0}", collationRow ["collation"]);
331 private void ValidateColumnMapping (DataTable table, DataTable tableCollations)
333 // So the problem here is that temp tables will not have any table collations. This prevents
334 // us from bulk inserting into temp tables. So for now we will skip the validation and
335 // let SqlServer tell us there is an issue rather than trying to do it here.
336 // So for now we will simply return and do nothing.
337 // TODO: At some point we should remove this function if we all agree its the right thing to do
340 // foreach (SqlBulkCopyColumnMapping _columnMapping in _columnMappingCollection) {
341 // if (ordinalMapping == false &&
342 // (_columnMapping.DestinationColumn == String.Empty ||
343 // _columnMapping.SourceColumn == String.Empty))
344 // throw new InvalidOperationException ("Mappings must be either all null or ordinal");
345 // if (ordinalMapping &&
346 // (_columnMapping.DestinationOrdinal == -1 ||
347 // _columnMapping.SourceOrdinal == -1))
348 // throw new InvalidOperationException ("Mappings must be either all null or ordinal");
349 // bool flag = false;
350 // if (ordinalMapping == false) {
351 // foreach (DataRow row in tableCollations.Rows) {
352 // if ((string)row ["name"] == _columnMapping.DestinationColumn) {
357 // if (flag == false)
358 // throw new InvalidOperationException ("ColumnMapping does not match");
360 // foreach (DataColumn col in table.Columns) {
361 // if (col.ColumnName == _columnMapping.SourceColumn) {
366 // if (flag == false)
367 // throw new InvalidOperationException ("ColumnName " +
368 // _columnMapping.SourceColumn +
369 // " does not match");
371 // if (_columnMapping.DestinationOrdinal >= tableCollations.Rows.Count)
372 // throw new InvalidOperationException ("ColumnMapping does not match");
377 private void BulkCopyToServer (DataTable table, DataRowState state)
379 if (connection == null || connection.State == ConnectionState.Closed)
380 throw new InvalidOperationException ("This method should not be called on a closed connection");
381 if (_destinationTableName == null)
382 throw new ArgumentNullException ("DestinationTableName");
383 if (isLocalConnection && connection.State != ConnectionState.Open)
386 if ((copyOptions & SqlBulkCopyOptions.KeepIdentity) == SqlBulkCopyOptions.KeepIdentity) {
387 SqlCommand cmd = new SqlCommand ("set identity_insert " +
388 table.TableName + " on",
390 cmd.ExecuteScalar ();
392 DataTable [] columnMetaDataTables = GetColumnMetaData ();
393 DataTable colMetaData = columnMetaDataTables [0];
394 DataTable tableCollations = columnMetaDataTables [1];
396 if (_columnMappingCollection.Count > 0) {
397 if (_columnMappingCollection [0].SourceOrdinal != -1)
398 ordinalMapping = true;
399 ValidateColumnMapping (table, tableCollations);
402 SqlCommand tmpCmd = new SqlCommand ();
403 TdsBulkCopy blkCopy = new TdsBulkCopy ((Tds)connection.Tds);
404 if (((Tds)connection.Tds).TdsVersion >= TdsVersion.tds70) {
405 string statement = "insert bulk " + DestinationTableName + " (";
406 statement += GenerateColumnMetaData (tmpCmd, colMetaData, tableCollations);
409 #region Check requested options and add corresponding modifiers to the statement
410 if ((copyOptions & insertModifiers) != SqlBulkCopyOptions.Default) {
411 statement += " WITH (";
412 bool commaRequired = false;
414 if ((copyOptions & SqlBulkCopyOptions.CheckConstraints) == SqlBulkCopyOptions.CheckConstraints) {
417 statement += "CHECK_CONSTRAINTS";
418 commaRequired = true;
421 if ((copyOptions & SqlBulkCopyOptions.TableLock) == SqlBulkCopyOptions.TableLock) {
424 statement += "TABLOCK";
425 commaRequired = true;
428 if ((copyOptions & SqlBulkCopyOptions.KeepNulls) == SqlBulkCopyOptions.KeepNulls) {
431 statement += "KEEP_NULLS";
432 commaRequired = true;
435 if ((copyOptions & SqlBulkCopyOptions.FireTriggers) == SqlBulkCopyOptions.FireTriggers) {
438 statement += "FIRE_TRIGGERS";
439 commaRequired = true;
444 #endregion Check requested options and add corresponding modifiers to the statement
446 blkCopy.SendColumnMetaData (statement);
448 blkCopy.BulkCopyStart (tmpCmd.Parameters.MetaParameters);
449 long noRowsCopied = 0;
450 foreach (DataRow row in table.Rows) {
451 if (row.RowState == DataRowState.Deleted)
452 continue; // Don't copy the row that's in deleted state
453 if (state != 0 && row.RowState != state)
455 bool isNewRow = true;
457 foreach (SqlParameter param in tmpCmd.Parameters) {
459 object rowToCopy = null;
460 if (_columnMappingCollection.Count > 0) {
461 if (ordinalMapping) {
462 foreach (SqlBulkCopyColumnMapping mapping
463 in _columnMappingCollection) {
464 if (mapping.DestinationOrdinal == i && param.Value == null) {
465 rowToCopy = row [mapping.SourceOrdinal];
466 SqlParameter parameter = new SqlParameter (mapping.SourceOrdinal.ToString (),
468 if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
469 parameter.SqlDbType = param.SqlDbType;
470 rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
472 string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
473 if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
474 if (row [i] != null && row [i] != DBNull.Value) {
475 size = ((string) parameter.Value).Length;
478 } else if (colType == "varchar" || colType == "text" || colType == "char") {
479 if (row [i] != null && row [i] != DBNull.Value)
480 size = ((string) parameter.Value).Length;
482 size = parameter.Size;
488 foreach (SqlBulkCopyColumnMapping mapping
489 in _columnMappingCollection) {
490 if (mapping.DestinationColumn == param.ParameterName) {
491 rowToCopy = row [mapping.SourceColumn];
492 SqlParameter parameter = new SqlParameter (mapping.SourceColumn, rowToCopy);
493 if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
494 parameter.SqlDbType = param.SqlDbType;
495 rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
497 string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
498 if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
499 if (row [mapping.SourceColumn] != null && row [mapping.SourceColumn] != DBNull.Value) {
500 size = ((string) rowToCopy).Length;
503 } else if (colType == "varchar" || colType == "text" || colType == "char") {
504 if (row [mapping.SourceColumn] != null && row [mapping.SourceColumn] != DBNull.Value)
505 size = ((string) rowToCopy).Length;
507 size = parameter.Size;
515 rowToCopy = row [param.ParameterName];
516 string colType = param.MetaParameter.TypeName;
518 If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
519 FIXME: Need to check for other types
521 if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
522 size = ((string) row [param.ParameterName]).Length;
524 } else if (colType == "varchar" || colType == "text" || colType == "char") {
525 size = ((string) row [param.ParameterName]).Length;
530 if (rowToCopy == null)
533 blkCopy.BulkCopyData (rowToCopy, isNewRow, size, param.MetaParameter);
537 } // foreach (SqlParameter)
538 if (_notifyAfter > 0) {
540 if (noRowsCopied >= _notifyAfter) {
541 RowsCopied (noRowsCopied);
545 } // foreach (DataRow)
546 blkCopy.BulkCopyEnd ();
549 private bool IsTextType(SqlDbType sqlType)
551 return (sqlType == SqlDbType.NText ||
552 sqlType == SqlDbType.NVarChar ||
553 sqlType == SqlDbType.Text ||
554 sqlType == SqlDbType.VarChar ||
555 sqlType == SqlDbType.Char ||
556 sqlType == SqlDbType.NChar);
559 public void WriteToServer (DataRow [] rows)
562 throw new ArgumentNullException ("rows");
563 if (rows.Length == 0)
565 DataTable table = new DataTable (rows [0].Table.TableName);
566 foreach (DataColumn col in rows [0].Table.Columns) {
567 DataColumn tmpCol = new DataColumn (col.ColumnName, col.DataType);
568 table.Columns.Add (tmpCol);
570 foreach (DataRow row in rows) {
571 DataRow tmpRow = table.NewRow ();
572 for (int i = 0; i < table.Columns.Count; i++) {
573 tmpRow [i] = row [i];
575 table.Rows.Add (tmpRow);
577 BulkCopyToServer (table, 0);
580 public void WriteToServer (DataTable table)
582 BulkCopyToServer (table, 0);
585 public void WriteToServer (IDataReader reader)
587 DataTable table = new DataTable ();
588 SqlDataAdapter adapter = new SqlDataAdapter ();
589 adapter.FillInternal (table, reader);
590 BulkCopyToServer (table, 0);
593 public void WriteToServer (DataTable table, DataRowState rowState)
595 BulkCopyToServer (table, rowState);
598 private void RowsCopied (long rowsCopied)
600 SqlRowsCopiedEventArgs e = new SqlRowsCopiedEventArgs (rowsCopied);
601 if (null != SqlRowsCopied) {
602 SqlRowsCopied (this, e);
610 public event SqlRowsCopiedEventHandler SqlRowsCopied;
614 void IDisposable.Dispose ()
616 //throw new NotImplementedException ();
617 if (isLocalConnection) {