Posts


This is a simple method of history tracking of database changes that in done within in the application. There are other methods to do this including creating triggers within the database itself.

Basically this is one small function that you pass in a dataset and transaction. It loops through each table and each row and column in the table and detects the current state of the row and records it in an audit table.

First lets take a look at the audit table. The audit table tracks the tablename, field that was changed, the original and new value of the changed column, the action taken (insert, modified, delete), the user that did the action and the date. The tablename and code (primary key) field can be used to search the history of a row. Also included is orig_binary and new_binary for storing values for binary columns instead of original and new.

SQL (SQLite)

CREATE TABLE [audit] (
[id] INTEGER  PRIMARY KEY,
[tablename] VARCHAR(50)  NOT NULL,
[field] NVARCHAR(50)  NOT NULL,
[original] NVARCHAR(4000)  NOT NULL,
[new] NVARCHAR(4000)  NOT NULL,
[action] NVARCHAR(10)  NOT NULL,
[user] NVARCHAR(50)  NOT NULL,
[date] DATE  NOT NULL,
[code] INTEGER  NOT NULL,
[orig_binary] BLOB  NULL,
[new_binary] BLOB  NULL
);


CREATE TABLE [actor] (
[id] INTEGER  PRIMARY KEY,
[first_name] VARCHAR(50)  NOT NULL,
[last_name] NVARCHAR(50)  NOT NULL,
[date_of_birth] NVARCHAR(25)  NOT NULL
);

So it should be obvious that this approach is to use one table for tracking all changes in every table. Another option would be to create an audit table for each table and every time a row is changed copy it to the audit table first. You would then have to scan the audit table and check each column to see what the change that was made.

I would like to point out that I do not particularly like the code shown below. I would prefer to use a RowUpdated event handler but since I am SQLiteDataAdapters with sql text instead of stored procedures with return row I am settling for this. In another post I will show using the updated event with Microsoft SQL server and Stored procedures.

Here is the code that set ups the transactions and calls the audit function. The audit function must be called before the DataAdapter update. This is all done within one transaction so that nothing is recorded in the audit table unless records are saved in the main table.

You should notice that the DoAudit function takes as parameters a DataSet that is to be tracked in the audit, a code (if empty it will use the primary key column as the code) and a SQLiteTransaction.

Please excuse the incompleteness of the class Program as I am in the middle of rewritting this article.

class Program
{
    private static SQLiteDataAdapter daActor;
    private static DataSet dsActor;
    private static Audit auditTracking;

    static void Main(string[] args)
    {
        auditTracking = new Audit();
        dsActor = new DataSet();

        SQLiteConnection cn = HelperFunctions.CreateConnection();
        daActor = new SQLiteDataAdapter("SELECT * FROM actor;", cn);
        if (System.IO.File.Exists("hello.db"))
        {
            daActor.Fill(dsActor);
        }

        bool exit = false;
        Console.WriteLine("h - for help");
        while (exit == false)
        {
            Console.Write("Command: ");
            string input = Console.ReadLine();
            switch (input)
            {
                case "q":
                    exit = true;
                    break;
                case "0":
                    HelperFunctions.CreateDatabase();
                    daActor.Fill(dsActor);
                    break;
                case "n":
                    NewActor();
                    break;
                case "p":
                    PrintAllActors();
                    break;
                case "pa":
                    PrintAuditTable();
                    break;
                case "h":
                    Console.WriteLine("q - quite program");
                    Console.WriteLine("0 - Create Database");
                    Console.WriteLine("n - Add new actor");
                    Console.WriteLine("p - Print all actors");
                    Console.WriteLine("pa - Print audit table");
                    Console.WriteLine("h - Print Help");
                    Console.WriteLine("");
                    break;
            }
        }

    }

    private static void PrintAuditTable()
    {
        DataTable dt = new DataTable();
        SQLiteConnection cn = HelperFunctions.CreateConnection();
        SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM audit;", cn);
        cn.Open();
        SQLiteDataReader reader = cmd.ExecuteReader();
        dt.Load(reader);
        reader.Close();

        foreach (DataRow row in dt.Rows)
        {
            Console.WriteLine("ID: " + row["id"]);
            Console.WriteLine("tablename: " + row["tablename"]);
            Console.WriteLine("field: " + row["field"]);
            Console.WriteLine("original: " + row["original"]);
            Console.WriteLine("new: " + row["new"]);
            Console.WriteLine("action: " + row["action"]);
            Console.WriteLine("user: " + row["user"]);
            Console.WriteLine("date: " + row["date"]);
            Console.WriteLine("code: " + row["code"]);
            Console.WriteLine("orig_binary: " + row["orig_binary"]);
            Console.WriteLine("new_binary: " + row["new_binary"]);
            Console.WriteLine("");
        }
    }

    private static void PrintAllActors()
    {
        foreach (DataRow row in dsActor.Tables[0].Rows)
        {
            Console.WriteLine("Actor: " + row["first_name"] + " " + row["last_name"]);
            Console.WriteLine("DOB: " + row["date_of_birth"]);
            Console.WriteLine("");
        }
    }
    private static void NewActor()
    {
        DataRow row = dsActor.Tables[0].NewRow();

        row["id"] = DBNull.Value;

        Console.Write("First Name: ");
        row["first_name"] = Console.ReadLine();

        Console.Write("Last Name: ");
        row["last_name"] = Console.ReadLine();

        Console.Write("Date of Birth: ");
        row["date_of_birth"] = Console.ReadLine();

        dsActor.Tables[0].Rows.Add(row);

        UpdateDatabase();
    }

    private static void UpdateDatabase()
    {
        SQLiteConnection cn = HelperFunctions.CreateConnection();

        cn.Open();
        daActor.SelectCommand.Connection = cn;
        SQLiteTransaction txn = cn.BeginTransaction();
        try
        {
            SQLiteCommandBuilder cmd = new SQLiteCommandBuilder(daActor);
            daActor.InsertCommand = cmd.GetInsertCommand();
            daActor.UpdateCommand = cmd.GetUpdateCommand();
            daActor.DeleteCommand = cmd.GetDeleteCommand();

            daActor.InsertCommand.Transaction = txn;
            daActor.UpdateCommand.Transaction = txn;
            daActor.DeleteCommand.Transaction = txn;

            // call the audit function.  If the daActor.Update command succeeds then
            // there will be an audit trail.  If it fails the audit will be rolled back.
            auditTracking.DoAudit(dsActor, "", txn);
            daActor.Update(dsActor);
            txn.Commit();
        }
        catch (Exception ex)
        {
            // rollback action and audit trail.
            txn.Rollback();
            TrapErrors(ex, true);
        }
        finally
        {
            cn.Close();
        }
    }

    public static void TrapErrors(Exception ex, bool showMessage)
    {
        if (showMessage)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

The Main function runs the code that lets the user enter new actors and then calls the UpdateDatabase function to update the actor and audit table.

Here is the audit class that does the actual work.

The DoAudit function is passed a DataSet and a Transaction. It will loop through each row in each table that is in the DataSet. If there are any changes to the values such as an Insert, Update, or Delete it will record this change in the audit table. It will attempt to identify the primary key and use that as the code column in the audit table.

public class Audit
{
    private SQLiteDataAdapter daAudit;


    /// <summary>
    /// Check the specfied table in the dataset and record them in the audit table.
    /// Currently is only an example and does not work
    /// </summary>
    /// <param name="ds">DataSet</param>
    /// <param name="code">string - generally the primary key of the table</param>
    /// <param name="txn">IDbTransaction</param>
    /// <remarks>Requires a table with columns: tablename, action, user, date, new, original, field, code.
    /// The "code" field is the one that is to be searched.</remarks>
    public void DoAudit(DataSet ds, string code, SQLiteTransaction txn)
    {
        if (ds.Tables.Count <= 0)
        {
            return;
        }

        DataSet dsAudit = new DataSet();
        DataRow row_Audit;
        daAudit = new SQLiteDataAdapter("Select * from audit WHERE 1=2;", txn.Connection);
        daAudit.Fill(dsAudit);

        dsAudit.Tables[0].Columns["id"].AllowDBNull = true;
        dsAudit.Tables[0].Columns["orig_binary"].AllowDBNull = true;
        dsAudit.Tables[0].Columns["new_binary"].AllowDBNull = true;

        SQLiteCommandBuilder cmd = new SQLiteCommandBuilder(daAudit);
        daAudit.InsertCommand = cmd.GetInsertCommand();
        daAudit.InsertCommand.Transaction = txn;


        daAudit.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
        daAudit.InsertCommand.Transaction = txn;

        string tableName = "";
        string primaryKey = "";


        foreach (DataTable tbl in ds.Tables)
        {
            tableName = tbl.TableName;

            if (ds.Tables[tableName].PrimaryKey.Length > 0)
            {
                primaryKey = ds.Tables[tableName].PrimaryKey[0].ColumnName.Trim();
            }

            foreach (DataRow x in tbl.Rows)
            {
                int codeID = -1;

                for (int i = 0; i <= tbl.Columns.Count - 1; i++)
                {
                    row_Audit = dsAudit.Tables[0].NewRow();
                    row_Audit["id"] = DBNull.Value;
                    row_Audit["tablename"] = tableName;
                    row_Audit["date"] = DateTime.Now;
                    row_Audit["action"] = x.RowState.ToString();
                    row_Audit["code"] = codeID;
                    row_Audit["user"] = System.Environment.UserName; //Login.LoggedInUser;
                    row_Audit["new_binary"] = null;
                    row_Audit["orig_binary"] = null;


                    string original = "";
                    string current = "";

                    // deletes should have blank current values
                    if (x.RowState != DataRowState.Deleted)
                    {
                        current = x[i, DataRowVersion.Current].ToString().Trim();
                    }

                    // Insert should have blank original values.
                    if (x.RowState != DataRowState.Added)
                    {
                        original = x[i, DataRowVersion.Original].ToString().Trim();
                    }


                    if (tbl.Columns[i].ColumnName == primaryKey)
                    {
                        try
                        {
                            if (HelperFunctions.IsNumeric(current, System.Globalization.NumberStyles.Integer))
                            {
                                codeID = int.Parse(current);
                            }
                            else
                            {
                                codeID = -1;
                            }
                        }
                        catch
                        {
                            codeID = -1;
                        }
                        row_Audit["code"] = codeID;
                    }

                    if (current != original)
                    {

                        row_Audit["field"] = ds.Tables[tableName].Columns[i].ColumnName;
                        row_Audit["new"] = current;
                        row_Audit["original"] = original;

                        dsAudit.Tables[0].Rows.Add(row_Audit);
                    }
                }

            }

            daAudit.Update(dsAudit);
        }
    }
}

As can be seen in this code it will also work on fields that are binary blobs.

New HelperFunctions class: This class has functions for creating a new sample database named hello.db, returning a connection to the sample database and testing if a field is numeric.


class HelperFunctions
{

    public static void CreateDatabase()
    {

        SQLiteConnection.CreateFile("hello.db");
        SQLiteConnection cn = CreateConnection();
        String.Format(CultureInfo.InvariantCulture, "Data Source = {0}; Version = 3", "database.sql");

        string sql = System.IO.File.ReadAllText("database.sql", System.Text.Encoding.UTF8);
        SQLiteCommand cmd = new SQLiteCommand(sql, cn);
        cmd.ExecuteNonQuery();
    }

    public static SQLiteConnection CreateConnection()
    {
        return new SQLiteConnection("Data Source = 'hello.db'; Version = 3");
    }

    static public bool IsNumeric(string val, System.Globalization.NumberStyles NumberStyle)
    {
        Double result;
        return Double.TryParse(val, NumberStyle, System.Globalization.CultureInfo.CurrentCulture, out result);
    }
}

C# version of turning a website into a chm file. Requires that html workshop (http://msdn.microsoft.com/library/en-us/htmlhelp/html/vsconhh1start.asp) is installed. If anyone knows of a working open source chm compiler let me know.

Can be used like: html2chm.Html2chm action = new html2chm.Html2chm(); This will run and prompt you for which website directory and which file in the directory to convert to a chm file.

Or it can be used like this: bool eachFileAsTopic = true; html2chm.Html2chm action = new html2chm.Html2chm(@”\Path\to\directory\to\convert”, @”mainTopic.html”, eachFileAsTopic);

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;
using System.Diagnostics;


namespace html2chm
{

    public class Html2chm
    {
        //http://www.majorsilence.com/csharp_html2chm
        //The generated CHM file is named YourCHMFile.chm on your desktop.

        string HHC;
        // Path to hhc.exe

        // Directory path where HHP file is stored
        // and base directory of the html files to be converted
        string RepBase;

        string FicHHP;
        // Path to HHP file

        string fileListString;


        public Html2chm() : this("", "", false)
        {
        }

        public Html2chm(string convertDirectory, string mainTopic, bool filesAsTopics)
        {
            List<string> fileList = new List<string>();

            HHC = System.IO.Path.Combine("C:\\Program Files\\HTML Help Workshop", "hhc.exe");

            if (System.IO.File.Exists(HHC) == false)
            {
                HHC = System.IO.Path.Combine("C:\\Program Files (x86)\\HTML Help Workshop", "hhc.exe");
                if (System.IO.File.Exists(HHC) == false)
                {
                    MessageBox.Show("In order to use this script, you need HTML Help Workshop" + System.Environment.NewLine + "http://msdn.microsoft.com/library/en-us/htmlhelp/html/vsconhh1start.asp");
                    return;
                }
            }

            if (convertDirectory == string.Empty)
            {
                System.Windows.Forms.FolderBrowserDialog DirectoryBrowser = default(System.Windows.Forms.FolderBrowserDialog);
                DirectoryBrowser = new System.Windows.Forms.FolderBrowserDialog();
                DirectoryBrowser.Description = "Which directory do you want to use?";
                if ((DirectoryBrowser.ShowDialog() == System.Windows.Forms.DialogResult.OK))
                {
                    RepBase = DirectoryBrowser.SelectedPath;
                }
            }
            else
            {
                RepBase = convertDirectory;
            }


            if (RepBase == null)
            {

                MessageBox.Show("Please choose a Folder");
            }
            else
            {
                fileList.AddRange(RecursiveFileList(new DirectoryInfo(RepBase), new DirectoryInfo(RepBase)));
                string fileHHC="";
                string FicHHC = System.IO.Path.Combine(RepBase, "chm-editor-Temp-HHC.hhc");
                foreach (string s in fileList)
                {
                    this.fileListString += s + System.Environment.NewLine;

                    if (filesAsTopics)
                    {
                        string value = s.Replace(RepBase, "");

                        string textValue = ""; 

                        if (value.StartsWith("\\"))
                        {
                            value = value.Remove(0, 1);
                        }

                        textValue = System.IO.Path.GetFileName(value); 
                        textValue = textValue.Replace(System.IO.Path.GetExtension(textValue), "");

                        fileHHC += "<LI><OBJECT type=\"text/sitemap\"><PARAM name=\"Name\" value=\"" + textValue + "\"><PARAM name=\"Local\" value=\"" + value + "\"><PARAM name=\"ImageNumber\" value=\"0\"></OBJECT>";
                    }
                }

                if (filesAsTopics)
                {
                    System.IO.File.WriteAllText(FicHHC, fileHHC);
                }

                FicHHP = System.IO.Path.Combine(RepBase, "chm-editor-Temp" + DateTime.Now.Millisecond.ToString() + ".HHP");
                string output = "[OPTIONS]" + System.Environment.NewLine;
                output += "Compatibility=1.1 or later" + System.Environment.NewLine;
                output += "Compiled file=" + System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "YourCHMFile.chm") + System.Environment.NewLine;
                string main_topic;

                if (mainTopic == string.Empty)
                {
                    main_topic = MainTopic();
                }
                else
                {
                    main_topic = mainTopic;
                }

                if (filesAsTopics)
                {
                    output += "Contents file=" + FicHHC + System.Environment.NewLine;
                }

                output += "Default topic=" + main_topic.Substring(RepBase.Length + 1, main_topic.Length - (RepBase.Length + 1)) + System.Environment.NewLine; //Strings.Mid(MainTopic(), Strings.Len(RepBase) + 2) + System.Environment.NewLine;
                output += "Display compile progress=No" + System.Environment.NewLine;
                output += "Language=0x409 English (standard)" + System.Environment.NewLine;
                // 0x40C - French
                // 0x407 - German
                output += "Title=(c) 2010 MajorSilence" + System.Environment.NewLine;
                output += System.Environment.NewLine + "[FILES]" + System.Environment.NewLine;
                output += this.fileListString + System.Environment.NewLine;
                output += System.Environment.NewLine + "[INFOTYPES]" + System.Environment.NewLine;

                System.IO.File.WriteAllText(FicHHP, output);

                System.Diagnostics.Process p = new System.Diagnostics.Process();
                p.StartInfo.FileName = HHC;
                p.StartInfo.Arguments = FicHHP;
                p.StartInfo.CreateNoWindow = true;
                p.Start();
                p.WaitForExit();

                // Finally Remove 
                try
                {
                    System.IO.File.Delete(FicHHP);

                    if (System.IO.File.Exists(FicHHC))
                    {
                        System.IO.File.Delete(FicHHC);
                    }
                }
                catch (Exception ex)
                {
                }
            }

            
        }

        // Recurse through directory and return string of files
        public List<String> RecursiveFileList(DirectoryInfo source, DirectoryInfo target)
        {
            // loop through each file in current directory
            List<string> fileList = new List<string>();

            foreach (FileInfo file in source.GetFiles())
            {
                if (file.Name.ToLower().EndsWith("html") | file.Name.ToLower().EndsWith("htm") | file.Name.ToLower().EndsWith("mov") | file.Name.ToLower().EndsWith("au"))
                {
                    fileList.Add(file.FullName);

                }
            }

            // Then loop through each directory
            foreach (DirectoryInfo dir in source.GetDirectories())
            {
                fileList.AddRange(RecursiveFileList(dir, new DirectoryInfo(dir.Name)));
            }


            return fileList;
        }


        // Select the root file (Main topic) in base directory
        public string MainTopic()
        {
            System.Windows.Forms.OpenFileDialog dlg = new System.Windows.Forms.OpenFileDialog();

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                return dlg.FileName;
            }

            return string.Empty;
        }

    }

}


I needed a screen video capture software that was free and open source and worked with several different video types. I also wanted the program to record the proper screen colours. So I wrote a small program to do this. It uses mencoder to do the video processing so I will be able to add almost any video format as the output. Current it outputs mpeg/mp3 in an avi container.

See the two attached files. Warning, this is very alpha quality software. When doing a new recording always make sure it is in a new empty folder. It should not harm any other files but I would not take the chance. Tested on 64 bit Vista. Not sure how well it will work on Windows XP or 7.

It currently uses a lot of memory. Can only record from the primary screen. Only does full screen recordings. Which is fine for my needs.

It also currently highlights the mouse location and records from the computer mic.

UPDATE: Get the latest release and news from http://majorsilence.com/screen_video_capture


So I can now play Cocaine Cowgirl acceptably good.

List of songs that that I can play but need more practice: Wonderwall Feliz Navidad O Holy Night I Don’t want to miss a thing Winter wonderland Have yourself a merry little Christmas Leaving on a jet plane I’m Yours


Simple download file example. Works with http, https, and ftp. If username and password are required add them to the client using System.Net.NetworkCredential(“username”, “password”).

Use the WebClient DownloadProgressChanged and DownloadFileCompleted events to display percent and start on finish action.

These examples will not work unless you create a winform to go along with them.

VB.net

Imports System.IO
Imports System.Net
Imports System.Threading
Imports System.Text

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDownload.Click
        download(txtDownloadPath.Text.Trim, txtSavePath.Text.Trim)
    End Sub

    Private Shared percent As Integer = 0

    Public Sub Download(ByVal urlPath As String, ByVal savePath As String)
        Dim client As WebClient = New WebClient()
        'client.Credentials = New System.Net.NetworkCredential("username", "password")

        AddHandler client.DownloadProgressChanged, AddressOf ClientDownloadProgressChanged
        AddHandler client.DownloadFileCompleted, AddressOf ClientDownloadFileCompleted

        client.DownloadFileAsync(New Uri(urlPath), savePath)

    End Sub

    Private Sub ClientDownloadProgressChanged(ByVal sender As Object, ByVal e As DownloadProgressChangedEventArgs)

        Dim bytesIn As Double = Double.Parse(e.BytesReceived.ToString())
        Dim totalBytes As Double = Double.Parse(e.TotalBytesToReceive.ToString())
        Dim percentage As Double = bytesIn / totalBytes * 100

        ProgressBar1.Value = Integer.Parse(Math.Truncate(percentage).ToString())
        PaintProgressBarPercent(ProgressBar1)
    End Sub

    Private Sub ClientDownloadFileCompleted(ByVal sender As Object, ByVal e As EventArgs)
        'AsyncCompletedEventArgs
        MessageBox.Show("Download Completed")
    End Sub

    Private Sub PaintProgressBarPercent(ByRef a As ProgressBar)
        Dim percent As Integer = CInt(((CDbl((ProgressBar1.Value - ProgressBar1.Minimum)) / CDbl((ProgressBar1.Maximum - ProgressBar1.Minimum))) * 100))

        Using gr As Graphics = ProgressBar1.CreateGraphics()
            Dim p1 As New PointF(CType(a.Width / 2 - (gr.MeasureString(percent & "%", SystemFonts.DefaultFont).Width / 2.0F), Single), CType(a.Height / 2 - (gr.MeasureString(percent & "%", SystemFonts.DefaultFont).Height / 2.0F), Single))
            Try
                gr.DrawString(percent & "%", SystemFonts.DefaultFont, Brushes.Black, p1)
            Catch ex As Exception
            End Try

        End Using

    End Sub

End Class

C# Example

using System.IO;
using System.Net;
using System.Threading;
using System.Text;

public class Form1
{
    private void Button1_Click(System.Object sender, System.EventArgs e)
    {
        download(txtDownloadPath.Text.Trim, txtSavePath.Text.Trim);
    }
    
    private static int percent = 0;
    
    public void Download(string urlPath, string savePath)
    {
        WebClient client = new WebClient();
        //client.Credentials = New System.Net.NetworkCredential("username", "password")
        
        client.DownloadProgressChanged += ClientDownloadProgressChanged;
        client.DownloadFileCompleted += ClientDownloadFileCompleted;
        
            
        client.DownloadFileAsync(new Uri(urlPath), savePath);
    }
    
    
    private void ClientDownloadProgressChanged(object sender, DownloadProgressChangedEventArgs e)
    {
        
        double bytesIn = double.Parse(e.BytesReceived.ToString());
        double totalBytes = double.Parse(e.TotalBytesToReceive.ToString());
        double percentage = bytesIn / totalBytes * 100;
        
        ProgressBar1.Value = int.Parse(Math.Truncate(percentage).ToString());
            
        PaintProgressBarPercent(ProgressBar1);
    }
    
    
    private void ClientDownloadFileCompleted(object sender, EventArgs e)
    {
        //AsyncCompletedEventArgs
        MessageBox.Show("Download Completed");
    }
    
    
    
    private void PaintProgressBarPercent(ref ProgressBar a)
    {
        int percent = (int)(((double)(ProgressBar1.Value - ProgressBar1.Minimum) / (double)(ProgressBar1.Maximum - ProgressBar1.Minimum)) * 100);
        
        using (Graphics gr = ProgressBar1.CreateGraphics()) {
            PointF p1 = new PointF((float)a.Width / 2 - (gr.MeasureString(percent + "%", SystemFonts.DefaultFont).Width / 2f), (float)a.Height / 2 - (gr.MeasureString(percent + "%", SystemFonts.DefaultFont).Height / 2f));
            try {
                gr.DrawString(percent + "%", SystemFonts.DefaultFont, Brushes.Black, p1);
            }
            catch (Exception ex) {
                
            }
            
        }
    }
}