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 public sealed class SqlBulkCopy : IDisposable
44 private int _batchSize = 0;
45 private int _notifyAfter = 0;
46 private int _bulkCopyTimeout = 0;
47 private SqlBulkCopyColumnMappingCollection _columnMappingCollection = new SqlBulkCopyColumnMappingCollection ();
48 private string _destinationTableName = null;
49 private bool ordinalMapping = false;
50 bool sqlRowsCopied = false;
51 bool identityInsert = false;
52 bool isLocalConnection = false;
53 SqlConnection connection;
54 SqlBulkCopyOptions copyOptions = SqlBulkCopyOptions.Default;
59 public SqlBulkCopy (SqlConnection connection)
61 this.connection = connection;
64 public SqlBulkCopy (string connectionString)
66 this.connection = new SqlConnection (connectionString);
67 isLocalConnection = true;
71 public SqlBulkCopy (string connectionString, SqlBulkCopyOptions copyOptions)
73 this.connection = new SqlConnection (connectionString);
74 this.copyOptions = copyOptions;
75 isLocalConnection = true;
76 throw new NotImplementedException ();
80 public SqlBulkCopy (SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction)
82 this.connection = connection;
83 this.copyOptions = copyOptions;
84 throw new NotImplementedException ();
91 public int BatchSize {
92 get { return _batchSize; }
93 set { _batchSize = value; }
96 public int BulkCopyTimeout {
97 get { return _bulkCopyTimeout; }
98 set { _bulkCopyTimeout = value; }
101 public SqlBulkCopyColumnMappingCollection ColumnMappings {
102 get { return _columnMappingCollection; }
105 public string DestinationTableName {
106 get { return _destinationTableName; }
107 set { _destinationTableName = value; }
110 public int NotifyAfter {
111 get { return _notifyAfter; }
114 throw new ArgumentOutOfRangeException ("NotifyAfter should be greater than or equal to 0");
115 _notifyAfter = value;
125 if (sqlRowsCopied == true) {
126 throw new InvalidOperationException ("Close should not be called from SqlRowsCopied event");
128 if (connection == null || connection.State == ConnectionState.Closed) {
134 private DataTable [] GetColumnMetaData ()
136 DataTable [] columnMetaDataTables = new DataTable [2];
137 SqlCommand cmd = new SqlCommand ("select @@trancount; " +
138 "set fmtonly on select * from " +
139 DestinationTableName + " set fmtonly off;" +
140 "exec sp_tablecollations_90 '" +
141 DestinationTableName + "'",
143 SqlDataReader reader = cmd.ExecuteReader ();
144 int i = 0; // Skipping 1st result
147 columnMetaDataTables [i - 1] = reader.GetSchemaTable ();
149 SqlDataAdapter adapter = new SqlDataAdapter ();
150 adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
151 columnMetaDataTables [i - 1] = new DataTable ();
152 adapter.FillInternal (columnMetaDataTables [i - 1], reader);
155 } while (reader.IsClosed == false && reader.NextResult());
157 return columnMetaDataTables;
160 private string GenerateColumnMetaData (SqlCommand tmpCmd, DataTable colMetaData, DataTable tableCollations)
163 string statement = "";
165 foreach (DataRow row in colMetaData.Rows) {
167 foreach (DataColumn col in colMetaData.Columns) { // FIXME: This line not required, remove later
169 if (_columnMappingCollection.Count > 0) {
170 if (ordinalMapping) {
171 foreach (SqlBulkCopyColumnMapping mapping
172 in _columnMappingCollection) {
173 if (mapping.DestinationOrdinal == i) {
179 foreach (SqlBulkCopyColumnMapping mapping
180 in _columnMappingCollection) {
181 if (mapping.DestinationColumn == (string) row ["ColumnName"]) {
190 if ((bool)row ["IsReadOnly"]) {
196 SqlParameter param = new SqlParameter ((string) row ["ColumnName"],
197 ((SqlDbType) row ["ProviderType"]));
199 if ((int)row ["ColumnSize"] != -1) {
200 param.Size = (int) row ["ColumnSize"];
202 tmpCmd.Parameters.Add (param);
208 bool insertSt = false;
209 foreach (DataRow row in colMetaData.Rows) {
210 if (_columnMappingCollection.Count > 0) {
213 foreach (SqlParameter param in tmpCmd.Parameters) {
214 if (ordinalMapping) {
215 foreach (SqlBulkCopyColumnMapping mapping
216 in _columnMappingCollection) {
217 if (mapping.DestinationOrdinal == i && param.Value == null) {
222 foreach (SqlBulkCopyColumnMapping mapping
223 in _columnMappingCollection) {
224 if (mapping.DestinationColumn == param.ParameterName &&
225 (string)row ["ColumnName"] == param.ParameterName) {
232 if (insertSt == true)
235 if (insertSt == false)
238 if ((bool)row ["IsReadOnly"]) {
241 string columnInfo = "";
242 if ((int)row ["ColumnSize"] != -1) {
243 columnInfo = string.Format ("{0}({1})",
244 (SqlDbType) row ["ProviderType"],
247 columnInfo = string.Format ("{0}", (SqlDbType) row ["ProviderType"]);
251 string columnName = (string) row ["ColumnName"];
252 statement += string.Format ("[{0}] {1}", columnName, columnInfo);
255 if (tableCollations != null) {
256 foreach (DataRow collationRow in tableCollations.Rows) {
257 if ((string)collationRow ["name"] == columnName) {
258 statement += string.Format (" COLLATE {0}", collationRow ["collation"]);
267 private void ValidateColumnMapping (DataTable table, DataTable tableCollations)
269 foreach (SqlBulkCopyColumnMapping _columnMapping in _columnMappingCollection) {
270 if (ordinalMapping == false &&
271 (_columnMapping.DestinationColumn == String.Empty ||
272 _columnMapping.SourceColumn == String.Empty))
273 throw new InvalidOperationException ("Mappings must be either all null or ordinal");
274 if (ordinalMapping &&
275 (_columnMapping.DestinationOrdinal == -1 ||
276 _columnMapping.SourceOrdinal == -1))
277 throw new InvalidOperationException ("Mappings must be either all null or ordinal");
279 if (ordinalMapping == false) {
280 foreach (DataRow row in tableCollations.Rows) {
281 if ((string)row ["name"] == _columnMapping.DestinationColumn) {
287 throw new InvalidOperationException ("ColumnMapping does not match");
289 foreach (DataColumn col in table.Columns) {
290 if (col.ColumnName == _columnMapping.SourceColumn) {
296 throw new InvalidOperationException ("ColumnName " +
297 _columnMapping.SourceColumn +
300 if (_columnMapping.DestinationOrdinal >= tableCollations.Rows.Count)
301 throw new InvalidOperationException ("ColumnMapping does not match");
306 private void BulkCopyToServer (DataTable table, DataRowState state)
308 if (connection == null || connection.State == ConnectionState.Closed)
309 throw new InvalidOperationException ("This method should not be called on a closed connection");
310 if (_destinationTableName == null)
311 throw new ArgumentNullException ("DestinationTableName");
312 if (identityInsert) {
313 SqlCommand cmd = new SqlCommand ("set identity_insert " +
314 table.TableName + " on",
316 cmd.ExecuteScalar ();
318 DataTable [] columnMetaDataTables = GetColumnMetaData ();
319 DataTable colMetaData = columnMetaDataTables [0];
320 DataTable tableCollations = columnMetaDataTables [1];
322 if (_columnMappingCollection.Count > 0) {
323 if (_columnMappingCollection [0].SourceOrdinal != -1)
324 ordinalMapping = true;
325 ValidateColumnMapping (table, tableCollations);
328 SqlCommand tmpCmd = new SqlCommand ();
329 TdsBulkCopy blkCopy = new TdsBulkCopy ((Tds)connection.Tds);
330 if (((Tds)connection.Tds).TdsVersion >= TdsVersion.tds70) {
331 string statement = "insert bulk " + DestinationTableName + " (";
332 statement += GenerateColumnMetaData (tmpCmd, colMetaData, tableCollations);
334 blkCopy.SendColumnMetaData (statement);
336 blkCopy.BulkCopyStart (tmpCmd.Parameters.MetaParameters);
337 long noRowsCopied = 0;
338 foreach (DataRow row in table.Rows) {
339 if (row.RowState == DataRowState.Deleted)
340 continue; // Don't copy the row that's in deleted state
341 if (state != 0 && row.RowState != state)
343 bool isNewRow = true;
345 foreach (SqlParameter param in tmpCmd.Parameters) {
347 object rowToCopy = null;
348 if (_columnMappingCollection.Count > 0) {
349 if (ordinalMapping) {
350 foreach (SqlBulkCopyColumnMapping mapping
351 in _columnMappingCollection) {
352 if (mapping.DestinationOrdinal == i && param.Value == null) {
353 rowToCopy = row [mapping.SourceOrdinal];
354 SqlParameter parameter = new SqlParameter (mapping.SourceOrdinal.ToString (),
356 if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
357 parameter.SqlDbType = param.SqlDbType;
358 rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
360 string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
361 if (colType == "nvarchar") {
362 if (row [i] != null) {
363 size = ((string) parameter.Value).Length;
367 size = parameter.Size;
373 foreach (SqlBulkCopyColumnMapping mapping
374 in _columnMappingCollection) {
375 if (mapping.DestinationColumn == param.ParameterName) {
376 rowToCopy = row [mapping.SourceColumn];
377 SqlParameter parameter = new SqlParameter (mapping.SourceColumn, rowToCopy);
378 if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
379 parameter.SqlDbType = param.SqlDbType;
380 rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
382 string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
383 if (colType == "nvarchar") {
384 if (row [mapping.SourceColumn] != null) {
385 size = ((string) rowToCopy).Length;
389 size = parameter.Size;
397 rowToCopy = row [param.ParameterName];
398 string colType = param.MetaParameter.TypeName;
400 If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
401 FIXME: Need to check for other types
403 if (colType == "nvarchar") {
404 size = ((string) row [param.ParameterName]).Length;
410 if (rowToCopy == null)
412 blkCopy.BulkCopyData (rowToCopy, size, isNewRow);
415 } // foreach (SqlParameter)
416 if (_notifyAfter > 0) {
418 if (noRowsCopied >= _notifyAfter) {
419 RowsCopied (noRowsCopied);
423 } // foreach (DataRow)
424 blkCopy.BulkCopyEnd ();
427 public void WriteToServer (DataRow [] rows)
430 throw new ArgumentNullException ("rows");
431 DataTable table = new DataTable (rows [0].Table.TableName);
432 foreach (DataColumn col in rows [0].Table.Columns) {
433 DataColumn tmpCol = new DataColumn (col.ColumnName, col.DataType);
434 table.Columns.Add (tmpCol);
436 foreach (DataRow row in rows) {
437 DataRow tmpRow = table.NewRow ();
438 for (int i = 0; i < table.Columns.Count; i++) {
439 tmpRow [i] = row [i];
441 table.Rows.Add (tmpRow);
443 BulkCopyToServer (table, 0);
446 public void WriteToServer (DataTable table)
448 BulkCopyToServer (table, 0);
451 public void WriteToServer (IDataReader reader)
453 DataTable table = new DataTable ();
454 SqlDataAdapter adapter = new SqlDataAdapter ();
455 adapter.FillInternal (table, reader);
456 BulkCopyToServer (table, 0);
459 public void WriteToServer (DataTable table, DataRowState rowState)
461 BulkCopyToServer (table, rowState);
464 private void RowsCopied (long rowsCopied)
466 SqlRowsCopiedEventArgs e = new SqlRowsCopiedEventArgs (rowsCopied);
467 if (null != SqlRowsCopied) {
468 SqlRowsCopied (this, e);
476 public event SqlRowsCopiedEventHandler SqlRowsCopied;
480 void IDisposable.Dispose ()
482 //throw new NotImplementedException ();
483 if (isLocalConnection) {