How can use complex SQL select statement for SQL dependency

Question

0.00/5 (No votes)

See more:

hi i am using sqldependecy to get notify from sqlserver and use signalr for send alarm to some users that store in database

before that i sent alarm for all user and use simple select but now i want using complex select

What I have tried:

SELECT [CarrierFid], [pelacknumber], [Speed], [Timeoverspeed] FROM [dbo].[OverSpeedAlert]

I want to use the following select instead of the above select :

C#

var queries = new[]{ @"SELECT [CarrierFid], [pelacknumber], [Speed], [Timeoverspeed] FROM [dbo].[OverSpeedAlert] "+
                                     " WHERE visited = 0 AND  CONVERT(varchar(10), Timeoverspeed )= FORMAT(GETDATE(),'dd/MM/yyyy')  "+
                                      " and CarrierFid in ( "+
                                       " SELECT dbo.AlarmsCarrier.carrierFid "+
                                               " FROM dbo.AlarmsCarrier INNER JOIN  "+
                                                    " dbo.AlarmUsers ON dbo.AlarmsCarrier.AlarmCarrierid = dbo.AlarmUsers.AlarmCarrierFid INNER JOIN "+
                                                      " dbo.Users ON dbo.AlarmUsers.UserFid = dbo.Users.ID "+
                                                          " WHERE (dbo.Users.UserName = '"+ username +"'))" };

Posted 12-May-20 0:40am

mohamad_ali

Updated 12-May-20 1:46am

Add a Solution

Comments

Richard Deeming 12-May-20 7:06am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

mohamad_ali 12-May-20 7:22am

thnx, ok I will change it

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-05-12T01:18:00

Solution 1

That query cannot be used with a SqlDependency:

Creating a Query for Notification | Microsoft Docs[^]:
Query notifications are supported for SELECT statements that meet the following requirements:
…
The statement must not contain subqueries…
…
The statement must not use any nondeterministic functions…

Your In clause uses a subquery, and GETDATE is nondeterministic.

Posted 12-May-20 1:18am

Richard Deeming

Comments

mohamad_ali 12-May-20 7:25am

What solution do you suggest for this problem? i should send alarm to particular users and i should used inner join between some table

Richard Deeming 12-May-20 7:34am

Set up a notification on a simpler query - for example:

SELECT dbo.AlarmsCarrier.carrierFid, dbo.AlarmUsers.UserFid
FROM dbo.AlarmsCarrier INNER JOIN dbo.AlarmUsers 
ON dbo.AlarmsCarrier.AlarmCarrierid = dbo.AlarmUsers.AlarmCarrierFid

That will notify you when an alarm is added or changed.

You can then run your query to find the alarms that you need to send.

mohamad_ali 12-May-20 7:50am

i used getdate for display today's messages if i removed this condition After a few days, many messages will be displayed

Richard Deeming 12-May-20 7:51am

As I said, use the notification to tell you when the alarms change. Then use your original query to find the alarms which need to be displayed.

Maciej Los 12-May-20 7:51am

5ed!

mohamad_ali 12-May-20 8:00am

i've done this before ,but dosent fire sqldependency
i useed 2 query first one is simple for check notify and second one for display some message but dosent work on display real time alarm only show after refresh page

Richard Deeming 12-May-20 8:03am

The SQL dependency should fire when the data returned by the monitored SELECT statement changes.

If it's not firing, then there may be something wrong with your Service Broker setup.

If it's firing but not notifying the user, then there's something wrong with the code that sends the notifications.

In either case, you'll need to debug your code to find and diagnose the problem.

Richard Deeming 12-May-20 8:04am

Also note that the SqlDependency won't fire when an alarm is due. It will only fire when the data changes.

mohamad_ali 12-May-20 8:13am

ok,thnx alot for your guide,and for giving me your time