How to auto detect row version, if some one update manually
-
Hi guys, I am looking for the best ways to create an api which will monitor the database table row version. if this api found any row update manually (i.e., directly login to SQL server) than it will send an acknowledgement with the following information: (1) LogIn detail of the data server. (2) Access database name with datatime. (3) Affected table name, row version and row number as well. I will be glad for your any suggestion / ides on that.
Thanks Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
-
Hi guys, I am looking for the best ways to create an api which will monitor the database table row version. if this api found any row update manually (i.e., directly login to SQL server) than it will send an acknowledgement with the following information: (1) LogIn detail of the data server. (2) Access database name with datatime. (3) Affected table name, row version and row number as well. I will be glad for your any suggestion / ides on that.
Thanks Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
Md. Marufuzzaman wrote:
I will be glad for your any suggestion
I suggest you don't cross-post: ask your question once, in a single forum. You should know that by now. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Md. Marufuzzaman wrote:
I will be glad for your any suggestion
I suggest you don't cross-post: ask your question once, in a single forum. You should know that by now. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Hi, Yes, you are right, actually this can be done from database end or may C# application end as well, that's way I post. anyway; I sincerely apology & thanks for your response.
Thanks Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
-
Md. Marufuzzaman wrote:
I will be glad for your any suggestion
I suggest you don't cross-post: ask your question once, in a single forum. You should know that by now. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
Univote countered.
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
-
Univote countered.
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
:rose:
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Hi guys, I am looking for the best ways to create an api which will monitor the database table row version. if this api found any row update manually (i.e., directly login to SQL server) than it will send an acknowledgement with the following information: (1) LogIn detail of the data server. (2) Access database name with datatime. (3) Affected table name, row version and row number as well. I will be glad for your any suggestion / ides on that.
Thanks Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
Md. Marufuzzaman wrote:
I am looking for the best ways to create an api which will monitor the database table row version.
if this api found any row update manually (i.e., directly login to SQL server) than it will send an acknowledgement with the following information:
(1) LogIn detail of the data server.
(2) Access database name with datatime.
(3) Affected table name, row version and row number as well.I question the requirement. If you have a small application/database then it is pointless. If you have a large application/database (and volume) then any such notification system would be worthless because it would overwhelm all other work. Further one doesn't identify a row in the database by "row number". For example if there are three users in the database and I change the last name of one of them I would not send a notification for "row 2" but rather that the user "jdoe" last name has changed to "Smith". Conversely you might have this sort of requirement. 1. You are displaying a list of users 2. You want to update that list if anyone else changes it That requires monitoring one table, not all. And then the client, not the database, polls the database as some small, but not real small interval, to check for changes. However even those sort of requirements are often pointless because they are often the result of made up requirements rather than real business need. For example - the admin claims that they need the above functionality. Then one asks the admin exactly how many admins are going to be updating the user table on a minute by minute basis. The answer will often be that such updates only seldom and that they are down via a admin process/task. As such there is no possibility that other admins would even be impacted by such an update because only one admin would be doing it at a time. Another example - consumer order entry system where someone claims that the customer address might change. The question then is what sort of customer calls up on a different phone to update their address at the same time that they are placing an order on another line? Conversely for business customers what sort of business is going to update their address at the same time and use two different values (excluding legal disputes between competing partners which no technological solution is going to fix.)
-
Md. Marufuzzaman wrote:
I am looking for the best ways to create an api which will monitor the database table row version.
if this api found any row update manually (i.e., directly login to SQL server) than it will send an acknowledgement with the following information:
(1) LogIn detail of the data server.
(2) Access database name with datatime.
(3) Affected table name, row version and row number as well.I question the requirement. If you have a small application/database then it is pointless. If you have a large application/database (and volume) then any such notification system would be worthless because it would overwhelm all other work. Further one doesn't identify a row in the database by "row number". For example if there are three users in the database and I change the last name of one of them I would not send a notification for "row 2" but rather that the user "jdoe" last name has changed to "Smith". Conversely you might have this sort of requirement. 1. You are displaying a list of users 2. You want to update that list if anyone else changes it That requires monitoring one table, not all. And then the client, not the database, polls the database as some small, but not real small interval, to check for changes. However even those sort of requirements are often pointless because they are often the result of made up requirements rather than real business need. For example - the admin claims that they need the above functionality. Then one asks the admin exactly how many admins are going to be updating the user table on a minute by minute basis. The answer will often be that such updates only seldom and that they are down via a admin process/task. As such there is no possibility that other admins would even be impacted by such an update because only one admin would be doing it at a time. Another example - consumer order entry system where someone claims that the customer address might change. The question then is what sort of customer calls up on a different phone to update their address at the same time that they are placing an order on another line? Conversely for business customers what sort of business is going to update their address at the same time and use two different values (excluding legal disputes between competing partners which no technological solution is going to fix.)
It all really depends on the OPs real requirements and design. Polling is not a very elegant solution and will bog down your database server if you have more then a few clients. * are there multiple clients from outside your firewall? if so, its unlikely they'll even be able to connect to the database directly anyways * do you really need instant real time sync'ed up data between clients? * there *IS* a built in mechanism for *PUSH* called SqlDependency, but it needs your design to be one where only a single instance server piece is connecting to the DB and clients connect to the server piece and the server piece pushes out the notifications itself * is the client application display only, or will clients be updating data? if so, you need to decide on your write policy because that will effect design as well. Does last writer always win? Probably not. Lets say User A grabs a row which is A B C D. User B grabs the same row and gets A B C D. Now User A changes B -> E, so the row in the DB is A E C D. User B still has A B C D and changes D to F. Now should the database contain A E C D, A B C F or A E C F. In most scenarios, its going to be A E C F. Honestly, at first glance, from the OPs question, I suspect he is getting concurrency exceptions and is going about it the wrong way to fix them :).
-
It all really depends on the OPs real requirements and design. Polling is not a very elegant solution and will bog down your database server if you have more then a few clients. * are there multiple clients from outside your firewall? if so, its unlikely they'll even be able to connect to the database directly anyways * do you really need instant real time sync'ed up data between clients? * there *IS* a built in mechanism for *PUSH* called SqlDependency, but it needs your design to be one where only a single instance server piece is connecting to the DB and clients connect to the server piece and the server piece pushes out the notifications itself * is the client application display only, or will clients be updating data? if so, you need to decide on your write policy because that will effect design as well. Does last writer always win? Probably not. Lets say User A grabs a row which is A B C D. User B grabs the same row and gets A B C D. Now User A changes B -> E, so the row in the DB is A E C D. User B still has A B C D and changes D to F. Now should the database contain A E C D, A B C F or A E C F. In most scenarios, its going to be A E C F. Honestly, at first glance, from the OPs question, I suspect he is getting concurrency exceptions and is going about it the wrong way to fix them :).
SledgeHammer01 wrote:
It all really depends on the OPs real requirements and design. Polling is not a very elegant solution and will bog down your database server if you have more then a few clients.
First you can do that with several hundred clients an not significantly impact any modern database on a reasonable database server as long as your polling is reasonable. Second if such a solution is needed at all the other possibilities are push or pull (polling). And attempting to push from a database to many clients is not a good idea.
SledgeHammer01 wrote:
* are there multiple clients from outside your firewall? if so, its unlikely they'll even be able to connect to the database directly anyways
And of course with that description they wouldn't be able to do anything at all with the database and thus the entire question would be pointless, so we can suppose that that is not the situation.
SledgeHammer01 wrote:
Does last writer always win? Probably not.
For most business models that is in fact entirely appropriate that the last write wins because real (versus imagined) business scenarios almost always fit that model.
SledgeHammer01 wrote:
Lets say User A grabs a row which is A B C D. User B grabs the same row and gets A B C D....
Yes that is an excellent explanation of an imagined scenario. However most businesses do not work like that. As per my previous example a customer doesn't update a shipping address at the same time using two different addresses.
-
SledgeHammer01 wrote:
It all really depends on the OPs real requirements and design. Polling is not a very elegant solution and will bog down your database server if you have more then a few clients.
First you can do that with several hundred clients an not significantly impact any modern database on a reasonable database server as long as your polling is reasonable. Second if such a solution is needed at all the other possibilities are push or pull (polling). And attempting to push from a database to many clients is not a good idea.
SledgeHammer01 wrote:
* are there multiple clients from outside your firewall? if so, its unlikely they'll even be able to connect to the database directly anyways
And of course with that description they wouldn't be able to do anything at all with the database and thus the entire question would be pointless, so we can suppose that that is not the situation.
SledgeHammer01 wrote:
Does last writer always win? Probably not.
For most business models that is in fact entirely appropriate that the last write wins because real (versus imagined) business scenarios almost always fit that model.
SledgeHammer01 wrote:
Lets say User A grabs a row which is A B C D. User B grabs the same row and gets A B C D....
Yes that is an excellent explanation of an imagined scenario. However most businesses do not work like that. As per my previous example a customer doesn't update a shipping address at the same time using two different addresses.
jschell wrote:
First you can do that with several hundred clients an not significantly impact any modern database on a reasonable database server as long as your polling is reasonable.
If you are writing a POP3 client or something of that nature, a 10 minute poll is reasonable. If you are writing something like a stock ticker where real time updates are important, 10 minutes is certainly not reasonable. Stock tickers need to update at LEAST once per second. Still connecting directly to the database is generally a bad design.
jschell wrote:
Second if such a solution is needed at all the other possibilities are push or pull (polling). And attempting to push from a database to many clients is not a good idea.
I already said that pushing directly from the database is not a good idea :)
jschell wrote:
And of course with that description they wouldn't be able to do anything at all with the database and thus the entire question would be pointless, so we can suppose that that is not the situation.
Not at all. From the OPs question, it sounded like he is just randomly trying to fix problems as they come up without having a big picture design. Having your database accessible from outside the firewall is the worst idea ever. More so, in a REAL corporate environment, you can not connect directly to the database from the client because security settings should not allow your app access to create users. So what happens in the real world (even in internal corp apps) is that only the database team has permissions to create / modify users, so you only get one database user (with db_datawriter and db_datareader only) and you have a server piece that connects to the DB using that user and all the clients connect to the server piece over some random port using TCP/IP, .NET remoting, web services or some other method and your app has a users table that it manages itself.
jschell wrote:
For most business models that is in fact entirely appropriate that the last write wins because real (versus imagined) business scenarios almost always fit that model.
jschell wrote:
Yes that is an excellent explanation of an imagined scenario. However most businesses do not work like that. As per my previous example a customer
-
jschell wrote:
First you can do that with several hundred clients an not significantly impact any modern database on a reasonable database server as long as your polling is reasonable.
If you are writing a POP3 client or something of that nature, a 10 minute poll is reasonable. If you are writing something like a stock ticker where real time updates are important, 10 minutes is certainly not reasonable. Stock tickers need to update at LEAST once per second. Still connecting directly to the database is generally a bad design.
jschell wrote:
Second if such a solution is needed at all the other possibilities are push or pull (polling). And attempting to push from a database to many clients is not a good idea.
I already said that pushing directly from the database is not a good idea :)
jschell wrote:
And of course with that description they wouldn't be able to do anything at all with the database and thus the entire question would be pointless, so we can suppose that that is not the situation.
Not at all. From the OPs question, it sounded like he is just randomly trying to fix problems as they come up without having a big picture design. Having your database accessible from outside the firewall is the worst idea ever. More so, in a REAL corporate environment, you can not connect directly to the database from the client because security settings should not allow your app access to create users. So what happens in the real world (even in internal corp apps) is that only the database team has permissions to create / modify users, so you only get one database user (with db_datawriter and db_datareader only) and you have a server piece that connects to the DB using that user and all the clients connect to the server piece over some random port using TCP/IP, .NET remoting, web services or some other method and your app has a users table that it manages itself.
jschell wrote:
For most business models that is in fact entirely appropriate that the last write wins because real (versus imagined) business scenarios almost always fit that model.
jschell wrote:
Yes that is an excellent explanation of an imagined scenario. However most businesses do not work like that. As per my previous example a customer
SledgeHammer01 wrote:
10 minutes is certainly not reasonable. Stock tickers need to update at LEAST once per second. Still connecting directly to the database is generally a bad design.
First I didn't say it was an absolute. There are certain to be some apps that require such functionality. Most do not. Second I didn't say connecting to the database directly was a good idea.
SledgeHammer01 wrote:
I already said that pushing directly from the database is not a good idea
As I did as well.
SledgeHammer01 wrote:
Having your database accessible from outside the firewall is the worst idea ever
Which has nothing to do with what I said. Nor with what the OP said. But to be clear I agree with that. However the reasons for that has nothing to do with what the OP was asking.
SledgeHammer01 wrote:
Ever been to a doctors office recently? You know how they are all fancy and computerized now? So you get your examination or whatever and you leave the room. The doctor starts typing up notes about your appointment. At the same time you are talking to the receptionist to schedule your next appointment. Doc saves his notes. Ooops... now receptionist saves your new appointment date. You just stomped all over docs notes because receptionist is the last writer!!! OH NOOOO!!! you cut into his golf time!!! now he has to retype his notes and gets pissed at you!!
Excellent example. And guess what? I have in fact work on exactly that sort of app. I am guessing you haven't because your scenario, at best, indicates a horrific design. Patient records are not part of the scheduling system. The schedule points to a patient - it isn't part of the patient. And examine notes and/or patient notes would not update the entire patient record anyways. (Not to mention of course that a true receptionist is not even going to have access to the patient medical data - the exact place where the doctors notes go.)
SledgeHammer01 wrote:
You could of course "lock" the record while it is being edited,
No I prefer to create correct designs and use real business rules.
