SqlDependency, and how to avoid it.

Peter Denton — Mon, 15 Oct 2007 09:19:41 GMT

G'day

I've got a form with 3 devexpress grids, each exposes a strataframe BO (Device, Conversation & Arguments) where Device is the parent of Conversation which is the parent of Arguments. All the Parent child filtering works fine. I've just implemented a method to duplicate the selected Conversation and its child Agruments, providing a new name and new guids to the new rows, and FKs. My problem is that I get an InvalidOperationException as follows:

System.InvalidOperationException was unhandled by user code
Message="When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance."
Source="System.Data"
StackTrace:
       at System.Data.SqlClient.SqlDependency.GetDefaultComposedOptions(String server, String failoverServer, IdentityUserNamePair identityUser, String database)
       at System.Data.SqlClient.SqlCommand.CheckNotificationStateAndAutoEnlist()
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
       at MicroFour.StrataFrame.Data.DbDataSourceItem.GetDataTable(DbCommand Command, OnChangeEventHandler CallBack)
       at MicroFour.StrataFrame.Data.SqlDataSourceItem.GetDataTable(DbCommand Command, OnChangeEventHandler CallBack)
       at MicroFour.StrataFrame.Data.DbDataSourceItem.GetDataTable(QueryInformation QueryInfo, OnChangeEventHandler CallBack)
       at MicroFour.StrataFrame.Data.DataLayer.GetDataTable(QueryInformation QueryInfo, Boolean RegisterNotification)
       at MicroFour.StrataFrame.Business.BusinessLayer.FillOrGetByParentPrimaryKey(Object[] ParentPrimaryKeys, Boolean ReplaceCurrentDataTable)
       at MicroFour.StrataFrame.Business.BusinessLayer.FillByParentPrimaryKey(Guid ParentPrimaryKey)
       at TMSSystemTableMaint.frmDVC.btnDuplicate_Click(Object sender, EventArgs e) in C:\development\TMS Redevelopment\TMS\TMSSystemTableMaint\frmDVC.vb:line 314
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
       at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

This is occuring when trying to add the child record, on the line:

[codesnippet]' Clone all the child DCA
boDCACopy.FillByParentPrimaryKey(OldDCVGUID)[/codesnippet]

I've used the same block of code successfully in several other forms (with other BOs), in the same project and don't have this problem.

I believe that SQLDependancy is tied up with Notifications, and I did play around with notifications in another project in this solution, but I have since removed and turned it off. I've checked in SQL Server Management Studio and there are no Notifications.

Anyone with any ideas?

Peter

RE: SqlDependency, and how to avoid it.

StrataFrame Team — Mon, 15 Oct 2007 09:19:41 GMT

Aha, glad you got it working :)

RE: SqlDependency, and how to avoid it.

Peter Denton — Thu, 11 Oct 2007 19:18:29 GMT

Thanks Ben,

Worked like a charm.

I now realise that I created the BO I was having a problem with by copying the one that I was playing around with notifications on.

Peter

RE: SqlDependency, and how to avoid it.

StrataFrame Team — Thu, 11 Oct 2007 09:35:00 GMT

There is a property called ReceiveQueryNotifications on the business object and if it's set to False, it won't try to register the QueryNotification, thereby removing the SqlDependency creation. It defaults to False, but it probably got turned on somewhere, so give that a shot.

StrataFrame Forum » .NET Forums » General .NET Discussion » SqlDependency, and how to avoid it.

SqlDependency, and how to avoid it.

RE: SqlDependency, and how to avoid it.

RE: SqlDependency, and how to avoid it.

RE: SqlDependency, and how to avoid it.