Extending database factory class to use auditing class

1. I don't see any reason to wrap code in an exception and just re-throw it (that is essentially like not having the wrapper in the first place). Also I believe that just clutters the code so I would consider removing the exception wrapper altogether.

2. The public Update method just delegates to the Insert method. That seems a bit counter intuitive to me. Update to my mind would perform an operation on an existing object, while insert adds a new one. I would consider renaming Insert to Update and have it as a method overload.

3. On second thoughts the Insert isn't really an insert at all as it will depend on the Sql supplied. It would consider calling that ExecuteNonQuery to better indicate it's intent.

4. As @Malachi pointed out, I'm not sure of the factory implementation. To better abstract your db from the core DbProvider factories I might consider creating my own interface and hiding everything behind that. This would allow your DbRepository to have no concept of where/how connections are created either by using internal framework facilities or some other mechanism.

5. Does the class really need to be static? That limits what you can do in my opinion. I might consider writing an extension method to create a db if you don't want to continue new'ing it everywhere. Otherwise you could consider injecting the DbRepository class into the methods you need and only do it's construct in one place?

Here is an alternative code solution:

class DBData
{
    public DBData() { }
    public DBData(object newValue, object oldValue)
    {
        this.NewValue = newValue;
        this.OldValue = oldValue;
    }

    public int UserKey { get; set; }
    public object NewValue { get; set; }
    public object OldValue { get; set; }

    public string Query { get; set; }
    public SqlParameter[] Parameters { get; set; }
}

interface IDbRepository 
{
    void Update(DBData data);
}

class DbRepository : IDbRepository
{
    private readonly IDbFactory _dbFactory;

    public DbContext(IDbFactory dbFactory)
    {
        _dbFactory = dbFactory;
    }

    public void ExecuteNonQuery(DBData data)
    {
        Audit(data.UserKey, data.NewValue, data.OldValue);
        ExecuteNonQuery(data.Query, data.Parameters);
    }

    private void ExecuteNonQuery(string sql, SqlParameter[] parameters)
    {
        using (DbConnection connection = _dbFactory.CreateConnection())
        {
            connection.Open();

            using (DbCommand command = _dbFactory.CreateCommand(connection, parameters))
            {
                command.ExecuteNonQuery();
            }
        }
    }

    /// <summary>
    /// Compare two objects and log the properties which have changed to the database.
    /// </summary>
    /// <param name="userKey">id of logged in user</param>
    /// <param name="newObject">object with changes</param>
    /// <param name="oldObject">copy of object before changes</param>
    public void Audit(int userKey, object newObject, object oldObject)
    {
        ChangeLogger logger = new ChangeLogger(userKey, newObject, oldObject);

        // The name of the object should give a good indication of what the change was from
        // E.g. ScanSchedule is obviously a change to the scan schedule
        string className = newObject.GetType().Name; 

        logger.Audit();
        if (!logger.Success)
        {
            throw logger.Exception;
        }

        logger.Changes.ForEach(log => Log(log.ObjectId, log.ValueNew, log.ValueOld, log.Property, className, DateTime.Now)); 
    }

    // I might consider moving this method into it's own Logger class
    private void Log(long userKey, string valueNew, string valueOld, string property, string className, DateTime changeDate)
    {
        string insertLog
            = "INSERT INTO ChangeLog "
            + "(UserKey, ObjectName, Property, NewValue, PreviousValue, ChangeDate) "
            + "VALUES "
            + "(@UserKey, @ObjectName, @Property, @NewValue, @PreviousValue, @ChangeDate)";

        Insert(insertLog,
            new[]
            {
                new SqlParameter("@UserKey", userKey),
                new SqlParameter("@ObjectName", className),
                new SqlParameter("@Property", property),
                new SqlParameter("@NewValue", valueNew),
                new SqlParameter("@PreviousValue", valueOld),
                new SqlParameter("@ChangeDate", changeDate),
            });
    }
}

static class DbRepositoryProvider
{
    private static readonly string connectionString  = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    public static IDbRepository GetRepository()
    {
        var factory = new DbFactory(connectionString);

        return new DbRepository(factory);
    }
}

class DbFactory : IDbFactory
{
    private readonly string _connectionString;
    private static readonly DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");

    public DbFactory(string connectionString)
    {
        _connectionString = connectionString;
    }

    public DbConnection CreateConnection() 
    {
        var connection = factory.CreateConnection();
        connection.ConnectionString = _connectionString;

        return connection;
    }

    public DbCommand CreateCommand(DbConnection connection)
    {
        var  command = factory.CreateCommand();     
        command.Connection = connection;
        command.CommandType = CommandType.Text;
        command.CommandText = sql;

        return command;
    }

    public DbCommand CreateCommand(DbConnection connection, SqlParameter[] parameters)
    {
        var command = CreateCommand(connection);
        AddAnyParameters(command, parameters);

        return command;     
    }

    private void AddAnyParameters(DbCommand, SqlParameter[] parameters)
    {
        if (parameters == null) return;

        foreach (var parameter in parameters.Where(p => p != null))
        {
            command.Parameters.Add(parameter);
        }   
    }   
}

And it's usage from your example would look something like:

private static void UpdateScanSchedule(DBData data, ScanSchedule schedule)
{
    data.Query
        = "UPDATE ScanSchedules " 
        + "SET GroupID = @GroupID, ScheduleType =@ScheduleType, " 
        + "RunDays =@RunDays ,RunDate =@RunDate, Description = @Description, " 
        + "RunTime = @RunTime, Ranges = @Ranges , Devices = @Devices, Excluded = @Excluded " 
        + "WHERE ScanScheduleID = @ScanScheduleID";

    data.Parameters
        = new[]
        {
            new SqlParameter("@GroupID", schedule.GroupID),
            new SqlParameter("@ScheduleType", schedule.ScheduleType),
            new SqlParameter("@RunDays", schedule.RunDays),
            new SqlParameter("@RunDate", schedule.RunDate),
            new SqlParameter("@Description", schedule.Description),
            new SqlParameter("@RunTime", schedule.RunTime),
            new SqlParameter("@Ranges", schedule.Ranges),
            new SqlParameter("@Devices", schedule.Devices),
            new SqlParameter("@Excluded", schedule.Excluded),
            new SqlParameter("@ScanScheduleID", schedule.ScanScheduleID),
        };

    var db = DbRepositoryProvider.GetRepository();
    db.Update(data);
}

What you could do is create a class that will create a factory and take parameters when it is constructed so that you don't have to assign all the properties the way you are, you just give the new factory wrapper all the information and then call what you need built, with a simple method call from the Factory object you create.

Inside your LogAction you should not be creating a SQL string, you should be passing parameters to a stored procedure on your database instead.

You are not separating your concerns, your data insertion should be done by the database and the code should just be sending the information.

you are already creating parameters and setting up an execute for a query, why not just turn it into a stored procedure call?

You can only use Object initialization if you were creating a brand new DbConnection

using (DbConnection connection = new DbConnection() {ConnectionString = connectionString})
{
    ...
}

Here is something that seems a little out of place

using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString;

    .....
}

using (DbConnection connection = factory.CreateConnection()
          {ConnectionString = connectionString})
{
     ....
}

using (DbCommand command = factory.CreateCommand())
{
    command.Connection = connection;
    command.CommandType = CommandType.Text;
    command.CommandText = sql;
    .......
}

using (DbCommand command = factory.CreateCommand()
       { Connection = connection,
         CommandType = CommandType.Text,
         CommandText = sql }
{
     ....   
}