none
Deleting rows from SQL 2005 using adapters and datatable RRS feed

  • Question

  • Hi,

    I am trying to delete rows from SQL server 2005 as follows :

    SqlDataAdapter adapter = new SQLDataAdapter("Select ID from LogIt", conn);
                    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
                    adapter.DeleteCommand = cb.GetDeleteCommand();
                    adapter.Update(table);

    Now, it is not deleting any rows from SQl server and when i checked it generated a delete command as :

    Delete from LogIt where ID=@p1

    and the value of @p1 in SqlParameters collection was null. I double checked and "table" has 5 rows.

    Any guess ?

    Thanks
    Kulvinder Singh
    Friday, September 19, 2008 11:24 AM

Answers

  • The SqlCommandBuilder uses the SqlDataAdapter.SelectCommand to generate the Insert/Update/Delete commands. It will do a  "adapter.SelectCommand.Execute(CommandBehavior.SchemaOnly)" to retrieve the schema information from the database, but not the data.

     

    // in your example:  assigning the Insert/Update/Delete command is not necessary because

    // the SqlCommandBuilder will listen to the adapter.OnRowUpdating event and generate the

    // appropriate command if it hasn't already been generated.

    // adapter.DeleteCommand = cb.GetDeleteCommand();

     

     

    When using Update, the order of execution is as follows:

    1. The values in the DataRow are moved to the parameter values.

    2. The OnRowUpdating event is raised.

    3. The command executes.

    4. If the command is set to FirstReturnedRecord, then the first returned result is placed in the DataRow.

    5. If there are output parameters, they are placed in the DataRow.

    6. The OnRowUpdated event is raised.

    7. AcceptChanges is called.

    // this example will delete all the data.

    SqlDataAdapter adapter = new SQLDataAdapter("Select ID from LogIt", conn);
    int fillCount = adapter.Fill(table);

    foreach(DataRow row in table.Rows) { row.Delete(); }

    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);

    int updateCount = adapter.Update(table);

     

     

    Monday, September 22, 2008 3:50 PM
    Moderator

All replies

  • You haven't deleted any row in the code so no rows will be deleted.
    Friday, September 19, 2008 1:32 PM
  • I want to know that if i write the query as "Select * from LogItData", will this retrieve all the rows from table in DB or this is just a reference for the dataadapter to use it while calculating the delete or insert or update commands ?
    Monday, September 22, 2008 11:39 AM
  • The SqlCommandBuilder uses the SqlDataAdapter.SelectCommand to generate the Insert/Update/Delete commands. It will do a  "adapter.SelectCommand.Execute(CommandBehavior.SchemaOnly)" to retrieve the schema information from the database, but not the data.

     

    // in your example:  assigning the Insert/Update/Delete command is not necessary because

    // the SqlCommandBuilder will listen to the adapter.OnRowUpdating event and generate the

    // appropriate command if it hasn't already been generated.

    // adapter.DeleteCommand = cb.GetDeleteCommand();

     

     

    When using Update, the order of execution is as follows:

    1. The values in the DataRow are moved to the parameter values.

    2. The OnRowUpdating event is raised.

    3. The command executes.

    4. If the command is set to FirstReturnedRecord, then the first returned result is placed in the DataRow.

    5. If there are output parameters, they are placed in the DataRow.

    6. The OnRowUpdated event is raised.

    7. AcceptChanges is called.

    // this example will delete all the data.

    SqlDataAdapter adapter = new SQLDataAdapter("Select ID from LogIt", conn);
    int fillCount = adapter.Fill(table);

    foreach(DataRow row in table.Rows) { row.Delete(); }

    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);

    int updateCount = adapter.Update(table);

     

     

    Monday, September 22, 2008 3:50 PM
    Moderator