Using CompositeCommandBuilder
The CompositeCommandBuilder can be used to execute code to access or modify TDV resources. The following example code deletes the first row of the current SELECT. The builder.GetUpdateCommand is necessary when using a Microsoft implementation, but is optional in the following sample.
To use the CompositeCommandBuilder
1. Define a CompositeDataAdapter object with a SQL statement and connection object.
2. Create a new CompositeCommandBuilder object, with a CompositeDataAdapter object as an argument.
3. Create a new DataTable object and clear it. Use a call to the da.Fill(dt) method to populate it with data.
4. To delete the first row, make this call: dt.Rows[0].Delete(). The deletion is finalized after calling the da.Update(dt) statement.
public void TestDelete()
{
CompositeConnection conn = GetConnection();
CompositeDataAdapter da = new CompositeDataAdapter("select * from products where ProductID in (1111)", conn);
CompositeCommandBuilder cb = new CompositeCommandBuilder(da);
DataTable dt = new DataTable();
dt.Clear();
da.Fill(dt);
dt.Rows[0].Delete();
da.Update(dt);
}
5. Insert rows into a cursor with specified values. Section A marks where the insertion of a new row begins.
public void TestInsert()
{
try
{
CompositeConnection conn = GetConnection();
CompositeDataAdapter da = new CompositeDataAdapter("select * from products", conn);
CompositeCommandBuilder cb = new CompositeCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
//Section A
//Create the new row
DataRow row = dt.NewRow();
//The values in the new row are set with:
row["ProductID"] = 1111;
row["ProductName"] = "TDV";
row["ProductDescription"] = DBNull.Value;
dt.Rows.Add(row);
//Insertion of the new rows into the database
da.Update(dt);
}
catch (Exception ex)
{
throw ex;
}
}
6. (Optionally) When using a Microsoft implementation, you must use the builder.GetUpdateCommand method. Here is some sample code from a Microsoft-based implementation:
public DataSet TestUpdate()
{
CompositeConnection conn = GetConnection();
String queryString = "select * from products where ProductID in (1111)";
String tableName = "products";
CompositeDataAdapter adapter = new CompositeDataAdapter();
adapter.SelectCommand = new CompositeCommand(queryString, conn);
CompositeCommandBuilder builder = new CompositeCommandBuilder(adapter);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, tableName);
dataSet.Tables[0].Rows[0]["ProductName"] = "Discovery";
builder.GetUpdateCommand();
adapter.Update(dataSet, tableName);
return dataSet;
}