CODEWITHSUNDEEP
c#sqlsql-serverlinq-to-sqllist
Beaker
Beaker

Reputation: 2884

Adding items to multiple tables with linq to sql

Let's say I have two tables one called Customers and one called CustomerAddresses. Then lets say I have a Customer object which contains a property that is of the type List<Address>. This property holds a list of all the different shipping addresses a customer may have used, so it could contain 0 - n addresses in it (just one string with the whole address for simplicity sake). Now I have a collection of Customer objects which I want to load into these two tables with a linq to sql procedure. The Customers table has a column called CustomerID which is an identity column and should auto-increment. The CustomerAddresses table has a column called CustomerID (and a column called Address as well) which connects the customers to the address(es) in the table. I think my main difficulty here is figuring out how to add a List to the Tables and have the CustomerID columns match up.

Thanks in advance,

Bob

Upvotes: 0

Views: 2174

Answers (2)

Dan Dumitru
Dan Dumitru

Reputation: 5423

I think this is what you need. I named CustomerAddresses the property you have in your customer object and holds the list of addresses.

static void SaveCustomer(Customer customer)
{
    using(var dc = new TestDC())
    {
        dc.Customers.InsertOnSubmit(customer);
        dc.SubmitChanges();

        foreach (CustomerAddress address in customer.CustomerAddresses)
        {
            address.CustomerId = customer.Id;
            dc.CustomerAddresses.InsertOnSubmit(address);
            dc.SubmitChanges()
        }
    }
}

And yes, you should put a FK between these tables, although it's not compulsory.

Upvotes: 1

Eran Betzalel
Eran Betzalel

Reputation: 4193

You add the customer rows (InsertOnSumbit), then activate the SubmitChanges() function which updates the customer objects you inserted with a newly generated ID.

using(var dc = new TestDC())
{
    var cust = new Customer();

    cust.Name = "Jack";

    dc.InsertOnSubmit(cust);

    SubmitChanges();  //  cust.Id will be updated with a new generated value
}

BTW, You can surround this code with TransactionScope for transactional support.

Enjoy.

Upvotes: 2

Related Questions