Infobright Statistics Viewer - MVVM

• Download WPF_Compression_Viewer.zip - 91.7 KB

Introduction

This .NET WPF application connects to an Infobright data warehouse database and displays compression statistics along with other useful information. The code follows the MVVM design pattern.

Here's what it looks like:

Once connected, the main window is displayed.

Table statistics are on the left. Column statistics are on the right. You can change databases using the drop-down menu.

Required software

• Visual Studio 2010
• .NET Framework 4
• ADO.NET driver for MySQL

In order to run the program in Visual Studio, you must download and install the ADO.NET driver. Once installed, you must add a project reference to MySql.Data. Now you should be able to build and execute the program.

A diagram of the views

Below, you will see the program tree along with a diagram of the views and their corresponding view-models.

You may notice that the ConnectDialog view is not shown. The DatabaseConnectionViewModel corresponds to this view.

Source code

There is a lot of code, so I will try to focus on the more interesting and/or confusing parts.

BrighthouseStatistics.xaml.cs

This is the main logic when the program starts. When the control is loaded, a new ConnectDialog window is created. If the connection is successful, the MySQL connection is passed to our view-model and focus is brought to the main window.

public partial class BrighthouseStatistics : UserControl
{
    private BrighthouseStatisticsViewModel _viewModel = null;
    public MySqlConnection MySqlConnection { get; set; }
    public BrighthouseStatistics()
    {
        InitializeComponent();
    }
    private void UserControl_Loaded(object sender, RoutedEventArgs e)
    {
        if (DesignerProperties.GetIsInDesignMode(this))
        {
            return;
        }
        else
        {
            this.IsEnabled = false;
            Window dialog = new ConnectDialog();
            dialog.Owner = Window.GetWindow(this);
            if (dialog.ShowDialog() == true)
            {
                DatabaseConnectionViewModel databaseConnectionViewModel = 
                    (DatabaseConnectionViewModel)dialog.DataContext;
                MySqlConnection = databaseConnectionViewModel.MySqlConnection;
                _viewModel = new BrighthouseStatisticsViewModel(MySqlConnection);
                this.DataContext = _viewModel;
                IsEnabled = true; 
            }
            else
            {
                Application.Current.Shutdown();
            }
        }
    }
} 

ConnectDialog.xaml

As you can see above, the ConnectDialog window has four textboxes where the user can enter login information. The connect button at the bottom of the window is only visible if the textboxes do not have validation errors. You can see how this is accomplished in the XAML below. Later, you will see how validation is implemented in the view-model.

<Button IsDefault="True" Content="Connect" Height="23" HorizontalAlignment="Right" Margin="0,0,8,8" 
    Name="btnTestConnection" VerticalAlignment="Bottom" Width="80" Click="btnConnect_Click">
    <Button.Style>
        <Style TargetType="{x:Type Button}">
            <Setter Property="IsEnabled" Value="false" />
            <Style.Triggers>
                <MultiDataTrigger>
                    <MultiDataTrigger.Conditions>
                        <Condition Binding="{Binding ElementName=tbHost, Path=(Validation.HasError)}" 
                            Value="false" />
                        <Condition Binding="{Binding ElementName=tbUser, Path=(Validation.HasError)}" 
                            Value="false" />
                        <Condition Binding="{Binding ElementName=pbPassword, Path=(Validation.HasError)}" 
                            Value="false" />
                        <Condition Binding="{Binding ElementName=tbPort, Path=(Validation.HasError)}" 
                            Value="false" />
                    </MultiDataTrigger.Conditions>
                    <Setter Property="IsEnabled" Value="true" />
                </MultiDataTrigger>
            </Style.Triggers>
        </Style>
    </Button.Style>
</Button>

ConnectDialog.xaml.cs

When the connect button is clicked, btnConnect_Click is called. This function executes the connect command inDatabaseConnectionViewModel. If the connection was successful, the ConnectDialog window will close. If the connection was unsuccessful, a MessageBox will show a detailed error message.

private void btnConnect_Click(object sender, RoutedEventArgs e)
{
    _viewModel.Connect(null);
    if (_viewModel.IsConnected)
    {
        DialogResult = true;
        this.Close();
    }
    else
    {
        MessageBox.Show(this, _viewModel.ConnectionError, "Connection Error"); 
    }
}

DatabaseConnectionViewModel.cs

This helper function determines if the connection was successful. Notice that we get the actual connection from a function in the Database class (I will talk about this class later). The parameters to this function are member variables of the DatabaseConnectionViewModel which are bound to the textboxes in the ConnectDialogwindow.

private void ConnectHelper()
{
    MySqlConnection sqlConnection = Database.GetSqlConnection(Host, User, Password, Port);

    try
    {
        sqlConnection.Open();

        if (sqlConnection.State == ConnectionState.Open)
        {
            if (IsBrighthouse(sqlConnection))
            {
                MySqlConnection = sqlConnection;
                IsConnected = true;
            }
            else
            {
                ConnectionError = "Failure! This is not an Infobright database.";
                MySqlConnection = null;
                IsConnected = false;
            }
        }
        else
        {
            ConnectionError = "Failure! The database connection could not be opened.";
            MySqlConnection = null;
            IsConnected = false;
        }
    }
    catch (MySqlException mySqlException)
    {
        ConnectionError = "Failure! " + mySqlException.Message;
        MySqlConnection = null;
        IsConnected = false;
    }
}

The validation of the login information is also done in this class using the IDataErrorInfo interface.

#region IDataErrorInfo Members

public string Error
{
    get { throw new NotImplementedException(); }
}

public string this[string columnName]
{
    get
    {
        string result = null;
        if (columnName == "Host")
        {
            if (string.IsNullOrEmpty(Host))
                result = "Please enter a host address.";
        }
        if (columnName == "User")
        {
            if (string.IsNullOrEmpty(User))
                result = "Please enter a user name.";
        }
        if (columnName == "Port")
        {
            if (Port < 0 || Port > 65535)
                result = "Please enter a valid port number.";
        }
        return result;
    }
}

#endregion

Database.cs

The Database class holds several critical functions that interact with the database and generate statistics.

This simple function uses the login information to create the MySqlConnection.

public static MySqlConnection GetSqlConnection(string host, string user, string password, int port)
{
    string connectionString = "server=" + host + ";user=" + user + 
           ";password=" + password + ";port=" + port + ";";
    MySqlConnection sqlConnection = new MySqlConnection(connectionString);

    return sqlConnection;
}

This function gets the Infobright database names and returns them in a list. Notice that the "SHOW DATABASES" query is not used. Instead, we use a query that only includes Infobright databases names.

public static IList<String> GetDatabases(MySqlConnection mySqlConnection)
{
    string query = "SELECT table_schema FROM information_schema.TABLES WHERE ENGINE =
                        'BRIGHTHOUSE' GROUP BY table_schema";

    IList<String> dbNames = new List<String>();

    MySqlCommand command = new MySqlCommand(query, mySqlConnection);
    MySqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        string dbName = reader.GetString(0);
        dbNames.Add(dbName);
    }

    reader.Close();

    return dbNames;
}

Here we have a more complicated function that gets table statistics for a specific database and returns them in a list of lists. Compression is stored in the table comment, so a helper function is used to parse the value.

public static IList<IList<String>> GetBrighthouseTables(MySqlConnection mySqlConnection)
{
    string query = "SHOW TABLE STATUS WHERE ENGINE='BRIGHTHOUSE'";

    IList<IList<String>> tableData = new List<IList<String>>();

    MySqlCommand command = new MySqlCommand(query, mySqlConnection);
    MySqlDataReader reader = command.ExecuteReader();

    try
    {
        while (reader.Read())
        {
            string tableName = reader.GetString(0);
            string comment = reader.GetString(17);
            string compression = GetCompressionFromTableComment(comment);
            string compressedSize = (double.Parse(reader.GetString(6)) / 1048576.0).ToString();

            IList<String> tableStats = new List<String>();
            tableStats.Add(tableName);
            tableStats.Add(compression);
            tableStats.Add(compressedSize);
            tableData.Add(tableStats);
        }
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message);
    }

    reader.Close();
    return tableData;
}

This is probably the most complicated function in the Database class. It calculates column statistics for a given table and returns them in a list of lists. Notice that when the GetCount helper function is called, the connection is cloned. This is because GetCount executes another query and two MySqlDataReaders cannot be open on the same connection. Another important piece of this code is the exception handling. When a MySqlException occurs, the connection can sometimes be closed. In order to prevent other errors in the program, the connection is reopened and the current database is reset.

public static IList<IList<String>> GetColumns(string tableName, MySqlConnection mySqlConnection)
{
    string query = "SHOW FULL COLUMNS FROM " + tableName;

    IList<IList<String>> columns = new List<IList<String>>();

    string currentDatabase = GetCurrentDatabase(mySqlConnection);

    MySqlCommand command = new MySqlCommand(query, mySqlConnection);
    MySqlDataReader reader = command.ExecuteReader();

    try
    {
        while (reader.Read())
        {
            IList<String> column = new List<String>();
            string columnName = reader.GetString(0);
            column.Add(columnName);

            string columnType = reader.GetString(1);
            column.Add(columnType);

            string columnComment = reader.GetString(8);
            string compression = GetCompressionFromColumnComment(columnComment);
            column.Add(compression);

            string count = GetCount(mySqlConnection.Clone(), currentDatabase, tableName, columnName);
            column.Add(count);

            columns.Add(column);
        }
    }
    catch (MySqlException ex)
    {
        MessageBox.Show("An error occured: " + ex.Message);
        if (mySqlConnection.State != ConnectionState.Open)
        {
            mySqlConnection.Open();
            if (currentDatabase != null)
            {
                UseDatabase(currentDatabase, mySqlConnection);
            }
        }
    }

    reader.Close();
    return columns;
}

Code Summary

Using the functions in the Database class, the various view-models use simple CollectionView or ListCollectionView bindings to display the data on the page. Please let me know if you have any questions or comments about any of the code.