Using C# to Select from SQL database Table

Question

Hello All-

I have a List of UserID's and a open connection to SQL Server. How can I loop through this List and Select matching UserID with First_Name and Last_Name columns? I assume the output can be in a datatable?

many thanks

Answer 1

It varies slightly depending on which type of SQL you're running, but this and this should get you started.

Answer 2

The most expedient way of doing this would be to:

1. Turn the List into a string containing a comma separated list of the userid values

2. Supply that CSV string into an IN clause, like:

   SELECT u.first_name,
          u.last_name
     FROM USER_TABLE u
    WHERE u.userid IN ([comma separated list of userids])
   

Otherwise, you could insert the values into a temp table and join to the users table:

SELECT u.first_name,
       u.last_name
  FROM USER_TABLE u
  JOIN #userlist ul ON ul.userid = u.userid

Answer 3

Write a function in your SQL database named ParseIntegerArray. This should convert a comma delimited string into a table of IDs, you can then join to this in your query. This also helps to avoid any SQL injection risk you could get from concatenating strings to build SQL. You can also use this function when working with LINQ to SQL or LINQ to Entities.

DECLARE @itemIds nvarchar(max)
SET itemIds = '1,2,3'

SELECT
    i.*
FROM
               dbo.Item AS i
    INNER JOIN dbo.ParseIntegerArray(@itemIds) AS id ON i.ItemId = id.Id

Answer 4

This article should help you: http://msdn.microsoft.com/en-us/library/aa496058%28SQL.80%29.aspx

I've used this in the past to create a stored procedure accepting a single comma delimited varchar parameter. My source from the C# program was a checked list box, and I built the comma delimited string using a foreach loop and a StringBuilder to do the concatenation. There might be better methods, depending on the number of items you have in your list though.

To come back to the SQL part, the fn_Split function discussed in the article, enables you to transform the comma delimited string back to a table variable that SQL Server can understand... and which you can query in your stored procedure.

Here is an example:

CREATE PROCEDURE GetSelectedItems
(
@SelectedItemsID  Varchar(MAX) -- comma-separated string containing the items to select
)
AS
SELECT * FROM Items
WHERE ItemID IN (SELECT Value FROM dbo.fn_Split(@SelectedItemsIDs,','))

RETURN
GO

Note that you could also use an inner join, instead of the IN() if you prefer.

If you don't have the fn_Split UDF on your SQL Server, you can find it here: http://odetocode.com/Articles/365.aspx

I hope this helps.