SqlCeCommandBuilder Class

Provides a means of automatically generating single-table commands used to reconcile changes made to a DataSet with the associated SQL Server Mobile database. This class cannot be inherited.

Namespace: System.Data.SqlServerCe
Assembly: System.Data.SqlServerCe (in system.data.sqlserverce.dll)

Syntax

'Declaration
Public NotInheritable Class SqlCeCommandBuilder
    Inherits DbCommandBuilder
'Usage
Dim instance As SqlCeCommandBuilder
public sealed class SqlCeCommandBuilder : DbCommandBuilder
public ref class SqlCeCommandBuilder sealed : public DbCommandBuilder
public final class SqlCeCommandBuilder extends DbCommandBuilder
public final class SqlCeCommandBuilder extends DbCommandBuilder

Remarks

You can create a SqlCeCommandBuilder object to automatically generate Transact-SQL statements for single-table updates if you set the SelectCommand property.

The SqlCeCommandBuilder registers itself as a listener for RowUpdating events whenever you set the DataAdapter property. You can only associate one SqlCeDataAdapter or SqlCeCommandBuilder object with each other at one time.

To generate INSERT, UPDATE, or DELETE statements, the SqlCeCommandBuilder uses the SelectCommand property to retrieve a required set of metadata automatically. If you change the SelectCommand after the metadata is retrieved (for example, after the first update), you should call the RefreshSchema method to update the metadata.

The SelectCommand must also return at least one primary key or unique column. If none are present, an InvalidOperation exception is generated, and the commands are not generated.

The SqlCeCommandBuilder also uses the Connection and Transaction properties referenced by the SelectCommand. You should call RefreshSchema if any of these properties are modified, or if the SelectCommand itself is replaced. Otherwise, the InsertCommand, UpdateCommand, and DeleteCommand properties retain their previous values.

If you call Dispose, the SqlCeCommandBuilder is disassociated from the SqlCeDataAdapter, and the generated commands are no longer used.

Example

The following example uses the SqlCeCommand, along with SqlCeDataAdapter and SqlCeConnection, to select rows from a data source. The example is passed a connection string, a query string, and a string that is the name of the database table. The example then creates a SqlCeCommandBuilder. That command builder is then used by the data adapter to update the modified DataSet in the local database.

Try
    Dim conn As New SqlCeConnection("Data Source = MyDatabase.sdf")
    conn.Open()

    Dim cmd As SqlCeCommand = conn.CreateCommand()
    cmd.CommandText = "SELECT * FROM employees"

    Dim adp As New SqlCeDataAdapter(cmd)

    Dim cb As New SqlCeCommandBuilder()
    cb.DataAdapter = adp

    MessageBox.Show(cb.GetUpdateCommand().CommandText)
    MessageBox.Show(cb.GetInsertCommand().CommandText)
    MessageBox.Show(cb.GetDeleteCommand().CommandText)

    Dim ds As New DataSet("test")
    adp.Fill(ds)

    ' Modify the contents of the DataSet
    '
    ds.Tables(0).Rows(0)("First Name") = "Joe"

    adp.Update(ds)

Catch e1 As Exception
    Console.WriteLine(e1.ToString())
End Try
try
{
    SqlCeConnection conn = new SqlCeConnection("Data Source = MyDatabase.sdf");
    conn.Open();

    SqlCeCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM employees";

    SqlCeDataAdapter adp = new SqlCeDataAdapter(cmd);

    SqlCeCommandBuilder cb = new SqlCeCommandBuilder();
    cb.DataAdapter = adp;

    MessageBox.Show(cb.GetUpdateCommand().CommandText);
    MessageBox.Show(cb.GetInsertCommand().CommandText);
    MessageBox.Show(cb.GetDeleteCommand().CommandText);

    DataSet ds = new DataSet("test");
    adp.Fill(ds);

    // Modify the contents of the DataSet
    //
    ds.Tables[0].Rows[0]["First Name"] = "Joe";

    adp.Update(ds);


catch (Exception e1)
{
    Console.WriteLine(e1.ToString());

Inheritance Hierarchy

System.Object
   System.MarshalByRefObject
     System.ComponentModel.Component
       System.Data.Common.DbCommandBuilder
        System.Data.SqlServerCe.SqlCeCommandBuilder

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

Platforms

Windows CE, Windows Mobile for Pocket PC, Windows Mobile for Smartphone, Windows XP Professional x64 Edition, Windows XP SP2

The .NET Framework does not support all versions of every platform. For a list of the supported versions, see System Requirements.

Version Information

.NET Compact Framework

Supported in: 2.0, 1.0

See Also

Reference

SqlCeCommandBuilder Members
System.Data.SqlServerCe Namespace