Eg. can I write something like this code:
public void InactiveCustomers(IEnumerable<Guid> customerIDs)
{
//...
myAdoCommand.CommandText =
"UPDATE Customer SET Active = 0 WHERE CustomerID in (@CustomerIDs)";
myAdoCommand.Parameters["@CustomerIDs"].Value = customerIDs;
//...
}
The only way I know is to Join my IEnumerable and then use string concatenation to build my SQL string.
You cannot pass a list as a single SQl Parameter. You could string.Join(',') the GUIDS such as "0000-0000-0000-0000, 1111-1111-1111-1111" but this would be high on database overhead and sub-optimal really. And you have to pass the whole string as single concatenated dynamic statement, you can't add it as a parameter.
Question:
Where are you getting your list of ID's that represent inactive customers from?
My suggestion is to approach the problem a little differently. Move all that logic into the database, something like:
Create procedure usp_DeactivateCustomers
@inactive varchar(50) /*or whatever values are required to identify inactive customers*/
AS
UPDATE Customer SET c.Active = 0
FROM Customer c JOIN tableB b ON c.CustomerID = b.CustomerID
WHERE b.someField = @inactive
And call it as a stored procedure:
public void InactiveCustomers(string inactive)
{
//...
myAdoCommand.CommandText =
"usp_DeactivateCustomers";
myAdoCommand.Parameters["@inactive"].Value = inactive;
//...
}
If a list of GUID's exist in a database, why do I need to: find them; put them in a generic list; unwind the list into a CSV/XML/Table variable, just to present them back to the DB again ????? They're already there! Am I missing something?