CODEWITHSUNDEEP
c#asp.netmysqldatabase-designlinq-to-sql
Vishal Torne
Vishal Torne

Reputation: 366

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

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...

Upvotes: 0

Views: 6073

Answers (3)

Mike C.
Mike C.

Reputation: 3114

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.

Upvotes: 1

LiaqatG
LiaqatG

Reputation: 367

If you look at a line from your own code,

data.Customers.InsertOnSubmit(NewCustomer);

This tells you the story that the InsertOnSubmit and InsertAllOnSubmit are invoked on a single table. I haven't tried this myself but you could work around and make your own method to do this for you.

Though as Pablo Lemurr has mentioned, you would be required to add the Customer record first and then Address record after as it references the foreign key.

Hope this helps.

Upvotes: 1

Lemur
Lemur

Reputation: 2665

It seems that you you have to insert a record into Customer Table first, and then into Customer_Addresses to make the db engine able to find the foreign key

Upvotes: 1

Related Questions