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.
Nope. Parameters are like SQL values in obeying first normal form, basically, there can only be one...
As you are probably aware, generating SQL strings is risky business: you leave yourself open to an SQL injection attack. As long as you're dealing with bona fide GUID's you should be fine, but otherwise you need to be sure to cleanse your input.
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?
Generally the way that you do this is to pass in a comma-separated list of values, and within your stored procedure, parse the list out and insert it into a temp table, which you can then use for joins. As of Sql Server 2005, this is standard practice for dealing with parameters that need to hold arrays.
Here's a good article on various ways to deal with this problem:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
But for Sql Server 2008, we finally get to pass table variables into procedures, by first defining the table as a custom type.
There is a good description of this (and more 2008 features) in this article:
You can use xml parameter type:
CREATE PROCEDURE SelectByIdList(@productIds xml) AS
DECLARE @Products TABLE (ID int)
INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)
SELECT * FROM
Products
INNER JOIN
@Products p
ON Products.ProductID = p.ID