Question about history

[Rafael Tejera]

I would like to create an history module to my application..

I need some advice to use a duplicate database with who, when and where fields added to that new duplicate table, or create a table with a character field saying which fields have been affected and others fields with who, when and where.

What is the best way to keep an historical table of other table?

Any example or article that I can read.

Sincerely,
Rafael
I'm new using C#.NET. I have C# 2003 with MS SQL Server 2000.

[Ignacio Machin \( .NET/ C# MVP \)]

Hi Rafael,

If this is a question especific to SQL I think you will get a better answer in a SQL Server NG. I have never had the need of this but I think that using a trigger will be the key, not sure if you will be able to get the who/when part though.

You can set a trigger FOR INSERT/UPDATE and compare the new fields to the original ones and act accordingly.

--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Rafael Tejera" <ra**********@hotmail.comwrote in message news:%2***************@TK2MSFTNGP02.phx.gbl...
I would like to create an history module to my application..

I need some advice to use a duplicate database with who, when and where fields added to that new duplicate table, or create a table with a character field saying which fields have been affected and others fields with who, when and where.

What is the best way to keep an historical table of other table?

Any example or article that I can read.

Sincerely,
Rafael
I'm new using C#.NET. I have C# 2003 with MS SQL Server 2000.

[Earl]

If the historical information will be referred to on a daily basis, it is
probably best to set up your history tables within the same database. It is
also unlikely that you will need ALL of the fields from your main tables in
your history tables. Here is a simplification of how I do it: In one of my
tables, I have all of the details of a particular item and another table
contains the current cost information (why cost is separate is topic for
another discussion and not relevant to this one). In my history table, I
save the foreign key of the item table, but instead of an FK from the cost
table, I save the cost info at the time the entry was made along with an
entry date and an edit date.
"Rafael Tejera" <ra**********@hotmail.comwrote in message
news:%2***************@TK2MSFTNGP02.phx.gbl...
I would like to create an history module to my application..

I need some advice to use a duplicate database with who, when and where
fields added to that new duplicate table, or create a table with a character
field saying which fields have been affected and others fields with who,
when and where.

What is the best way to keep an historical table of other table?

Any example or article that I can read.

Sincerely,
Rafael
I'm new using C#.NET. I have C# 2003 with MS SQL Server 2000.

[Kevin Spencer]

I built some classes to do this, and it wasn't easy. But now I can have an
application that performs database operations that can be undone and redone.
Let me see if I can sketch it out for you:

I created 2 tables in a database: ChangeLog and ChageRows:

ChangeLog
ChangeID (identity)
UserName (nvarchar)
ChangeTime (DateTime)
TableName (nvarchar)
ChangeType (int)
RolledBack (bool)

ChangeRows
RowID (identity)
ChangeID (int - foreign key to changeLog)
RowIndex (int)
RowValues (nvarchar)

The ChangeType stores an enumerated value from an enumeration I created:

DataChangedEventType
Uninitialized = -1,
RowUpdated = 0,
RowAdded = 1,
RowDeleted = 2,
RowsUpdated = 3,
RowsAdded = 4,
RowsDeleted = 5,
OperationCancelled = 6

Next, I created several interfaces and generic classes:

IHistoryEntry - Interface for HistoryList member classes.
IHistoryList - Interface used by History Client
to access HistoryList members
IHistoryAction : IList - Provides non-Generic access
to HistoryActionList as IList

HistoryList<T : IEnumerable<T>, IEnumerable,
ICollection<T>, ICollection, IList<T>, IList, IHistoryList
where T: IHistoryEntry - Represents a History List of any data type.
HistoryActionList<T: List<T>, IHistoryAction
where T : IHistoryEntry - Undo or Redo List in a HistoryList.

Now, this is a bit more complex than you perhaps need, because I designed it
to be able to work with strongly-typed instances of any type of data. I also
created a ToolStrip Control with Undo and Redo buttons on it.

Basically, this is how it works. You create your custom HistoryEntry class,
using the IHistoryList interface, and then you wire up event handlers to
actions in your interface. Each database action is of one of the types in
the enumeration. When, for example, a row in a DataTable (this is what I
used this for initially) changes, I use the ItemArray of the row to get an
array of all the row values, which I store as a pipe-delimited string in the
RowValues column of the ChangeRows table. This records the current state of
the row. I then make the change, and record the change in the database. The
RowIndex is the current index of the changed row in the database. When
undoing, each action is undone in reverse order, ensuring the state
reverting back in its original form. Deleting a row works the same way. And
so on. Redoing works the same way. The state of the row is stored in the
History list prior to re-doing the last action, and then the action is
re-done. And of course, if you undo to a certain point, and then make a
change, the ReDo list is cleared.

The hardest part (all of it is hard, of course) is building the UI to work
with it. There are 2 History Lists, the Undo and the Redo History List, and
when you undo, the Undone item is moved from the Undo to the Redo History
List, and so on.

That's it in a nutshell. Any more and you'd have to pay me! ;-)

--
HTH,

Kevin Spencer
Microsoft MVP
Digital Carpenter

A man, a plan, a canal,
a palindrome that has gone to s**t.

"Rafael Tejera" <ra**********@hotmail.comwrote in message
news:%2***************@TK2MSFTNGP02.phx.gbl...
I would like to create an history module to my application..

I need some advice to use a duplicate database with who, when and where
fields added to that new duplicate table, or create a table with a character
field saying which fields have been affected and others fields with who,
when and where.

What is the best way to keep an historical table of other table?

Any example or article that I can read.

Sincerely,
Rafael
I'm new using C#.NET. I have C# 2003 with MS SQL Server 2000.