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.
34 using System.Data.Common;
36 using Mono.Data.Tds.Protocol;
38 namespace System.Data.SqlClient {
39 /// <summary>Efficient way to bulk load SQL Server table with several data rows at once</summary>
40 public sealed class SqlBulkCopy : IDisposable
43 private const string transConflictMessage = "Must not specify SqlBulkCopyOptions.UseInternalTransaction " +
44 "and pass an external Transaction at the same time.";
46 private const SqlBulkCopyOptions insertModifiers =
47 SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.TableLock |
48 SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.FireTriggers;
53 private int _batchSize = 0;
54 private int _notifyAfter = 0;
55 private int _bulkCopyTimeout = 0;
56 private SqlBulkCopyColumnMappingCollection _columnMappingCollection = new SqlBulkCopyColumnMappingCollection ();
57 private string _destinationTableName = null;
58 private bool ordinalMapping = false;
59 private bool sqlRowsCopied = false;
60 private bool isLocalConnection = false;
61 private SqlConnection connection;
62 private SqlTransaction externalTransaction;
63 private SqlBulkCopyOptions copyOptions = SqlBulkCopyOptions.Default;
68 public SqlBulkCopy (SqlConnection connection)
70 if (connection == null) {
71 throw new ArgumentNullException("connection");
74 this.connection = connection;
77 public SqlBulkCopy (string connectionString)
79 if (connectionString == null) {
80 throw new ArgumentNullException("connectionString");
83 this.connection = new SqlConnection (connectionString);
84 isLocalConnection = true;
88 public SqlBulkCopy (string connectionString, SqlBulkCopyOptions copyOptions)
90 if (connectionString == null) {
91 throw new ArgumentNullException ("connectionString");
94 this.connection = new SqlConnection (connectionString);
95 isLocalConnection = true;
97 if ((copyOptions & SqlBulkCopyOptions.UseInternalTransaction) == SqlBulkCopyOptions.UseInternalTransaction)
98 throw new NotImplementedException ("We don't know how to process UseInternalTransaction option.");
100 this.copyOptions = copyOptions;
104 public SqlBulkCopy (SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction)
106 if (connection == null) {
107 throw new ArgumentNullException ("connection");
110 this.connection = connection;
111 this.copyOptions = copyOptions;
113 if ((copyOptions & SqlBulkCopyOptions.UseInternalTransaction) == SqlBulkCopyOptions.UseInternalTransaction) {
114 if (externalTransaction != null)
115 throw new ArgumentException (transConflictMessage);
118 this.externalTransaction = externalTransaction;
120 if ((copyOptions & SqlBulkCopyOptions.UseInternalTransaction) == SqlBulkCopyOptions.UseInternalTransaction)
121 throw new NotImplementedException ("We don't know how to process UseInternalTransaction option.");
123 this.copyOptions = copyOptions;
130 public int BatchSize {
131 get { return _batchSize; }
132 set { _batchSize = value; }
135 public int BulkCopyTimeout {
136 get { return _bulkCopyTimeout; }
137 set { _bulkCopyTimeout = value; }
140 public SqlBulkCopyColumnMappingCollection ColumnMappings {
141 get { return _columnMappingCollection; }
144 public string DestinationTableName {
145 get { return _destinationTableName; }
146 set { _destinationTableName = value; }
149 public int NotifyAfter {
150 get { return _notifyAfter; }
153 throw new ArgumentOutOfRangeException ("NotifyAfter should be greater than or equal to 0");
154 _notifyAfter = value;
164 if (sqlRowsCopied == true) {
165 throw new InvalidOperationException ("Close should not be called from SqlRowsCopied event");
167 if (connection == null || connection.State == ConnectionState.Closed) {
173 private DataTable [] GetColumnMetaData ()
175 DataTable [] columnMetaDataTables = new DataTable [2];
176 SqlCommand cmd = new SqlCommand ("select @@trancount; " +
177 "set fmtonly on select * from " +
178 DestinationTableName + " set fmtonly off;" +
179 "exec sp_tablecollations_90 '" +
180 DestinationTableName + "'",
183 if (externalTransaction != null)
184 cmd.Transaction = externalTransaction;
186 SqlDataReader reader = cmd.ExecuteReader ();
187 int i = 0; // Skipping 1st result
190 columnMetaDataTables [i - 1] = reader.GetSchemaTable ();
192 SqlDataAdapter adapter = new SqlDataAdapter ();
193 adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
194 columnMetaDataTables [i - 1] = new DataTable (DestinationTableName);
195 adapter.FillInternal (columnMetaDataTables [i - 1], reader);
198 } while (reader.IsClosed == false && reader.NextResult());
200 return columnMetaDataTables;
203 private string GenerateColumnMetaData (SqlCommand tmpCmd, DataTable colMetaData, DataTable tableCollations)
206 string statement = "";
208 foreach (DataRow row in colMetaData.Rows) {
210 foreach (DataColumn col in colMetaData.Columns) { // FIXME: This line not required, remove later
212 if (_columnMappingCollection.Count > 0) {
213 if (ordinalMapping) {
214 foreach (SqlBulkCopyColumnMapping mapping
215 in _columnMappingCollection) {
216 if (mapping.DestinationOrdinal == i) {
222 foreach (SqlBulkCopyColumnMapping mapping
223 in _columnMappingCollection) {
224 if (mapping.DestinationColumn == (string) row ["ColumnName"]) {
233 if ((bool)row ["IsReadOnly"]) {
239 SqlParameter param = new SqlParameter ((string) row ["ColumnName"],
240 ((SqlDbType) row ["ProviderType"]));
242 if ((int)row ["ColumnSize"] != -1) {
243 param.Size = (int) row ["ColumnSize"];
246 short numericPresision = (short)row ["NumericPrecision"];
247 if (numericPresision != 255) {
248 param.Precision = (byte) numericPresision;
251 short numericScale = (short)row ["NumericScale"];
252 if (numericScale != 255) {
253 param.Scale = (byte) numericScale;
256 param.IsNullable = (bool)row ["AllowDBNull"];
257 tmpCmd.Parameters.Add (param);
263 bool insertSt = false;
264 foreach (DataRow row in colMetaData.Rows) {
265 SqlDbType sqlType = (SqlDbType) row ["ProviderType"];
266 if (_columnMappingCollection.Count > 0) {
269 foreach (SqlParameter param in tmpCmd.Parameters) {
270 if (ordinalMapping) {
271 foreach (SqlBulkCopyColumnMapping mapping
272 in _columnMappingCollection) {
273 if (mapping.DestinationOrdinal == i && param.Value == null) {
278 foreach (SqlBulkCopyColumnMapping mapping
279 in _columnMappingCollection) {
280 if (mapping.DestinationColumn == param.ParameterName &&
281 (string)row ["ColumnName"] == param.ParameterName) {
288 if (insertSt == true)
291 if (insertSt == false)
294 if ((bool)row ["IsReadOnly"]) {
298 int columnSize = (int)row ["ColumnSize"];
299 string columnInfo = "";
301 if (columnSize >= TdsMetaParameter.maxVarCharCharacters && sqlType == SqlDbType.Text)
302 columnInfo = "VarChar(max)";
303 else if (columnSize >= TdsMetaParameter.maxNVarCharCharacters && sqlType == SqlDbType.NText)
304 columnInfo = "NVarChar(max)";
305 else if (IsTextType(sqlType) && columnSize != -1) {
306 columnInfo = string.Format ("{0}({1})",
308 columnSize.ToString());
310 columnInfo = string.Format ("{0}", sqlType);
313 if ( sqlType == SqlDbType.Decimal)
314 columnInfo += String.Format("({0},{1})", row ["NumericPrecision"], row ["NumericScale"]);
318 string columnName = (string) row ["ColumnName"];
319 statement += string.Format ("[{0}] {1}", columnName, columnInfo);
322 if (IsTextType(sqlType) && tableCollations != null) {
323 foreach (DataRow collationRow in tableCollations.Rows) {
324 if ((string)collationRow ["name"] == columnName) {
325 statement += string.Format (" COLLATE {0}", collationRow ["collation"]);
334 private void ValidateColumnMapping (DataTable table, DataTable tableCollations)
336 // So the problem here is that temp tables will not have any table collations. This prevents
337 // us from bulk inserting into temp tables. So for now we will skip the validation and
338 // let SqlServer tell us there is an issue rather than trying to do it here.
339 // So for now we will simply return and do nothing.
340 // TODO: At some point we should remove this function if we all agree its the right thing to do
343 // foreach (SqlBulkCopyColumnMapping _columnMapping in _columnMappingCollection) {
344 // if (ordinalMapping == false &&
345 // (_columnMapping.DestinationColumn == String.Empty ||
346 // _columnMapping.SourceColumn == String.Empty))
347 // throw new InvalidOperationException ("Mappings must be either all null or ordinal");
348 // if (ordinalMapping &&
349 // (_columnMapping.DestinationOrdinal == -1 ||
350 // _columnMapping.SourceOrdinal == -1))
351 // throw new InvalidOperationException ("Mappings must be either all null or ordinal");
352 // bool flag = false;
353 // if (ordinalMapping == false) {
354 // foreach (DataRow row in tableCollations.Rows) {
355 // if ((string)row ["name"] == _columnMapping.DestinationColumn) {
360 // if (flag == false)
361 // throw new InvalidOperationException ("ColumnMapping does not match");
363 // foreach (DataColumn col in table.Columns) {
364 // if (col.ColumnName == _columnMapping.SourceColumn) {
369 // if (flag == false)
370 // throw new InvalidOperationException ("ColumnName " +
371 // _columnMapping.SourceColumn +
372 // " does not match");
374 // if (_columnMapping.DestinationOrdinal >= tableCollations.Rows.Count)
375 // throw new InvalidOperationException ("ColumnMapping does not match");
380 private void BulkCopyToServer (DataTable table, DataRowState state)
382 if (connection == null || connection.State == ConnectionState.Closed)
383 throw new InvalidOperationException ("This method should not be called on a closed connection");
384 if (_destinationTableName == null)
385 throw new ArgumentNullException ("DestinationTableName");
386 if (isLocalConnection && connection.State != ConnectionState.Open)
389 if ((copyOptions & SqlBulkCopyOptions.KeepIdentity) == SqlBulkCopyOptions.KeepIdentity) {
390 SqlCommand cmd = new SqlCommand ("set identity_insert " +
391 table.TableName + " on",
393 cmd.ExecuteScalar ();
395 DataTable [] columnMetaDataTables = GetColumnMetaData ();
396 DataTable colMetaData = columnMetaDataTables [0];
397 DataTable tableCollations = columnMetaDataTables [1];
399 if (_columnMappingCollection.Count > 0) {
400 if (_columnMappingCollection [0].SourceOrdinal != -1)
401 ordinalMapping = true;
402 ValidateColumnMapping (table, tableCollations);
405 SqlCommand tmpCmd = new SqlCommand ();
406 TdsBulkCopy blkCopy = new TdsBulkCopy ((Tds)connection.Tds);
407 if (((Tds)connection.Tds).TdsVersion >= TdsVersion.tds70) {
408 string statement = "insert bulk " + DestinationTableName + " (";
409 statement += GenerateColumnMetaData (tmpCmd, colMetaData, tableCollations);
412 #region Check requested options and add corresponding modifiers to the statement
413 if ((copyOptions & insertModifiers) != SqlBulkCopyOptions.Default) {
414 statement += " WITH (";
415 bool commaRequired = false;
417 if ((copyOptions & SqlBulkCopyOptions.CheckConstraints) == SqlBulkCopyOptions.CheckConstraints) {
420 statement += "CHECK_CONSTRAINTS";
421 commaRequired = true;
424 if ((copyOptions & SqlBulkCopyOptions.TableLock) == SqlBulkCopyOptions.TableLock) {
427 statement += "TABLOCK";
428 commaRequired = true;
431 if ((copyOptions & SqlBulkCopyOptions.KeepNulls) == SqlBulkCopyOptions.KeepNulls) {
434 statement += "KEEP_NULLS";
435 commaRequired = true;
438 if ((copyOptions & SqlBulkCopyOptions.FireTriggers) == SqlBulkCopyOptions.FireTriggers) {
441 statement += "FIRE_TRIGGERS";
442 commaRequired = true;
447 #endregion Check requested options and add corresponding modifiers to the statement
449 blkCopy.SendColumnMetaData (statement);
451 blkCopy.BulkCopyStart (tmpCmd.Parameters.MetaParameters);
452 long noRowsCopied = 0;
453 foreach (DataRow row in table.Rows) {
454 if (row.RowState == DataRowState.Deleted)
455 continue; // Don't copy the row that's in deleted state
456 if (state != 0 && row.RowState != state)
458 bool isNewRow = true;
460 foreach (SqlParameter param in tmpCmd.Parameters) {
462 object rowToCopy = null;
463 if (_columnMappingCollection.Count > 0) {
464 if (ordinalMapping) {
465 foreach (SqlBulkCopyColumnMapping mapping
466 in _columnMappingCollection) {
467 if (mapping.DestinationOrdinal == i && param.Value == null) {
468 rowToCopy = row [mapping.SourceOrdinal];
469 SqlParameter parameter = new SqlParameter (mapping.SourceOrdinal.ToString (),
471 if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
472 parameter.SqlDbType = param.SqlDbType;
473 rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
475 string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
476 if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
477 if (row [i] != null && row [i] != DBNull.Value) {
478 size = ((string) parameter.Value).Length;
481 } else if (colType == "varchar" || colType == "text" || colType == "char") {
482 if (row [i] != null && row [i] != DBNull.Value)
483 size = ((string) parameter.Value).Length;
485 size = parameter.Size;
491 foreach (SqlBulkCopyColumnMapping mapping
492 in _columnMappingCollection) {
493 if (mapping.DestinationColumn == param.ParameterName) {
494 rowToCopy = row [mapping.SourceColumn];
495 SqlParameter parameter = new SqlParameter (mapping.SourceColumn, rowToCopy);
496 if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
497 parameter.SqlDbType = param.SqlDbType;
498 rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
500 string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
501 if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
502 if (row [mapping.SourceColumn] != null && row [mapping.SourceColumn] != DBNull.Value) {
503 size = ((string) rowToCopy).Length;
506 } else if (colType == "varchar" || colType == "text" || colType == "char") {
507 if (row [mapping.SourceColumn] != null && row [mapping.SourceColumn] != DBNull.Value)
508 size = ((string) rowToCopy).Length;
510 size = parameter.Size;
518 rowToCopy = row [param.ParameterName];
519 string colType = param.MetaParameter.TypeName;
521 If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
522 FIXME: Need to check for other types
524 if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
525 size = ((string) row [param.ParameterName]).Length;
527 } else if (colType == "varchar" || colType == "text" || colType == "char") {
528 size = ((string) row [param.ParameterName]).Length;
533 if (rowToCopy == null)
536 blkCopy.BulkCopyData (rowToCopy, isNewRow, size, param.MetaParameter);
540 } // foreach (SqlParameter)
541 if (_notifyAfter > 0) {
543 if (noRowsCopied >= _notifyAfter) {
544 RowsCopied (noRowsCopied);
548 } // foreach (DataRow)
549 blkCopy.BulkCopyEnd ();
552 private bool IsTextType(SqlDbType sqlType)
554 return (sqlType == SqlDbType.NText ||
555 sqlType == SqlDbType.NVarChar ||
556 sqlType == SqlDbType.Text ||
557 sqlType == SqlDbType.VarChar ||
558 sqlType == SqlDbType.Char ||
559 sqlType == SqlDbType.NChar);
562 public void WriteToServer (DataRow [] rows)
565 throw new ArgumentNullException ("rows");
566 if (rows.Length == 0)
568 DataTable table = new DataTable (rows [0].Table.TableName);
569 foreach (DataColumn col in rows [0].Table.Columns) {
570 DataColumn tmpCol = new DataColumn (col.ColumnName, col.DataType);
571 table.Columns.Add (tmpCol);
573 foreach (DataRow row in rows) {
574 DataRow tmpRow = table.NewRow ();
575 for (int i = 0; i < table.Columns.Count; i++) {
576 tmpRow [i] = row [i];
578 table.Rows.Add (tmpRow);
580 BulkCopyToServer (table, 0);
583 public void WriteToServer (DataTable table)
585 BulkCopyToServer (table, 0);
588 public void WriteToServer (IDataReader reader)
590 DataTable table = new DataTable ();
591 SqlDataAdapter adapter = new SqlDataAdapter ();
592 adapter.FillInternal (table, reader);
593 BulkCopyToServer (table, 0);
596 public void WriteToServer (DataTable table, DataRowState rowState)
598 BulkCopyToServer (table, rowState);
601 private void RowsCopied (long rowsCopied)
603 SqlRowsCopiedEventArgs e = new SqlRowsCopiedEventArgs (rowsCopied);
604 if (null != SqlRowsCopied) {
605 SqlRowsCopied (this, e);
613 public event SqlRowsCopiedEventHandler SqlRowsCopied;
617 void IDisposable.Dispose ()
619 //throw new NotImplementedException ();
620 if (isLocalConnection) {