2 // System.Data.Common.DbDataAdapter.cs
5 // Rodrigo Moya (rodrigo@ximian.com)
6 // Tim Coleman (tim@timcoleman.com)
9 // Copyright (C) Tim Coleman, 2002-2003
13 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
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:
23 // The above copyright notice and this permission notice shall be
24 // included in all copies or substantial portions of the Software.
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.
36 using System
.Collections
;
37 using System
.ComponentModel
;
39 using System
.Runtime
.InteropServices
;
41 namespace System
.Data
.Common
{
42 public abstract class DbDataAdapter
: DataAdapter
, ICloneable
46 public const string DefaultSourceTableName
= "Table";
47 const string DefaultSourceColumnName
= "Column";
53 protected DbDataAdapter()
58 protected DbDataAdapter(DbDataAdapter adapter
) : base(adapter
)
68 protected virtual IDbConnection BaseConnection
{
69 get { throw new NotImplementedException (); }
70 set { throw new NotImplementedException (); }
73 public IDbConnection Connection
{
74 get { return BaseConnection; }
75 set { BaseConnection = value; }
79 IDbCommand DeleteCommand
{
80 get { return ((IDbDataAdapter) this).DeleteCommand; }
84 protected internal CommandBehavior FillCommandBehavior
{
85 get { throw new NotImplementedException (); }
86 set { throw new NotImplementedException (); }
90 IDbCommand InsertCommand
{
91 get { return ((IDbDataAdapter) this).InsertCommand; }
96 protected virtual IDbCommand
this [[Optional
] StatementType statementType
] {
97 get { throw new NotImplementedException (); }
98 set { throw new NotImplementedException (); }
101 protected virtual DbProviderFactory ProviderFactory
{
102 get { throw new NotImplementedException (); }
106 IDbCommand SelectCommand
{
107 get { return ((IDbDataAdapter) this).SelectCommand; }
112 public IDbTransaction Transaction
{
113 get { throw new NotImplementedException (); }
114 set { throw new NotImplementedException (); }
118 public int UpdateBatchSize
{
119 get { throw new NotImplementedException (); }
120 set { throw new NotImplementedException (); }
124 IDbCommand UpdateCommand
{
125 get { return ((IDbDataAdapter) this).UpdateCommand; }
128 #endregion // Properties
132 #if ONLY_1_0 || ONLY_1_1
134 [DataCategory ("Fill")]
135 [DataSysDescription ("Event triggered when a recoverable error occurs during Fill.")]
136 public event FillErrorEventHandler FillError
;
145 public virtual void BeginInit ()
147 throw new NotImplementedException ();
151 protected abstract RowUpdatedEventArgs
CreateRowUpdatedEvent (DataRow dataRow
, IDbCommand command
, StatementType statementType
, DataTableMapping tableMapping
);
152 protected abstract RowUpdatingEventArgs
CreateRowUpdatingEvent (DataRow dataRow
, IDbCommand command
, StatementType statementType
, DataTableMapping tableMapping
);
154 private FillErrorEventArgs
CreateFillErrorEvent (DataTable dataTable
, object[] values
, Exception e
)
156 FillErrorEventArgs args
= new FillErrorEventArgs (dataTable
, values
);
158 args
.Continue
= false;
162 protected override void Dispose (bool disposing
)
165 IDbDataAdapter da
= (IDbDataAdapter
) this;
166 if (da
.SelectCommand
!= null) {
167 da
.SelectCommand
.Dispose();
168 da
.SelectCommand
= null;
170 if (da
.InsertCommand
!= null) {
171 da
.InsertCommand
.Dispose();
172 da
.InsertCommand
= null;
174 if (da
.UpdateCommand
!= null) {
175 da
.UpdateCommand
.Dispose();
176 da
.UpdateCommand
= null;
178 if (da
.DeleteCommand
!= null) {
179 da
.DeleteCommand
.Dispose();
180 da
.DeleteCommand
= null;
187 public virtual void EndInit ()
189 throw new NotImplementedException ();
193 public override int Fill (DataSet dataSet
)
195 return Fill (dataSet
, 0, 0, DefaultSourceTableName
, SelectCommand
, CommandBehavior
.Default
);
198 public int Fill (DataTable dataTable
)
200 if (dataTable
== null)
201 throw new NullReferenceException ();
203 return Fill (dataTable
, SelectCommand
, CommandBehavior
.Default
);
206 public int Fill (DataSet dataSet
, string srcTable
)
208 return Fill (dataSet
, 0, 0, srcTable
, SelectCommand
, CommandBehavior
.Default
);
212 protected override int Fill (DataTable dataTable
, IDataReader dataReader
)
214 protected virtual int Fill (DataTable dataTable
, IDataReader dataReader
)
217 if (dataReader
.FieldCount
== 0) {
225 string tableName
= SetupSchema (SchemaType
.Mapped
, dataTable
.TableName
);
226 if (tableName
!= null) {
227 dataTable
.TableName
= tableName
;
228 FillTable (dataTable
, dataReader
, 0, 0, ref count
);
237 protected virtual int Fill (DataTable dataTable
, IDbCommand command
, CommandBehavior behavior
)
239 CommandBehavior commandBehavior
= behavior
;
240 // first see that the connection is not close.
241 if (command
.Connection
.State
== ConnectionState
.Closed
)
243 command
.Connection
.Open ();
244 commandBehavior
|= CommandBehavior
.CloseConnection
;
246 return Fill (dataTable
, command
.ExecuteReader (commandBehavior
));
251 public int Fill (int startRecord
, int maxRecords
, DataTable
[] dataTables
)
253 throw new NotImplementedException ();
257 public int Fill (DataSet dataSet
, int startRecord
, int maxRecords
, string srcTable
)
259 return this.Fill (dataSet
, startRecord
, maxRecords
, srcTable
, SelectCommand
, CommandBehavior
.Default
);
264 protected virtual int Fill (DataTable
[] dataTables
, int startRecord
, int maxRecords
, IDbCommand command
, CommandBehavior behavior
)
266 throw new NotImplementedException ();
271 protected override int Fill (DataSet dataSet
, string srcTable
, IDataReader dataReader
, int startRecord
, int maxRecords
)
273 protected virtual int Fill (DataSet dataSet
, string srcTable
, IDataReader dataReader
, int startRecord
, int maxRecords
)
277 throw new ArgumentException ("The startRecord parameter was less than 0.");
279 throw new ArgumentException ("The maxRecords parameter was less than 0.");
286 string tableName
= srcTable
;
288 // Non-resultset queries like insert, delete or update aren't processed.
289 if (dataReader
.FieldCount
!= -1)
291 tableName
= SetupSchema (SchemaType
.Mapped
, tableName
);
292 if (tableName
!= null) {
294 // check if the table exists in the dataset
295 if (dataSet
.Tables
.Contains (tableName
))
296 // get the table from the dataset
297 dataTable
= dataSet
.Tables
[tableName
];
299 dataTable
= new DataTable(tableName
);
300 dataSet
.Tables
.Add (dataTable
);
303 if (!FillTable (dataTable
, dataReader
, startRecord
, maxRecords
, ref count
)) {
307 tableName
= String
.Format ("{0}{1}", srcTable
, ++resultIndex
);
313 } while (dataReader
.NextResult ());
322 protected virtual int Fill (DataSet dataSet
, int startRecord
, int maxRecords
, string srcTable
, IDbCommand command
, CommandBehavior behavior
)
324 if (MissingSchemaAction
== MissingSchemaAction
.AddWithKey
)
325 behavior
|= CommandBehavior
.KeyInfo
;
326 CommandBehavior commandBehavior
= behavior
;
327 if (command
.Connection
.State
== ConnectionState
.Closed
) {
328 command
.Connection
.Open ();
329 commandBehavior
|= CommandBehavior
.CloseConnection
;
331 return Fill (dataSet
, srcTable
, command
.ExecuteReader (commandBehavior
), startRecord
, maxRecords
);
334 private bool FillTable (DataTable dataTable
, IDataReader dataReader
, int startRecord
, int maxRecords
, ref int counter
)
336 if (dataReader
.FieldCount
== 0) {
340 int counterStart
= counter
;
342 int[] mapping
= BuildSchema (dataReader
, dataTable
, SchemaType
.Mapped
);
344 for (int i
= 0; i
< startRecord
; i
++) {
348 while (dataReader
.Read () && (maxRecords
== 0 || (counter
- counterStart
) < maxRecords
)) {
350 dataTable
.BeginLoadData ();
351 dataTable
.LoadDataRow (dataReader
, mapping
, AcceptChangesDuringFill
);
352 dataTable
.EndLoadData ();
355 catch (Exception e
) {
356 object[] readerArray
= new object[dataReader
.FieldCount
];
357 object[] tableArray
= new object[mapping
.Length
];
358 // we get the values from the datareader
359 dataReader
.GetValues (readerArray
);
360 // copy from datareader columns to table columns according to given mapping
361 for (int i
= 0; i
< mapping
.Length
; i
++) {
362 tableArray
[i
] = readerArray
[mapping
[i
]];
364 FillErrorEventArgs args
= CreateFillErrorEvent (dataTable
, tableArray
, e
);
374 public override DataTable
[] FillSchema (DataSet dataSet
, SchemaType schemaType
)
376 return FillSchema (dataSet
, schemaType
, SelectCommand
, DefaultSourceTableName
, CommandBehavior
.Default
);
379 public DataTable
FillSchema (DataTable dataTable
, SchemaType schemaType
)
381 return FillSchema (dataTable
, schemaType
, SelectCommand
, CommandBehavior
.Default
);
384 public DataTable
[] FillSchema (DataSet dataSet
, SchemaType schemaType
, string srcTable
)
386 return FillSchema (dataSet
, schemaType
, SelectCommand
, srcTable
, CommandBehavior
.Default
);
389 [MonoTODO ("Verify")]
390 protected virtual DataTable
FillSchema (DataTable dataTable
, SchemaType schemaType
, IDbCommand command
, CommandBehavior behavior
)
392 behavior
|= CommandBehavior
.SchemaOnly
| CommandBehavior
.KeyInfo
;
393 if (command
.Connection
.State
== ConnectionState
.Closed
) {
394 command
.Connection
.Open ();
395 behavior
|= CommandBehavior
.CloseConnection
;
398 IDataReader reader
= command
.ExecuteReader (behavior
);
401 string tableName
= SetupSchema (schemaType
, dataTable
.TableName
);
402 if (tableName
!= null)
404 BuildSchema (reader
, dataTable
, schemaType
);
414 [MonoTODO ("Verify")]
415 protected virtual DataTable
[] FillSchema (DataSet dataSet
, SchemaType schemaType
, IDbCommand command
, string srcTable
, CommandBehavior behavior
)
417 behavior
|= CommandBehavior
.SchemaOnly
| CommandBehavior
.KeyInfo
;
418 if (command
.Connection
.State
== ConnectionState
.Closed
) {
419 command
.Connection
.Open ();
420 behavior
|= CommandBehavior
.CloseConnection
;
423 IDataReader reader
= command
.ExecuteReader (behavior
);
424 ArrayList output
= new ArrayList ();
425 string tableName
= srcTable
;
430 tableName
= SetupSchema (schemaType
, tableName
);
431 if (tableName
!= null)
433 if (dataSet
.Tables
.Contains (tableName
))
434 table
= dataSet
.Tables
[tableName
];
437 table
= new DataTable(tableName
);
438 dataSet
.Tables
.Add (table
);
440 BuildSchema (reader
, table
, schemaType
);
442 tableName
= String
.Format ("{0}{1}", srcTable
, ++index
);
449 return (DataTable
[]) output
.ToArray (typeof (DataTable
));
454 public DataSet
GetDataSet ()
456 throw new NotImplementedException ();
460 public DataTable
GetDataTable ()
462 throw new NotImplementedException ();
466 private string SetupSchema (SchemaType schemaType
, string sourceTableName
)
468 DataTableMapping tableMapping
= null;
470 if (schemaType
== SchemaType
.Mapped
)
472 tableMapping
= DataTableMappingCollection
.GetTableMappingBySchemaAction (TableMappings
, sourceTableName
, sourceTableName
, MissingMappingAction
);
474 if (tableMapping
!= null)
475 return tableMapping
.DataSetTable
;
479 return sourceTableName
;
482 [EditorBrowsable (EditorBrowsableState
.Advanced
)]
483 public override IDataParameter
[] GetFillParameters ()
485 IDataParameter
[] parameters
= new IDataParameter
[SelectCommand
.Parameters
.Count
];
486 SelectCommand
.Parameters
.CopyTo (parameters
, 0);
490 // this method builds the schema for a given datatable. it returns a int array with
491 // "array[ordinal of datatable column] == index of source column in data reader".
492 // each column in the datatable has a mapping to a specific column in the datareader,
493 // the int array represents this match.
495 private int[] BuildSchema (IDataReader reader
, DataTable table
, SchemaType schemaType
)
498 int[] mapping
= new int[reader
.FieldCount
]; // mapping the reader indexes to the datatable indexes
499 ArrayList primaryKey
= new ArrayList ();
500 ArrayList sourceColumns
= new ArrayList ();
502 foreach (DataRow schemaRow
in reader
.GetSchemaTable ().Rows
) {
503 // generate a unique column name in the source table.
504 string sourceColumnName
;
505 if (schemaRow
.IsNull("ColumnName"))
506 sourceColumnName
= DefaultSourceColumnName
;
508 sourceColumnName
= (string) schemaRow
["ColumnName"];
510 string realSourceColumnName
= sourceColumnName
;
512 for (int i
= 1; sourceColumns
.Contains (realSourceColumnName
); i
+= 1)
513 realSourceColumnName
= String
.Format ("{0}{1}", sourceColumnName
, i
);
514 sourceColumns
.Add(realSourceColumnName
);
516 // generate DataSetColumnName from DataTableMapping, if any
517 string dsColumnName
= realSourceColumnName
;
518 DataTableMapping tableMapping
= null;
519 tableMapping
= DataTableMappingCollection
.GetTableMappingBySchemaAction (TableMappings
, table
.TableName
, table
.TableName
, MissingMappingAction
);
520 if (tableMapping
!= null)
523 table
.TableName
= tableMapping
.DataSetTable
;
524 // check to see if the column mapping exists
525 DataColumnMapping columnMapping
= DataColumnMappingCollection
.GetColumnMappingBySchemaAction(tableMapping
.ColumnMappings
, realSourceColumnName
, MissingMappingAction
);
526 if (columnMapping
!= null)
529 columnMapping
.GetDataColumnBySchemaAction(
531 (Type
)schemaRow
["DataType"],
532 MissingSchemaAction
);
536 // if the column is not in the table - add it.
537 if (table
.Columns
.IndexOf(col
) == -1)
539 if (MissingSchemaAction
== MissingSchemaAction
.Add
|| MissingSchemaAction
== MissingSchemaAction
.AddWithKey
)
540 table
.Columns
.Add(col
);
543 if (!schemaRow
["IsKey"].Equals (DBNull
.Value
))
544 if ((bool) (schemaRow
["IsKey"]))
545 primaryKey
.Add (col
);
547 // add the ordinal of the column as a key and the index of the column in the datareader as a value.
548 mapping
[col
.Ordinal
] = readerIndex
;
554 if (primaryKey
.Count
> 0)
555 table
.PrimaryKey
= (DataColumn
[])(primaryKey
.ToArray(typeof (DataColumn
)));
561 object ICloneable
.Clone ()
563 throw new NotImplementedException ();
567 public int Update (DataRow
[] dataRows
)
569 if (dataRows
== null)
570 throw new ArgumentNullException("dataRows");
572 if (dataRows
.Length
== 0)
575 if (dataRows
[0] == null)
576 throw new ArgumentException("dataRows[0].");
578 DataTable table
= dataRows
[0].Table
;
580 throw new ArgumentException("table is null reference.");
582 // all rows must be in the same table
583 for (int i
= 0; i
< dataRows
.Length
; i
++)
585 if (dataRows
[i
] == null)
586 throw new ArgumentException("dataRows[" + i
+ "].");
587 if (dataRows
[i
].Table
!= table
)
588 throw new ArgumentException(
591 + "] is from a different DataTable than DataRow[0].");
594 // get table mapping for this rows
595 DataTableMapping tableMapping
= TableMappings
.GetByDataSetTable(table
.TableName
);
596 if (tableMapping
== null)
598 tableMapping
= DataTableMappingCollection
.GetTableMappingBySchemaAction(
602 MissingMappingAction
);
603 if (tableMapping
!= null) {
604 foreach (DataColumn col
in table
.Columns
) {
605 if (tableMapping
.ColumnMappings
.IndexOf (col
.ColumnName
) >= 0)
607 DataColumnMapping columnMapping
= DataColumnMappingCollection
.GetColumnMappingBySchemaAction (tableMapping
.ColumnMappings
, col
.ColumnName
, MissingMappingAction
);
608 if (columnMapping
== null)
609 columnMapping
= new DataColumnMapping (col
.ColumnName
, col
.ColumnName
);
610 tableMapping
.ColumnMappings
.Add (columnMapping
);
613 ArrayList cmc
= new ArrayList ();
614 foreach (DataColumn col
in table
.Columns
)
615 cmc
.Add (new DataColumnMapping (col
.ColumnName
, col
.ColumnName
));
617 new DataTableMapping (
620 cmc
.ToArray (typeof (DataColumnMapping
)) as DataColumnMapping
[]);
624 DataRow
[] copy
= new DataRow
[dataRows
.Length
];
625 Array
.Copy(dataRows
, 0, copy
, 0, dataRows
.Length
);
626 return Update(copy
, tableMapping
);
629 public override int Update (DataSet dataSet
)
631 return Update (dataSet
, DefaultSourceTableName
);
634 public int Update (DataTable dataTable
)
637 int index = TableMappings.IndexOfDataSetTable (dataTable.TableName);
639 throw new ArgumentException ();
640 return Update (dataTable, TableMappings [index]);
642 DataTableMapping tableMapping
= TableMappings
.GetByDataSetTable (dataTable
.TableName
);
643 if (tableMapping
== null)
645 tableMapping
= DataTableMappingCollection
.GetTableMappingBySchemaAction (
649 MissingMappingAction
);
650 if (tableMapping
!= null) {
651 foreach (DataColumn col
in dataTable
.Columns
) {
652 if (tableMapping
.ColumnMappings
.IndexOf (col
.ColumnName
) >= 0)
654 DataColumnMapping columnMapping
= DataColumnMappingCollection
.GetColumnMappingBySchemaAction (tableMapping
.ColumnMappings
, col
.ColumnName
, MissingMappingAction
);
655 if (columnMapping
== null)
656 columnMapping
= new DataColumnMapping (col
.ColumnName
, col
.ColumnName
);
657 tableMapping
.ColumnMappings
.Add (columnMapping
);
660 ArrayList cmc
= new ArrayList ();
661 foreach (DataColumn col
in dataTable
.Columns
)
662 cmc
.Add (new DataColumnMapping (col
.ColumnName
, col
.ColumnName
));
664 new DataTableMapping (
667 cmc
.ToArray (typeof (DataColumnMapping
)) as DataColumnMapping
[]);
670 return Update (dataTable
, tableMapping
);
673 private int Update (DataTable dataTable
, DataTableMapping tableMapping
)
675 DataRow
[] rows
= new DataRow
[dataTable
.Rows
.Count
];
676 dataTable
.Rows
.CopyTo (rows
, 0);
677 return Update (rows
, tableMapping
);
681 protected virtual int Update (DataRow
[] dataRows
, DataTableMapping tableMapping
)
685 foreach (DataRow row
in dataRows
) {
686 StatementType statementType
= StatementType
.Update
;
687 IDbCommand command
= null;
688 string commandName
= String
.Empty
;
689 bool useCommandBuilder
= false;
691 switch (row
.RowState
) {
692 case DataRowState
.Added
:
693 statementType
= StatementType
.Insert
;
694 command
= InsertCommand
;
695 commandName
= "Insert";
697 case DataRowState
.Deleted
:
698 statementType
= StatementType
.Delete
;
699 command
= DeleteCommand
;
700 commandName
= "Delete";
702 case DataRowState
.Modified
:
703 statementType
= StatementType
.Update
;
704 command
= UpdateCommand
;
705 commandName
= "Update";
707 case DataRowState
.Unchanged
:
709 case DataRowState
.Detached
:
710 throw new NotImplementedException ();
714 useCommandBuilder
= true;
716 RowUpdatingEventArgs args
= CreateRowUpdatingEvent (row
, command
, statementType
, tableMapping
);
717 OnRowUpdating (args
);
719 if (args
.Status
== UpdateStatus
.ErrorsOccurred
)
722 if (command
== null && args
.Command
!= null)
723 command
= args
.Command
;
724 else if (command
== null)
725 throw new InvalidOperationException (String
.Format ("Update requires a valid {0}Command when passed a DataRow collection with modified rows.", commandName
));
727 if (!useCommandBuilder
) {
728 DataColumnMappingCollection columnMappings
= tableMapping
.ColumnMappings
;
730 foreach (IDataParameter parameter
in command
.Parameters
) {
731 string dsColumnName
= parameter
.SourceColumn
;
732 if (columnMappings
.Contains(parameter
.SourceColumn
))
733 dsColumnName
= columnMappings
[parameter
.SourceColumn
].DataSetColumn
;
734 DataRowVersion rowVersion
= DataRowVersion
.Default
;
736 // Parameter version is ignored for non-update commands
737 if (statementType
== StatementType
.Update
)
738 rowVersion
= parameter
.SourceVersion
;
739 if (statementType
== StatementType
.Delete
)
740 rowVersion
= DataRowVersion
.Original
;
742 parameter
.Value
= row
[dsColumnName
, rowVersion
];
746 CommandBehavior commandBehavior
= CommandBehavior
.Default
;
747 if (command
.Connection
.State
== ConnectionState
.Closed
)
749 command
.Connection
.Open ();
750 commandBehavior
|= CommandBehavior
.CloseConnection
;
753 IDataReader reader
= null;
756 // use ExecuteReader because we want to use the commandbehavior parameter.
757 // so the connection will be closed if needed.
758 reader
= command
.ExecuteReader (commandBehavior
);
759 int tmp
= reader
.RecordsAffected
;
760 // if the execute does not effect any rows we throw an exception.
762 throw new DBConcurrencyException("Concurrency violation: the " + commandName
+"Command affected 0 records.");
764 OnRowUpdated (CreateRowUpdatedEvent (row
, command
, statementType
, tableMapping
));
765 row
.AcceptChanges ();
769 if (ContinueUpdateOnError
)
770 row
.RowError
= e
.Message
;// do somthing with the error
784 public int Update (DataSet dataSet
, string sourceTable
)
786 MissingMappingAction mappingAction
= MissingMappingAction
;
787 if (mappingAction
== MissingMappingAction
.Ignore
)
788 mappingAction
= MissingMappingAction
.Error
;
789 DataTableMapping tableMapping
= DataTableMappingCollection
.GetTableMappingBySchemaAction (TableMappings
, sourceTable
, sourceTable
, mappingAction
);
791 DataTable dataTable
= dataSet
.Tables
[tableMapping
.DataSetTable
];
792 if (dataTable
== null)
793 throw new ArgumentException ("sourceTable");
795 return Update (dataTable
, tableMapping
);
798 #if ONLY_1_0 || ONLY_1_1
799 protected virtual void OnFillError (FillErrorEventArgs
value)
801 if (FillError
!= null)
802 FillError (this, value);
806 protected abstract void OnRowUpdated (RowUpdatedEventArgs
value);
807 protected abstract void OnRowUpdating (RowUpdatingEventArgs
value);
809 #endregion // Methods