Creating a SQLCommand for use in a SQLDataAdapter

Question

I am trying to delete data from a table using a SQLDataAdapter, and to do so I need to give it a DeleteCommand.

The SQL I would use to delete a row is:

DELETE FROM table WHERE ID = x

The problem is thus: How do I specify to the DataAdapter what to replace x with? The SQL to generate the DataAdapter is slightly different (no joins) than the data table it's being told to update (an outer join).

How can I do this?

Answer 1

use SourceColumn and SourceVersion SqlParameter properties:

        var deleteCommand = connection.CreateCommand();
        deleteCommand = "DELETE FROM table WHERE ID = @ID";
        var param = new SqlParameter("ID");
        param.SourceColumn = "the Select Column";
        param.SourceVersion = DataRowVersion.Original;
        deleteCommand.Parameters.Add (param);

Answer 2

Here you can pass parameter to delete command :

// Create the DeleteCommand.
command = new SqlCommand(
    "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);

// Add the parameters for the DeleteCommand.
parameter = command.Parameters.Add(
      "@CustomerID", SqlDbType.NChar, 5, "CustomerID");
parameter.SourceVersion = DataRowVersion.Original;

adapter.DeleteCommand = command;

The code taken from MSDN