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

http://s43.radikal.ru/i100/1002/1d/c664780e92d5.jpg

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?

You have "linq-to-sql" in your tags - do you want a C# / LINQ solution, or are you looking for a T-SQL (SQL Server based) solution?? — marc_s, Feb 24 '10 at 21:32
@marc_s I use LINQ to SQL in my app, but I'm not sure if this tool would be appropriate for such a task. If yes, I'll use LINQ. But maybe with T-SQL it can be done in more standard and easy way. So, I just need your advise and help. Thanks! — rem, Feb 24 '10 at 21:38
yes, LINQ-to-SQL is great for the ORM part - mapping tables to objects, so you can easily work with a Customer, an Order etc. but it's less well suited for batch-oriented operations, like copying a whole bunch of orders from one table to another - for those things, I'd use straight T-SQL (either in a Stored Proc, or by using the ExecuteQuery method on the DataContext — marc_s, Feb 24 '10 at 21:41

score 2 · Accepted Answer · answered Feb 24 '10 at 21:31

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.

I made as you suggested and it works like a charm. This trick with additional PK holding table and InnerJoin is something I was looking for. Very easy, efficient and elegant way. Thank you very much! — rem, Feb 25 '10 at 08:00

Ryan Alford · Answer 2 · 2010-02-24T22:09:43.370

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.

score 1 · Answer 3 · answered Feb 24 '10 at 21:32

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

Reaching child records in a SQL Server table

3 Answers3