29.2 Properties Reference

Boolean acceptChangesDuringFill = DataAdapter.AcceptChangesDuringFill;
DataAdapter.AcceptChangesDuringFill = acceptChangesDuringFill;

Gets or sets a value that indicates whether AcceptChanges( ) is called on a DataRow after the row is added to a DataTable using the Fill( ) method.

Example

The following example demonstrates the effect of setting the AcceptChangesDuringFill property to both true and false using the Orders table in the Northwind database:

// connection and select command strings
String connString = "Data Source = (local);Integrated security = SSPI;" + 
    "Initial Catalog = Northwind;";
String sqlSelect = "SELECT * FROM Orders";

// create a new DataSet to receive the data
DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connString);

da.AcceptChangesDuringFill = true;
da.Fill(ds, "Orders");
// each row in the Orders table has RowState = Unchanged

// remove all rows from the Orders table
ds.Tables["Orders"].Clear();

da.AcceptChangesDuringFill = false;
da.Fill(ds, "Orders");
// each row in the Orders table has RowState = Inserted

// manually call AcceptChanges
ds.AcceptChanges(); 
// each row in the Orders table has RowState = Unchanged

Notes

If AcceptChangesDuringFill property is false, and AcceptChanges( ) isn't called, newly added rows have a RowState of Inserted.

The default value of the AcceptChangesDuringFill property is true.

Boolean continueUpdateOnError = DataAdapter.ContinueUpdateOnError;
DataAdapter.ContinueUpdateOnError = continueUpdateOnError;

Gets or sets a value indicating whether the DataAdapter should raise an exception and stop processing remaining updates when an error is encountered.

Example

The following example demonstrates how to set the ContinueUpdateOnError property:

SqlDataAdapter da = new SqlDataAdapter();
da.ContinueUpdateOnError = true;

Notes

If this value if true, and an error occurs while updating a row, the RowError property of that row is set to the error information, the update of the row isn't performed, and processing continues with the next row.

The default value of the ContinueUpdateOnError property false.

SqlCommand deleteCommand = DataAdapter.DeleteCommand;
DataAdapter.DeleteCommand = deleteCommand;

Gets or sets the command, either a SQL statement or a stored procedure, that deletes the DataSet records marked for deletion from the data source when the Update( ) method is called.

Example

The following example shows how to set the DeleteCommand, InsertCommand, SelectCommand, and UpdateCommand properties using the Shippers table in the Northwind database:

// the SQL statements for delete, insert, select, and update
String sqlSelect = "SELECT ShipperID, CompanyName, Phone FROM Shippers";
String sqlDelete = "DELETE FROM Shippers WHERE ShipperID=@ShipperID";
String sqlInsert = "INSERT Shippers (CompanyName, Phone) " +
    "VALUES (@CompanyName, @Phone)";
String sqlUpdate = "UPDATE Shippers SET CompanyName=@CompanyName, " +
    "Phone=@Phone WHERE ShipperID=@ShipperID";

// build the connection
String connString = "Data Source = (local);Integrated security = SSPI;" + 
    "Initial Catalog = Northwind;";
SqlConnection conn = new SqlConnection(connString);

// create the update command objects using SQL statements
SqlCommand selectCommand = new SqlCommand(sqlSelect, conn);
SqlCommand deleteCommand = new SqlCommand(sqlDelete, conn);
SqlCommand insertCommand = new SqlCommand(sqlInsert, conn);
SqlCommand updateCommand = new SqlCommand(sqlUpdate, conn);

// set up the parameters for the command objects
SqlParameterCollection cparams;

// delete command parameters
cparams = deleteCommand.Parameters;
cparams.Add("@ShipperID", SqlDbType.Int, 0, "ShipperID");

// insert command parameters
cparams = insertCommand.Parameters;
cparams.Add("@ShipperID", SqlDbType.Int, 0, "ShipperID");
cparams["@ShipperID"].Direction = ParameterDirection.Output;
cparams.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
cparams.Add("@ShipperPhone", SqlDbType.NVarChar, 24, "ShipperPhone");

// update command parameters
cparams = updateCommand.Parameters;
cparams.Add("@ShipperID", SqlDbType.Int, 0, "ShipperID");
cparams.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
cparams.Add("@ShipperPhone", SqlDbType.NVarChar, 24, "ShipperPhone");

// create the DataAdapter
SqlDataAdapter da  =  new SqlDataAdapter(sqlSelect, connString);

// set the command objects for the DataAdapter
da.DeleteCommand = deleteCommand;
da.InsertCommand = insertCommand;
da.UpdateCommand = updateCommand;

This example uses dynamic SQL statements rather than stored procedures. To use stored procedures, set the command text for each update command object to the name of the stored procedure and set each CommandType property to StoredProcedure. The affected lines for the delete command are shown here:

// replace line 3 with the following line - the stored procedure
// DeleteShipper must exist on the server.
String SqlDelete = "DeleteShipper";

// insert after deleteCommand is instantiated.
deleteCommand.CommandType = CommandType.StoredProcedure;

Note

If the DeleteCommand property isn't set, and the DataSet has a primary key, the DeleteCommand can be generated automatically using the CommandBuilder after specifying the SelectCommand property.

SqlCommand insertCommand = DataAdapter.InsertCommand
DataAdapter.InsertCommand = insertCommand;

Gets or sets the command, either a SQL statement or a stored procedure, used to insert new DataSet records into the data source when the Update( ) method is called.

Example

See the Example for the DeleteCommand property in this chapter.

Note

If the InsertCommand property isn't set, and the DataSet has a primary key, the InsertCommand can be automatically generated using the CommandBuilder after specifying the SelectCommand property.

MissingMappingAction mma = DataAdapter.MissingMappingAction;
DataAdapter.MissingMappingAction = mma;

Specifies the action to take when columns or tables in the incoming data don't have matching columns or tables in the DataSet. The value is one of the MissingMappingAction enumeration values described on Table 29-6.

Table 29-6. MissingMappingAction enumeration

Value	Description
Passthrough	Create missing objects in the DataSet using the column and table names from the data source. This is the default value.
Ignore	Ignore table and columns in the data source that don't exist in the DataSet.
Error	If the data source contains tables or columns that don't appear in the DataSet, a SytemException is raised.

Example

The following example shows how to set the MissingMappingAction and MissingSchemaAction properties so that an error is raised if columns or tables in the incoming schema or data from the data source don't have matching columns in the DataSet.

// connection and select command strings
String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";
String selectCommand = "SELECT * FROM Orders";

SqlDataAdapter da = new sqlDataAdapter(selectCommand, connString);
da.MissingMappingAction = MissingMappingAction.Error;
da.MissingSchemaAction = MissingSchemaAction.Error;

Note

An ArgumentException is raised if an attempt is made to set the value of this property to a value other than one of the MissingMappingAction values.

MissingSchemaAction msa = DataAdapter.MissingSchemaAction;
DataAdapter.MissingSchemaAction = msa;

Specifies the action to take when the columns or tables in the incoming schema data don't have matching columns or tables in the DataSet. The value is one of the MissingSchemaAction enumeration values described in Table 29-7. The default value is Add.

Table 29-7. MissingSchemaAction enumeration

Value	Description
Add	If the data source contains columns or tables that don't exist in the DataSet, DataColumn, and DataTable, objects required to complete the schema are added to the DataSet. This is the default value.
AddWithKey	In addition to the behavior of the MissingSchemaAction.Add, this causes key information to be added.
Error	If the data source contains tables or columns that don't appear in the DataSet, a SytemException is raised.
Ignore	Tables and columns in the data source that don't exist in the DataSet are ignored.

Example

See the Example for the MissingMappingAction property in this chapter.

Note

An ArgumentException is raised if an attempt is made to set the value of this property to a value other than one of the MissingSchemaAction values.

SqlCommand selectCommand = DataAdapter.SelectCommand;
DataAdapter.SelectCommand = selectCommand;

Gets or sets the command, either a SQL statement or a stored procedure, that selects records from the data source when using the Fill( ) method.

Example

See the Example for the DeleteCommand property in this chapter.

Note

If the SelectCommand doesn't return any rows, no tables are added to the DataSet during the Fill( ) operation, and no exception is raised.

SqlCommand updateCommand = DataAdapter.UpdateCommand;
DataAdapter.UpdateCommand = updateCommand;

Gets or sets the command, either a SQL statement or a stored procedure, that updates modified DataSet records in the data source when the Update( ) method is called.

Example

See the Example for the DeleteCommand property in this chapter.

Note

If the UpdateCommand property isn't set, and the DataSet has a primary key, the UpdateCommand can be generated automatically using the CommandBuilder after specifying the SelectCommand property.