Need a query builder for Deleting specific Rows

Question

Need a query builder for BulkDeleting specific Rows by using SqlBulkCopy.

 List<Part> parts = new List<Part>();      
parts.Add(new Part() { PartName = "crank arm", PartId = 1234 });
parts.Add(new Part() { PartName = "chain ring", PartId = 1334 });
parts.Add(new Part() { PartName = "regular seat", PartId = 1434 });
parts.Add(new Part() { PartName = "banana seat", PartId = 1444 });
parts.Add(new Part() { PartName = "cassette", PartId = 1534 });
parts.Add(new Part() { PartName = "shift lever", PartId = 1634 });


 var DeleteQuery = new StringBuilder();
foreach (Part aPart in parts)
{
      DeleteQuery = "DELETE FROM tablename WHERE TestcolumnID IN aPart";
}

 using (var bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default, transaction as SqlTransaction))
                {
                    var command = new SqlCommand
                    {
                        Connection = destinationConnection,
                        CommandType = CommandType.Text,
                        CommandText = DeleteQuery,
                        Transaction = transaction as SqlTransaction
                    };
                    await command.ExecuteNonQueryAsync();
    }

Kindly let me know what has to changed here

Answer 1

Finally I got to know the mistake ..

   IEnumerable<string> ids = new List<string>();  
            var count2 = 0;  
            idList.ForEach(p =>  
            {  
                ids = ids.Append("@id" +  count2);  
                ++count2;  
            });  
  
              
            var DeleteQuery = $"DELETE FROM Recognitions WHERE RecognitionId IN ({string.Join(",", ids)})";  




 DELETE FROM Parts WHERE PartId IN (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,@p28,@p29,@p30,@p31,@p32,@p33,@p34,@p35,@p36,@p37,@p38,@p39,@p40,@p41,@p42,@p43,@p44,@p45,@p46,@p47,@p48,@p49) This is the Missing Part.  

Thanks for the Support : @Bruce (SqlWork.com) , @Erland Sommarskog ,@Karen Payne MVP ,@Naomi ,@Michael Taylor

Answer 2

Or keep it simple:

var command = new SqlCommand
{
   Connection = connection,
   CommandType = CommandType.Text,
   CommandText = @"DELETE Recognitions WHERE RecognitionID IN (SELECT convert(int, value) FROM string_split(@list, ',') WHERE len(value)> 0)"
   Transaction = transaction as SqlTransaction
};
string list = "";
foreach (var idBatch in idBatches)
{
   list += idBatch.ToString() + ",";
}
command.Parameters.Add("@list", SqlDbType.NVarChar, -1).Value = list;
command.ExecuteNonQuery();

You need SQL 2016 or later for string_split.

Answer 3

Did you try my code?

Source for code below and class project

If not, here is an example where I mocked up data with NuGet package Bogus.

public class BogusOperations  
{  
    public static List<Part> PartsList(int count = 50)  
    {  
        int id = 1456;  
        return new Faker<Part>()  
            .CustomInstantiator(f => new Part(id++))  
            .RuleFor(part =>  
                    part.PartName,  
                f => f.Commerce.Product())  
            .Generate(count);  
    }  
}  

In a separate class create the WHERE IN

public static (string actual, string exposed) DeleteOrUpdateExample(string commandText, List<int> identifiers)  
{  
  
    using var cn = new SqlConnection() { ConnectionString = GetSqlWhereDatabaseConnection() };  
    using var cmd = new SqlCommand() { Connection = cn };  
      
    cmd.CommandText = SqlWhereInParamBuilder.BuildInClause(commandText + " ({0})", "p", identifiers);  
  
  
    cmd.AddParamsToCommand("p", identifiers);  
  
    return (cmd.CommandText, cmd.ActualCommandText());  
  
}  

Usage

var partIdentifiers = BogusOperations.PartsList().Select(part => part.PartId).ToList();  
  
var (actual, exposed) = DataOperations.DeleteOrUpdateExample(  
    "DELETE FROM Parts WHERE PartId IN", partIdentifiers);  
  
Console.WriteLine(actual);  
Console.WriteLine(exposed);  

Results

DELETE FROM Parts WHERE PartId IN (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,@p28,@p29,@p30,@p31,@p32,@p33,@p34,@p35,@p36,@p37,@p38,@p39,@p40,@p41,@p42,@p43,@p44,@p45,@p46,@p47,@p48,@p49)  
DELETE FROM Parts WHERE PartId IN (1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,14570,14571,14572,14573,14574,14575,14576,14577,14578,14579,14580,14581,14582,14583,14584,14585,14586,14587,14588,14589,14590,14591,14592,14593,14594,14595,14596,14597,14598,14599,14600,14601,14602,14603,14604,14605,14606,14607,14608,14609)  

Answer 4

If I understand the problem, you have a list of Items you wish to delete. I would import the list into a table. From there, writing a delete script is straight forward.

Where does the list of items come from? If it is a file or a stream then you can use standard SQL tooling to import the data.

Answer 5

No, that code does not work. It seems to be cobbled from various pieces somewhat at random and with not too much coherence.

You add parameters to SQL command, but the SQL statement does not have any parameters. And furthermore, it is not legal T-SQL. Those braces are out of place. I can see that you took it from Karen's post, but I think she meant the braces to be placeholders for replacement in C#.

The text for QueryTemplate should read:

DELETE Recognitions WHERE RecognitionID IN (@0, @1, @2, ....)

and there should be at as many @n as there are elements in idBatches. In fact, the best would be to build the string as you add the parameters.

Beware that you cannot have more that 2100 parameters a time.