Delete records from datatable where column value between...

[dand11]

I have a datatable where I need to delete records from it where the primary key is between say 1 and 10. How can I go about doing this efficiently?

 

[jmeckley]

there are a couple ways

using(IDbConnection connection = new SqlConnection())
{
foreach(DataRow row in table.Rows)
{
   int id = (int)row.["id"];
   if(id > 0 && id <= 10)
   {
      using(IDbCommand command = connection.CreateCommand())
      {
         command.CommandText = "delete from [table] where [id] = @id";
         IDbParameter parameter = command.CreateParameter("id");
         parameter.Value = id;
         command.Parameters.Add(parameter);
         command.ExecuteNonQuery();
      }
   }
}
}

or

using(IDbConnection connection = new SqlConnection())
using(IDbCommand command = connection.CreateCommand())
{
   command.CommandText = "delete from [table] where [id] between @floor and @ceiling";

   IDbParameter floor = command.CreateParameter("floor");
   floor.Value = 1;
   command.Parameters.Add(floor);

   IDbParameter ceiling = command.CreateParameter("ceiling");
   ceiling.Value = 10;
   command.Parameters.Add(ceiling);

   command.ExecuteNonQuery();
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[dand11]

Thanks but I don't want to delte it from the database just from the DataTable in the Dataset.

 

[dand11]

I was hoping that there may be a method or function sort of like:

MyDataSet.MyDataTable.ROWS.Delete(1,10)


Instead of having to loop thourgh them all.

 

[jmeckley]

oh, well then yes, looping is the way to go.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jmeckley]

you could also use a dataview to filter the datatable, and return a new datatable. but this messes with alot of strings. I would choose looping over this.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[dand11]

Thanks jmeckley. I am now getting an error with the following code: The error states "InvalidCastException - SearchForVehicle
Specified cast is not valid." The error happens at my declaration of the variable "id". Why am I getting this error?

 foreach (DataRow row in myDataTable.Rows)
            {
                

               int id = (int)row["RowNum"];

              
               oLog.EnterEventLog("After", System.Diagnostics.EventLogEntryType.FailureAudit);

               if (id < _StartRow && id > _EndRow)
               {
                   row.Delete();
               }
            }

 

[dand11]

Please disregard the following statement in the above code:

oLog.EnterEventLog("After", System.Diagnostics.EventLogEntryType.FailureAudit);

 

[dand11]

I was able to get by the cast error and have been able to delete the rows from the dataset but now I'm getting a DeletedRowInaccessableException error. Below is my code I'm using to delete rows from the datatable.

private void RemoveRowsForPaging (DataTable myDataTable)
        {
           

            AddRowNumColumn(myDataTable);


            foreach (DataRow row in myDataTable.Rows)
            {
                

               int id = (int)row["RowNum"];



               if (id < _StartRow || id > _EndRow)
               {
                   row.Delete();
             
               }
            }

        
        }


        private void AddRowNumColumn(DataTable myDataTable)
        {
            // Initialize DataColumn 
            DataColumn RowNum = new DataColumn();

            // Add First DataColumn 
            // AllowDBNull property 
            RowNum.AllowDBNull = false;

            // set AutoIncrement property to true 
            RowNum.AutoIncrement = true;

            // set AutoIncrementSeed property equal to 1 
            RowNum.AutoIncrementSeed = 1;

            // set AutoIncrementStep property equal to 1 
            RowNum.AutoIncrementStep = 1;

            // set ColumnName property to specify the column name 
            RowNum.ColumnName = "RowNum";

            // set DataType property of the column as Integer 
            RowNum.DataType = System.Type.GetType("System.Int32");


            // set Unique property of DataColumn to true to allow unqiue value for this column in each row 
            //RowNum.Unique = true;

           

            // Add and Create a first DataColumn 
            myDataTable.Columns.Add(RowNum);
            int i = 1;
            foreach (DataRow row in myDataTable.Rows)
            {
                row["RowNum"] = i;

                i++;
            }
           
            
        }

 

[dand11]

I was able to correct it. There was another method that needed to be called before I called these. Thx for everything.

 

[jmeckley]

your doing this the hard way.
1. you don't need to add a row, you have the row index
2. treat the table as an immutable object, and remove the rows.

public DataTable FilterTable(DataTable table, int floor, int ceiling)
{
   DataTable results = new DataTable();
   results.Schema = table.GetSchema(); //not 100% on this
   for(int i=0; i<table.Rows.Count; i++)
   {
      if(floor < id && id <= ceiling )
        results.AddRow(table.Rows[i].ItemArray);
   }
   return results;
}

you should try to do an much sever (database) filter as possible. if you're loading 1000's of records into memory only to display ~20 you will hit preformance problems.

Jason Meckley
Programmer
Specialty Bakers, Inc.