Note: This namespace, class, or member is supported only in version 1.1 of the .NET Framework.

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

For a list of all members of this type, see SqlCeCommandBuilder Members.

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

[Visual Basic]
NotInheritable Public Class SqlCeCommandBuilder
   Inherits Component
[C#]
public sealed class SqlCeCommandBuilder : Component
[C++]
public __gc __sealed class SqlCeCommandBuilder : public Component
[JScript]
public class SqlCeCommandBuilder extends Component

Thread Safety

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

Remarks

SqlCeCommandBuilder creates SQL statements that do not detect conflicts. This behavior differs from that of System.Data.SqlClient.

The SqlCeCommandBuilder registers itself as a listener for RowUpdating events whenever you set the DataAdapter property. You can associate only 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 meta data automatically. If you change the SelectCommand after the meta data has been retrieved (for example, after the first update), you should call the RefreshSchema method to update the meta data.

The SelectCommand must also return at least one primary key or unique column. If there is none 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

[Visual Basic, C#] 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.

[Visual Basic] 
Public Shared Function SelectSqlCeRows(ByVal myConnection As String, ByVal mySelectQuery As String, ByVal myTableName As String) As DataSet
    Dim myConn As New SqlCeConnection(myConnection)
    Dim myDataAdapter As New SqlCeDataAdapter()

    myDataAdapter.SelectCommand = New SqlCeCommand(mySelectQuery, myConn)
    Dim cb As SqlCeCommandBuilder = New SqlCeCommandBuilder(myDataAdapter)

    myConn.Open()

    Dim ds As DataSet = New DataSet()
    myDataAdapter.Fill(ds, myTableName)

    ' Code to modify data in DataSet goes here 

    ' Without the SqlCeCommandBuilder this line would fail.
    myDataAdapter.Update(ds, myTableName)
    myConn.Close()
End Function

[C#] 
public static DataSet SelectSqlCeRows(string myConnection, string mySelectQuery, string myTableName) {
    SqlCeConnection myConn         = new SqlCeConnection(myConnection);
    SqlCeDataAdapter myDataAdapter = new SqlCeDataAdapter();
    myDataAdapter.SelectCommand    = new SqlCeCommand(mySelectQuery, myConn);
    SqlCeCommandBuilder cb         = new SqlCeCommandBuilder(myDataAdapter);

    myConn.Open();

    DataSet ds = new DataSet();
    myDataAdapter.Fill(ds, myTableName);

    //code to modify data in DataSet goes here

    //Without the SqlCeCommandBuilder this line would fail
    myDataAdapter.Update(ds, myTableName);

    myConn.Close();

    return ds;
}

[C++, JScript] No example is available for C++ or JScript. To view a Visual Basic or C# example, click the Language Filter button in the upper-left corner of the page.

Requirements

Namespace: System.Data.SqlServerCe

Platforms: .NET Compact Framework - Windows CE .NET

Assembly: System.Data.Sqlserverce (in System.Data.Sqlserverce.dll)

See Also

SqlCeCommandBuilder Members | System.Data.SqlServerCe Namespace