What is the optimum way of getting records from da

Question

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 ids, and start date and end date to get sale records. One of the problem is because of SQL parameter limit (2100), I cannot send more than 2100 parameters to the statement. Now I am trying to receive records store by store (store number is more than 4000 and also item amount). But I could not figure out a good way to do it.

Any one who spent time to help appreciated.

This is data access:

public List<SalesList> ExecuteSales(List<string> items, int storeID, int W1,int W2,int vendorID,int retailerID)
{
        SqlCommand command = new SqlCommand();

        string statement = "SELECT  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,ITEM_TBL I  "
                         + "WHERE SA.RETAILER_ID=I.RETAILER_ID "
                         + "AND SA.RETAILER_ID =   @RetailerID "
                         + "AND SA.VENDOR_NBR  =   @VendorID "
                         + "AND SA.STORE_NBR  =   @StoreID "
                         + "AND SA.ITEM_NBR=I.ITEM_NBR "
                         +"AND SA.ITEM_NBR  IN (";

        command.Parameters.AddWithValue("@RetailerID", retailerID);
        command.Parameters.AddWithValue("@VendorID",vendorID);
        command.Parameters.AddWithValue("@StoreID", storeID);   

        for (int i = 0; i < items.Count; i++)
        {
            if (i > 0)
            {
                statement += ", ";
            }
            string paramStr = "@itemNo" + i;
            statement += paramStr;
            command.Parameters.Add(paramStr, System.Data.SqlDbType.Int);
            command.Parameters[paramStr].Value = items[i];
        }
        statement += ") ";

        //statement += "AND STORE_NBR IN (";
        //for (int i = 0; i < stores.Count; i++)
        //{
        //    if (i > 0)
        //    {
        //        statement += ", ";
        //    }
        //    string paramStr = "@storeNo" + i;
        //    statement += paramStr;
        //    command.Parameters.Add(paramStr, System.Data.SqlDbType.Int);
        //    command.Parameters[paramStr].Value = stores[i];
        //}
        //statement += ") ";

        statement += "AND WEEK IN (";
        for (int i = 0; i <1; i++)
        {
            if (i > 0)
            {
                statement += ", ";
            }
            string paramStr = "@W" + i;
            statement += paramStr;
            command.Parameters.Add(paramStr, System.Data.SqlDbType.Int);
            command.Parameters[paramStr].Value = W1;
        }
         W1=W1+1;
        for (int counter=W1; counter < W2;counter++ )
        {
            if (counter > 0)
            {
                statement += ", ";
            }
            string paramStr = "@W" + counter;
            statement += paramStr;
            command.Parameters.Add(paramStr, System.Data.SqlDbType.Int);
            command.Parameters[paramStr].Value = W1++;
        }

        statement += ") ";

        statement += "GROUP BY I.ITEM_NBR,I.VENDOR_STK_NBR,I.ITEM_DESC1,I.ITEM_DESC2 Order By I.ITEM_DESC2 ";

        command.CommandText = statement;
        command.Connection = connection;

        List<SalesList> sales = new List<SalesList>();
        SalesList sale;
        string itemDescription;
        string desc1;
        string desc2;
        int count = 0;
        try
        {
            connection.Open();

            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                sale = new SalesList();

                sale.ItemNumber = Convert.ToInt32(reader["ITEM_NBR"].ToString().TrimEnd());

                if (reader["ITEM_DESC1"] != null)
                {
                    desc1 = reader["ITEM_DESC1"].ToString().TrimEnd();
                }
                else { desc1 = ""; }

                if (reader["ITEM_DESC2"] != null)
                {
                    desc2 = reader["ITEM_DESC2"].ToString().TrimEnd();
                }
                else { desc2 = ""; }

                if (!desc1.Equals(desc2) || !desc2.Equals(desc1))
                { itemDescription = desc1 + " " + desc2; }
                else { itemDescription = desc2; }

                sale.ItemDescription2 = itemDescription;

                sale.PosQuantity = Convert.ToInt32(reader["POS_QTY"].ToString().TrimEnd());
                sale.VendorStockNumber = reader["VENDOR_STK_NBR"].ToString().TrimEnd();


                if (reader["POS_SALES"].ToString() != "")
                {
                    sale.PosSales = Convert.ToDouble(reader["POS_SALES"].ToString().TrimEnd());
                }
                else { sale.PosSales = 0; }

                sales.Add(sale);
            }
        }
        catch (SqlException se)
        {
            Debug.WriteLine("---------- DEBUG INFORMATION ----------");
            Debug.WriteLine(se.Message);
            Debug.WriteLine("=======================================");
            throw se;
        }
        finally
        {
            connection.Close();
        }
        return sales;
    }

And this is business layer :

private List<SalesList> ExecuteSales(List<string> items, List<string> stores, string date1, string date2, int vendorID, int retailerID) {

        int W1 = CalculateWeek(date1);
        int W2 = CalculateWeek(date2);
        SalesListDL salesListDO = new SalesListDL();
        List<SalesList> sales = new List<SalesList>();
        List<SalesList> salesX = new List<SalesList>();

        for (int counter = 0; counter < stores.Count; counter++)
        {
                int storeID = Convert.ToInt32(stores[counter]);

                salesX = salesListDO.ExecuteSales(items, storeID, W1, W2, vendorID, retailerID);
                if (salesX.Count > 0)
                {
                    foreach (SalesList saleX in salesX.ToList())
                    {
                        int index = sales.FindIndex(item => item.ItemNumber == saleX.ItemNumber);
                        if (index > 0)
                        {
                            sales[index].PosQuantity = +saleX.PosQuantity;
                            sales[index].PosSales = +saleX.PosSales;
                            salesX.Remove(saleX);
                        }
                        else { sales.Add(saleX); }
                    }
                }
        }

        return sales;
    }

Answer 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:

CREATE PROCEDURE cp_ExecuteSales
     @RetailerID VARCHAR(50),
     @VendorID VARCHAR(50),
     @StoreID VARCHAR(50),
     @ItemIds tvpItems READONLY
AS
  SELECT  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
        INNER JOIN ITEM_TBL I ON SA.RETAILER_ID = I.RETAILER_ID 
            AND SA.ITEM_NBR = I.ITEM_NBR
        INNER JOIN @ItemIds ID ON SA.ITEM_NBR = ID.ItemNumber
  WHERE SA.RETAILER_ID=I.RETAILER_ID
        AND SA.RETAILER_ID = @RetailerID
        AND SA.VENDOR_NBR  = @VendorID
        AND SA.STORE_NBR  = @StoreID
        AND 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