Karatas, FERHAT
I would love to change the world, but they won't give me the source code!
Using MissingSchemaAction.AddWithKey
25.01.2007 17:28:09 - Filed under : Ado.net
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
with 0 comments

What do you think ?
Security Code 6437
Check S. Code
Name  
Email  
Home Page  
Comments