Logging Database Row alteration

Question

I am using a strongly typed dataset in my C# application and I would like to be able to log any changes a user makes to a row. What is the best way to do this?

Answer 1

Do you want to be able to access the changes from your C# application? Or is this just for logging/auditing purposes? Is your strongly typed dataset an XSD dataset that maps to a SQL Server table?

Triggers on the SQL server

In a large ordering system I helped developed at CB Richard Ellis, we created extra tables to hold versioned data. Say you have a Request table. Create a Request_changes table. Then you set up triggers for INSERT and UPDATE like this:

CREATE TRIGGER [dbo].[tr_Request_update]
ON [dbo].[Request]
FOR UPDATE
AS
    SET NOCOUNT ON
    INSERT INTO dbo.Request_changes
    (
        --ID,      // taken care of by IDENTITY(1,1)
        --LogDate, // uses default value of GetDate()
        ChangeAction,
        SQLServerUser,

        RequestID,
        UserIDContact,
        etc
    )
    SELECT
        'UPDATE',
        system_user,

        -- Request fields
        RequestID,
        UserIDContact,
        etc
    FROM
        inserted

    SET NOCOUNT OFF
GO

create TRIGGER [dbo].[tr_Request_insert]
ON [dbo].[Request]
FOR INSERT
AS
    SET NOCOUNT ON
    INSERT INTO dbo.Request_changes
    (
        --ID,      // taken care of by IDENTITY(1,1)
        --LogDate, // uses default value of GetDate()
        ChangeAction,
        SQLServerUser,

        RequestID,
        UserIDContact,
        Title,
        etc
    )
    SELECT
        'INSERT',
        system_user,

        -- Request fields
        RequestID,
        UserIDContact,
        etc
    FROM
        inserted

    SET NOCOUNT OFF
GO

You don't necessarily have access to the _changes table (unless you map that to another XSD dataset), but it's great for logging and auditing and tracking down bugs and history.

Other options

Perhaps you have an XSD dataset that isn't backed by a persistent store such as SQL server, and you're just using in memory datasets. There are plenty of other design patterns and well known constructs to notify you when property values have changed.

Also, if you are using XSD data sets, is it because you are working on an existing code base or a legacy application? If you are doing new development, I would steer away from them. It's an old technology that MS is no longer actively developing. If you want to stay in the MS stack that ships with current versions of Visual Studio, your best bets would be Linq to Sql or Entity Framework, both of which should be easier to deal with, and are more flexible (for instance, I don't think XSD data sets handle null values very well, and they don't support .NET nullable types in the generated classes).