SqlCeDataAdapter.UpdateCommand Property
Gets or sets an SQL statement used to update records in the data source.
Namespace: System.Data.SqlServerCe
Assembly: System.Data.SqlServerCe (in system.data.sqlserverce.dll)
Syntax
'Declaration
Public Property UpdateCommand As SqlCeCommand
'Usage
Dim instance As SqlCeDataAdapter
Dim value As SqlCeCommand
value = instance.UpdateCommand
instance.UpdateCommand = value
public SqlCeCommand UpdateCommand { get; set;
public:
property SqlCeCommand^ UpdateCommand {
SqlCeCommand^ get ();
void set (SqlCeCommand^ value);
/** @property */
public SqlCeCommand get_UpdateCommand ()
/** @property */
public void set_UpdateCommand (SqlCeCommand value)
public function get UpdateCommand () : SqlCeCommand
public function set UpdateCommand (value : SqlCeCommand)
Property Value
A SqlCeCommand used during Update to update records in the data source that correspond to modified rows in the DataSet.
Remarks
During Update, if this property is not set and primary key information is present in the DataSet, the UpdateCommand can be generated automatically. To have this property generated automatically, set the SelectCommand property and use the SqlCeCommandBuilder. Then, any additional commands that you do not set are generated by the SqlCeCommandBuilder. This generation logic requires key column information to be present in the DataSet.
When UpdateCommand is assigned to a previously-created SqlCeCommand, the SqlCeCommand is not cloned. The UpdateCommand maintains a reference to the previously-created SqlCeCommand object.
Note
If execution of this command returns rows, these rows may be merged with the DataSet, depending on how you set the UpdatedRowSource property of the SqlCeCommand object.
Example
The following example creates a SqlCeDataAdapter, modifies a dataset, and then calls the Update method to update the data source.
Dim cmd As SqlCeCommand = Nothing
Dim adp As SqlCeDataAdapter = Nothing
Try
adp = New SqlCeDataAdapter()
Dim conn As New SqlCeConnection("Data Source = MyDatabase.sdf")
' Create the SelectCommand
'
cmd = conn.CreateCommand()
cmd.CommandText = "SELECT [Employee ID], [First Name], [Last Name] FROM Employees"
adp.SelectCommand = cmd
' Create the InsertCommand
'
cmd = conn.CreateCommand()
cmd.CommandText = "INSERT INTO Employees ([First Name],[Last Name]) VALUES (@first, @last)"
Dim p As SqlCeParameter = Nothing
p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name")
p.SourceVersion = DataRowVersion.Original
p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name")
p.SourceVersion = DataRowVersion.Original
adp.InsertCommand = cmd
' Create the UpdateCommand
'
cmd = conn.CreateCommand()
cmd.CommandText = "UPDATE Employees SET [First Name] = @first, " + _
"[Last Name] = @last WHERE [Employee ID] = @employeeID"
p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name")
p.SourceVersion = DataRowVersion.Current
p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name")
p.SourceVersion = DataRowVersion.Current
p = cmd.Parameters.Add("@employeeID", SqlDbType.NVarChar, 20, "Employee ID")
p.SourceVersion = DataRowVersion.Original
adp.UpdateCommand = cmd
' Populate the dataset with the results from the SELECT statement
'
Dim ds As New DataSet()
adp.Fill(ds)
' Modify the dataset
'
MessageBox.Show("Number of rows: " & ds.Tables(0).Rows.Count)
' Insert some rows
'
ds.Tables(0).Rows.Add(New Object() {Nothing, "Nancy", "Smith")
ds.Tables(0).Rows.Add(New Object() {Nothing, "Joe", "Clayton")
' Update some rows
'
ds.Tables(0).Rows(1)(1) = "David"
ds.Tables(0).Rows(1)(2) = "Johnson"
' This will execute two INSERT and one UPDATE statements
'
adp.Update(ds.Tables(0))
Catch e As Exception
MessageBox.Show(e.Message)
Finally
If Not Nothing Is adp.SelectCommand Then
adp.SelectCommand.Dispose()
End If
If Not Nothing Is adp.InsertCommand Then
adp.InsertCommand.Dispose()
End If
End Try
SqlCeCommand cmd = null;
SqlCeDataAdapter adp = null;
try
{
adp = new SqlCeDataAdapter();
SqlCeConnection conn = new SqlCeConnection("Data Source = MyDatabase.sdf");
// Create the SelectCommand
//
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT [Employee ID], [First Name], [Last Name] FROM Employees";
adp.SelectCommand = cmd;
// Create the InsertCommand
//
cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Employees ([First Name],[Last Name]) VALUES (@first, @last)";
SqlCeParameter p = null;
p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name");
p.SourceVersion = DataRowVersion.Original;
p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name");
p.SourceVersion = DataRowVersion.Original;
adp.InsertCommand = cmd;
// Create the UpdateCommand
//
cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE Employees SET [First Name] = @first, " +
"[Last Name] = @last WHERE [Employee ID] = @employeeID";
p = cmd.Parameters.Add("@first", SqlDbType.NVarChar, 10, "First Name");
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add("@last", SqlDbType.NVarChar, 20, "Last Name");
p.SourceVersion = DataRowVersion.Current;
p = cmd.Parameters.Add("@employeeID", SqlDbType.NVarChar, 20, "Employee ID");
p.SourceVersion = DataRowVersion.Original;
adp.UpdateCommand = cmd;
// Populate the dataset with the results from the SELECT statement
//
DataSet ds = new DataSet();
adp.Fill(ds);
// Modify the dataset
//
MessageBox.Show("Number of rows: " + ds.Tables[0].Rows.Count);
// Insert some rows
//
ds.Tables[0].Rows.Add(new object[] { null, "Nancy", "Smith" );
ds.Tables[0].Rows.Add(new object[] { null, "Joe", "Clayton" );
// Update some rows
//
ds.Tables[0].Rows[1][1] = "David";
ds.Tables[0].Rows[1][2] = "Johnson";
// This will execute two INSERT and one UPDATE statements
//
adp.Update(ds.Tables[0]);
catch (Exception e)
{
MessageBox.Show(e.Message);
finally
{
if (null != adp.SelectCommand) adp.SelectCommand.Dispose();
if (null != adp.InsertCommand) adp.InsertCommand.Dispose();
.NET Framework Security
- Full trust for the immediate caller. This member cannot be used by partially trusted code. For more information, see .
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
SqlCeDataAdapter Class
SqlCeDataAdapter Members
System.Data.SqlServerCe Namespace
DeleteCommand
InsertCommand
SelectCommand