Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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.

Namespace:  System.Data.SqlServerCe
Assembly:  System.Data.SqlServerCe (in System.Data.SqlServerCe.dll)
public sealed class SqlCeCommandBuilder : DbCommandBuilder

The SqlCeCommandBuilder type exposes the following members.

  NameDescription
Public methodSqlCeCommandBuilder()Initializes a new instance of the SqlCeCommandBuilder class.
Public methodSqlCeCommandBuilder(SqlCeDataAdapter)Initializes a new instance of the SqlCeCommandBuilder class with associated SqlCeDataAdapter and SqlCeCommand objects.
Top
  NameDescription
Protected propertyCanRaiseEvents (inherited from Component)
Public propertyCatalogLocationNot supported in SQL Server Compact. (Overrides DbCommandBuilder.CatalogLocation.)
Public propertyCatalogSeparatorNot supported in SQL Server Compact. (Overrides DbCommandBuilder.CatalogSeparator.)
Public propertyConflictOptionSpecifies which ConflictOption is to be used by the SqlCeCommandBuilder. (Overrides DbCommandBuilder.ConflictOption.)
Public propertyContainer (inherited from Component)
Public propertyDataAdapterGets or sets a SqlCeDataAdapter object for which SQL statements are automatically generated.
Protected propertyDesignMode (inherited from Component)
Protected propertyEvents (inherited from Component)
Public propertyQuotePrefixGets 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 propertyQuoteSuffixGets 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 propertySchemaSeparatorNot supported in SQL Server Compact. (Overrides DbCommandBuilder.SchemaSeparator.)
Public propertySetAllValues (inherited from DbCommandBuilder)
Public propertySite (inherited from Component)
Top
  NameDescription
Protected methodApplyParameterInfo (inherited from DbCommandBuilder)
Public methodCreateObjRef (inherited from MarshalByRefObject)
Public methodDispose() (inherited from Component)
Protected methodDispose(Boolean) (inherited from DbCommandBuilder)
Public methodEquals (inherited from Object)
Protected methodFinalize (inherited from Component)
Public methodGetDeleteCommand()Gets the automatically generated SqlCeCommand object required to perform deletions on the database when an application calls Update on the SqlCeDataAdapter.
Public methodGetDeleteCommand(Boolean) (inherited from DbCommandBuilder)
Public methodGetHashCode (inherited from Object)
Public methodGetInsertCommand()Gets the automatically generated SqlCeCommand object required to perform inserts on the database when an application calls Update on the SqlCeDataAdapter.
Public methodGetInsertCommand(Boolean) (inherited from DbCommandBuilder)
Public methodGetLifetimeService (inherited from MarshalByRefObject)
Protected methodGetParameterName(Int32) (inherited from DbCommandBuilder)
Protected methodGetParameterName(String) (inherited from DbCommandBuilder)
Protected methodGetParameterPlaceholder (inherited from DbCommandBuilder)
Protected methodGetSchemaTable (inherited from DbCommandBuilder)
Protected methodGetService (inherited from Component)
Public methodGetType (inherited from Object)
Public methodGetUpdateCommand()Gets the automatically generated SqlCeCommand object required to perform updates on the database when an application calls Update on the SqlCeDataAdapter.
Public methodGetUpdateCommand(Boolean) (inherited from DbCommandBuilder)
Protected methodInitializeCommand (inherited from DbCommandBuilder)
Public methodInitializeLifetimeService (inherited from MarshalByRefObject)
Protected methodMemberwiseClone() (inherited from Object)
Protected methodMemberwiseClone(Boolean) (inherited from MarshalByRefObject)
Public methodQuoteIdentifierReturns the correct quoted form of the specified identifier. (Overrides DbCommandBuilder.QuoteIdentifier(String).)
Public methodRefreshSchema (inherited from DbCommandBuilder)
Protected methodRowUpdatingHandler (inherited from DbCommandBuilder)
Protected methodSetRowUpdatingHandler (inherited from DbCommandBuilder)
Public methodToString (inherited from Component)
Public methodUnquoteIdentifierReturns the correct unquoted form of the specified identifier. (Overrides DbCommandBuilder.UnquoteIdentifier(String).)
Top
  NameDescription
Public eventDisposed (inherited from Component)
Top

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.

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


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.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.