code_junkie

Is it possible to send a collection of ID's as a ADO.NET SQL parameter?

Nov 11th, 2011
110
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.04 KB | None | 0 0
  1. public void InactiveCustomers(IEnumerable<Guid> customerIDs)
  2. {
  3. //...
  4. myAdoCommand.CommandText =
  5. "UPDATE Customer SET Active = 0 WHERE CustomerID in (@CustomerIDs)";
  6. myAdoCommand.Parameters["@CustomerIDs"].Value = customerIDs;
  7. //...
  8. }
  9.  
  10. CREATE PROCEDURE SelectByIdList(@productIds xml) AS
  11.  
  12. DECLARE @Products TABLE (ID int)
  13.  
  14. INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
  15. FROM @productIds.nodes('/Products/id') as ParamValues(ID)
  16.  
  17. SELECT * FROM
  18. Products
  19. INNER JOIN
  20. @Products p
  21. ON Products.ProductID = p.ID
  22.  
  23. Create procedure usp_DeactivateCustomers
  24. @inactive varchar(50) /*or whatever values are required to identify inactive customers*/
  25. AS
  26. UPDATE Customer SET c.Active = 0
  27. FROM Customer c JOIN tableB b ON c.CustomerID = b.CustomerID
  28. WHERE b.someField = @inactive
  29.  
  30. public void InactiveCustomers(string inactive)
  31. {
  32. //...
  33. myAdoCommand.CommandText =
  34. "usp_DeactivateCustomers";
  35. myAdoCommand.Parameters["@inactive"].Value = inactive;
  36. //...
  37. }
Add Comment
Please, Sign In to add comment