What Is The Optimum Way Of Getting Records From Database In Scenario That You Have To Pass Lists That Each Of Them Has More Than 2000 Parameters?

Any comments on this code pieces to make it more professional will be gladly accepted. This is why I opened this topic. But the main concern is this; I need to pass item ids, store

Solution 1:

Table valued parameters is the way to go if this is indeed the way you need to approach this topic.

• First, switch to a stored procedure since you're using SQL 2008 or newer.
• Second, read up on the using statement for disposing of your sql items.

Psuedo data layer:

public List<SalesList> ExecuteSales(List<string> items, int storeID, int W1, int W2, int vendorID, int retailerID)
{
    var sales = new List<SalesList>();
    var table = new DataTable();
    table.Columns.Add("ItemNumber");
    foreach (var item in items)
    {
        table.Rows.Add(item);
    }
    using (var connection = new SqlConnection("ConnectionString"))
    {
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "cp_ExecuteSales";
            command.Parameters.AddWithValue("@RetailerID", retailerID);
            command.Parameters.AddWithValue("@VendorID", vendorID);
            command.Parameters.AddWithValue("@StoreID", storeID);
            var tvp = new SqlParameter("@ItemIds", SqlDbType.Structured)
            {
                 TypeName = "tvpItems",
                 Value = table
            };
            command.Parameters.Add(tvp);
            using (var reader = command.ExecuteReader())
            {
                //DoWork
            }
        }
    }
    return sales;
}

Create the tvp:

CREATE TYPE [dbo].[tvpItems] AS TABLE(
[ItemNumber][int] NULL

)

Create the stored proc:

CREATEPROCEDURE cp_ExecuteSales
     @RetailerIDVARCHAR(50),
     @VendorIDVARCHAR(50),
     @StoreIDVARCHAR(50),
     @ItemIds tvpItems READONLY
ASSELECT  I.ITEM_NBR
          ,I.ITEM_DESC1
          ,I.ITEM_DESC2
          ,I.VENDOR_STK_NBR
          ,SUM(SA.POS_QTY) AS POS_QTY
          ,SUM(SA.POS_SALES) AS POS_SALES
  FROM  SALES_FTBL SA
        INNERJOIN ITEM_TBL I ON SA.RETAILER_ID = I.RETAILER_ID 
            AND SA.ITEM_NBR = I.ITEM_NBR
        INNERJOIN@ItemIds ID ON SA.ITEM_NBR = ID.ItemNumber
  WHERE SA.RETAILER_ID=I.RETAILER_ID
        AND SA.RETAILER_ID =@RetailerIDAND SA.VENDOR_NBR  =@VendorIDAND SA.STORE_NBR  =@StoreIDAND SA.ITEM_NBR=I.ITEM_NBR

If you need to add a second set of number parameters, then you can pass multiple parameters of different types to the database. In the past, we've created several generic types to support varying list of data types rather than having to manage a lot of table types.

CREATE TYPE [dbo].[IntList] AS TABLE(
    [Value][Int] NULL
)

Important things to remember:

• The parameter type for a tvp must be SqlDbType.Structured
• The TypeName for the parameter must match the Table Value Parameter type name.
• The Table Value Parameter parameter in the stored procedure must be declared as READONLY