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.
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
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.
The following example demonstrates how to set the ContinueUpdateOnError property:
SqlDataAdapter da = new SqlDataAdapter(); da.ContinueUpdateOnError = true;
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.
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;
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.
See the Example for the DeleteCommand property in this chapter.
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.
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. |
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;
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.
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. |
See the Example for the MissingMappingAction property in this chapter.
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.
See the Example for the DeleteCommand property in this chapter.
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.
See the Example for the DeleteCommand property in this chapter.
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.