Sql dependency

Question

Sql dependency

Nr 1

Hello friends, I want to do SQL Dependency capability in a database file without SQL management the default file is in (c:\data.mdf)
I run the service broker in the file, unfortunately I apply a record in the database, it will not be updated in another system, please help

Olaf Helper 47,436 Reputation points

2021-10-18T06:14:42.65+00:00

I don't understand a single word of your post, may can you explain it more detailed, please?
Nr 1 Reputation point

2021-10-18T09:16:34.187+00:00

Thank you. Unfortunately, I use translate google. The words may not be translated correctly.
I enable the broker service query on the database file
I also put SqlDependency code in the program. Unfortunately, when I set a new record in system number 1, for example, in system number 2, no changes are applied.
Also keep in mind that I am not using sql server, I only have one mdf file in the path (c:\data.mdf)
Dan Guzman 9,401 Reputation points

2021-10-18T10:21:34.697+00:00

Add your SqlDependency application code and query to your question.
Nr 1 Reputation point

2021-10-18T13:05:27.667+00:00

Thank you
Dan Guzman 9,401 Reputation points

2021-10-18T21:44:55.71+00:00

Also, please include the de_OnChange method, as code (formatted with the code sample button) rather than image.
Nr 1 Reputation point

2021-10-19T00:25:04.68+00:00

Thanks ; Image edited
Jack J Jun 25,296 Reputation points

2021-10-19T07:32:45.197+00:00

@Nr , I want to know if system1 has the same Data Source with the system2. We need to use the correct data source based the on correspond system.
Nr 1 Reputation point

2021-10-19T13:15:31.187+00:00

Hello, yes, the database is in a shared folder
Jack J Jun 25,296 Reputation points

2021-10-20T02:42:31.853+00:00

@Nr , I mean that we need to ensure that the two systems have the same sql server Instance. For example, not all computer have the same instance like (LocalDB)MSSQLLocalDB, so we need to check it.

Does the second system also install sql server or sql server components?
Nr 1 Reputation point

2021-10-20T03:03:30.097+00:00

When it does not display the executable file as 2 windows that I run in one system.
Nr 1 Reputation point

2021-10-20T03:11:05.443+00:00

Isn't the display and execution of dependency due to the fact that the database is not in SQL Management? Or is it not due to the lack of database attachment to SQL Management?
Jack J Jun 25,296 Reputation points

2021-10-20T06:05:57.753+00:00

@Nr ,
->Isn't the display and execution of dependency due to the fact that the database is not in SQL Management?
As usual, If you install the related SQL Management, you can connect to the database successfully. Now can you connect to the database in system2?
-> Or is it not due to the lack of database attachment to SQL Management?
We often use SQL server management or Visual studio components to connect to database. You can have a check.

1 answer

Your answer

Olaf Helper 47,436 Reputation points

2021-10-18T06:14:42.65+00:00

I don't understand a single word of your post, may can you explain it more detailed, please?
Nr 1 Reputation point

2021-10-18T09:16:34.187+00:00

Thank you. Unfortunately, I use translate google. The words may not be translated correctly.
I enable the broker service query on the database file
I also put SqlDependency code in the program. Unfortunately, when I set a new record in system number 1, for example, in system number 2, no changes are applied.
Also keep in mind that I am not using sql server, I only have one mdf file in the path (c:\data.mdf)
Dan Guzman 9,401 Reputation points

2021-10-18T10:21:34.697+00:00

Add your SqlDependency application code and query to your question.
Nr 1 Reputation point

2021-10-18T13:05:27.667+00:00

Thank you
Dan Guzman 9,401 Reputation points

2021-10-18T21:44:55.71+00:00

Also, please include the de_OnChange method, as code (formatted with the code sample button) rather than image.
Nr 1 Reputation point

2021-10-19T00:25:04.68+00:00

Thanks ; Image edited
Jack J Jun 25,296 Reputation points

2021-10-19T07:32:45.197+00:00

@Nr , I want to know if system1 has the same Data Source with the system2. We need to use the correct data source based the on correspond system.
Nr 1 Reputation point

2021-10-19T13:15:31.187+00:00

Hello, yes, the database is in a shared folder
Jack J Jun 25,296 Reputation points

2021-10-20T02:42:31.853+00:00

@Nr , I mean that we need to ensure that the two systems have the same sql server Instance. For example, not all computer have the same instance like (LocalDB)MSSQLLocalDB, so we need to check it.

Does the second system also install sql server or sql server components?
Nr 1 Reputation point

2021-10-20T03:03:30.097+00:00

When it does not display the executable file as 2 windows that I run in one system.
Nr 1 Reputation point

2021-10-20T03:11:05.443+00:00

Isn't the display and execution of dependency due to the fact that the database is not in SQL Management? Or is it not due to the lack of database attachment to SQL Management?
Jack J Jun 25,296 Reputation points

2021-10-20T06:05:57.753+00:00

@Nr ,
->Isn't the display and execution of dependency due to the fact that the database is not in SQL Management?
As usual, If you install the related SQL Management, you can connect to the database successfully. Now can you connect to the database in system2?
-> Or is it not due to the lack of database attachment to SQL Management?
We often use SQL server management or Visual studio components to connect to database. You can have a check.

Answer 1

Dan Guzman 9,401

I see a couple of issues with the code in your image. First, need to (re)load data in the Frm_OnNewHome method. The current method does nothing after the notification so the bound data source will remain unchanged. Also, a SqlDependency stops automatically after it fires so you need to start it after each OnChange.

Below is the full working code from a test on my machine with the Save button implementation. I ran 2 instances of the application on the same box and SqlDependency.OnChange was fired whenever the Save button was clicked on either form. I added code for the Save button, which I didn't see in your code as well as error checking for a notification subscription error.

Nr 1 Reputation point

2021-10-20T10:41:20.113+00:00

Unfortunately not
Dan Guzman 9,401 Reputation points

2021-10-20T11:41:13.327+00:00
Note that Service Broker is initially disabled when the database is attached, even though you previously enabled in Server Explorer. Include this code in your form constructor before SqlDependency.Start(). Together with the other changes I recommended, the OnChange event will be fired.

using (var connection = new SqlConnection(connstr)) using (var cmd = new SqlCommand(@"IF(SELECT is_broker_enabled FROM sys.databases WHERE name = N'C:\TEMP\DATA.MDF') = 0 ALTER DATABASE[C:\TEMP\DATA.MDF] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;", connection)) { connection.Open(); cmd.ExecuteNonQuery(); }
Nr 1 Reputation point

2021-10-22T06:04:47.677+00:00

Failed
Dan Guzman 9,401 Reputation points

2021-10-22T14:26:44.797+00:00

@Nr , failed in what way? Did you get an error with ALTER DATABASE? The login you executing the command will of course need permissions.
Nr 1 Reputation point

2021-10-22T14:53:28.3+00:00

Runs and has no errors. But, by changing the tables, the datatable will not run in concurrent programs.
Dan Guzman 9,401 Reputation points

2021-10-23T11:37:35.977+00:00

@Nr , I'm not sure what is different in your code so I updated my answer with the complete code that works for me. Please review. Unfortunately, the anti-malware filters prevent me from posting the code as text instead of image.

Share via

Sql dependency

1 answer

Your answer