class/System.Data/System.Data.SqlClient/SqlBulkCopy.cs

   1 //
   2 // System.Data.SqlClient.SqlBulkCopy.cs
   3 //
   4 // Author:
   5 //   Nagappan A (anagappan@novell.com)
   6 //
   7 // (C) Novell, Inc 2007
   8
   9 //
  10 // Copyright (C) 2007 Novell, Inc (http://www.novell.com)
  11 //
  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:
  19 //
  20 // The above copyright notice and this permission notice shall be
  21 // included in all copies or substantial portions of the Software.
  22 //
  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.
  30 //
  31 #if NET_2_0
  32
  33 using System;
  34 using System.Data;
  35 using System.Data.Common;
  36 using Mono.Data.Tds;
  37 using Mono.Data.Tds.Protocol;
  38
  39 namespace System.Data.SqlClient {
  40         public sealed class SqlBulkCopy : IDisposable
  41         {
  42                 #region Fields
  43
  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;
  55
  56                 #endregion
  57
  58                 #region Constructors
  59                 public SqlBulkCopy (SqlConnection connection)
  60                 {
  61                         this.connection = connection;
  62                 }
  63
  64                 public SqlBulkCopy (string connectionString)
  65                 {
  66                         this.connection = new SqlConnection (connectionString);
  67                         isLocalConnection = true;
  68                 }
  69
  70                 [MonoTODO]
  71                 public SqlBulkCopy (string connectionString, SqlBulkCopyOptions copyOptions)
  72                 {
  73                         this.connection = new SqlConnection (connectionString);
  74                         this.copyOptions = copyOptions;
  75                         isLocalConnection = true;
  76                         throw new NotImplementedException ();
  77                 }
  78
  79                 [MonoTODO]
  80                 public SqlBulkCopy (SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction)
  81                 {
  82                         this.connection = connection;
  83                         this.copyOptions = copyOptions;
  84                         throw new NotImplementedException ();
  85                 }
  86
  87                 #endregion
  88
  89                 #region Properties
  90
  91                 public int BatchSize {
  92                         get { return _batchSize; }
  93                         set { _batchSize = value; }
  94                 }
  95
  96                 public int BulkCopyTimeout {
  97                         get { return _bulkCopyTimeout; }
  98                         set { _bulkCopyTimeout = value; }
  99                 }
 100
 101                 public SqlBulkCopyColumnMappingCollection ColumnMappings  {
 102                         get { return _columnMappingCollection; }
 103                 }
 104
 105                 public string DestinationTableName {
 106                         get { return _destinationTableName; }
 107                         set { _destinationTableName = value; }
 108                 }
 109
 110                 public int NotifyAfter {
 111                         get { return _notifyAfter; }
 112                         set {
 113                                 if (value < 0)
 114                                         throw new ArgumentOutOfRangeException ("NotifyAfter should be greater than or equal to 0");
 115                                 _notifyAfter = value;
 116                         }
 117                 }
 118
 119                 #endregion
 120
 121                 #region Methods
 122
 123                 public void Close ()
 124                 {
 125                         if (sqlRowsCopied == true) {
 126                                 throw new InvalidOperationException ("Close should not be called from SqlRowsCopied event");
 127                         }
 128                         if (connection == null || connection.State == ConnectionState.Closed) {
 129                                 return;
 130                         }
 131                         connection.Close ();
 132                 }
 133
 134                 private DataTable [] GetColumnMetaData ()
 135                 {
 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 + "'",
 142                                                          connection);
 143                         SqlDataReader reader = cmd.ExecuteReader ();
 144                         int i = 0; // Skipping 1st result
 145                         do {
 146                                   if (i == 1) {
 147                                         columnMetaDataTables [i - 1] = reader.GetSchemaTable ();
 148                                   } else if (i == 2) {
 149                                         SqlDataAdapter adapter = new SqlDataAdapter ();
 150                                         adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 151                                         columnMetaDataTables [i - 1] = new DataTable ();
 152                                         adapter.FillInternal (columnMetaDataTables [i - 1], reader);
 153                                 }
 154                                 i++;
 155                         } while (reader.IsClosed == false && reader.NextResult());
 156                         reader.Close ();
 157                         return columnMetaDataTables;
 158                 }
 159
 160                 private string GenerateColumnMetaData (SqlCommand tmpCmd, DataTable colMetaData, DataTable tableCollations)
 161                 {
 162                         bool flag = false;
 163                         string statement = "";
 164                         int i = 0;
 165                         foreach (DataRow row in colMetaData.Rows) {
 166                                 flag = false;
 167                                 foreach (DataColumn col in colMetaData.Columns) { // FIXME: This line not required, remove later
 168                                         object value = null;
 169                                         if (_columnMappingCollection.Count > 0) {
 170                                                 if (ordinalMapping) {
 171                                                         foreach (SqlBulkCopyColumnMapping mapping
 172                                                                  in _columnMappingCollection) {
 173                                                                 if (mapping.DestinationOrdinal == i) {
 174                                                                         flag = true;
 175                                                                         break;
 176                                                                 }
 177                                                         }
 178                                                 } else {
 179                                                         foreach (SqlBulkCopyColumnMapping mapping
 180                                                                  in _columnMappingCollection) {
 181                                                                 if (mapping.DestinationColumn == (string) row ["ColumnName"]) {
 182                                                                         flag = true;
 183                                                                         break;
 184                                                                 }
 185                                                         }
 186                                                 }
 187                                                 if (flag == false)
 188                                                         break;
 189                                         }
 190                                         if ((bool)row ["IsReadOnly"]) {
 191                                                 if (ordinalMapping)
 192                                                         value = false;
 193                                                 else
 194                                                         break;
 195                                         }
 196                                         SqlParameter param = new SqlParameter ((string) row ["ColumnName"],
 197                                                                                ((SqlDbType) row ["ProviderType"]));
 198                                         param.Value = value;
 199                                         if ((int)row ["ColumnSize"] != -1) {
 200                                                 param.Size = (int) row ["ColumnSize"];
 201                                         }
 202                                         tmpCmd.Parameters.Add (param);
 203                                         break;
 204                                 }
 205                                 i++;
 206                         }
 207                         flag = false;
 208                         bool insertSt = false;
 209                         foreach (DataRow row in colMetaData.Rows) {
 210                                 if (_columnMappingCollection.Count > 0) {
 211                                         i = 0;
 212                                         insertSt = false;
 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) {
 218                                                                         insertSt = true;
 219                                                                 }
 220                                                         }
 221                                                 } else {
 222                                                         foreach (SqlBulkCopyColumnMapping mapping
 223                                                                  in _columnMappingCollection) {
 224                                                                 if (mapping.DestinationColumn == param.ParameterName &&
 225                                                                     (string)row ["ColumnName"] == param.ParameterName) {
 226                                                                         insertSt = true;
 227                                                                         param.Value = null;
 228                                                                 }
 229                                                         }
 230                                                 }
 231                                                 i++;
 232                                                 if (insertSt == true)
 233                                                         break;
 234                                         }
 235                                         if (insertSt == false)
 236                                                 continue;
 237                                 }
 238                                 if ((bool)row ["IsReadOnly"]) {
 239                                         continue;
 240                                 }
 241                                 string columnInfo = "";
 242                                 if ((int)row ["ColumnSize"] != -1) {
 243                                         columnInfo = string.Format ("{0}({1})",
 244                                                                     (SqlDbType) row ["ProviderType"],
 245                                                                     row ["ColumnSize"]);
 246                                 } else {
 247                                         columnInfo = string.Format ("{0}", (SqlDbType) row ["ProviderType"]);
 248                                 }
 249                                 if (flag)
 250                                         statement += ", ";
 251                                 string columnName = (string) row ["ColumnName"];
 252                                 statement += string.Format ("[{0}] {1}", columnName, columnInfo);
 253                                 if (flag == false)
 254                                         flag = true;
 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"]);
 259                                                         break;
 260                                                 }
 261                                         }
 262                                 }
 263                         }
 264                         return statement;
 265                 }
 266
 267                 private void ValidateColumnMapping (DataTable table, DataTable tableCollations)
 268                 {
 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");
 278                                 bool flag = false;
 279                                 if (ordinalMapping == false) {
 280                                         foreach (DataRow row in tableCollations.Rows) {
 281                                                 if ((string)row ["name"] == _columnMapping.DestinationColumn) {
 282                                                         flag = true;
 283                                                         break;
 284                                                 }
 285                                         }
 286                                         if (flag == false)
 287                                                 throw new InvalidOperationException ("ColumnMapping does not match");
 288                                         flag = false;
 289                                         foreach (DataColumn col in table.Columns) {
 290                                                 if (col.ColumnName == _columnMapping.SourceColumn) {
 291                                                         flag = true;
 292                                                         break;
 293                                                 }
 294                                         }
 295                                         if (flag == false)
 296                                                 throw new InvalidOperationException ("ColumnName " +
 297                                                                                      _columnMapping.SourceColumn +
 298                                                                                      " does not match");
 299                                 } else {
 300                                         if (_columnMapping.DestinationOrdinal >= tableCollations.Rows.Count)
 301                                                 throw new InvalidOperationException ("ColumnMapping does not match");
 302                                 }
 303                         }
 304                 }
 305
 306                 private void BulkCopyToServer (DataTable table, DataRowState state)
 307                 {
 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",
 315                                                                  connection);
 316                                 cmd.ExecuteScalar ();
 317                         }
 318                         DataTable [] columnMetaDataTables = GetColumnMetaData ();
 319                         DataTable colMetaData = columnMetaDataTables [0];
 320                         DataTable tableCollations = columnMetaDataTables [1];
 321
 322                         if (_columnMappingCollection.Count > 0) {
 323                                 if (_columnMappingCollection [0].SourceOrdinal != -1)
 324                                         ordinalMapping = true;
 325                                 ValidateColumnMapping (table, tableCollations);
 326                         }
 327
 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);
 333                                 statement += ")";
 334                                 blkCopy.SendColumnMetaData (statement);
 335                         }
 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)
 342                                         continue;
 343                                 bool isNewRow = true;
 344                                 int i = 0;
 345                                 foreach (SqlParameter param in tmpCmd.Parameters) {
 346                                         int size = 0;
 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 (),
 355                                                                                                                    rowToCopy);
 356                                                                         if (param.MetaParameter.TypeName != parameter.MetaParameter.TypeName) {
 357                                                                                 parameter.SqlDbType = param.SqlDbType;
 358                                                                                 rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
 359                                                                         }
 360                                                                         string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
 361                                                                         if (colType == "nvarchar") {
 362                                                                                 if (row [i] != null) {
 363                                                                                         size = ((string) parameter.Value).Length;
 364                                                                                         size <<= 1;
 365                                                                                 }
 366                                                                         } else {
 367                                                                                 size = parameter.Size;
 368                                                                         }
 369                                                                         break;
 370                                                                 }
 371                                                         }
 372                                                 } else {
 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);
 381                                                                         }
 382                                                                         string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
 383                                                                         if (colType == "nvarchar") {
 384                                                                                 if (row [mapping.SourceColumn] != null) {
 385                                                                                         size = ((string) rowToCopy).Length;
 386                                                                                         size <<= 1;
 387                                                                                 }
 388                                                                         } else {
 389                                                                                 size = parameter.Size;
 390                                                                         }
 391                                                                         break;
 392                                                                 }
 393                                                         }
 394                                                 }
 395                                                 i++;
 396                                         } else {
 397                                                 rowToCopy = row [param.ParameterName];
 398                                                 string colType = param.MetaParameter.TypeName;
 399                                                 /*
 400                                                   If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
 401                                                   FIXME: Need to check for other types
 402                                                 */
 403                                                 if (colType == "nvarchar") {
 404                                                         size = ((string) row [param.ParameterName]).Length;
 405                                                         size <<= 1;
 406                                                 } else {
 407                                                         size = param.Size;
 408                                                 }
 409                                         }
 410                                         if (rowToCopy == null)
 411                                                 continue;
 412                                         blkCopy.BulkCopyData (rowToCopy, size, isNewRow);
 413                                         if (isNewRow)
 414                                                 isNewRow = false;
 415                                 } // foreach (SqlParameter)
 416                                 if (_notifyAfter > 0) {
 417                                         noRowsCopied ++;
 418                                         if (noRowsCopied >= _notifyAfter) {
 419                                                 RowsCopied (noRowsCopied);
 420                                                 noRowsCopied = 0;
 421                                         }
 422                                 }
 423                         } // foreach (DataRow)
 424                         blkCopy.BulkCopyEnd ();
 425                 }
 426
 427                 public void WriteToServer (DataRow [] rows)
 428                 {
 429                         if (rows == null)
 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);
 435                         }
 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];
 440                                 }
 441                                 table.Rows.Add (tmpRow);
 442                         }
 443                         BulkCopyToServer (table, 0);
 444                 }
 445
 446                 public void WriteToServer (DataTable table)
 447                 {
 448                         BulkCopyToServer (table, 0);
 449                 }
 450
 451                 public void WriteToServer (IDataReader reader)
 452                 {
 453                         DataTable table = new DataTable ();
 454                         SqlDataAdapter adapter = new SqlDataAdapter ();
 455                         adapter.FillInternal (table, reader);
 456                         BulkCopyToServer (table, 0);
 457                 }
 458
 459                 public void WriteToServer (DataTable table, DataRowState rowState)
 460                 {
 461                         BulkCopyToServer (table, rowState);
 462                 }
 463
 464                 private void RowsCopied (long rowsCopied)
 465                 {
 466                         SqlRowsCopiedEventArgs e = new SqlRowsCopiedEventArgs (rowsCopied);
 467                         if (null != SqlRowsCopied) {
 468                                 SqlRowsCopied (this, e);
 469                         }
 470                 }
 471
 472                 #endregion
 473
 474                 #region Events
 475
 476                 public event SqlRowsCopiedEventHandler SqlRowsCopied;
 477
 478                 #endregion
 479
 480                 void IDisposable.Dispose ()
 481                 {
 482                         //throw new NotImplementedException ();
 483                         if (isLocalConnection) {
 484                                 Close ();
 485                                 connection = null;
 486                         }
 487                 }
 488
 489         }
 490 }
 491
 492 #endif