This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(163.2, 37%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EIG%3C/text%3E%3C/svg%3E)
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
If you want to perform a DELETE FROM ... WHERE IN with parameters for each key, see my dynamic where in repository.
I intentionally did not show a DELETE WHERE IN but that doesn't matter, the key is
DELETE FROM SomeTable WHERE SomeColumn in ({0})
Granted you need to add the library but it will keep data secure from tampering.
Normally I don't recommend using strings directly in queries as this is a SQL injection attack target but in your case you are in complete control over the string being injected so this should be OK
But SQL injection is only one of the issues with this pattern.
One issue is cache littering. That is, if this query runs frequently, with different lists, each command will gets it entry in the SQL Server cache. While this can be mitigated with "optimize for ad hoc workloads", I think it would be wrong as a developer to assume that this setting is in force. And it still creates a cache entry, if a smaller one. But admittedly, if this query runs only once a day, it's not a big issue.
Another possible issue is performance if these lists can be huge. A few hundred are not a problem, but if you send in a list of hundred thousand items, the parse and compile time can be severe.
There are some more alternatives.
One is indeed SqlBulkCopy, but then you need to create a table to copy the values to, and then use that table in the query. This can be a little bulky, if you pardon the pun.
You can also use a table-value parameter. You would need to create a table type for this. I have an article on my web site that shows some examples with table-valued parameters.
Yet other options are to package the parameters into XML or JSON and then shred the values in a temp table in the SQL code.
Tried Below Code but Not working....
private static void DeleteEntities(IEnumerable<string> idBatches, string queryTemplate, SqlConnection connection, SqlTransaction transaction)
{
var count = 0;
var command = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = queryTemplate,
Transaction = transaction as SqlTransaction
};
foreach (var idBatch in idBatches)
{
command.Parameters.AddWithValue($"{count}", idBatch);
++count;
}
command.ExecuteNonQuery();
}
Exception : Incorrect Syntax Near Zero
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.
What has to be changed in the Below Code..
private static void DeleteEntities(IEnumerable<string> idBatches, string queryTemplate, SqlConnection connection, SqlTransaction transaction)
{
var count = 0;
var command = new SqlCommand
{
Connection = connection,
CommandType = CommandType.Text,
CommandText = queryTemplate,
Transaction = transaction as SqlTransaction
};
foreach (var idBatch in idBatches)
{
command.Parameters.AddWithValue($"{count}", idBatch);
++count;
}
command.ExecuteNonQuery();
}
Below code is working....but Issue with SQL Injection,
private static void DeleteEntities(IEnumerable<string> idBatches, string queryTemplate, SqlConnection connection, SqlTransaction transaction)
{
foreach (var idBatch in idBatches)
{
var commandText = string.Format(queryTemplate, idBatch);
var command = new SqlCommand(commandText, connection, transaction);
command.ExecuteNonQuery();
}
}
Requirement : Need to Add command.Parameters.AddWithValue to move away from SQL Injection.
Nothing has to be changed in that piece of code. What you need to change is the QueryTemplate parameter. But it would makes sense to build it in the loop above to add the parameters rather than pass it from the outside.
I can take it from the Parameter and We can Rebuild ....let me know the Code changes.
When I said that nothing had to be changed, I was referring to the first piece of code. I open the thread before you added the working code with the warning for SQL injection, so I did not see that one first.
And, yes, the warning is correct. You should not inline parameter values in SQL strings. Ever. All sorts of problems, including SQL injection.
So work from the first piece of code, but change the contents of QueryTemplate to include the parameters.
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.
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