Adding schema information to a DataSet before filling it with data
ensures that primary key constraints are included with the DataTable
objects in the DataSet. As a result, when additional calls to fill the
DataSet are made, the primary key column information is used to match
new rows from the data source with current rows in each DataTable, and
current data in the tables is overwritten with data from the data
source.
Without the schema information, the
new rows from the data source are appended to the DataSet, resulting in
duplicate rows. Example : DataSet
custDataSet = new DataSet(); custAdapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey; custAdapter.Fill(custDataSet,
"Customers");
Using FillSchema or
setting the MissingSchemaAction to
AddWithKey requires extra processing at the data
source to determine primary key column information. This additional
processing can hinder performance. If you know the primary key
information at design time, we recommend that you explicitly specify
the primary key column or columns in order to achieve optimal
performance.
PS : You should use AddWithKey option if you want to handle "Constraint Exception". I use it in my little replication engine program like this ;
using (OracleCommand command = new OracleCommand()) { command.CommandText = "SELECT * FROM " + tableName + " WHERE " + primaryKeyField + "=" + drow[primaryKeyField]; command.Connection = connection;
adapter.SelectCommand = command; adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable dt = new DataTable(); adapter.Fill(dt);
// Commandbuilder using (OracleCommandBuilder builder = new OracleCommandBuilder()) { builder.DataAdapter = adapter;
// INSERT DataRow dr = dt.NewRow(); foreach (DataColumn dc in dt.Columns) { dr[dc.ColumnName] = drow[dc.ColumnName]; }
try { dt.Rows.Add(dr); }
catch (ConstraintException) { // UPDATE DataRow row = dt.Rows[0]; foreach (DataColumn dc in dt.Columns) { row[dc.ColumnName] = drow[dc.ColumnName]; } } adapter.Update(dt); } }
Related Terminology :
MissingSchemaAction.AddWithKey, Primary Key, Constraint Exception
|