C# – Insert data in two Different tables on InsertOnSubmit() Using Linq asp.net c#

asp.netcdatabase-designlinq-to-sqlMySQL

i am working on a project on online shopping with asp.net c# and Mysql. I am using Devart Linqconnect (LinqtoMysql). I have two tables in mysql customers and customer addreesses:

Customer Table
CustomerID Int
Customerhone varchar(20);
customer Password Varchar(20);
email varchar(20)
firstname char(50)
Lastname(50)
username varshar(50)

Customer_Addresses
Customer_address_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
Customer_ID INT NOT NULL,
address1 CHAR(250),
address2 CHAR(250),
city CHAR(20),
state CHAR(20),
pincode VARCHAR(20),
PRIMARY KEY(Customer_address_ID),
FOREIGN KEY (Customer_ID) REFERENCES customers(Customer_ID)

When i write this code on the registration of a customer using LINQ to mysql:

 using (ShoppingDataContext data = new ShoppingDataContext())
        {
            Customer NewCustomer = new Customer();
            CustomerAddress newaddress = new CustomerAddress();
            newaddress.CustomerID = NewCustomer.CustomerID;
            NewCustomer.CustomerFirstname = TextBoxFirstName.Text;
            NewCustomer.CustomerLastname = TextBoxLastname.Text;
            NewCustomer.CustomerEmail = TextBoxEmail.Text;
            NewCustomer.Username = TextBoxusername.Text;
            NewCustomer.CustomerPassword = TextBoxPassword.Text;
            newaddress.Address1 = TextBoxAddress1.Text;
            newaddress.Address2 = TextBoxAddress2.Text;
            newaddress.City = TextBoxCity.Text;
            newaddress.State = TextBoxState.Text;
            newaddress.Pincode = TextBoxPincode.Text;
            System.Web.Security.Membership.CreateUser(TextBoxusername.Text, TextBoxPassword.Text);
            data.Customers.InsertOnSubmit(NewCustomer);
            PanelRegister.Visible = false;
            ConfimPanel.Visible = true;

        }

Will this code work to insert the data to two tables. Please help.
Will the customer_address table be able to detect that the customer address entered is of the customer table according to the customerId being the foreign key..

one more thing i am using modal popup panel of ajax toolkit and added registration and and login tables in the panel..

My Registration Panel:
enter image description here

Thanks in advance…

Best Answer

I highly recommend you organize your code like this:

 using (ShoppingDataContext data = new ShoppingDataContext())
 {
     Customer newCustomer = new Customer()
     {
         CustomerFirstname = TextBoxFirstName.Text,
         CustomerLastname = TextBoxLastname.Text,
         CustomerEmail = TextBoxEmail.Text,
         Username = TextBoxusername.Text,
         CustomerPassword = TextBoxPassword.Text
     };

     //now I'd like to be proven wrong here, but I believe you need to insert
     //and submit at this point
     data.Customers.InsertOnSubmit(newCustomer);
     data.SubmitChanges();

     CustomerAddress newaddress = new CustomerAddress()
     {
          CustomerID = NewCustomer.CustomerID,
          Address1 = TextBoxAddress1.Text,
          Address2 = TextBoxAddress2.Text,
          City = TextBoxCity.Text,
          State = TextBoxState.Text,
          Pincode = TextBoxPincode.Text,
     };
     //add new address to your customer and save
     newCustomer.CustomerAddresses.Add(newAddress);
     //it has been a while since I used linq2sql so you may need one of these:
     //newCustomer.CustomerAddresses.InsertOnSubmit(newAddress);
     //newCustomer.CustomerAddresses.Attach(newAddress);
     //basically use intellisense to help you figure out the right one, you might
     //have some trial and error here
     data.SubmitChanges();

     System.Web.Security.Membership.CreateUser(TextBoxusername.Text, TextBoxPassword.Text);

     PanelRegister.Visible = false;
     ConfimPanel.Visible = true;

  }

Also, notice where the insert and db.SubmitChanges() was moved to. Now I'm not sure if you can do this in a single go with one SubmitChanges() or if you need two like shown. I'd be interested in you giving an update on that.

You can view a simple insert example here:

http://msdn.microsoft.com/en-us/library/bb386941.aspx

EDIT: You might want to wrap this all in a transactionscope so it succeeds or fails as a unit

using(TransactionScope scope = new TransactionScope())
{
  using(ShoppingDataContext data = new ShoppingDataContext())
  {
    //the rest of your code
  }
}

Just a thought.

Related Topic