SqlCeCommandBuilder Class

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

Inheritance Hierarchy

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

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
[<SealedAttribute>]
type SqlCeCommandBuilder =  
    class
        inherit DbCommandBuilder
    end
public final class SqlCeCommandBuilder extends DbCommandBuilder

The SqlCeCommandBuilder type exposes the following members.

Constructors

  Name Description
Public method SqlCeCommandBuilder() Initializes a new instance of the SqlCeCommandBuilder class.
Public method SqlCeCommandBuilder(SqlCeDataAdapter) Initializes a new instance of the SqlCeCommandBuilder class with associated SqlCeDataAdapter and SqlCeCommand objects.

Top

Properties

  Name Description
Protected property CanRaiseEvents (inherited from Component)
Public property CatalogLocation Not supported in SQL Server Compact. (Overrides DbCommandBuilder.CatalogLocation.)
Public property CatalogSeparator Not supported in SQL Server Compact. (Overrides DbCommandBuilder.CatalogSeparator.)
Public property ConflictOption Specifies which ConflictOption is to be used by the SqlCeCommandBuilder. (Overrides DbCommandBuilder.ConflictOption.)
Public property Container (inherited from Component)
Public property DataAdapter Gets or sets a SqlCeDataAdapter object for which SQL statements are automatically generated.
Protected property DesignMode (inherited from Component)
Protected property Events (inherited from Component)
Public property QuotePrefix Gets or sets the beginning character or characters to use when specifying SQL Server database objects (for example, tables or columns) whose names contain characters such as spaces or reserved tokens. (Overrides DbCommandBuilder.QuotePrefix.)
Public property QuoteSuffix Gets or sets the ending character or characters to use when specifying SQL Server database objects (for example, tables or columns) whose names contain characters, such as spaces or reserved tokens. (Overrides DbCommandBuilder.QuoteSuffix.)
Public property SchemaSeparator Not supported in SQL Server Compact. (Overrides DbCommandBuilder.SchemaSeparator.)
Public property SetAllValues (inherited from DbCommandBuilder)
Public property Site (inherited from Component)

Top

Methods

  Name Description
Protected method ApplyParameterInfo (inherited from DbCommandBuilder)
Public method CreateObjRef (inherited from MarshalByRefObject)
Public method Dispose() (inherited from Component)
Protected method Dispose(Boolean) (inherited from DbCommandBuilder)
Public method Equals (inherited from Object)
Protected method Finalize (inherited from Component)
Public method GetDeleteCommand() Gets the automatically generated SqlCeCommand object required to perform deletions on the database when an application calls Update on the SqlCeDataAdapter.
Public method GetDeleteCommand(Boolean) (inherited from DbCommandBuilder)
Public method GetHashCode (inherited from Object)
Public method GetInsertCommand() Gets the automatically generated SqlCeCommand object required to perform inserts on the database when an application calls Update on the SqlCeDataAdapter.
Public method GetInsertCommand(Boolean) (inherited from DbCommandBuilder)
Public method GetLifetimeService (inherited from MarshalByRefObject)
Protected method GetParameterName(Int32) (inherited from DbCommandBuilder)
Protected method GetParameterName(String) (inherited from DbCommandBuilder)
Protected method GetParameterPlaceholder (inherited from DbCommandBuilder)
Protected method GetSchemaTable (inherited from DbCommandBuilder)
Protected method GetService (inherited from Component)
Public method GetType (inherited from Object)
Public method GetUpdateCommand() Gets the automatically generated SqlCeCommand object required to perform updates on the database when an application calls Update on the SqlCeDataAdapter.
Public method GetUpdateCommand(Boolean) (inherited from DbCommandBuilder)
Protected method InitializeCommand (inherited from DbCommandBuilder)
Public method InitializeLifetimeService (inherited from MarshalByRefObject)
Protected method MemberwiseClone() (inherited from Object)
Protected method MemberwiseClone(Boolean) (inherited from MarshalByRefObject)
Public method QuoteIdentifier Returns the correct quoted form of the specified identifier. (Overrides DbCommandBuilder.QuoteIdentifier(String).)
Public method RefreshSchema (inherited from DbCommandBuilder)
Protected method RowUpdatingHandler (inherited from DbCommandBuilder)
Protected method SetRowUpdatingHandler (inherited from DbCommandBuilder)
Public method ToString (inherited from Component)
Public method UnquoteIdentifier Returns the correct unquoted form of the specified identifier. (Overrides DbCommandBuilder.UnquoteIdentifier(String).)

Top

Events

  Name Description
Public event Disposed (inherited from Component)

Top

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.

Examples

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());
}

Thread Safety

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

See Also

Reference

System.Data.SqlServerCe Namespace