SqlDependency. Can't realize. - C# Discussion Boards

31-Mar-13 0:05

I am working with MS SQL Server. I need to realize this:
When one client of db changes some record of the table, this changes must be sent to all clients that subscribed with SqlDependency.
But event onNotificationChange from SqlDependency brings me

OnNotificationChange(object caller,SqlNotificationEventArgs e)

caller thet in fact is object of SqlDependency class and contains

Id = "c333b019-ff09-4d62-9584-074b009d56ba;70e64cf4-465f-4e54-937b-024432e6b2ca"

,
and e that contains discribes changes in db like this:

SQL

info: insert
source: data
type: change

But i need to receive changed record directly. Because in my variant i need to exequte new query and then search for changes.
Many clients will be do much load to the server.

Eddy Vluggen31-Mar-13 0:31

31-Mar-13 0:31

Member 9955006 wrote:
But i need to receive changed record directly. Because in my variant i need to exequte new query and then search for changes.

Many clients will be do much load to the server.

Your client will have to update it's cached version of the record. Fetching the record again would indeed cause a lot of traffic, so the framework doesn't do that. I assume you have a DataTable in memory?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Jeka Developer1-Apr-13 2:43

1-Apr-13 2:43

Quote:
Your client will have to update it's cached version of the record. Fetching the record again would indeed cause a lot of traffic, so the framework doesn't do that. I assume you have a DataTable in memory?

i dont use dataTable.. But if it wold be work...
What you mean "to update it's cached version of the record"?
to update all the dataTable using Select query? Or there is another way?

Eddy Vluggen1-Apr-13 2:55

1-Apr-13 2:55

Jeka Developer wrote:
What you mean "to update it's cached version of the record"?

You have a record "somewhere" in memory, otherwise it'd be impossible to display it. Whether it be a list, a datatable or raw values, you'll have fetched the data once.

You'll need to update that information, and update the display. How that'll be done will depend on your architecture and the way you "load" data.

Jeka Developer wrote:
to update all the dataTable using Select query?

You already have the data, and the changes. Why would you want to select what you already have? That'd cause every client to load the table again, causing a lot of needless traffic.

Your best bet it to write a small prototype, and work from there.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Jeka Developer1-Apr-13 4:57

1-Apr-13 4:57

Eddy Vluggen wrote:
You'll need to update that information

okay, tell pls how i can to update my row(one row that was changed) in DataTable if i already have 10 rows here or 10k.
And how i would know which row from those 10 i need to update?
my SqlDependency onChange event only informs me when data was changed and type of those changes(insert, delete, update).

Eddy Vluggen1-Apr-13 11:41

1-Apr-13 11:41

Jeka Developer wrote:
okay, tell pls how i can to update my row(one row that was changed) in DataTable if i already have 10 rows here or 10k.

And how i would know which row from those 10 i need to update?

It was not designed for use in client applications, where hundreds or thousands of client computers would have SqlDependency objects set up for a single database server.

Source: MSDN[^]

You'd know by having a "changes" queue where each client post his/her changes. Fetching the "changes" only would keep traffic low. You could also execute your query and refetch 10k rows on every "HasChanges"-event, but if 100 clients go fetch the complete table every time someone updates a row - you'll be killing the server Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Jeka Developer2-Apr-13 10:24

2-Apr-13 10:24

Eddy Vluggen wrote:
you'll be killing the server

I already done that=)
I described not corectly my architecture. I have 3 WCF services, that are subscribed with SqlDependency, but each service have its own clients(hundreds, thousands...). So Sql Server have only 3 clients(wcf services) but changes occurs very often=)

Eddy Vluggen3-Apr-13 6:52

3-Apr-13 6:52

Jeka Developer wrote:
I already done that=)

Color me surprised. Lots of companies attempt to build a framework that "automagically" updates the screen if "something" in the database changes. Most of them fail.

If it's an administration-package, then it's simply the wrong design. If you're building a visualization, then it might be the start of a working prototype. Either way, you can expect a whole lot of work before this works well enough to release it to >1000 customers.

Jeka Developer wrote:
I have 3 WCF services, that are subscribed with SqlDependency, but each service have its own clients(hundreds, thousands...). So Sql Server have only 3 clients(wcf services) but changes occurs very often=)

Three dependencies, does that mean monitoring three tables?

From your previous post, I gather that there's a lot of records in each table. It'd be costing a lot of resources if every client had to fetch the changes; hence the suggestion to create another server-app that holds those changes in memory. That way the client retrieves his/her dataset once (regardless of how/where you cache it) and gets the changes on that set from the new server-app.

Important note; you can't abuse the SqlDependency to monitor all tables and expect it to update thousands of clients. It's not an "OnChange" event on the record-level, nor will anything similar be implemented anytime soon.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

jschell1-Apr-13 8:12

jschell

1-Apr-13 8:12

Member 9955006 wrote:
this changes must be sent to all clients that

I suggest you review the business requirement and business case that lead to this conclusion.

Such implementations are very complex and require a great deal of supporting business cases to deal with the alternatives.

On the other hand this sort of functionally is almost never realistic - there are no valid business cases that support it.

Jeka Developer2-Apr-13 10:26

Regarding class instance and memory allocation c#

2-Apr-13 10:26

jschell wrote:
I suggest you review the business requirement and business case that lead to this conclusion.

This is test task that i was given by employer Smile | :)

SNMP

Sahar.H30-Mar-13 22:05

Sahar.H

30-Mar-13 22:05

Dear Ramanan.T,
we are under graduate students,we need to use SNMP in our graduation project and we need more information about how to create MIB using c#,how to save it and the steps to create the connection between a manager and an agent..

we are looking to hear from you soon..
thank you

sahar.h

Re: SNMP

Pete O'Hanlon30-Mar-13 23:09

Pete O'Hanlon

30-Mar-13 23:09

Is this related to an article? If it is, you should post your question at the end of the article. It's unlikely that this person is going to happen across this question here.

I was brought up to respect my elders. I don't respect many people nowadays.

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Tridip Bhattacharjee30-Mar-13 20:33

Tridip Bhattacharjee

30-Mar-13 20:33

static void Main()
    {
        Employee e =new Employee();

    }

public class Employee
{
    static int counter=0;
    double mydata = 500;
    private string _FirstName;
    
    public string FirstName {
        get { return _FirstName; }
        set { _FirstName = value ; }
    } private string _LastName;
    
    public string LastName {
        get { return _LastName; }
        set { _LastName = value; }
    }
    
    public double CalculateValue() {

    double _local=10; mydata=mydata * _local; return mydata ;
    }

}

1) i just like to know when i will run my program then who call the static main() function

2) when employee class instance will be created then how memory will be allocated for this class?

3) where this variable will store in heap or stack ?

static int counter=0;
double mydata = 500;
private string _FirstName;

4) where _local variable will be store when calculatevalue will be called ?

5) the important things is where data is stored when we assign data to variable. in stack or heap ?

suppose i have variable called Name="Hello" or Salary=5000 where Hello & 5000 as value will be stored ? heap or stack ?

6) where static variable is stored declared in class?

7) static class load into memory when program just run even if i do not call or use them ?

i heard that memory is allocated when we create instance of non-static class. so in case of static class we can not create instance so how they load into memory. just automatically by CLR when program just invoke or when we use that static class first time ? please explian. thanks

tbhattacharjee

Re: Regarding class instance and memory allocation c#

Eddy Vluggen31-Mar-13 1:12

Re: Regarding class instance and memory allocation c#

31-Mar-13 1:12

Tridip Bhattacharjee wrote:
1) i just like to know when i will run my program then who call the static main() function

Windows. It loads the assembly, looks for an entrypoint. That your "main" method. To be more specific, the .NET runtime would do that.

Tridip Bhattacharjee wrote:
2) when employee class instance will be created then how memory will be allocated for this class?

Some memory will already have been allocated once the assembly is loaded. Once you create an object of that class, it'll need to allocate memory for those fields (not for the methods). Those fields are either valuetypes (full list of their sizes on MSDN[^]) or Reference types (pointers). Unless those point to an object, their memory-allocation will be limited to space for that pointer.

Tridip Bhattacharjee wrote:
3) where this variable will store in heap or stack ?

Being classmembers, heap.

Six important .NET concepts: Stack, heap, value types, reference types, boxing, and unboxing[^]

Tridip Bhattacharjee wrote:
4) where _local variable will be store when calculatevalue will be called ?

Stack, as all local declared value-types will be.

Tridip Bhattacharjee wrote:
5) the important things is where data is stored when we assign data to variable. in stack or heap ?

Someone is overstating the importance between stack and heap. I feel a truckload of .ToArray conversions coming up for "performance reasons". Valuetypes to the stack, pointers on the stack pointing to lots of bytes in the heap. That's for the executing method; not for every class loaded in the framework.

Tridip Bhattacharjee wrote:
Name="Hello" or Salary=5000 where Hello & 5000

Hello is a string and thus a reference type, salary an integer and a value-type. The first on the heap, second on the stack. Combining them like that would either result in an exception (C#), or a new string (VB) which would be a reference type again.

Tridip Bhattacharjee wrote:
6) where static variable is stored declared in class?

Heap, until it's used.

Tridip Bhattacharjee wrote:
7) static class load into memory when program just run even if i do not call or use them ?

Yes/no. Everything in your assembly will use memory, since it'll be added to the list of "known types loaded". No, it will not run it's static constructor nor allocate memory for it's members until it's first use, as is documented on MSDN.

Tridip Bhattacharjee wrote:
i heard that memory is allocated when we create instance of non-static class. so in case of static class we can not create instance so how they load into memory.

By calling it's constructor[^].

Now here are my questions; why did the interface disappear from this example? And why isn't there a reference to a struct? Would an object in a struct be considered a value-type or a reference-type? What about static structs? Smile | :)

Here's some reading;

C# Heap(ing) Vs Stack(ing) in .NET: Part I[^]
Links to parts 2, 3 and 4 at the bottom of the page. Good explanation with supporting graphics.
C# Programming Guide[^]
Second chapter is on the Main method and why it's there, third chapter on the basic datatypes.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Tridip Bhattacharjee1-Apr-13 4:29

Tridip Bhattacharjee

1-Apr-13 4:29

thanks for clear answer. i heard that all local variable store on stack. one more question is that if local variable is ref type declared in function and when function will be called then where local variable ref will be stored....in stack or heap. ?

two good question here u highlighted. those are
a) Would an object in a struct be considered a value-type or a reference-type?
b) What about static structs?
c) interface is contract so at all any memory is allocated for interface ?

if possible give answer for that two question.

tbhattacharjee

Re: Regarding class instance and memory allocation c#

Eddy Vluggen1-Apr-13 11:49