public void Add()
{
DataColumnMapping col1 = new DataColumnMapping("sourceName", "dataSetName");
int t = _columnMapCollection.Add(col1);
Assert.Equal(0, t);
bool eq1 = col1.Equals(_columnMapCollection[0]);
Assert.True(eq1);
Assert.Equal(1, _columnMapCollection.Count);
DataColumnMapping col2;
col2 = _columnMapCollection.Add("sourceID", "dataSetID");
bool eq2 = col2.Equals(_columnMapCollection[1]);
Assert.True(eq2);
Assert.Equal(2, _columnMapCollection.Count);
}
private void OnRowUpdating(Object sender, NpgsqlRowUpdatingEventArgs value)
{
switch (value.StatementType)
{
case StatementType.Insert:
value.Command = GetInsertCommand(value.Row, false);
break;
case StatementType.Update:
value.Command = GetUpdateCommand(value.Row, false);
break;
case StatementType.Delete:
value.Command = GetDeleteCommand(value.Row, false);
break;
}
DataColumnMappingCollection columnMappings = value.TableMapping.ColumnMappings;
foreach (IDataParameter parameter in value.Command.Parameters)
{
string dsColumnName = parameter.SourceColumn;
if (columnMappings.Contains(parameter.SourceColumn))
{
DataColumnMapping mapping = columnMappings[parameter.SourceColumn];
if (mapping != null)
{
dsColumnName = mapping.DataSetColumn;
}
}
DataRowVersion rowVersion = DataRowVersion.Default;
if (value.StatementType == StatementType.Update)
{
rowVersion = parameter.SourceVersion;
}
if (value.StatementType == StatementType.Delete)
{
rowVersion = DataRowVersion.Original;
}
parameter.Value = value.Row [dsColumnName, rowVersion];
}
}
public void DataColumnMapping_GetHashCode()
{
var dataColumnMapping = new DataColumnMapping()
{
IndexNumber = 1,
InputColumn = "a",
OutputColumn = new DbColumn()
{
ColumnName = "a",
DataType = typeof(string),
MaxLength = 1,
IsNullable = false,
IsAutoIncrement = false
}
};
var actual = dataColumnMapping.GetHashCode();
Assert.AreNotEqual(0, actual);
}
/// <summary>
/// 绑定指定行到指定节点
/// </summary>
public void BindRow(System.Windows.Forms.TreeListViewItem node, DataRow dr)
{
DataTableMapping dtm = GetTableMapping(dr.Table);
for (int i = 0; i < TreeListView.Columns.Count; i++)
{
string nodeHeaderName = TreeListView.Columns[i].Text;
int cmIndex = dtm.ColumnMappings.IndexOf(nodeHeaderName);
if (cmIndex > -1)
{
DataColumnMapping dcm = dtm.ColumnMappings[cmIndex];
string dsColumnName = dcm.DataSetColumn;
if (dr.Table.Columns.Contains(dsColumnName))
{
string str = Apq.Convert.ChangeType <string>(dr[dsColumnName], string.Empty);
if (i == 0)
{
node.Text = str;
}
else
{
node.SubItems.Add(str);
}
}
else
{
if (i > 0)
{
node.SubItems.Add(string.Empty);
}
}
}
}
// 添加隐藏数据
foreach (string ColName in _HiddenColNames)
{
string str = Apq.Convert.ChangeType <string>(dr[ColName], string.Empty);
node.SubItems.Add(str);
}
}
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);
}
public void AddWords()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
string commandString = "INSERT Orders VALUES( @CustomerNo, '11-12-2011', @Goods)";
int orderid = 2;
int customerNo = 2;
// DateTime orderDate = new DateTime(11,12,2011);
string goods = "m";
SqlCommand command = new SqlCommand(commandString, connection);
command.Parameters.Add("OrderId", orderid);
command.Parameters.Add("CustomerNo", customerNo);
command.Parameters.Add("Goods", goods);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTableMapping mapping = adapter.TableMappings.Add("Table", "Покупець");
var columnMapping = new DataColumnMapping[]
{
new DataColumnMapping("CustomerNo", "Покупець")
};
mapping.ColumnMappings.AddRange(columnMapping);
try
{
connection.Open();
command.Transaction = connection.BeginTransaction();
command.ExecuteNonQuery();
command.Transaction.Commit();
}
catch (Exception)
{
command.Transaction.Rollback();
throw;
}
}
}
public void CopyTo()
{
DataColumnMapping[] colcops = new DataColumnMapping[5];
_columnMapCollection.AddRange(_cols);
_columnMapCollection.CopyTo(colcops, 0);
bool eq;
for (int i = 0; i < 5; i++)
{
eq = _columnMapCollection[i].Equals(colcops[i]);
Assert.True(eq);
}
colcops = new DataColumnMapping[7];
_columnMapCollection.CopyTo(colcops, 2);
for (int i = 0; i < 5; i++)
{
eq = _columnMapCollection[i].Equals(colcops[i + 2]);
Assert.True(eq);
}
eq = _columnMapCollection[0].Equals(colcops[0]);
Assert.False(eq);
eq = _columnMapCollection[0].Equals(colcops[1]);
Assert.False(eq);
}
/// <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);
}
// <Snippet1>
public void CreateDataColumnMapping()
{
DataColumnMapping mapping =
new DataColumnMapping("Description", "DataDescription");
}
public void ToString_OK()
{
DataColumnMapping dataColumnMapping = new DataColumnMapping("the source", "");
Assert.Equal("the source", dataColumnMapping.ToString());
}
/// <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);
}
static void Main(string[] args)
{
DataSet dsUsers = new DataSet("Users");
try
{
OleDbConnection dbConn =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Password=;User ID=Admin;Data Source=db.mdb");
// Create a data adapter to retrieve records from db
OleDbDataAdapter daUsers =
new OleDbDataAdapter("SELECT ID,fn,ln,cty,st" +
" FROM tabUsers", dbConn);
// Define each column to map
DataColumnMapping dcmUserID =
new DataColumnMapping("ID", "UserID");
DataColumnMapping dcmFirstName =
new DataColumnMapping("fn", "FirstName");
DataColumnMapping dcmLastName =
new DataColumnMapping("ln", "LastName");
DataColumnMapping dcmCity =
new DataColumnMapping("cty", "City");
DataColumnMapping dcmState =
new DataColumnMapping("st", "State");
// Define the table containing the mapped columns
DataTableMapping dtmUsers =
new DataTableMapping("Table", "User");
dtmUsers.ColumnMappings.Add(dcmUserID);
dtmUsers.ColumnMappings.Add(dcmFirstName);
dtmUsers.ColumnMappings.Add(dcmLastName);
dtmUsers.ColumnMappings.Add(dcmCity);
dtmUsers.ColumnMappings.Add(dcmState);
// Activate the mapping mechanism
daUsers.TableMappings.Add(dtmUsers);
// Fill the dataset
daUsers.Fill(dsUsers);
DataColumn[] dcaKey = { dsUsers.Tables[0].Columns["UserID"] };
dsUsers.Tables[0].PrimaryKey = dcaKey;
// Declare a command builder to create SQL instructions
// to create and update records.
OleDbCommandBuilder cb = new OleDbCommandBuilder(daUsers);
// Update an existing record in the DataSet
DataRow r = dsUsers.Tables[0].Rows.Find(8);
if (r != null)
{
r["FirstName"] = "Venus";
r["LastName"] = "Williams";
r["City"] = "Houston";
r["State"] = "Texas";
// Update the record in the database
daUsers.Update(dsUsers.GetChanges());
// Align in-memory data with the data source ones
dsUsers.AcceptChanges();
// Print success message
Console.WriteLine("The record has been updated " +
"successfully.");
}
else
{
Console.WriteLine("No record found...");
}
}
catch (System.Exception ex)
{
// Reject DataSet changes
dsUsers.RejectChanges();
// An error occurred. Show the error message
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// [Microsoft.Office.Interop.Excel]建立表列结构(保留不使用的页(Worksheet).(创建/覆盖文件)
/// </summary>
/// <param name="FileName">文件路径</param>
/// <param name="dtmc">取其 Source 属性值建立空文件</param>
public static void BuildSheets(string FileName, DataTableMappingCollection dtmc)
{
Application app = new Application();
try
{
Workbook wb;
if (File.Exists(FileName))
{
wb = app.Workbooks.Open(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
wb = app.Workbooks.Add(Type.Missing);
}
foreach (DataTableMapping dtm in dtmc)
{
try
{
Worksheet ws;
try
{
ws = wb.Worksheets[dtm.SourceTable] as Worksheet;
// 清空表
ws.UsedRange.ClearContents();
}
catch
{
ws = wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Worksheet;
// 表名
ws.Name = dtm.SourceTable;
}
((_Worksheet)ws).Activate();
// 列名
for (int i = 1; i <= dtm.ColumnMappings.Count; i++)
{
DataColumnMapping dcm = dtm.ColumnMappings[i - 1];
ws.Cells[1, i] = dcm.SourceColumn;
}
}
catch (System.Exception ex)
{
Apq.GlobalObject.ApqLog.Warn(string.Format("构建文件结构[{0}]失败:", dtm.SourceTable), ex);
}
}
// 保存
if (File.Exists(FileName))
{
wb.Save();
}
else
{
wb.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, true);
}
// 关闭
wb.Close(XlSaveAction.xlDoNotSaveChanges, FileName, false);
wb = null;
}
finally
{
try
{
Int64 appID = 0;
IntPtr hwnd = new IntPtr(app.Hwnd);
int appThreadID = Apq.DllImports.User32.GetWindowThreadProcessId(hwnd, ref appID);
// 退出 Excel
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
if (appID > 0)
{
try
{
Process pr = Process.GetProcessById(System.Convert.ToInt32(appID));
pr.Kill();
}
catch { }
}
app = null;
GC.Collect();
}
catch (System.Exception ex)
{
Apq.GlobalObject.ApqLog.Warn("关闭Excel失败", ex);
}
}
}
public void GetDataColumnBySchemaAction_String_String_DataTable_Type_MissingSchemaAction_MissingDataTableThrowsException()
{
AssertExtensions.Throws <ArgumentNullException>("dataTable", () => DataColumnMapping.GetDataColumnBySchemaAction("", "", null, typeof(string), new MissingSchemaAction()));
}
public void GetDataColumnBySchemaAction_String_String_DataTable_Type_MissingSchemaAction_MissingDataSetColumnReturnsNull()
{
DataColumn dataColumn = DataColumnMapping.GetDataColumnBySchemaAction("", null, new DataTable(), typeof(string), new MissingSchemaAction());
Assert.Null(dataColumn);
}
public ExportItem(string queryName, DataColumnMapping[] columns)
: base(columns)
{
this.PortalKey = queryName;
}
static void Main(string[] args)
{
DataSet dsUsers = new DataSet("Users");
try
{
// Define a connection object
OleDbConnection dbConn = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Password=;User ID=Admin;Data Source=db.mdb");
// Create a data adapter to retrieve records from DB
OleDbDataAdapter daUsers =
new OleDbDataAdapter("SELECT ID,fn,ln,cty,st" +
" FROM tabUsers", dbConn);
// Define each column to map
DataColumnMapping dcmUserID =
new DataColumnMapping("ID", "UserID");
DataColumnMapping dcmFirstName =
new DataColumnMapping("fn", "FirstName");
DataColumnMapping dcmLastName =
new DataColumnMapping("ln", "LastName");
DataColumnMapping dcmCity =
new DataColumnMapping("cty", "City");
DataColumnMapping dcmState =
new DataColumnMapping("st", "State");
// Define the table containing the mapped columns
DataTableMapping dtmUsers = new DataTableMapping("Table", "User");
dtmUsers.ColumnMappings.Add(dcmUserID);
dtmUsers.ColumnMappings.Add(dcmFirstName);
dtmUsers.ColumnMappings.Add(dcmLastName);
dtmUsers.ColumnMappings.Add(dcmCity);
dtmUsers.ColumnMappings.Add(dcmState);
// Activate the mapping mechanism
daUsers.TableMappings.Add(dtmUsers);
// Fill the dataset
daUsers.Fill(dsUsers);
// Declare a command builder to create SQL instructions
// to create and update records.
OleDbCommandBuilder cb = new OleDbCommandBuilder(daUsers);
// Insert a new record in the DataSet
DataRow r = dsUsers.Tables[0].NewRow();
r["FirstName"] = "Eddie";
r["LastName"] = "Robinson";
r["City"] = "Houston";
r["State"] = "Texas";
dsUsers.Tables[0].Rows.Add(r);
// Insert the record in the database
daUsers.Update(dsUsers.GetChanges());
// Align in-memory data with the data source ones
dsUsers.AcceptChanges();
// Print successfully message
Console.WriteLine("A new record has been" + " added to the database.");
}
catch (Exception ex)
{
// Reject DataSet changes
dsUsers.RejectChanges();
// An error occurred. Show the error message
Console.WriteLine(ex.Message);
}
}
