mcs/class/System.Data/System.Data.SqlClient/SqlDataAdapter.cs

   1 //
   2 // System.Data.SqlClient.SqlDataAdapter.cs
   3 //
   4 // Author:
   5 //   Rodrigo Moya (rodrigo@ximian.com)
   6 //   Daniel Morgan (danmorg@sc.rr.com)
   7 //   Tim Coleman (tim@timcoleman.com)
   8 //       Veerapuram Varadhan  (vvaradhan@novell.com)
   9 //
  10 // (C) Ximian, Inc 2002
  11 // Copyright (C) 2002 Tim Coleman
  12 //
  13 // Copyright (C) 2004, 2009 Novell, Inc (http://www.novell.com)
  14 //
  15 // Permission is hereby granted, free of charge, to any person obtaining
  16 // a copy of this software and associated documentation files (the
  17 // "Software"), to deal in the Software without restriction, including
  18 // without limitation the rights to use, copy, modify, merge, publish,
  19 // distribute, sublicense, and/or sell copies of the Software, and to
  20 // permit persons to whom the Software is furnished to do so, subject to
  21 // the following conditions:
  22 //
  23 // The above copyright notice and this permission notice shall be
  24 // included in all copies or substantial portions of the Software.
  25 //
  26 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  27 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  28 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  29 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  30 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  31 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  32 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  33 //
  34
  35 using System;
  36 using System.Collections;
  37 using System.ComponentModel;
  38 using System.Data;
  39 using System.Data.Common;
  40
  41 namespace System.Data.SqlClient {
  42         [DefaultEvent ("RowUpdated")]
  43         [DesignerAttribute ("Microsoft.VSDesigner.Data.VS.SqlDataAdapterDesigner, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.ComponentModel.Design.IDesigner")]
  44         [ToolboxItemAttribute ("Microsoft.VSDesigner.Data.VS.SqlDataAdapterToolboxItem, "+ Consts.AssemblyMicrosoft_VSDesigner)]
  45
  46         public sealed class SqlDataAdapter : DbDataAdapter, IDbDataAdapter, IDataAdapter, ICloneable
  47         {
  48
  49 #region Copy from old DataColumn
  50                 internal static bool CanAutoIncrement (Type type)
  51                 {
  52                         switch (Type.GetTypeCode (type)) {
  53                                 case TypeCode.Int16:
  54                                 case TypeCode.Int32:
  55                                 case TypeCode.Int64:
  56                                 case TypeCode.Decimal:
  57                                         return true;
  58                         }
  59
  60                         return false;
  61                 }
  62 #endregion
  63
  64 #region Copy from old DataAdapter
  65
  66                 private const string DefaultSourceColumnName = "Column";
  67
  68                 internal FillErrorEventArgs CreateFillErrorEvent (DataTable dataTable, object[] values, Exception e)
  69                 {
  70                         FillErrorEventArgs args = new FillErrorEventArgs (dataTable, values);
  71                         args.Errors = e;
  72                         args.Continue = false;
  73                         return args;
  74                 }
  75
  76                 internal void OnFillErrorInternal (FillErrorEventArgs value)
  77                 {
  78                         OnFillError (value);
  79                 }
  80
  81                 // this method builds the schema for a given datatable. it returns a int array with
  82                 // "array[ordinal of datatable column] == index of source column in data reader".
  83                 // each column in the datatable has a mapping to a specific column in the datareader,
  84                 // the int array represents this match.
  85                 internal int[] BuildSchema (IDataReader reader, DataTable table, SchemaType schemaType)
  86                 {
  87                         return BuildSchema (reader, table, schemaType, MissingSchemaAction,
  88                                             MissingMappingAction, TableMappings);
  89                 }
  90
  91                 /// <summary>
  92                 ///     Creates or Modifies the schema of the given DataTable based on the schema of
  93                 ///     the reader and the arguments passed.
  94                 /// </summary>
  95                 internal static int[] BuildSchema (IDataReader reader, DataTable table,
  96                                                    SchemaType schemaType,
  97                                                    MissingSchemaAction missingSchAction,
  98                                                    MissingMappingAction missingMapAction,
  99                                                    DataTableMappingCollection dtMapping
 100                                                    )
 101                 {
 102                         int readerIndex = 0;
 103                         // FIXME : this fails if query has fewer columns than a table
 104                         int[] mapping = new int[table.Columns.Count]; // mapping the reader indexes to the datatable indexes
 105
 106                         for(int i=0; i < mapping.Length; i++) {
 107                                 mapping[i] = -1;
 108                         }
 109
 110                         ArrayList primaryKey = new ArrayList ();
 111                         ArrayList sourceColumns = new ArrayList ();
 112                         bool createPrimaryKey = true;
 113
 114                         DataTable schemaTable = reader.GetSchemaTable ();
 115
 116                         DataColumn ColumnNameCol =  schemaTable.Columns["ColumnName"];
 117                         DataColumn DataTypeCol = schemaTable.Columns["DataType"];
 118                         DataColumn IsAutoIncrementCol = schemaTable.Columns["IsAutoIncrement"];
 119                         DataColumn AllowDBNullCol = schemaTable.Columns["AllowDBNull"];
 120                         DataColumn IsReadOnlyCol = schemaTable.Columns["IsReadOnly"];
 121                         DataColumn IsKeyCol = schemaTable.Columns["IsKey"];
 122                         DataColumn IsUniqueCol = schemaTable.Columns["IsUnique"];
 123                         DataColumn ColumnSizeCol = schemaTable.Columns["ColumnSize"];
 124
 125                         foreach (DataRow schemaRow in schemaTable.Rows) {
 126                                 // generate a unique column name in the source table.
 127                                 string sourceColumnName;
 128                                 string realSourceColumnName ;
 129                                 if (ColumnNameCol == null || schemaRow.IsNull(ColumnNameCol) ||
 130                                     (string)schemaRow [ColumnNameCol] == String.Empty) {
 131                                         sourceColumnName = DefaultSourceColumnName;
 132                                         realSourceColumnName = DefaultSourceColumnName + "1";
 133                                 } else {
 134                                         sourceColumnName = (string) schemaRow [ColumnNameCol];
 135                                         realSourceColumnName = sourceColumnName;
 136                                 }
 137
 138                                 for (int i = 1; sourceColumns.Contains (realSourceColumnName); i += 1)
 139                                         realSourceColumnName = String.Format ("{0}{1}", sourceColumnName, i);
 140                                 sourceColumns.Add(realSourceColumnName);
 141
 142                                 // generate DataSetColumnName from DataTableMapping, if any
 143                                 DataTableMapping tableMapping = null;
 144
 145                                 //FIXME : The sourcetable name shud get passed as a parameter..
 146                                 int index = dtMapping.IndexOfDataSetTable (table.TableName);
 147                                 string srcTable = (index != -1 ? dtMapping[index].SourceTable : table.TableName);
 148                                 tableMapping = DataTableMappingCollection.GetTableMappingBySchemaAction (dtMapping, ADP.IsEmpty (srcTable) ? " " : srcTable, table.TableName, missingMapAction);
 149                                 if (tableMapping != null) {
 150                                         table.TableName = tableMapping.DataSetTable;
 151                                         // check to see if the column mapping exists
 152                                         DataColumnMapping columnMapping = DataColumnMappingCollection.GetColumnMappingBySchemaAction(tableMapping.ColumnMappings, realSourceColumnName, missingMapAction);
 153                                         if (columnMapping != null) {
 154                                                 Type columnType = schemaRow[DataTypeCol] as Type;
 155                                                 DataColumn col = columnType != null ? columnMapping.GetDataColumnBySchemaAction(
 156                                                                                                                                 table ,
 157                                                                                                                                 columnType,
 158                                                                                                                                 missingSchAction) : null;
 159
 160                                                 if (col != null) {
 161                                                         // if the column is not in the table - add it.
 162                                                         if (table.Columns.IndexOf(col) == -1) {
 163                                                                 if (missingSchAction == MissingSchemaAction.Add
 164                                                                     || missingSchAction == MissingSchemaAction.AddWithKey)
 165                                                                         table.Columns.Add(col);
 166
 167                                                                 int[] tmp = new int[mapping.Length + 1];
 168                                                                 Array.Copy(mapping,0,tmp,0,col.Ordinal);
 169                                                                 Array.Copy(mapping,col.Ordinal,tmp,col.Ordinal + 1,mapping.Length - col.Ordinal);
 170                                                                 mapping = tmp;
 171                                                         }
 172
 173                                                         if (missingSchAction == MissingSchemaAction.AddWithKey) {
 174                                                                 object value = (AllowDBNullCol != null) ? schemaRow[AllowDBNullCol] : null;
 175                                                                 bool allowDBNull = value is bool ? (bool)value : true;
 176
 177                                                                 value = (IsKeyCol != null) ? schemaRow[IsKeyCol] : null;
 178                                                                 bool isKey = value is bool ? (bool)value : false;
 179
 180                                                                 value = (IsAutoIncrementCol != null) ? schemaRow[IsAutoIncrementCol] : null;
 181                                                                 bool isAutoIncrement = value is bool ? (bool)value : false;
 182
 183                                                                 value = (IsReadOnlyCol != null) ? schemaRow[IsReadOnlyCol] : null;
 184                                                                 bool isReadOnly = value is bool ? (bool)value : false;
 185
 186                                                                 value = (IsUniqueCol != null) ? schemaRow[IsUniqueCol] : null;
 187                                                                 bool isUnique = value is bool ? (bool)value : false;
 188
 189                                                                 col.AllowDBNull = allowDBNull;
 190                                                                 // fill woth key info
 191                                                                 if (isAutoIncrement && CanAutoIncrement(columnType)) {
 192                                                                         col.AutoIncrement = true;
 193                                                                         if (!allowDBNull)
 194                                                                                 col.AllowDBNull = false;
 195                                                                 }
 196
 197                                                                 if (columnType == DbTypes.TypeOfString) {
 198                                                                         col.MaxLength = (ColumnSizeCol != null) ? (int)schemaRow[ColumnSizeCol] : 0;
 199                                                                 }
 200
 201                                                                 if (isReadOnly)
 202                                                                         col.ReadOnly = true;
 203
 204                                                                 if (!allowDBNull && (!isReadOnly || isKey))
 205                                                                         col.AllowDBNull = false;
 206                                                                 if (isUnique && !isKey && !columnType.IsArray) {
 207                                                                         col.Unique = true;
 208                                                                         if (!allowDBNull)
 209                                                                                 col.AllowDBNull = false;
 210                                                                 }
 211
 212                                                                 // This might not be set by all DataProviders
 213                                                                 bool isHidden = false;
 214                                                                 if (schemaTable.Columns.Contains ("IsHidden")) {
 215                                                                         value = schemaRow["IsHidden"];
 216                                                                         isHidden = ((value is bool) ? (bool)value : false);
 217                                                                 }
 218
 219                                                                 if (isKey && !isHidden) {
 220                                                                         primaryKey.Add (col);
 221                                                                         if (allowDBNull)
 222                                                                                 createPrimaryKey = false;
 223                                                                 }
 224                                                         }
 225                                                         // add the ordinal of the column as a key and the index of the column in the datareader as a value.
 226                                                         mapping[col.Ordinal] = readerIndex++;
 227                                                 }
 228                                         }
 229                                 }
 230                         }
 231                         if (primaryKey.Count > 0) {
 232                                 DataColumn[] colKey = (DataColumn[])(primaryKey.ToArray(typeof (DataColumn)));
 233                                 if (createPrimaryKey)
 234                                         table.PrimaryKey = colKey;
 235                                 else {
 236                                         UniqueConstraint uConstraint = new UniqueConstraint(colKey);
 237                                         for (int i = 0; i < table.Constraints.Count; i++) {
 238                                                 if (table.Constraints[i].Equals(uConstraint)) {
 239                                                         uConstraint = null;
 240                                                         break;
 241                                                 }
 242                                         }
 243
 244                                         if (uConstraint != null)
 245                                                 table.Constraints.Add(uConstraint);
 246                                 }
 247                         }
 248                         return mapping;
 249                 }
 250
 251                 internal int FillInternal (DataTable dataTable, IDataReader dataReader)
 252                 {
 253                         if (dataReader.FieldCount == 0) {
 254                                 dataReader.Close ();
 255                                 return 0;
 256                         }
 257
 258                         int count = 0;
 259
 260                         try {
 261                                 string tableName = SetupSchema (SchemaType.Mapped, dataTable.TableName);
 262                                 if (tableName != null) {
 263                                         dataTable.TableName = tableName;
 264                                         FillTable (dataTable, dataReader, 0, 0, ref count);
 265                                 }
 266                         } finally {
 267                                 dataReader.Close ();
 268                         }
 269
 270                         return count;
 271                 }
 272
 273                 internal bool FillTable (DataTable dataTable, IDataReader dataReader, int startRecord, int maxRecords, ref int counter)
 274                 {
 275                         if (dataReader.FieldCount == 0)
 276                                 return false;
 277
 278                         int counterStart = counter;
 279
 280                         int[] mapping = BuildSchema (dataReader, dataTable, SchemaType.Mapped);
 281
 282                         int [] sortedMapping = new int [mapping.Length];
 283                         int length = sortedMapping.Length;
 284                         for (int i = 0; i < sortedMapping.Length; i++) {
 285                                 if (mapping [i] >= 0)
 286                                         sortedMapping [mapping [i]] = i;
 287                                 else
 288                                         sortedMapping [--length] = i;
 289                         }
 290
 291                         for (int i = 0; i < startRecord; i++) {
 292                                 dataReader.Read ();
 293                         }
 294
 295                         dataTable.BeginLoadData ();
 296                         object [] values = new object [length];
 297                         while (dataReader.Read () && (maxRecords == 0 || (counter - counterStart) < maxRecords)) {
 298                                 try {
 299                                         for (int iColumn = 0; iColumn < values.Length; iColumn++)
 300                                                 values [iColumn] = dataReader [iColumn];
 301                                         dataTable.LoadDataRow (values, AcceptChangesDuringFill);
 302                                         counter++;
 303                                 }
 304                                 catch (Exception e) {
 305                                         object[] readerArray = new object [dataReader.FieldCount];
 306                                         object[] tableArray = new object [mapping.Length];
 307                                         // we get the values from the datareader
 308                                         dataReader.GetValues (readerArray);
 309                                         // copy from datareader columns to table columns according to given mapping
 310                                         for (int i = 0; i < mapping.Length; i++) {
 311                                                 if (mapping [i] >= 0) {
 312                                                         tableArray [i] = readerArray [mapping [i]];
 313                                                 }
 314                                         }
 315                                         FillErrorEventArgs args = CreateFillErrorEvent (dataTable, tableArray, e);
 316                                         OnFillErrorInternal (args);
 317
 318                                         // if args.Continue is not set to true or if a handler is not set, rethrow the error..
 319                                         if(!args.Continue)
 320                                                 throw e;
 321                                 }
 322                         }
 323                         dataTable.EndLoadData ();
 324                         return true;
 325                 }
 326
 327                 internal string SetupSchema (SchemaType schemaType, string sourceTableName)
 328                 {
 329                         DataTableMapping tableMapping = null;
 330
 331                         if (schemaType == SchemaType.Mapped) {
 332                                 tableMapping = DataTableMappingCollection.GetTableMappingBySchemaAction (TableMappings, sourceTableName, sourceTableName, MissingMappingAction);
 333                                 if (tableMapping != null)
 334                                         return tableMapping.DataSetTable;
 335                                 return null;
 336                         } else
 337                                 return sourceTableName;
 338                 }
 339 #endregion
 340
 341                 #region Fields
 342
 343                 int updateBatchSize;
 344                 #endregion
 345
 346                 #region Constructors
 347
 348                 public SqlDataAdapter () : this ((SqlCommand) null)
 349                 {
 350                 }
 351
 352                 public SqlDataAdapter (SqlCommand selectCommand)
 353                 {
 354                         SelectCommand = selectCommand;
 355                         UpdateBatchSize = 1;
 356                 }
 357
 358                 public SqlDataAdapter (string selectCommandText, SqlConnection selectConnection)
 359                         : this (new SqlCommand (selectCommandText, selectConnection))
 360                 {
 361                 }
 362
 363                 public SqlDataAdapter (string selectCommandText, string selectConnectionString)
 364                         : this (selectCommandText, new SqlConnection (selectConnectionString))
 365                 {
 366                 }
 367
 368                 #endregion
 369
 370                 #region Properties
 371
 372                 [DefaultValue (null)]
 373                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DBCommandEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 374                 public new SqlCommand DeleteCommand { get; set; }
 375
 376                 [DefaultValue (null)]
 377                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DBCommandEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 378                 public new SqlCommand InsertCommand { get; set; }
 379
 380                 [DefaultValue (null)]
 381                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DBCommandEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 382                 public new SqlCommand SelectCommand { get; set; }
 383
 384                 [DefaultValue (null)]
 385                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DBCommandEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 386                 public new  SqlCommand UpdateCommand { get; set; }
 387
 388                 IDbCommand IDbDataAdapter.SelectCommand {
 389                         get { return SelectCommand; }
 390                         set { SelectCommand = (SqlCommand) value; }
 391                 }
 392
 393                 IDbCommand IDbDataAdapter.InsertCommand {
 394                         get { return InsertCommand; }
 395                         set { InsertCommand = (SqlCommand) value; }
 396                 }
 397
 398                 IDbCommand IDbDataAdapter.UpdateCommand {
 399                         get { return UpdateCommand; }
 400                         set { UpdateCommand = (SqlCommand) value; }
 401                 }
 402                 IDbCommand IDbDataAdapter.DeleteCommand {
 403                         get { return DeleteCommand; }
 404                         set { DeleteCommand = (SqlCommand) value; }
 405                 }
 406
 407                 public override int UpdateBatchSize {
 408                         get { return updateBatchSize; }
 409                         set {
 410                                 if (value < 0)
 411                                         throw new ArgumentOutOfRangeException ("UpdateBatchSize");
 412                                 updateBatchSize = value;
 413                         }
 414                 }
 415
 416                 #endregion // Properties
 417
 418                 #region Methods
 419
 420                 protected override RowUpdatedEventArgs CreateRowUpdatedEvent (DataRow dataRow, IDbCommand command, StatementType statementType, DataTableMapping tableMapping)
 421                 {
 422                         return new SqlRowUpdatedEventArgs (dataRow, command, statementType, tableMapping);
 423                 }
 424
 425
 426                 protected override RowUpdatingEventArgs CreateRowUpdatingEvent (DataRow dataRow, IDbCommand command, StatementType statementType, DataTableMapping tableMapping)
 427                 {
 428                         return new SqlRowUpdatingEventArgs (dataRow, command, statementType, tableMapping);
 429                 }
 430
 431
 432                 protected override void OnRowUpdated (RowUpdatedEventArgs value)
 433                 {
 434                         if (RowUpdated != null)
 435                                 RowUpdated (this, (SqlRowUpdatedEventArgs) value);
 436                 }
 437
 438                 protected override void OnRowUpdating (RowUpdatingEventArgs value)
 439                 {
 440                         if (RowUpdating != null)
 441                                 RowUpdating (this, (SqlRowUpdatingEventArgs) value);
 442                 }
 443
 444                 [MonoTODO]
 445                 object ICloneable.Clone()
 446                 {
 447                         throw new NotImplementedException ();
 448                 }
 449
 450                 // All the batch methods, should be implemented, if supported,
 451                 // by individual providers
 452
 453                 [MonoTODO]
 454                 protected override int AddToBatch (IDbCommand command)
 455                 {
 456                         throw new NotImplementedException ();
 457                 }
 458
 459                 [MonoTODO]
 460                 protected override void ClearBatch ()
 461                 {
 462                         throw new NotImplementedException ();
 463                 }
 464
 465                 [MonoTODO]
 466                 protected override int ExecuteBatch ()
 467                 {
 468                         throw new NotImplementedException ();
 469                 }
 470
 471                 [MonoTODO]
 472                 protected override IDataParameter GetBatchedParameter (int commandIdentifier, int  parameterIndex)
 473                 {
 474                         throw new NotImplementedException ();
 475                 }
 476
 477                 [MonoTODO]
 478                 protected override void InitializeBatching ()
 479                 {
 480                         throw new NotImplementedException ();
 481                 }
 482
 483                 [MonoTODO]
 484                 protected override void TerminateBatching ()
 485                 {
 486                         throw new NotImplementedException ();
 487                 }
 488                 #endregion // Methods
 489
 490                 #region Events and Delegates
 491
 492                 public event SqlRowUpdatedEventHandler RowUpdated;
 493
 494                 public event SqlRowUpdatingEventHandler RowUpdating;
 495
 496                 #endregion // Events and Delegates
 497         }
 498 }