CODEWITHSUNDEEP
c#sqlcommand
A.HADDAD
A.HADDAD

Reputation: 1906

How to pass list of guid as a parameter to a sql command

I need to filter a sql request by passing a list of id to , this is the command:

var command = "select Software.Name as SoftwareName,SoftwareType.Name as SoftwareType from AssetManagement.Asset as Software inner join AssetManagement.AssetType as SoftwareType on (SoftwareType.Id = Software.TypeId) where Software.Id  in (@P)";


 cmd.Parameters.AddWithValue("@P", authorizedSoftwaresId);

authorizedSoftwaresId is a list of string , containing data like :

"7D23968B-9005-47A9-9C37-0573629EECF9,F1982165-3F6D-4F35-A6AB-05FA116BA279" with that it returns to me just one row, I tried adding quotes foreach value but i got "converstion from string caractere to uniqueidentifier failed " exception

Upvotes: 0

Views: 1489

Answers (1)

Vic F
Vic F

Reputation: 1459

This is a pretty common problem and the answer might depend on your database engine and whether you're using ADO.Net, Dapper, etc.

I'll give you some hints from my own experience with this problem on both MS Sql Server and PostgreSQL.

  1. A lot of people think AddWithValue is a devil. You might consider Add instead.
  2. The IN (@P) in your SQL statement might be the source of your problem. Try the Any option instead. See Difference between IN and ANY operators in SQL ; I've had success with this change in similar situations.
  3. If all of your inputs are GUIDs, you might consider changing the type to a collection of GUIDs, although I don't think this is the fix, you have to try everything when you're stuck.
  4. If you have to, you can parse the string version of your collection and add the ticks (') around each value. This choice has consequences, like it may prevent you from using a parameter (@P), and instead construct the final SQL statement you desire (i.e., manually construct the entire WHERE clause through string manipulations and lose the parameter.)

Good luck.

Upvotes: 0

Related Questions