LINQ to SQL How can I submit changes after closing the connection?

Question

I am accessing a database using LINQ, and making changes to the records. I retrieve the records like so:

using(var db = new DatabaseDataContext(connectionString))
{
    var query =
        from item in db.table
        where item.senddate == null
        select item;

    results = query.ToList();
}

After getting the items, I process each one, which requires communication with a web service, and can take more than a minute per record. When the record has been updated, I need to submit the changes, but I am not sure how to proceed. Currently, I do the following:

List<table> toProcess = QueryDb();  // Calls the previously displayed method

foreach (table item in toProcess)
{
     item.result = QueryDb(item.xmlrequest);
     PostToDb(item);
}

...

private bool PostToDb(table submit)
{
    submit.senddate = DateTime.Now;

    using (var db = new DatabaseDbDataContext(connectionString))
    {
        db.SubmitChanges();
    }

    return result;
}

db.SubmitChanges() does not submit any changes, but I haven't really set that the object "submit" holds the changes. How do I submit the changes made to the entry without keeping the Db connection open for several minutes while the files are processed one at a time?

Thank you in advance for any assistance.

PARTIALLY RESOLVED

I don't think this is the best solution, but I was able to get it to work with the following changes to my code:

private bool PostToDb(table submit)
{
    using (var db = new DatabaseDataContext(connectionString))
    {
        var query =
            from item in db.table
            where item.iprimaryid.Equals(submit.iprimaryid)
            select item;

        //  There can only be one result, but this was the only 
        //  way I could think to access it
        foreach (var item in query)
        {
            item.senddate = DateTime.Now;
            item.result = submit.result;
        }

        db.SubmitChanges();
    }

    return result;
}

score 3 · Accepted Answer · answered Jul 27 '12 at 17:57

3

You need to use the Attach method for your DataContext. So, your steps are:

1) Query object from the database using one instance of your DataContext
2) Make some changes
3) Create the new DataContext instance and attach modified entities.

using (var dataContext = new DataContext(ConnectionString))
{
     dataContext.TABLE_NAME.Attach(modifiedEntity);
     dataContext.SubmitChanges();
}

answered Jul 27 '12 at 17:57

Vitaly Slobodin

1,359
12
10

Thank you for your response Vitaly. I did try to do this initially, but there were still no changes. After doing some research, I found that changes had to be made after the Attach was added. Am I misunderstanding this? – Tim Jul 27 '12 at 18:06
1

You can try add the following line, before calling `SubmitChanges`: `dataContext.Refresh(RefreshMode.KeepCurrentValues, modifiedEntity);` This will "tell" to LINQ to SQL to check if there were any updates to object and to keep the new values. – Vitaly Slobodin Jul 27 '12 at 18:29
1

That fixed it. I had to have the Attach and then the Refresh, and the changes were submitted. Thank you again! – Tim Jul 27 '12 at 19:05
@VitalySlobodin has the proper answer! – petrosmm Jul 31 '19 at 14:05

score 0 · Answer 2 · answered Jul 27 '12 at 17:45

0

Can you declare your data context in an outer scope and pass it as a parameter to the methods that need to access the database? Something like this:

using (var db = new DatabaseDbDataContext(connectionString))
{
    var toProcess = QueryDb(db);

    // Do whatever processing you need to do...

    toProcess.ForEach(t => t.SendDate = DateTime.Now);

    db.SubmitChanges();
}

answered Jul 27 '12 at 17:45

Duane Theriot

2,135
1
13
16

Hello Duane; As I am understanding this, it would keep the connection open until all the files have been processed, which could take several minutes or longer depending on the number of entries. Did I misunderstand what you said? And thank you very much for your response. – Tim Jul 27 '12 at 17:49
Just because the context is in scope and the connection exists, doesn't mean that the connection is -open-. The connection is managed automatically -- it will only be open when you're retrieving your data and when your're updating your data, unless you're in a transaction. – Duane Theriot Jul 27 '12 at 18:38
I knew that the request was not made until it was actually needed, but I did not realize that the connection was closed the entire time unless communication was happening. I figured it was more like working with a file, where it must be closed manually or a hook is still left in it and I didn't want to leave a connection open. Thank you again for your help. – Tim Jul 27 '12 at 22:14

LINQ to SQL How can I submit changes after closing the connection?

PARTIALLY RESOLVED

2 Answers2