none
Deleting Rows from a Table using a DataTable RRS feed

  • Question

  • Hi Everyone

    I am trying to do something like this.

    I need to delete data from a Sql Server/Oracle Data Table.The users have the choice of deleting by a particular column (not neccessary Primary key but an external Id which is unique in our app e.g EMPLOYEE_ADDRESS Table has EMPLOYEE_ADDRESS_ID as PK but delete by EMPLOYEE_ADDRESS.EXTERNAL_ID_1). Hence i create the data table with only 1 column (EXTERNAL_ID_1) and that is the key of the data table. Also the data adapter Deletecommand is DELETE FROM EMPLOYEE_ADDRESS WHERE EXTERNAL_ID_1 = :EXTERNAL_ID_1

    When the data table has one row in it (say EXTERNAL_ID_1 = EXT_ID1 (Refer code below)  - the delete using the data adapter Update method goes through fine.

    More that one row (say EXT_ID1 and EXT_ID2) - no error and no delete

    A sample code is attached below. The behaviour is consistent in both Oracle/sqlServer. I am using .Net 4.0. Maybe i am doing something Crazy!!!!!!

     

    Thanks in advance - siddharth

     

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.Common;

    namespace TestDb
    {
        class Program
        {
            static void Main(string[] args)
            {
                List<string> strIds = new List<string>();
               
                //Two Ids does not work
                strIds.Add("EXT_ID1");
                strIds.Add("EXT_ID2");           
               
                //DbDelete dl = new DbDelete("Oracle.DataAccess.Client", "Data Source=AEDB009A;User Id=MI530DEV2_DLT;Password=MI530DEV2_DLT");
                DbDelete dl = new DbDelete("System.Data.SqlClient", "User Id=MI520DLT2;Password=MI520DLT2;Data Source=AEDB008","@");
                dl.Init();
                dl.DeleteRow(strIds);
                dl.Apply();
                dl.Dispose();
            }
        }

        public class DbDelete
        {
            private DbProviderFactory _fact;
            private DbConnection _conn;
            private DataTable _dt;
            private DbDataAdapter _adapter;
            private string _strPrepender = string.Empty;

            public DbDelete(string InvariantProvider,string ConnectionString,string Prepender)
            {
                _fact = DbProviderFactories.GetFactory(InvariantProvider);
                _conn = _fact.CreateConnection();
                _conn.ConnectionString = ConnectionString;
                _strPrepender = Prepender;
            }

            public void Init()
            {
                try
                {
                    _conn.Open();

                    //create the data table
                    _dt = new DataTable("EMPLOYEE_ADDRESS");
                    DbCommand command = _conn.CreateCommand();
                    command.CommandText = "SELECT EXTERNAL_ID_1 FROM EMPLOYEE_ADDRESS";
                    DbDataAdapter da = _fact.CreateDataAdapter();
                    da.SelectCommand = command;
                    da.FillSchema(_dt, SchemaType.Source);
                    DataColumn[] dcc = new DataColumn[1];
                    dcc[0] = _dt.Columns[0];
                    _dt.PrimaryKey = dcc;

                    //Initialize the data adapter for delete
                    _adapter = _fact.CreateDataAdapter();
                    DbCommand selCommand = _conn.CreateCommand();
                    selCommand.CommandText = @"SELECT EMPLOYEE_ADDRESS_ID,EMPLOYEE_ID,LINE_1_ADDRESS,LINE_2_ADDRESS,LINE_3_ADDRESS,CITY,STATE,ADDRESS_TYPE,POSTAL_AREA,POSTAL_AREA_EXTENSION,STATUS,STATUS_CHANGE_DATE,COUNTRY,UPDATE_EMPLOYEE_ID,EXTERNAL_ID_1,GLOBAL_EMPLOYEE_ADDRESS_ID FROM EMPLOYEE_ADDRESS";               
                    _adapter.SelectCommand = selCommand;
                    _adapter.ContinueUpdateOnError = true;
                    _adapter.AcceptChangesDuringUpdate = false;
                    _adapter.UpdateBatchSize = 100;
                    _adapter.DeleteCommand = _conn.CreateCommand();
                    _adapter.DeleteCommand.CommandText = string.Format("DELETE FROM EMPLOYEE_ADDRESS WHERE EXTERNAL_ID_1 = {0}EXTERNAL_ID_1",_strPrepender);
                    DbParameter extIdParameter = _fact.CreateParameter();
                    extIdParameter.ParameterName = string.Format("{0}EXTERNAL_ID_1",_strPrepender);
                    extIdParameter.Direction = ParameterDirection.Input;
                    extIdParameter.SourceColumn = "EXTERNAL_ID_1";
                    extIdParameter.DbType = DbType.String;
                    _adapter.DeleteCommand.Parameters.Add(extIdParameter);
                }
                catch (Exception ex)
                {
                }
            }

            public void Dispose()
            {
                if (_conn.State == ConnectionState.Open)
                    _conn.Close();
                _conn.Dispose();
            }

            public void DeleteRow(List<string> Ids)
            {
                foreach (string id in Ids)
                {
                    DataRow dr = _dt.NewRow();
                    dr["EXTERNAL_ID_1"] = id;
                    _dt.Rows.Add(dr);
                    dr.AcceptChanges();
                    dr.Delete();
                }
            }

            public void Apply()
            {
                DbTransaction t = _conn.BeginTransaction();
                _adapter.SelectCommand.Transaction = t;
                _adapter.DeleteCommand.Transaction = t;
                _adapter.Update(_dt);
                if (_dt.HasErrors)
                    t.Rollback();
                else
                    t.Commit();
            }
        }
    }

    Wednesday, December 1, 2010 6:40 PM

Answers

  • Hi Val

     

    Found the problem. The data adapter UpdatedRowSource property was not set correctly. Setting it to NONE - the code works absolutely fine.

     

    Thanks for your input

    Regards

    Siddharth

    Friday, December 3, 2010 8:10 PM

All replies

  • First of all, are you sure that you have no error? Your exception handling code catches exceptions and ignores them. Second, to be able to delete rows based on information from DataTable, those rows in DataTable should be marked as deleted. Then UPDATE method goes through all the deleted rows inside of DataTable and for each one marked as deleted it execute DeleteCommand. If nothing is deleted in DataTable or marked as deleted, it will not do anything.


    Val Mazur (MVP)

    http://www.xporttools.net

    Thursday, December 2, 2010 12:04 PM
    Moderator
  • Hi Val

    There are no exceptions during the Init. This is where i create the data adapter and set the property ContinueUpdateOnError = true.

    Also if you observe the Delete function i created the new row, added to the data table, change the state to Deleted and applied the called the Update method. The datatable.HasErrors is false so the transaction commited but nothing was deleted from the database.

    My bigger concern is - why does the code above work if i have 1 row in the datatable and does not when there are more than 1.

     

    Regards

    Siddharth

    Thursday, December 2, 2010 2:07 PM
  • Did you try to trace or debug the code to see if transaction is not rolled back inside of Apply method? Also, try to run SQL profiler to see what kind of SQL statements are sent to SQL server and check if those statements is what you would expect.


    Val Mazur (MVP)

    http://www.xporttools.net

    Friday, December 3, 2010 11:38 AM
    Moderator
  • Hi Val

     

    Found the problem. The data adapter UpdatedRowSource property was not set correctly. Setting it to NONE - the code works absolutely fine.

     

    Thanks for your input

    Regards

    Siddharth

    Friday, December 3, 2010 8:10 PM