C# Nested Loop for inserting multiples into DB

Question

I have an application used for warehousing furniture. If the user enters a line with a qty > 1, I have a simple while loop where the line is broken out to be many unique lines inserted into a SQL db. I.e. Item chair Qty 3

Becomes

Item    |Qty|Unique Key
Chair   |1  |1234
Chair   |1  |1235
Chair   |1  |1236

--

 while (Qty >= 1)
        {

            //Go Get Next Num
            // GetNextNum("LN");
            int NextNum = GetNextNum("LN");  //Method to get next number from DB table
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            string connStr = ConfigurationManager.ConnectionStrings["FurnitureDB"].ConnectionString;
            conn.ConnectionString = connStr;
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            //SQL Parameter List here

            cmd.CommandText = "InsertSODtl";

            SqlDataReader datareader = cmd.ExecuteReader();
            Qty--;
        }

My challenge is, there are some items that have a multiplier, where one item will be in 3 pieces. In which case I append a number behind the unique key:

Item Table  -  Qty 1 - Unique Key
------------------------------------
Table      | 1       | 1234
Table      | 1       | 12341
Table      | 1       | 12342

So I need a nested while loop/for loop or some method to insert the appropriate number of pieces. So a table for example - there could be 2 tables, each with 3 pieces. So I need to

Item Table  -  Qty 1 - Unique Key
------------------------------------
Table      | 1       | 1234
Table      | 1       | 12341
Table      | 1       | 12342
Table      | 1       | 1235
Table      | 1       | 12351
Table      | 1       | 12352

I'm struggling to come up with the logic to do this correctly.

Thanks for your suggestions.

Answer 1

One option I would look at is doing this all from within the stored procedure to limit the queries you need to execute. This can be really easily achieved using any type of for loop in SQL. Now i have a basic table setup as below.

ItemTable

Item      | nvarchar(50)
Qty       | int
UniqueKey | int

ItemClassification

Item           | nvarchar(50) 
NumberOfPieces | int

Then a simple stored procedure such as:

CREATE PROCEDURE [dbo].[InsertSODtl]
    @item nvarchar(50),
    @uniqueKey int
AS 
BEGIN
    SET NOCOUNT ON
    DECLARE @pieces int = (SELECT TOP 1 NumberOfPieces FROM ItemClassification WHERE Item = @item)

    INSERT INTO ItemTable (Item, Quantity, UniqueKey) VALUES (@item, 1, @uniqueKey)

    IF @pieces IS NOT NULL AND @pieces > 1
        BEGIN
            DECLARE @count int = 1;
            WHILE @count < @pieces -- < assures we end -1 max piece count
                BEGIN 
                    INSERT INTO ItemTable VALUES (@item, 1, CAST(CAST(@uniqueKey as nvarchar(10)) + CAST(@count as nvarchar(5)) as int))
                    SET @count = @count + 1
                END
        END
END

Like I said it is pretty basic and probably not exactly how your tables look but the concept is there.

First query the amount of pieces in the ItemClassification table for the Item type (simple string).

Next insert the record into the ItemTable

Finally if @pieces is not null and @pieces > 1 then we run a loop from 1 to @pieces - 1 inserting back into the ItemTable appending the value to the unique key. Now we have to cast the @uniqueKey and @count as string (nvarchar(10)) as we need to concat the values and not add them together.

Again a pretty basic example but limits the required queries.

This also doesnt require a change to your code and can be done in one query.

Example Use:

EXEC InsertSODtl 'Table', 1234
EXEC InsertSODtl 'Table', 1235
EXEC InsertSODtl 'Chair', 1236

Results in: