Reaching child records in a SQL Server table

Question

Out of my lack of SQL Server experience and taking into account that this task is a usual one for Line of Business applications, I'd like to ask, maybe there is a standard, common way of doing the following database operation:

Assume we have two tables, connected with each other by one-to-many relationship, for example SalesOderHeader and SalesOrderLines

Field SalesHeaderNo is a PK in SalesOderHeader table and a FK in SalesOrderLines table.

In a front-end app a User selects some number of records in the SalesOderHeader table, using for example Date range, or IsSelected field by clicking checkbox fields in a GridView. Then User performs some operations (let it be just "move to another table") on selected range of Sales Orders.

My question is:

How, in this case, I can reach child records in the SalesOrderLines table for performing the same operations (in our case "move to another table") over these child records in as easy, correct, fast and elegant way as possible?

Answer 1

I don't know about "Best Practices", but this is what I use:

 var header = db.SalesOrderHeaders.SingleOrDefault(h => h.SaleHeaderNo == 14);
 IEnumerable<SalesOrderLine> list = header.SalesOrderLines.AsEnumerable();

 // now your list contains the "many" records for the header
 foreach (SalesOrderLine line in list)
 {  
      // some code
 }

I tried to model it after your table design, but the names may be a little different.

Now whether this is the "best practices" way, I am not sure.

EDITED: Noticed that you want to update them all, possibly move to another table. Since LINQ-To-SQL can't do bulk inserts/updates, you would probably want to use T-SQL for that.

Answer 2

If you're okay with a T-SQL based solution (as opposed to C# / LINQ) - you could do something like this:

-- define a table to hold the primary keys of the selected master rows
DECLARE @MasterIDs TABLE (HeaderNo INT)

-- fill that table somehow, e.g. by passing in values from a C# apps or something

INSERT INTO dbo.NewTable(LineCodeNo, Item, Quantity, Price)
   SELECT SalesLineCodeNo, Item, Quantity, Price 
   FROM dbo.SalesOrderLine sol
   INNER JOIN @MasterIDs m ON m.HeaderNo = sol.SalesHeaderNo

With this, you can insert a whole set of rows from your child table into a new table based on a selection criteria.

Answer 3

Your question is still a bit vague to me in that I'm not exactly sure what would be entailed by "move to another table." Does that mean there is another table with the exact schema of both your sample tables?

However, here's stab at a solution. When a user commits on a SalesOrderHeader record, some operation will be performed that looks like:

Update SalesOrderHeader
Set....
Where SalesOrderHeaderNo = @SalesOrderHeaderNo

Or

Insert SomeOtherTable
Select ...
From SalesOrderHeader 
Where SalesOrderHeaderNo = @SalesOrderHeaderNo

In that same operation, is there a reason you can't also do something to the line items such as:

Insert SomeOtherTableItems
Select ...
From SalesOrderLineItems
Where SalesOrderHeaderNo = @SalesOrderHeaderNo