30.3 Methods Reference

CommandBuilder.DeriveParameters(Command cmd);

Populates the Parameters collection for the stored procedure Command object from the data source.

Parameters

cmd

The Command object referencing the stored procedure for which the parameters are to be derived.

Example

The following example shows how to use the DeriveParameters( ) method to retrieve parameters for a stored procedure command:

String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("CustOrderHist", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
conn.Close();

foreach(SqlParameter param in cmd.Parameters)
{
    // ... do something with the SqlParameter param
}

Notes

The Command must be a stored procedure. Attempting to call this method on any other type of command, or on a stored procedure that doesn't exist in the data source, results in an InvalidOperationException.

Calling this method overwrites any existing information in the Parameters collection for the Command.

This method shouldn't be used when maximum performance is required. Because this method requires a round trip to the data source, it's better to specify the Parameters collection for stored procedure explicitly.

SqlCommand deleteCommand = CommandBuilder.GetDeleteCommand();

Returns a reference to the automatically generated Command object, which deletes data from the data source when the Update( ) method of the data adapter is called.

Parameters

None.

Example

The following example demonstrates how to retrieve the automatically generated DeleteCommand, InsertCommand, and UpdateCommand objects using the CommandBuilder:

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

SqlDataAdapter da=new SqlDataAdapter(sqlSelect, connString);

// create the command builder
SqlCommandBuilder cb = new SqlCommandBuilder(da);

// retrieve the Command objects
SqlCommand deleteCommand = cb.GetDeleteCommand();
SqlCommand insertCommand = cb.GetInsertCommand();
SqlCommand updateCommand = cb.GetUpdateCommand();

Notes

If the SELECT command that is the basis of the automatically generated DELETE command is changed, the RefreshSchema( ) method must be called. Otherwise the GetDeleteCommand( ) method returns a statement for the previous schema.

Calling one of the GetDeleteCommand( ), GetInsertCommand( ), or GetUpdateCommand( ) methods, or the Update( ) method of the DataAdapter generates all delete, insert, and update command logic.

SqlCommand insertCommand = CommandBuilder.GetInsertCommand();

Returns a reference to the automatically generated Command object, which inserts data into the data source when the Update( ) method of the data adapter is called.

Parameters

None.

Example

See the Example for the GetDeleteCommand( ) method in this chapter.

Notes

If the select command that is the basis of the automatically generated INSERT command is changed, the RefreshSchema( ) method must be called. Otherwise the GetInsertCommand( ) method returns a statement for the previous schema.

Calling one of the GetDeleteCommand( ), GetInsertCommand( ), or GetUpdateCommand( ) methods or the Update( ) method of the DataAdapter, generates all delete, insert, and update command logic.

SqlCommand updateCommand = CommandBuilder.GetUpdateCommand();

Returns a reference to the automatically generated Command object, which updates data within the data source when the Update( ) method of the data adapter is called.

Parameters

None.

Example

See the Example for the GetDeleteCommand( ) method in this chapter.

Notes

If the SELECT command that is the basis of the automatically generated UPDATE command is changed, the RefreshSchema( ) method must be called. Otherwise the GetUpdateCommand( ) method returns a statement for the previous schema.

Calling one of the GetDeleteCommand( ), GetInsertCommand( ), or GetUpdateCommand( ) methods or the Update( ) method of the DataAdapter, generates all delete, insert, and update command logic.

CommandBuilder.RefreshSchema();

Refreshes the schema that automatically generates the DeleteCommand, InsertCommand, and UpdateCommand.

Parameters

None.

Example

The following example demonstrates how to use the RefreshSchema( ) method:

// connection and select command strings
String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";
SqlConnection conn = new SqlConnection(connString);

String sqlSelect = "SELECT * FROM Orders";
SqlCommand selectCommand = new SqlCommand(sqlSelect, conn);

SqlDataAdapter da = new SqlDataAdapter(selectCommand);

// create the command builder
SqlCommandBuilder cb = new SqlCommandBuilder(da);
// retrieve the DeleteCommand
SqlCommand deleteCommand = cb.GetDeleteCommand();

// change the select statement
sqlSelect = "SELECT OrderID, CustomerID, EmployeeID FROM Orders";
selectCommand = new SqlCommand(sqlSelect, conn);
// have to call RefreshSchema before retrieving the DeleteCommand again
cb.RefreshSchema();
deleteCommand = cb.GetDeleteCommand();

Notes

The RefreshSchema( ) method should be called whenever the SELECT statement associated with the CommandBuilder changes.

The RefreshSchema( ) method doesn't cause the updating logic in the DeleteCommand, InsertCommand, and UpdateCommand objects to be regenerated immediately. The updating logic is regenerated when the DataAdapter Update( ) method is called or when the GetDeleteCommand( ), GetInsertCommand( ), or GetUpdateCommand( ) method is called.