CODEWITHSUNDEEP
c#.netdataset
Mike
Mike

Reputation: 1295

How to fill .NET dataset using an array filter?

I would like to fill a .NET dataset using a filter of a criteria array. The dataset is too large to load and filter after loading. In C#, the fill would look something like:

List<int> customerIDs;
...
myAdapter.FillByCustomerIDs(customerIDs);

Which would generate the SQL clause

  WHERE CustomerID IN (x,x,x)

in which the x,x,x comes from the customerIDs array.

I cannot find any way to pass this type of filter to the TableAdapter Query Configuration Wizard.

Upvotes: 0

Views: 535

Answers (1)

Alex Filipovici
Alex Filipovici

Reputation: 32571

In SQL create a function (note that the @s parameter has a length of 1024, which you might want to increase):

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(1024))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT 
        CAST(SUBSTRING(@s, start, 
            CASE 
                WHEN stop > 0 THEN stop-start 
                ELSE 512 
            END) as int) AS s
    FROM Pieces
  )
  GO

and a stored procedure:

CREATE PROCEDURE [dbo].[GetCustomerIds]
    @ids nvarchar(max),
    @sep char(1)
AS
    SELECT * 
    FROM Customers 
    WHERE CustomerId in 
        (SELECT s FROM dbo.Split(@sep,@ids))
RETURN 0

In the TableAdapter Query Configuration Wizard, bind the FillByCustomerIds command to the above stored procedure. Now, you may have the following usage:

List<int> customerIDs = new List<int>() { 1, 2, 3, 4 };
myAdapter
    .FillByCustomerIds(dt, 
    customerIDs.Aggregate<int, string>("", 
        (s, i) => s + i.ToString() + ","), ",");

Upvotes: 1

Related Questions