30.3 Methods Reference

30.3 Methods Reference

DeriveParameters

 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.

GetDeleteCommand

 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.

GetInsertCommand

 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.

GetUpdateCommand

 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.

RefreshSchema

 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.



ADO. NET in a Nutshell
ADO.NET in a Nutshell
ISBN: 0596003617
EAN: 2147483647
Year: 2005
Pages: 415

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net