29.2 Properties Reference | ADO.NET in a Nutshell

AcceptChangesDuringFill

 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 .

ContinueUpdateOnError

 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 .

DeleteCommand

 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.

InsertCommand

 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

 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

 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.

SelectCommand

 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.

UpdateCommand

 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.