SQL Server timeout exception when extending Linq partial methods

In .NET 4.0 and Linq to SQL, I am trying to use a partial class to "trigger" changes from within an update method (an existing DBML method). For simplicity, imagine a table Things with columns Id and Value

The auto gen DBML contains a method OnValueChanged, I'll extend that and as an exercise try to change one value in one other row :

   public partial class Things
    {
        partial void OnValueChanged()
        {
            MyAppDataContext dc = new MyAppDataContext();
            var q = from o in dc.GetTable<Things>() where o.Id == 13 select o;
            foreach (Things o in q)
            {
                o.Value = "1";  // try to change some other row
            }
            try
            {
                dc.SubmitChanges();
            }
            catch (Exception)
            {
                // SQL timeout occurs 
            }
        }
    }

A SQL timeout error occurs. I suspect that the datacontext is getting confused trying to SubmitChanges() before the current OnValueChanged() method has disposed of it's datacontext, but I am not sure.

Mostly I cannot find an example of a good pattern for triggering updates against a DB within an existing DBML generated method.

Can anyone provide any pointers on why this doesn't work and how I can accomplish something that works OK? (I realize I can trigger in the SQL databa开发者_Go百科se, but do not want to take that route.)

Thanks!

First, you aren't disposing of the DataContext at all in your function. Wrap it in a using statement.

The actual issue is coming from the fact that you're recursively calling yourself by setting the Value property on the retrieved values. You're just running into the timeout before you can hit a StackOverflowException.

It's unclear what you're trying to do here; if you're trying to allow different behavior between when you set the Value property here versus anywhere else, then it's simple enough to use a flag. In your partial class, declare an internal instance boolean auto property called UpdatingValue, and set it to true on each item inside your foreach block before you update the value, then set it to false after you update the value. Then, as the first line in OnValueChanged, check to ensure that UpdatingValue is false.

Like this:

public partial class Things
{
     internal bool UpdatingValue { get; set; }

     partial void OnValueChanged()
     {
         if (UpdatingValue) return;

         using(MyAppDataContext dc = new MyAppDataContext())
         {
             var q = from o in dc.GetTable<Things>() where o.Id == 13 select o;
             foreach (Things o in q)
             {
                 o.UpdatingValue = true;
                 o.Value = "1";  // try to change some other row
                 o.UpdatingValue = false;
             }

             dc.SubmitChanges();
         }
     }
 }

I would suspect that you may have introduced infinite recursion by changing the values of Things in the OnValueChanged event handler of Things.

To me, a cleaner solution to your problem is not to generate your class in a DBML file, but instead use LinqToSql attributes on a class you create. By doing so you can do your "trigger" modifications in the setters of your properties/columns.

I had a similar issue. I don't think it is a bug in your code, I'm leaning toward a bug in how the SqlDependency works. I did the same this as you, but I incrementally tested it. If the select statement return 1-100 rows, then it worked fine. If the select statement returned 1000 rows, then I would get the SqlException (timeout).

It is not a stack overflow issue (at least not in this client code). Putting a break point at the OnValueChanged event handler reveals that it does not get called again while the SubmitChanges call is hanging.

It is possible that there is a requirement that the OnValueChanged call must return before you can call SubmitChanges. Maybe calling SubmitChanges on a different thread might help.

My solution was to wrap the code in a big try/catch block to catch the SqlException. If it happens, then I perform the same query, but I don't use an SqlDependency and don't attach it to the command. This does not hang the SubmitChanges call anymore. Then right after that, I recreate the SqlDependency and then make the query again, to reregister the dependency.

This is not ideal, but at least it will process all the rows eventually. The problem only occurs if there are a lot of rows to be selected, and if the program is working smoothly, this should not happen as it is constantly catching up.

    public Constructor(string connString, CogTrkDBLog logWriter0)
    {
        connectionString = connString;
        logWriter = logWriter0;

        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT is_broker_enabled FROM sys.databases WHERE name = 'cogtrk'", conn))
            {
                bool r = (bool) cmd.ExecuteScalar();
                if (!r)
                {
                    throw new Exception("is_broker_enabled was false");
                }
            }
        }
        if (!CanRequestNotifications())
        {
            throw new Exception("Not enough permission to run");
        }


        // Remove any existing dependency connection, then create a new one.
        SqlDependency.Stop(connectionString);
        SqlDependency.Start(connectionString);

        if (connection == null)
        {
            connection = new SqlConnection(connectionString);
            connection.Open();
        }

        if (command == null)
        {
            command = new SqlCommand(GetSQL(), connection);
        }

        GetData(false);
        GetData(true);
    }


    private string GetSQL()
    {
        return "SELECT id, command, state, value " +
        " FROM dbo.commandqueue WHERE state = 0 ORDER BY id";

    }
    void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        // Remove the handler, since it is only good
        // for a single notification.
        SqlDependency dependency = (SqlDependency)sender;
        dependency.OnChange -= dependency_OnChange;

        GetData(true);
    }

    void GetData(bool withDependency)
    {
        lock (this)
        {
            bool repeat = false;
            do {
                repeat = false;
                try
                {
                    GetDataRetry(withDependency);
                }
                catch (SqlException)
                {
                    if (withDependency) {
                        GetDataRetry(false);
                        repeat = true;
                    }
                }
            } while (repeat);
        }
    }

    private void GetDataRetry(bool withDependency)
    {
        // Make sure the command object does not already have
        // a notification object associated with it.
        command.Notification = null;

        // Create and bind the SqlDependency object
        // to the command object.

        if (withDependency)
        {
            SqlDependency dependency = new SqlDependency(command);
            dependency.OnChange += dependency_OnChange;
        }


        Console.WriteLine("Getting a batch of commands");
        // Execute the command.
        using (SqlDataReader reader = command.ExecuteReader())
        {
            using (CommandQueueDb db = new CommandQueueDb(connectionString))
            {
                foreach (CommandEntry c in db.Translate<CommandEntry>(reader))
                {
                    Console.WriteLine("id:" + c.id);
                    c.state = 1;
                    db.SubmitChanges();
                }
            }
        }
    }