CODEWITHSUNDEEP
c#sql-serverwinformsvisual-studio-2013tableadapter
RAPTOR
RAPTOR

Reputation: 124

C# TableAdapter Fill/Get by Multiple IDs

I'm faced with a little problem. The situation is: I have a MSSQL table which contains IDs (int, identity, primarykey), and names (string). This table is huge, so i don't want to fill the entire dataset just for one LINQ-query. I have a search algorithm, which fills a List<int> with more than one ID.

I want to load the matching rows in a datagridview with the following code:

dataGridView1.DataSource = tbl_WorklistTableAdapter.GetDataByID(ids_here);

But i can't handle multiple IDs, just a single. The GetDataByID() code needs to be this (i think):

SELECT [ID]
      ,[NAME]
  FROM [DataBase].[dbo].[tbl_Namelist]
  WHERE ID IN (@searchterm)

However WHERE ID IN accepts comma-separated ints, like 1,2,3. But the @variable is just one int.

How should i convert string to ints?

Thank you, and sorry for bad eng. :)

Upvotes: 1

Views: 1131

Answers (2)

Caleb Mauer
Caleb Mauer

Reputation: 672

You can use the built-in function SPLIT_STRING function: 

SELECT [ID], [NAME]
FROM [DataBase].[dbo].[tbl_Namelist]
WHERE ID IN (SELECT VALUE FROM SPLIT_STRING(@searchterm,','))

This only works on Compatibility level 130 or greater.

If you are on an older version of SQL Server you can follow this answer, which defines an equivalent function: 

SELECT [ID], [NAME]
FROM [DataBase].[dbo].[tbl_Namelist]
WHERE ID IN (SELECT VALUE FROM fn_split_string(@searchterm,','))

Upvotes: 0

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

In that case you need to change in sql so you need to generate one temp table for comma separated id and apply condition in the your query.

Like:-

DECLARE @variables VARCHAR(200) = @searchterm -- Your Variable
CREATE TABLE #temp(ID NUMERIC) DECLARE @temp VARCHAR(200)
SET @temp = @variables WHILE LEN(@temp) > 0 BEGIN DECLARE @t1 VARCHAR(200)
IF   CHARINDEX(',',@temp) > 0 
 BEGIN
   SET @t1 = SUBSTRING(@temp,0,CHARINDEX(',',@temp))
   INSERT INTO #TEMP SELECT @t1
   SET @temp = SUBSTRING(@temp,CHARINDEX(',',@temp)+1,len(@temp)) 
 END 
 ELSE 
 BEGIN
   INSERT INTO #TEMP SELECT @temp
   SET @temp = '' 
   END 
END  
SELECT [ID]
  ,[NAME]
FROM [DataBase].[dbo].[tbl_Namelist]
WHERE ID IN (SELECT ID FROM #temp)

Upvotes: 1

Related Questions