private void UpdateParameterValues( IDbCommand command, StatementType statementType, DataRow row, DataTableMapping tableMapping) { foreach (DbParameter parameter in command.Parameters) { // Process only input parameters if ((parameter.Direction == ParameterDirection.Input || parameter.Direction == ParameterDirection.InputOutput) && !String.IsNullOrEmpty(parameter.SourceColumn)) { DataColumn column = null; /* Get the DataColumnMapping that matches the given * column name */ DataColumnMapping columnMapping = tableMapping.GetColumnMappingBySchemaAction( parameter.SourceColumn, MissingMappingAction); if (columnMapping != null) { column = columnMapping.GetDataColumnBySchemaAction(row.Table, null, MissingSchemaAction); if (column != null) { DataRowVersion dataRowVersion = DataRowVersion.Default; if (statementType == StatementType.Insert) { dataRowVersion = DataRowVersion.Current; } else if (statementType == StatementType.Update) { dataRowVersion = parameter.SourceVersion; } else if (statementType == StatementType.Delete) { dataRowVersion = DataRowVersion.Original; } if (parameter.SourceColumnNullMapping) { parameter.Value = IsNull(row[column, dataRowVersion]) ? 1 : 0; } else { parameter.Value = row[column, dataRowVersion]; } } } } } }
private DataColumn GetParameterDataColumn(string columnName, DataTableMapping mappings, DataRow row) { if (!IsEmpty(columnName)) { DataColumnMapping dataColumnMapping = mappings.GetColumnMappingBySchemaAction(columnName, missingMapping); if (dataColumnMapping != null) { return(dataColumnMapping.GetDataColumnBySchemaAction(row.Table, null, MissingSchemaAction.Error)); } } return(null); }
private void AddParameter( VirtuosoCommand command, ColumnData column, DataRowVersion version, DataTableMapping mapping, DataRow row) { Debug.WriteLineIf(CLI.FnTrace.Enabled, "VirtuosoCommandBuilder.AddParameter()"); VirtuosoParameter parameter = new VirtuosoParameter(); string name = String.Format("p{0}", command.Parameters.Count); object value = null; if (row != null) { Debug.WriteLineIf(Switch.Enabled, " row != null"); //string datasetColumn = column.columnName; if (mapping != null) { Debug.WriteLineIf(Switch.Enabled, " mapping != null"); DataColumnMapping columnMapping = mapping.GetColumnMappingBySchemaAction( column.columnName, adapter.MissingMappingAction); if (columnMapping != null) { Debug.WriteLineIf(Switch.Enabled, " columnMapping != null"); DataColumn dataColumn = columnMapping.GetDataColumnBySchemaAction( row.Table, column.columnType.bufferType.type, adapter.MissingSchemaAction); if (dataColumn != null) { Debug.WriteLineIf(Switch.Enabled, " dataColumn != null"); value = row[dataColumn, version]; } } } } Debug.WriteLineIf(Switch.Enabled, " value: " + value); parameter.ParameterName = name; parameter.VirtDbType = column.columnType.vdbType; parameter.Precision = (byte)column.precision; parameter.Scale = (byte)column.scale; parameter.Size = 0; parameter.IsNullable = column.IsNullable; parameter.Direction = ParameterDirection.Input; parameter.SourceColumn = column.columnName; parameter.SourceVersion = version; parameter.Value = value; command.Parameters.Add(parameter); }
public void GetDataColumnBySchemaAction_String_String_DataTable_Type_MissingSchemaAction_DataColumnExpressionExistsThrowsException() { DataColumn priceColumn = new DataColumn(); priceColumn.DataType = typeof(decimal); priceColumn.ColumnName = "price"; priceColumn.DefaultValue = 50; DataColumn taxColumn = new DataColumn(); taxColumn.DataType = typeof(decimal); taxColumn.ColumnName = "tax"; taxColumn.Expression = "price * 0.0862"; DataTable dataTable = new DataTable(); dataTable.Columns.Add(priceColumn); dataTable.Columns.Add(taxColumn); Assert.Throws <InvalidOperationException>(() => DataColumnMapping.GetDataColumnBySchemaAction("", "tax", dataTable, typeof(string), new MissingSchemaAction())); }
private DataColumn GetDataColumn( string columnName, DataTableMapping tableMapping, DataRow row) { DataColumn dataColumn = null; // Get the DataColumnMapping that matches // the given column name DataColumnMapping columnMapping = tableMapping.GetColumnMappingBySchemaAction( columnName, this.dataAdapter.MissingMappingAction); if (columnMapping != null) { // Get the DataColumn for the given column name dataColumn = columnMapping.GetDataColumnBySchemaAction( row.Table, null, this.dataAdapter.MissingSchemaAction); } return(dataColumn); }
/// <summary> /// Review .NET Framework documentation. /// </summary> protected override int Update(DataRow[] dataRows, DataTableMapping tableMapping) { int updated = 0; IDbCommand command = null; StatementType statementType = StatementType.Insert; ICollection <IDbConnection> connections = new List <IDbConnection>(); RowUpdatingEventArgs updatingArgs = null; Exception updateException = null; foreach (DataRow row in dataRows) { updateException = null; if (row.RowState == DataRowState.Detached || row.RowState == DataRowState.Unchanged) { continue; } switch (row.RowState) { case DataRowState.Unchanged: case DataRowState.Detached: continue; case DataRowState.Added: command = InsertCommand; statementType = StatementType.Insert; break; case DataRowState.Modified: command = UpdateCommand; statementType = StatementType.Update; break; case DataRowState.Deleted: command = DeleteCommand; statementType = StatementType.Delete; break; } /* The order of execution can be reviewed in the .NET 1.1 documentation * * 1. The values in the DataRow are moved to the parameter values. * 2. The OnRowUpdating event is raised. * 3. The command executes. * 4. If the command is set to FirstReturnedRecord, then the first returned result is placed in the DataRow. * 5. If there are output parameters, they are placed in the DataRow. * 6. The OnRowUpdated event is raised. * 7 AcceptChanges is called. */ try { updatingArgs = CreateRowUpdatingEvent(row, command, statementType, tableMapping); /* 1. Update Parameter values (It's very similar to what we * are doing in the FbCommandBuilder class). * * Only input parameters should be updated. */ if (command != null && command.Parameters.Count > 0) { try { UpdateParameterValues(command, statementType, row, tableMapping); } catch (Exception ex) { updatingArgs.Errors = ex; updatingArgs.Status = UpdateStatus.ErrorsOccurred; } } // 2. Raise RowUpdating event OnRowUpdating(updatingArgs); if (updatingArgs.Status == UpdateStatus.SkipAllRemainingRows) { break; } else if (updatingArgs.Status == UpdateStatus.ErrorsOccurred) { if (updatingArgs.Errors == null) { throw new InvalidOperationException("RowUpdatingEvent: Errors occurred; no additional information is available."); } throw updatingArgs.Errors; } else if (updatingArgs.Status == UpdateStatus.SkipCurrentRow) { updated++; continue; } else if (updatingArgs.Status == UpdateStatus.Continue) { if (command != updatingArgs.Command) { command = updatingArgs.Command; } if (command == null) { /* Samples of exceptions thrown by DbDataAdapter class * * Update requires a valid InsertCommand when passed DataRow collection with new rows * Update requires a valid UpdateCommand when passed DataRow collection with modified rows. * Update requires a valid DeleteCommand when passed DataRow collection with deleted rows. */ throw new InvalidOperationException(CreateExceptionMessage(statementType)); } // 3. Execute the command if (command.Connection.State == ConnectionState.Closed) { command.Connection.Open(); // Track command connection connections.Add(command.Connection); } int rowsAffected = command.ExecuteNonQuery(); if (rowsAffected == 0) { throw new DBConcurrencyException(new DBConcurrencyException().Message, null, new DataRow[] { row }); } updated++; // http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=933212&SiteID=1 if (statementType == StatementType.Insert) { row.AcceptChanges(); } /* 4. If the command is set to FirstReturnedRecord, then the * first returned result is placed in the DataRow. * * We have nothing to do in this case as there are no * support for batch commands. */ /* 5. Check if we have output parameters and they should * be updated. * * Only output parameters should be updated */ if (command.UpdatedRowSource == UpdateRowSource.OutputParameters || command.UpdatedRowSource == UpdateRowSource.Both) { // Process output parameters foreach (IDataParameter parameter in command.Parameters) { if ((parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.ReturnValue || parameter.Direction == ParameterDirection.InputOutput) && !String.IsNullOrEmpty(parameter.SourceColumn)) { DataColumn column = null; DataColumnMapping columnMapping = tableMapping.GetColumnMappingBySchemaAction( parameter.SourceColumn, MissingMappingAction); if (columnMapping != null) { column = columnMapping.GetDataColumnBySchemaAction( row.Table, null, MissingSchemaAction); if (column != null) { row[column] = parameter.Value; } } } } } } } catch (Exception ex) { row.RowError = ex.Message; updateException = ex; } if (updatingArgs != null && updatingArgs.Status == UpdateStatus.Continue) { // 6. Raise RowUpdated event RowUpdatedEventArgs updatedArgs = CreateRowUpdatedEvent(row, command, statementType, tableMapping); OnRowUpdated(updatedArgs); if (updatedArgs.Status == UpdateStatus.SkipAllRemainingRows) { break; } else if (updatedArgs.Status == UpdateStatus.ErrorsOccurred) { if (updatingArgs.Errors == null) { throw new InvalidOperationException("RowUpdatedEvent: Errors occurred; no additional information available."); } throw updatedArgs.Errors; } else if (updatedArgs.Status == UpdateStatus.SkipCurrentRow) { } else if (updatingArgs.Status == UpdateStatus.Continue) { // If the update result is an exception throw it if (!ContinueUpdateOnError && updateException != null) { CloseConnections(connections); throw updateException; } // 7. Call AcceptChanges if (AcceptChangesDuringUpdate && !row.HasErrors) { row.AcceptChanges(); } } } else { // If the update result is an exception throw it if (!ContinueUpdateOnError && updateException != null) { CloseConnections(connections); throw updateException; } } } CloseConnections(connections); return(updated); }
/// <summary> /// Creates or Modifies the schema of the given DataTable based on the schema of /// the reader and the arguments passed. /// </summary> internal static int[] BuildSchema(IDataReader reader, DataTable table, SchemaType schemaType, MissingSchemaAction missingSchAction, MissingMappingAction missingMapAction, DataTableMappingCollection dtMapping ) { int readerIndex = 0; // FIXME : this fails if query has fewer columns than a table int[] mapping = new int[table.Columns.Count]; // mapping the reader indexes to the datatable indexes for (int i = 0; i < mapping.Length; i++) { mapping[i] = -1; } ArrayList primaryKey = new ArrayList(); ArrayList sourceColumns = new ArrayList(); bool createPrimaryKey = true; DataTable schemaTable = reader.GetSchemaTable(); DataColumn ColumnNameCol = schemaTable.Columns["ColumnName"]; DataColumn DataTypeCol = schemaTable.Columns["DataType"]; DataColumn IsAutoIncrementCol = schemaTable.Columns["IsAutoIncrement"]; DataColumn AllowDBNullCol = schemaTable.Columns["AllowDBNull"]; DataColumn IsReadOnlyCol = schemaTable.Columns["IsReadOnly"]; DataColumn IsKeyCol = schemaTable.Columns["IsKey"]; DataColumn IsUniqueCol = schemaTable.Columns["IsUnique"]; DataColumn ColumnSizeCol = schemaTable.Columns["ColumnSize"]; foreach (DataRow schemaRow in schemaTable.Rows) { // generate a unique column name in the source table. string sourceColumnName; string realSourceColumnName; if (ColumnNameCol == null || schemaRow.IsNull(ColumnNameCol) || (string)schemaRow [ColumnNameCol] == String.Empty) { sourceColumnName = DefaultSourceColumnName; realSourceColumnName = DefaultSourceColumnName + "1"; } else { sourceColumnName = (string)schemaRow [ColumnNameCol]; realSourceColumnName = sourceColumnName; } for (int i = 1; sourceColumns.Contains(realSourceColumnName); i += 1) { realSourceColumnName = String.Format("{0}{1}", sourceColumnName, i); } sourceColumns.Add(realSourceColumnName); // generate DataSetColumnName from DataTableMapping, if any DataTableMapping tableMapping = null; //FIXME : The sourcetable name shud get passed as a parameter.. int index = dtMapping.IndexOfDataSetTable(table.TableName); string srcTable = (index != -1 ? dtMapping[index].SourceTable : table.TableName); tableMapping = DataTableMappingCollection.GetTableMappingBySchemaAction(dtMapping, ADP.IsEmpty(srcTable) ? " " : srcTable, table.TableName, missingMapAction); if (tableMapping != null) { table.TableName = tableMapping.DataSetTable; // check to see if the column mapping exists DataColumnMapping columnMapping = DataColumnMappingCollection.GetColumnMappingBySchemaAction(tableMapping.ColumnMappings, realSourceColumnName, missingMapAction); if (columnMapping != null) { Type columnType = schemaRow[DataTypeCol] as Type; DataColumn col = columnType != null?columnMapping.GetDataColumnBySchemaAction( table, columnType, missingSchAction) : null; if (col != null) { // if the column is not in the table - add it. if (table.Columns.IndexOf(col) == -1) { if (missingSchAction == MissingSchemaAction.Add || missingSchAction == MissingSchemaAction.AddWithKey) { table.Columns.Add(col); } int[] tmp = new int[mapping.Length + 1]; Array.Copy(mapping, 0, tmp, 0, col.Ordinal); Array.Copy(mapping, col.Ordinal, tmp, col.Ordinal + 1, mapping.Length - col.Ordinal); mapping = tmp; } if (missingSchAction == MissingSchemaAction.AddWithKey) { object value = (AllowDBNullCol != null) ? schemaRow[AllowDBNullCol] : null; bool allowDBNull = value is bool?(bool)value : true; value = (IsKeyCol != null) ? schemaRow[IsKeyCol] : null; bool isKey = value is bool?(bool)value : false; value = (IsAutoIncrementCol != null) ? schemaRow[IsAutoIncrementCol] : null; bool isAutoIncrement = value is bool?(bool)value : false; value = (IsReadOnlyCol != null) ? schemaRow[IsReadOnlyCol] : null; bool isReadOnly = value is bool?(bool)value : false; value = (IsUniqueCol != null) ? schemaRow[IsUniqueCol] : null; bool isUnique = value is bool?(bool)value : false; col.AllowDBNull = allowDBNull; // fill woth key info if (isAutoIncrement && CanAutoIncrement(columnType)) { col.AutoIncrement = true; if (!allowDBNull) { col.AllowDBNull = false; } } if (columnType == DbTypes.TypeOfString) { col.MaxLength = (ColumnSizeCol != null) ? (int)schemaRow[ColumnSizeCol] : 0; } if (isReadOnly) { col.ReadOnly = true; } if (!allowDBNull && (!isReadOnly || isKey)) { col.AllowDBNull = false; } if (isUnique && !isKey && !columnType.IsArray) { col.Unique = true; if (!allowDBNull) { col.AllowDBNull = false; } } // This might not be set by all DataProviders bool isHidden = false; if (schemaTable.Columns.Contains("IsHidden")) { value = schemaRow["IsHidden"]; isHidden = ((value is bool) ? (bool)value : false); } if (isKey && !isHidden) { primaryKey.Add(col); if (allowDBNull) { createPrimaryKey = false; } } } // add the ordinal of the column as a key and the index of the column in the datareader as a value. mapping[col.Ordinal] = readerIndex++; } } } } if (primaryKey.Count > 0) { DataColumn[] colKey = (DataColumn[])(primaryKey.ToArray(typeof(DataColumn))); if (createPrimaryKey) { table.PrimaryKey = colKey; } else { UniqueConstraint uConstraint = new UniqueConstraint(colKey); for (int i = 0; i < table.Constraints.Count; i++) { if (table.Constraints[i].Equals(uConstraint)) { uConstraint = null; break; } } if (uConstraint != null) { table.Constraints.Add(uConstraint); } } } return(mapping); }
public void GetDataColumnBySchemaAction_String_String_DataTable_Type_MissingSchemaAction_MissingDataSetColumnReturnsNull() { DataColumn dataColumn = DataColumnMapping.GetDataColumnBySchemaAction("", null, new DataTable(), typeof(string), new MissingSchemaAction()); Assert.Null(dataColumn); }
public void GetDataColumnBySchemaAction_String_String_DataTable_Type_MissingSchemaAction_MissingDataTableThrowsException() { AssertExtensions.Throws <ArgumentNullException>("dataTable", () => DataColumnMapping.GetDataColumnBySchemaAction("", "", null, typeof(string), new MissingSchemaAction())); }
/// <summary> /// Review .NET Framework documentation. /// </summary> protected override int Update(DataRow[] dataRows, DataTableMapping tableMapping) { int updated = 0; IDbCommand command = null; StatementType statementType = StatementType.Insert; ArrayList connections = new ArrayList(); RowUpdatingEventArgs updatingArgs = null; Exception updateException = null; foreach (DataRow row in dataRows) { if (row.RowState == DataRowState.Detached || row.RowState == DataRowState.Unchanged) { continue; } switch (row.RowState) { case DataRowState.Added: command = this.insertCommand; statementType = StatementType.Insert; break; case DataRowState.Modified: command = this.updateCommand; statementType = StatementType.Update; break; case DataRowState.Deleted: command = this.deleteCommand; statementType = StatementType.Delete; break; } /* The order of execution can be reviewed in the .NET 1.1 documentation * * 1. The values in the DataRow are moved to the parameter values. * 2. The OnRowUpdating event is raised. * 3. The command executes. * 4. If the command is set to FirstReturnedRecord, then the first returned result is placed in the DataRow. * 5. If there are output parameters, they are placed in the DataRow. * 6. The OnRowUpdated event is raised. * 7 AcceptChanges is called. */ try { /* 1. Update Parameter values (It's very similar to what we * are doing in the FbCommandBuilder class). * * Only input parameters should be updated. */ if (command != null && command.Parameters.Count > 0) { this.UpdateParameterValues(command, statementType, row, tableMapping); } // 2. Raise RowUpdating event updatingArgs = this.CreateRowUpdatingEvent(row, command, statementType, tableMapping); this.OnRowUpdating(updatingArgs); if (updatingArgs.Status == UpdateStatus.SkipAllRemainingRows) { break; } else if (updatingArgs.Status == UpdateStatus.ErrorsOccurred) { if (updatingArgs.Errors == null) { throw new InvalidOperationException("RowUpdatingEvent: Errors occurred; no additional is information available."); } throw updatingArgs.Errors; } else if (updatingArgs.Status == UpdateStatus.SkipCurrentRow) { } else if (updatingArgs.Status == UpdateStatus.Continue) { if (command != updatingArgs.Command) { command = updatingArgs.Command; } if (command == null) { /* Samples of exceptions thrown by DbDataAdapter class * * Update requires a valid InsertCommand when passed DataRow collection with new rows * Update requires a valid UpdateCommand when passed DataRow collection with modified rows. * Update requires a valid DeleteCommand when passed DataRow collection with deleted rows. */ string message = this.CreateExceptionMessage(statementType); throw new InvalidOperationException(message); } /* Validate that the command has a connection */ if (command.Connection == null) { throw new InvalidOperationException("Update requires a command with a valid connection."); } // 3. Execute the command if (command.Connection.State == ConnectionState.Closed) { command.Connection.Open(); // Track command connection connections.Add(command.Connection); } int rowsAffected = command.ExecuteNonQuery(); if (rowsAffected == 0) { throw new DBConcurrencyException("An attempt to execute an INSERT, UPDATE, or DELETE statement resulted in zero records affected."); } updated++; /* 4. If the command is set to FirstReturnedRecord, then the * first returned result is placed in the DataRow. * * We have nothing to do in this case as there are no * support for batch commands. */ /* 5. Check if we have output parameters and they should * be updated. * * Only output paraneters should be updated */ if (command.UpdatedRowSource == UpdateRowSource.OutputParameters || command.UpdatedRowSource == UpdateRowSource.Both) { // Process output parameters foreach (IDataParameter parameter in command.Parameters) { if ((parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.ReturnValue || parameter.Direction == ParameterDirection.InputOutput) && parameter.SourceColumn != null && parameter.SourceColumn.Length > 0) { DataColumn column = null; DataColumnMapping columnMapping = tableMapping.GetColumnMappingBySchemaAction( parameter.SourceColumn, this.MissingMappingAction); if (columnMapping != null) { column = columnMapping.GetDataColumnBySchemaAction( row.Table, null, this.MissingSchemaAction); if (column != null) { row[column] = parameter.Value; } } } } } } } catch (Exception ex) { row.RowError = ex.Message; updateException = ex; } if (updatingArgs.Status == UpdateStatus.Continue) { // 6. Raise RowUpdated event RowUpdatedEventArgs updatedArgs = this.CreateRowUpdatedEvent(row, command, statementType, tableMapping); this.OnRowUpdated(updatedArgs); if (updatedArgs.Status == UpdateStatus.SkipAllRemainingRows) { break; } else if (updatedArgs.Status == UpdateStatus.ErrorsOccurred) { if (updatingArgs.Errors == null) { throw new InvalidOperationException("RowUpdatedEvent: Errors occurred; no additional information available."); } throw updatedArgs.Errors; } else if (updatedArgs.Status == UpdateStatus.SkipCurrentRow) { } else if (updatingArgs.Status == UpdateStatus.Continue) { // If the update result is an exception throw it if (!this.ContinueUpdateOnError && updateException != null) { this.CloseConnections(connections); throw updateException; } // 7. Call AcceptChanges row.AcceptChanges(); } } else { // If the update result is an exception throw it if (!this.ContinueUpdateOnError && updateException != null) { this.CloseConnections(connections); throw updateException; } } updateException = null; } this.CloseConnections(connections); return(updated); }