Myujth

I need help from someone who knows SQL, because that is a problem beyond my SQL skills. That are my tables :

CREATE TABLE [dbo].[Orders]
(
 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 customerName VARCHAR(30) NOT NULL,
 customerAddress VARCHAR(30) NOT NULL,
 submitDate DATE NOT NULL,
 realizationDate DATE,
 deliveryTypeId INT NOT NULL

 FOREIGN KEY(deliveryTypeId) REFERENCES deliveryTypes(id)
);

CREATE TABLE [dbo].[OrderItems]
(
 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 orderId INT NOT NULL,
 productId INT NOT NULL,
 productQuantity INT,

 FOREIGN KEY(orderId) REFERENCES Orders(id), 
 FOREIGN KEY(productId) REFERENCES Products(id)
);

I need to create a stored procedure that inserts to two tables in the database one to many related records (in fact that's really part of many-to-many bindings, but I need to add only to these two tables). In my application (.NET) I need to pass one record "Orders" and one or more records "OrderItems".

My problems are:

• If I insert "Order" I will not know what value to set in OrderItems -> orderId. I found the SCOPE_IDENTITY function, but I do not really know how to use it in my case.


• How do I insert OrderItems records in the table if I do not know how many of them will be? I only know that there can be one or more of them.


• I do not know how to call the procedure receiving many OrderItems from the application level and what arguments should I declare in the stored procedure

I really appreciate all the help, because I face a few hours with this problem and I really do not know how to do it.

EDIT : This is the code that I've come up with so far:

CREATE PROCEDURE spAddOrder
 (@customerName VARCHAR(30),
 @customerAddress VARCHAR(30),
 @submitDate DATE,
 @deliveryTypeId INT
 -- I don't know how to pass many OrderItems rows as argument
)
AS
BEGIN
 INSERT INTO Orders(customerName, customerAddress, submitDate, deliveryTypeId)
 VALUES (@customerName, @customerAddress, @submitDate, @deliveryTypeId)

 SELECT SCOPE_IDENTITY() AS orderId

 -- I don't know how to do it for unknown number of rows
 INSERT INTO OrderItems(orderId, productId, productQuantity)
 VALUES (@orderId, @productId, @productQuantity)
END;
GO

While this question might be considered as too broad and therefor off topic, it is well written and describe a problem a lot of inexperienced developers are facing - so I think it deserves an answer.

So let's break it down to its ingredients:

1. Q: How to get the value of the identity column back after an insert?
   A: SQL Server provides several ways to do that, the simplest would probably be scope_identity, but the best would be using the output clause.




2. Q: How to send multiple rows to a stored procedure?
   A: Use a table valued parameter.

Now let's see how exactly are we going to do this.

The first thing we need to do is create a user defined table type to be used for the table valued parameter - so:

CREATE TYPE [dbo].[Udt_OrderItems] AS TABLE
(
 productId int NOT NULL,
 productQuantity int,
);
GO

Then, we can create a stored procedure using this type as a table valued parameter.
We will also send the order details as scalar parameters to the stored procedure:

CREATE PROCEDURE stp_InsertOrderWithItems
(
 @customerName varchar(30),
 @customerAddress varchar(30),
 @submitDate date,
 @realizationDate date,
 @deliveryTypeId int,
 @orderItems dbo.Udt_OrderItems readonly -- Table valued parameters must be readonly
)
AS

 DECLARE @Ids AS TABLE (orderId int NOT NULL) -- for the output clause

 INSERT INTO dbo.Orders (customerName, customerAddress, submitDate, realizationDate, deliveryTypeId)
 OUTPUT inserted.Id INTO @Ids
 VALUES(@customerName, @customerAddress, @submitDate, @realizationDate, @deliveryTypeId)

 INSERT INTO dbo.OrderItems (orderId, productId, productQuantity)
 SELECT orderId, productId, productQuantity
 FROM @orderItems
 CROSS JOIN @Ids -- We only have one value in @Ids so cross join is safe
GO

As for the c# part, It depends how are you connecting to the database - I'll show an example of the basic ADO.Net version:

using(var con = new SqlConnection(connectionString))

 using(var cmd = new SqlCommand(con, "stp_InsertOrderWithItems"))
 
 var dt = new DataTable()
 dt.Columns.Add("productId", typeof(int));
 dt.Columns.Add("productQuantity", typeof(int));

 // populate data table here

 cmd.Parameters.Add("@customerName", SqlDbType.VarChar, 30).Value = customerName;
 // all the other scalar parameters here...
 cmd.Parameters.Add(@orderItems, SqlDbType.Structured).Value = dt;

 con.Open();
 cmd.ExecuteNonQuery();