Setting up a SQL Database and Writing Event Data to the Database

Article
07/28/2009

This topic references code examples about how to set up a database connection, how to specify a table to copy the event data, and so on. It also describes parts of the example that use certain data structures and constructs used for maintenance. For the code example, see Example code for Event Listening.

Example

Description

The example sets up a connection to the SQL database whose name and connection string is specified in the application settings. This topic describes the steps and illustrates the database-related operations by referencing code examples in this topic.

Useful classes and data structures

To facilitate writing data to the SQL table, the example uses a hash table. This hash table maps the column names in the database table to the Xpath for the desired value in the event XML data.

''' <summary>
''' The in-memory mapping between the column name and Xml Xpath leading to value.
''' </summary>
Dim nameToXPathMapping As New Hashtable()

/// <summary>
/// The in-memory mapping between the column name and Xml Xpath leading to value.
/// </summary>
Hashtable nameToXPathMapping = new Hashtable();

The following shows how the hash table is used to fill in the data in the database table:

''' <summary>
''' Given the XML for an event, loads the event into a row-format and adds it to the eventDataSet.
''' </summary>
''' <param name="EventXml">Event XML to load into DataSet.</param>
''' <param name="eventDataSet">The dataset to load event into.</param>
Private Sub AddEventIntoDataSet(ByVal EventXml As String, ByRef eventDataSet As DataSet)

    Dim newRow As DataRow = eventDataSet.Tables("Event").NewRow()

    For Each de As DictionaryEntry In nameToXPathMapping

        newRow(de.Key) = RunXPathOnDocument(de.Value, EventXml)
    Next

    newRow("EventXml") = EventXml
    eventDataSet.Tables("Event").Rows.Add(newRow)
End Sub

/// <summary>
/// Given the XML for an event, loads the event into a row-format and adds it to the eventDataSet.
/// </summary>
/// <param name="EventXml">Event XML to load into DataSet.</param>
/// <param name="eventDataSet">The dataset to load event into.</param>
internal void AddEventIntoDataSet(string EventXml, ref DataSet eventDataSet)
{
    DataRow newRow = eventDataSet.Tables["Event"].NewRow();

    foreach (DictionaryEntry de in nameToXPathMapping)
    {
        newRow[(string)de.Key] = RunXPathOnDocument((string)de.Value, EventXml);
    }

    newRow["EventXml"] = EventXml;
    eventDataSet.Tables["Event"].Rows.Add(newRow);
}

A dataset is created to store the incoming event data.

''' <summary>
''' Event data set used to store incoming events.
''' </summary>
Dim eventDs As New DataSet()

/// <summary>
/// Event data set used to store incoming events.
/// </summary>
DataSet eventDs = new DataSet();

A bookmark tracks the progress of the example through the event log and enables subsequent runs of the example to start from where the last run stopped. The bookmark stream is saved to disk in the same directory as the running application and is called bookmark.stream.
```
Dim bookMarkToStartFrom As EventBookmark = Nothing
Dim fsBookmark As FileStream
```
```
EventBookmark bookMarkToStartFrom = null;
FileStream fsBookmark;
```

The following shows how the bookmarks are used when reading event data is resumed.

Try
    If System.IO.File.Exists("bookmark.stream") Then

        fsBookmark = New FileStream("bookmark.stream", FileMode.Open)
        Dim formatter As New BinaryFormatter()
        bookMarkToStartFrom = formatter.Deserialize(fsBookmark)

    Else
        fsBookmark = New FileStream("bookmark.stream", FileMode.Create)
    End If

Catch e As Exception

    Console.WriteLine( _
    "Could not load or create bookmark on disk directory: " & _
    "{0} error: {1}.", System.IO.Directory.GetCurrentDirectory(), e.Message)
    bookMarkToStartFrom = Nothing
End Try

try
{
    if (System.IO.File.Exists("bookmark.stream"))
    {
        fsBookmark = new FileStream("bookmark.stream", FileMode.Open);
        BinaryFormatter formatter = new BinaryFormatter();
        bookMarkToStartFrom = (EventBookmark)formatter.Deserialize(fsBookmark);
    }
    else
    {
        fsBookmark = new FileStream("bookmark.stream", FileMode.Create);
    }


}
catch (Exception e)
{
    Console.WriteLine("Could not load or create bookmark on disk directory: {0} error: {1}.", System.IO.Directory.GetCurrentDirectory(), e.Message);
    bookMarkToStartFrom = null;
}

The RunXPathOnDocument method helps resolve XPath to an actual value in an event. It uses classes and methods from the System.Xml namespace to manipulate the XML data.

The LoadNameToXPathHash method loads the name to XPath mapping from the settings into memory for easier retrieval. The name is the key and the XPath is its value.

''' <summary>
''' Loads the Name vs XPath from the settings into memory for easy retrieval.
''' Name is used as key.
''' Xpath is used as Value.
''' </summary>
''' <param name="SqlTableDefinition">The string Name=XPath mapping to EventXml (Example: ProviderName=Event/System/Provider/@Name,...).</param>
''' <param name="names">The Hashtable to load the mapping to.</param>
Private Sub LoadNameToXPathHash(ByVal SqlTableDefinition As String, ByRef names As Hashtable)

    Dim sqlColumns() As String = SqlTableDefinition.Split(",")
    names.Clear()

    For Each sqlColumnExpr As String In sqlColumns

        Dim Pivot As Integer = sqlColumnExpr.IndexOf("=") + 1
        Dim Len As Integer = sqlColumnExpr.Length

        Dim Name As String = sqlColumnExpr.Substring(0, Pivot - 1).Trim()
        Dim Value As String = sqlColumnExpr.Substring(Pivot, Len - Pivot).Trim()

        Try
            names.Add(Name, Value)

        Catch dupName As DuplicateNameException

            Console.WriteLine( _
            "ERROR! Attempted to use ColumnName {0} twice," & _
            " which is not allowed. Edit the Configurations' SqlTableDefinition setting " & _
            "to remove duplicate names.", Name)
            Throw dupName
        End Try
    Next
End Sub

/// <summary>
    /// Loads the Name vs XPath from the settings into memory for easy retrieval.
    /// Name is used as the key.
    /// Xpath is used as the value.
    /// </summary>
    /// <param name="SqlTableDefinition">The string Name=XPath mapping to EventXml (Example: ProviderName=Event/System/Provider/@Name,...).</param>
    /// <param name="names">The Hashtable to load the mapping to.</param>
    internal void LoadNameToXPathHash(string SqlTableDefinition, ref Hashtable names)
    {
        string[] sqlColumns = SqlTableDefinition.Split(',');
        names.Clear();

        foreach (string sqlColumnExpr in sqlColumns)
        {
            int Pivot = sqlColumnExpr.IndexOf('=') + 1;
            int Len = sqlColumnExpr.Length;

            string Name = sqlColumnExpr.Substring(0, Pivot - 1).Trim();
            string Value = sqlColumnExpr.Substring(Pivot, Len - Pivot).Trim();

            try
            {
                names.Add(Name, Value);
            }
            catch (DuplicateNameException dupName)
            {
                Console.WriteLine("ERROR! Attempted to use ColumnName {0} twice, which is not allowed. Edit the Configurations' SqlTableDefinition setting to remove duplicate names.", Name);
                throw dupName;
            }
        }
    }

The SQL connection string specified in the application settings is used to open a connection to the SQL database.

sqlConn = New SqlConnection(Settings.Default.SqlConnectionString)
sqlConn.Open()

sqlConn = new SqlConnection(Settings.Default.SqlConnectionString);
sqlConn.Open();

The CreateSqlSchemaDefinition method creates and executes the T-SQL statement to create a schema based on the Name to Xpath mapping from the settings file.

''' <summary>
''' Creates and executes T-SQL statement to create a schema based on the Name=Xpath mapping from the config file.
''' </summary>
''' <param name="sqlConn">The active Sql connection to use.</param>
''' <param name="SqlTableDefinition"></param>
Private Sub CreateSqlSchemaDefinition()

    Dim sqlComnd As SqlCommand = sqlConn.CreateCommand()

    sqlComnd.CommandText = String.Format("USE [{0}]", Settings.Default.DatabaseName) & Environment.NewLine & _
                           "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Event]') AND type in (N'U'))" & _
                           Environment.NewLine & "DROP TABLE [dbo].[Event]" & Environment.NewLine & _
                           "CREATE TABLE [dbo].[Event](" & Environment.NewLine

    For Each de As DictionaryEntry In nameToXPathMapping
        sqlComnd.CommandText += String.Format( _
        "[{0}] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,", de.Key) & Environment.NewLine
    Next

    sqlComnd.CommandText += "[EventXml] [xml] NULL" & Environment.NewLine
    sqlComnd.CommandText += ") ON [PRIMARY]"

    sqlComnd.ExecuteNonQuery()

    ClearBookmark()
End Sub

/// <summary>
/// Creates and executes T-SQL statement to create a schema based on the Name=Xpath mapping from the config file.
/// </summary>
/// <param name="sqlConn">The active Sql connection to use.</param>
/// <param name="SqlTableDefinition"></param>
internal void CreateSqlSchemaDefinition()
{
    SqlCommand sqlComnd = sqlConn.CreateCommand();

    sqlComnd.CommandText = String.Format("USE [{0}]\n", Settings.Default.DatabaseName) +
                           "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Event]') AND type in (N'U'))\n" +
                           "DROP TABLE [dbo].[Event]\n" +
                           "CREATE TABLE [dbo].[Event](\n";

    foreach (DictionaryEntry de in nameToXPathMapping)
    {
        sqlComnd.CommandText += String.Format("[{0}] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,\n", de.Key);
    }

    sqlComnd.CommandText += "[EventXml] [xml] NULL\n";
    sqlComnd.CommandText += ") ON [PRIMARY]";

    sqlComnd.ExecuteNonQuery();

    ClearBookmark();
}

The DoesDbTableExist method verifies that the database with the specified name exists on the server.

''' <summary>
''' Runs a query to check if a database exists on a SQL server.
''' </summary>
''' <param name="sqlConn">An active sql connection.</param>
''' <param name="TableName">The table name to check on.</param>
''' <returns>true if table exists, false if it doesn't exist, exception on error.</returns>
Private Function DoesDbTableExist(ByVal TableName As String) As Boolean

    Dim eventDA As New SqlDataAdapter( _
    String.Format("SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]')", _
    TableName), sqlConn)
    Dim mySqlCommandBuilder As New SqlCommandBuilder(eventDA)

    If Not eventDA.SelectCommand.ExecuteScalar() Is Nothing Then
        Return True
    End If

    Return False
End Function

/// <summary>
/// Runs a query to check if a database exists on a SQL server.
/// </summary>
/// <param name="sqlConn">An active sql connection.</param>
/// <param name="TableName">The table name to check on.</param>
/// <returns>true if table exists, false if it doesn't exist, exception on error.</returns>
internal bool DoesDbTableExist(string TableName)
{
    SqlDataAdapter eventDA = new SqlDataAdapter(String.Format("SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]')", TableName), sqlConn);
    SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(eventDA);

    if (null != eventDA.SelectCommand.ExecuteScalar())
    {
        return true;
    }

    return false;
}

The DoesDbSchemaNeedRecreating method compares the database schema and the name to Xpath mapping to decide if the existing data base table is adequate for writing the event data or a new one must be created.

''' <summary>
''' Runs a comparison between DB schema and the name-To-Xpath mapping.
''' </summary>
''' <param name="eventDs">The filled in DataSet, from DB.</param>
''' <returns>true if DB schema does not match, else false.</returns>
Private Function DoesDbSchemaNeedRecreating(ByVal eventDs As DataSet) As Boolean

    Dim DbSchemaColumnCount As Integer = eventDs.Tables("Event").Columns.Count

    ' Db Schema always has one additional column for the complete Event Xml
    If Not nameToXPathMapping.Count = DbSchemaColumnCount - 1 Then
        Return True
    End If

    For Each dc As DataColumn In eventDs.Tables("Event").Columns

        If 0 <> (String.Compare(dc.ColumnName, "EventXml", True) AndAlso _
            Not nameToXPathMapping.ContainsKey(dc.ColumnName)) Then
            Return True
        End If
    Next

    Return False
End Function

/// <summary>
/// Runs a comparison between DB schema and the name-To-Xpath mapping.
/// </summary>
/// <param name="eventDs">The filled in DataSet, from DB.</param>
/// <returns>true if DB schema does not match, else false.</returns>
internal bool DoesDbSchemaNeedRecreating(DataSet eventDs)
{
    int DbSchemaColumnCount = eventDs.Tables["Event"].Columns.Count;

    // Db Schema always has one additional column for the complete Event Xml
    if (nameToXPathMapping.Count != DbSchemaColumnCount - 1)
    {
        return true;
    }

    foreach (DataColumn dc in eventDs.Tables["Event"].Columns)
    {
        if (0 != String.Compare(dc.ColumnName, "EventXml", true) &&
            !nameToXPathMapping.ContainsKey(dc.ColumnName))
            return true;
    }

    return false;
}

The LoadSchemaAndEvents method connects to SQL to fill or create a new table if none exists. When writing to an XML file instead of the database, it simply creates the schema in the dataset.

''' <summary>
''' Initializes program state. Specifically, connects to SQL to fill and/or re/create in 
''' the schema for the DataSet. When in "XML mode" simply creates the schema in the dataset.
''' </summary>
''' <returns>true upon success else false and errorcode is set.</returns>
Private Function LoadSchemaAndEvents() As Boolean

    eventDs.DataSetName = "Events"

    If Not Settings.Default.XmlMode Then

        Dim eventDA As New SqlDataAdapter("select * from Event", sqlConn)
        Dim mySqlCommandBuilder As New SqlCommandBuilder(eventDA)
        Dim DbEventTableCreated As Boolean = DoesDbTableExist("Event")

        If DbEventTableCreated Then

            eventDA.MissingSchemaAction = System.Data.MissingSchemaAction.AddWithKey
            eventDA.FillSchema(eventDs, System.Data.SchemaType.Source, "Event")

            'check to see if schema in DB is in sync with schema in the config file (SqlTableDefinition)
            If DoesDbSchemaNeedRecreating(eventDs) Then

                Dim selection As ConsoleKey = ConsoleKey.N

                ' Schema is out of sync so we need to delete the table and re-create it in the Db
                ' Ask user if it is O.K. to delete the old schema
                Console.WriteLine("Schema in configuration is out of sync with the SQL database, " & _
                "the Event table needs to be recreated.")
                Console.WriteLine("WARNING: All existing rows/data in database {0} " & _
                "from the Event Table will be deleted. Proceed? " & _
                "(At the console press y/n)", Settings.Default.DatabaseName)

                Do
                    selection = Console.ReadKey().Key

                    If selection = ConsoleKey.Y Then

                        CreateSqlSchemaDefinition()

                    ElseIf selection = ConsoleKey.N Then

                        Console.WriteLine("Schema not in sync, cannot continue.")
                        Environment.ExitCode = 87
                        Return False

                    Else

                        Console.WriteLine("")
                        Console.WriteLine("Invalid input, expected (y)es or (n)o, try again...")
                    End If

                Loop While Not selection = ConsoleKey.Y
            End If

        Else

            ' DB schema doesn't exist, create new one
            CreateSqlSchemaDefinition()
        End If

        ' Load schema into the DataSet
        eventDA.FillSchema(eventDs, System.Data.SchemaType.Source, "Event")

    Else

        ' In XML mode load schema into DataSet, XML will be saved straight out of eventDs dataSet
        Dim dt As DataTable = eventDs.Tables.Add("Event")

        For Each de As DictionaryEntry In nameToXPathMapping

            dt.Columns.Add(de.Key, GetType(String))
        Next

        dt.Columns.Add("EventXml")
    End If

    Return True
End Function

/// <summary>
/// Initializes program state. Specifically, connects to SQL to fill and/or re/create in 
/// the schema for the DataSet. When in "XML mode" simply creates the schema in the dataset.
/// </summary>
/// <returns>true upon success else false and errorcode is set.</returns>
internal bool LoadSchemaAndEvents()
{
    eventDs.DataSetName = "Events";

    if (!Settings.Default.XmlMode)
    {
        SqlDataAdapter eventDA = new SqlDataAdapter("select * from Event", sqlConn);
        SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(eventDA);
        bool DbEventTableCreated = DoesDbTableExist("Event");

        if (DbEventTableCreated)
        {
            eventDA.MissingSchemaAction = System.Data.MissingSchemaAction.AddWithKey;
            eventDA.FillSchema(eventDs, System.Data.SchemaType.Source, "Event");

            //check to see if schema in DB is in sync with schema in the config file (SqlTableDefinition)
            if (DoesDbSchemaNeedRecreating(eventDs))
            {
                ConsoleKey selection = ConsoleKey.N;

                //Schema is out of sync so we need to delete the table and re-create it in the Db
                //Ask user if it is O.K. to delete the old schema
                Console.WriteLine("Schema in configuration is out of sync with the SQL database, the Event table needs to be recreated.");
                Console.WriteLine("WARNING: All existing rows/data in database {0} from the Event Table will be deleted. Proceed? (At the console press y/n)", Settings.Default.DatabaseName);

                do
                {
                    selection = Console.ReadKey().Key;

                    if (selection == ConsoleKey.Y)
                    {
                        CreateSqlSchemaDefinition();
                    }
                    else if (selection == ConsoleKey.N)
                    {
                        Console.WriteLine("Schema not in sync, cannot continue.");
                        Environment.ExitCode = 87;
                        return false;
                    }
                    else
                    {
                        Console.WriteLine("");
                        Console.WriteLine("Invalid input, expected (y)es or (n)o, try again...");
                    }
                }
                while (selection != ConsoleKey.Y);
            }
        }
        else
        {
            //DB schema doesn't exist, create new one
            CreateSqlSchemaDefinition();
        }

        //Load schema into the DataSet
        eventDA.FillSchema(eventDs, System.Data.SchemaType.Source, "Event");
    }
    else
    {
        //In XML mode load schema into DataSet, XML will be saved straight out of eventDs dataSet
        DataTable dt = eventDs.Tables.Add("Event");

        foreach (DictionaryEntry de in nameToXPathMapping)
        {
            dt.Columns.Add((string)de.Key, typeof(string));
        }

        dt.Columns.Add("EventXml");
    }

    return true;
}

Compiling the Code

This code example requires references to the System.dll and System.Core.dll files. Additionally, it references the System.Data.dll and System.Xm.dll to manipulate the event XML and write out the data to a SQL database.

Share via

Setting up a SQL Database and Writing Event Data to the Database

Example

Description

Useful classes and data structures

Compiling the Code

See Also

Concepts

Additional resources

Share via

Setting up a SQL Database and Writing Event Data to the Database

Example

Description

Useful classes and data structures

Data Base related snippets

Compiling the Code

See Also

Concepts

Additional resources