Audit trail design

Question

Currently working on ASP.Net MVC 4 application. I have been asked to log all the changes by the user in the application. Its nothing but Audit Trail

Here is my controller code

 public ActionResult SaveBasicInfo(PatientBasicInfoViewModel basicInfoViewModel)
    {

        if (ModelState.IsValid)
        {
            var loggedInUserPersonId = ((User)Session["CurrentUser"]).PersonId;
            long tenantId = TenantContext.TenantId;
            var patient = Mapper.Map<PatientBasicInfoViewModel,
                                    Patient>(basicInfoViewModel);
            patient.TenantId = tenantId;

            if (patient.PatientId > 0)
            {
                patient.UpdatedBy = loggedInUserPersonId;
                patient.UpdatedDateTime = DateTime.Now;
                patientService.UpdatePatientDetails(patient);                  
                //Here need to do Audit Log 
                //Current User modified this fields values from `xx` to `yy` on               
            }
            else
            {
                patient.AddedBy = loggedInUserPersonId;
                patientService.AddPatient(patient);
               // Here need to Log, This user created this patient record on Date
            }


          return RedirectToAction("Details", new { id = patient.PatientId });
        }

        return RedirectToAction("Details");
    }

Could some one help me to design the table structure for this which is flexible for all the combination.

Also How do i get both the old and new values to be logged?

Also referred Ideas on database design for capturing audit trails, but didn't get :(

Alternative Solution: creating a shadow/history table for each table that needs audit and use database Trigger. explained here.

Problem with this is , how i know this column value was changed from this to that by this user? I need to show all the history by user and also for a record

Note: We are not using Entity Framework, we use simple stored procedure and ADO.Net and use POCO classes

Help me to choose the best one from below referred here

1. A Separate "History" Table for Each Table Being Audited

2. A Consolidated "History" Table for All Tables Whose Changes are Being Tracked

Answer 1

in Ado.NET

You can Use DataTable to Retrieve your data ,

You can get the complete change set of modified rows on a datatable using the following

 DataTable ChangedDataTable= yourdataTable.GetChanges(DataRowState.Modified);

then you can use the DataRowVersion to get the Original and Current Versions for each Modified Row

DataRowObject[0, DataRowVersion.Original] //by Column index
DataRowObject["ColumnName", DataRowVersion.Original] //by column name 

Answer 2

1- Override the SaveChanges Method in your Context Class

2-

     public override int SaveChanges()
        {
            var autoDetectChanges = Configuration.AutoDetectChangesEnabled;

            try
            {
                Configuration.AutoDetectChangesEnabled = false;
                ChangeTracker.DetectChanges();

                foreach (var entry in ChangeTracker.Entries().Where(e => e.State == EntityState.Added || e.State == EntityState.Modified))
                {
                   // Save entry
// here you can get the Original Values or Current Values
entry.CurrentValues 
entry.OriginalValues
                }

                ChangeTracker.DetectChanges();

                Configuration.ValidateOnSaveEnabled = false;

                return base.SaveChanges();
            }
            finally
            {
                Configuration.AutoDetectChangesEnabled = autoDetectChanges;
            }
        }