|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
RECORD COMPARISONI need to rapidly compare an old record with it's newest version, in
order to general alerts. My code is in vb.net (.net 2.0). The record has many related tables; what is going to be the quickest way to compare the records? Almost every field will need to be compared, and also related records; deletion and additions. I'd just hate to do it by iterating every field of every record.... Thanks in advance! If you are using SQL2005, look into Event and Query Notifications.
Show quote "Bmack500" <brett.m***@gmail.com> wrote in message news:1171315998.061772.157400@v45g2000cwv.googlegroups.com... >I need to rapidly compare an old record with it's newest version, in > order to general alerts. My code is in vb.net (.net 2.0). > > The record has many related tables; what is going to be the quickest > way to compare the records? Almost every field will need to be > compared, and also related records; deletion and additions. > > I'd just hate to do it by iterating every field of every record.... > > Thanks in advance! > Have you already saved the changes to the database?
Robin S. ---------------------------------- Show quote "Bmack500" <brett.m***@gmail.com> wrote in message news:1171315998.061772.157400@v45g2000cwv.googlegroups.com... >I need to rapidly compare an old record with it's newest version, in > order to general alerts. My code is in vb.net (.net 2.0). > > The record has many related tables; what is going to be the quickest > way to compare the records? Almost every field will need to be > compared, and also related records; deletion and additions. > > I'd just hate to do it by iterating every field of every record.... > > Thanks in advance! > The original data is already saved to the database, but I could save
the new data to a second db or whatever it takes to do a valid comparison. It's sql server 2000 enterprise, not 2005 (unfortunately). Show quote On Feb 12, 9:49 pm, "RobinS" <Rob...@NoSpam.yah.none> wrote: > Have you already saved the changes to the database? > > Robin S. > ----------------------------------"Bmack500" <brett.m***@gmail.com> wrote in message > > news:1171315998.061772.157400@v45g2000cwv.googlegroups.com... > > >I need to rapidly compare an old record with it's newest version, in > > order to general alerts. My code is in vb.net (.net 2.0). > > > The record has many related tables; what is going to be the quickest > > way to compare the records? Almost every field will need to be > > compared, and also related records; deletion and additions. > > > I'd just hate to do it by iterating every field of every record.... > > > Thanks in advance! Well, here is where I was going. If you have a dataset and the users have
made changes to it, and you have *not* committed the changes, you can easily figure out what rows were changed, and what the changes were. So are you saying that you have *not* committed the (new) changes yet? Is your data in DataTables or DataSets (not that it really matters, I'm just asking). Robin S. Ts'i mahnu uterna ot twan ot geifur hingts uto. ----------------------------------------------- Show quote "Bmack500" <brett.m***@gmail.com> wrote in message news:1171372467.607538.275600@p10g2000cwp.googlegroups.com... > The original data is already saved to the database, but I could save > the new data to a second db or whatever it takes to do a valid > comparison. > It's sql server 2000 enterprise, not 2005 (unfortunately). > > On Feb 12, 9:49 pm, "RobinS" <Rob...@NoSpam.yah.none> wrote: >> Have you already saved the changes to the database? >> >> Robin S. >> ----------------------------------"Bmack500" <brett.m***@gmail.com> >> wrote in message >> >> news:1171315998.061772.157400@v45g2000cwv.googlegroups.com... >> >> >I need to rapidly compare an old record with it's newest version, in >> > order to general alerts. My code is in vb.net (.net 2.0). >> >> > The record has many related tables; what is going to be the quickest >> > way to compare the records? Almost every field will need to be >> > compared, and also related records; deletion and additions. >> >> > I'd just hate to do it by iterating every field of every record.... >> >> > Thanks in advance! > The changes are not committed; currently I'm just clearing the
database and writing fresh. It's a database I use to collect server information through WMI. I would just like to implement a change notification mechanism; i.e., a new local users is created, deleted; the duplex settings on a nic are changed; etc.... So I just need to compare the existing data with the new incoming data, and decide what to do from there. There will be no users making constant changes. Show quote On Feb 13, 1:34 pm, "RobinS" <Rob...@NoSpam.yah.none> wrote: > Well, here is where I was going. If you have a dataset and the users have > made changes to it, and you have *not* committed the changes, you can > easily figure out what rows were changed, and what the changes were. So are > you saying that you have *not* committed the (new) changes yet? > > Is your data in DataTables or DataSets (not that it really matters, I'm > just asking). > > Robin S. > Ts'i mahnu uterna ot twan ot geifur hingts uto. > -----------------------------------------------"Bmack500" <brett.m***@gmail.com> wrote in message > > news:1171372467.607538.275600@p10g2000cwp.googlegroups.com... > > > The original data is already saved to the database, but I could save > > the new data to a second db or whatever it takes to do a valid > > comparison. > > It's sql server 2000 enterprise, not 2005 (unfortunately). > > > On Feb 12, 9:49 pm, "RobinS" <Rob...@NoSpam.yah.none> wrote: > >> Have you already saved the changes to the database? > > >> Robin S. > >> ----------------------------------"Bmack500" <brett.m***@gmail.com> > >> wrote in message > > >>news:1171315998.061772.157400@v45g2000cwv.googlegroups.com... > > >> >I need to rapidly compare an old record with it's newest version, in > >> > order to general alerts. My code is in vb.net (.net 2.0). > > >> > The record has many related tables; what is going to be the quickest > >> > way to compare the records? Almost every field will need to be > >> > compared, and also related records; deletion and additions. > > >> > I'd just hate to do it by iterating every field of every record.... > > >> > Thanks in advance! If the changes have been made to your dataset but not pushed down into the
database yet, you can see the original and new values, as well as determining what rows are added and deleted, by examining the dataset. If you are comparing a current dataset against what is already stored in the database, you are going to have to write your own match/merge routine. It's unclear to me which way you are going. If you have a dataset and it has modifications made to it but not saved to the datasource, this is how you can determine what has been changed: First, there is a RowState property on each DataRow that is set to Unchanged, Detached, Added, Modified, or Deleted. To examine pending changes, you can use the DataRowVersion enumeration. The ones you are interested in are Current and Original. I haven't tested this, but I think it will work. 'If you set these before the loop, it doesn't have to ' search for them every time you touch a row; ' this improves performance considerably. Dim myTable as DataTable = myDataSet("Products") Dim CompanyNameCol as DataColumn = myTable.Columns("CompanyName") For each dr as DataRow in myTable.Rows debug.print("RowState = " & dr.RowState.ToString) debug.print("DataRowVersion.Current = {0}", _ dr(CompanyNameCol),DataRowVersion.Current)) debug.print("DataRowVersion.Original = {0}", _ dr(CompanyNameCol),DataRowVersion.Original)) Next dr For more info, check out Dave Sceppa's book, "ADO.Net: Core Reference", or these links: http://msdn2.microsoft.com/en-us/library/system.data.datarowview.aspx http://msdn2.microsoft.com/en-us/library/system.data.dataviewrowstate.aspx Good luck. Robin S. Ts'i mahnu uterna ot twan ot geifur hingts uto. ----------------------------------------------- Show quote "Bmack500" <brett.m***@gmail.com> wrote in message news:1171461463.276214.109600@p10g2000cwp.googlegroups.com... > The changes are not committed; currently I'm just clearing the > database and writing fresh. It's a database I use to collect server > information through WMI. > I would just like to implement a change notification mechanism; i.e., > a new local users is created, deleted; the duplex settings on a nic > are changed; etc.... > So I just need to compare the existing data with the new incoming > data, and decide what to do from there. There will be no users making > constant changes. > > On Feb 13, 1:34 pm, "RobinS" <Rob...@NoSpam.yah.none> wrote: >> Well, here is where I was going. If you have a dataset and the users >> have >> made changes to it, and you have *not* committed the changes, you can >> easily figure out what rows were changed, and what the changes were. So >> are >> you saying that you have *not* committed the (new) changes yet? >> >> Is your data in DataTables or DataSets (not that it really matters, I'm >> just asking). >> >> Robin S. >> Ts'i mahnu uterna ot twan ot geifur hingts uto. >> -----------------------------------------------"Bmack500" >> <brett.m***@gmail.com> wrote in message >> >> news:1171372467.607538.275600@p10g2000cwp.googlegroups.com... >> >> > The original data is already saved to the database, but I could save >> > the new data to a second db or whatever it takes to do a valid >> > comparison. >> > It's sql server 2000 enterprise, not 2005 (unfortunately). >> >> > On Feb 12, 9:49 pm, "RobinS" <Rob...@NoSpam.yah.none> wrote: >> >> Have you already saved the changes to the database? >> >> >> Robin S. >> >> ----------------------------------"Bmack500" <brett.m***@gmail.com> >> >> wrote in message >> >> >>news:1171315998.061772.157400@v45g2000cwv.googlegroups.com... >> >> >> >I need to rapidly compare an old record with it's newest version, in >> >> > order to general alerts. My code is in vb.net (.net 2.0). >> >> >> > The record has many related tables; what is going to be the >> >> > quickest >> >> > way to compare the records? Almost every field will need to be >> >> > compared, and also related records; deletion and additions. >> >> >> > I'd just hate to do it by iterating every field of every record.... >> >> >> > Thanks in advance! > >  |
|||||||||||||||||||||||
Other interesting topics