• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1761
  • Last Modified:

LINQ to SQL insertonsubmit, multiple instances before submitchanges

My code constructed as below writes only 1 record to the database.  What am I doing wrong?

AllItemData is an object, the members of which are two lists.  This code only refers to the first list.  There are 6000+ items in the list, and the code does iterate through all 6000+.
private void Items_Click(object sender, EventArgs e)
        {
            AllItemData AllItemDatas = this.GetItemData();

            List<DataIn> DataIns = AllItemDatas.ItemData;
            dalDataContext context = new dalDataContext();
            itemTable i = new itemTable();
            foreach (DataIn d in DataIns)
            {
                try
                {
                    i.item_no = d.item_no;

                    context.itemTables.InsertOnSubmit(i);
                }
                catch (Exception ex)
                {
                    //TODO: do something with exeption
                }

            }

            try
            {
                context.SubmitChanges();
            }
            catch (Exception ex)
            {
                //TODO: do something with exception
            }

        }

Open in new window

0
g_johnson
Asked:
g_johnson
  • 2
  • 2
1 Solution
 
Bob LearnedCommented:
It looks like you are only creating one instance of the itemTable class, and then trying to repeatedly insert new records.

    itemTable i = new itemTable();

You will need to create a new instance for each iteration through the loop, so that the DataContext.InsertOnSubmit will mark that instance to insert.
0
 
g_johnsonAuthor Commented:
So, move these two lines inside the loop?

            context = new dalDataContext();
            itemTable i = new itemTable();
0
 
Bob LearnedCommented:
no, just the itemTable line.  You wouldn't want to create a new DataContext for each record.  You only need one instance of the DataContext.
0
 
MikeDotNet555Commented:
Don't move the context only the itemTable:
try
                {
                    itemTable i = new itemTable();
                    i.item_no = d.item_no;

                    context.itemTables.InsertOnSubmit(i);
                }
                catch (Exception ex)
                {
                    //TODO: do something with exeption
                }

Open in new window

0
 
g_johnsonAuthor Commented:
Thank you.  I get it.

Would you agree that one of the issues with this method is that you don't know about invalid data (e.g., 32 characters for a 30 character field) until you hit the submit?  And therefore, input must be controlled / validated?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now