In this post I will walk you through the creation of a Windows.Forms based user interface using ClojureCLR.
The Setup
To follow along with the blog you will need to have ClojureCLR installed and have access to a MySQL and SQL Server instances. If you haven’t installed it yet follow the directions in my Getting Started with Clojure-clr post. If you need to install one or both of the bases you can download and install them from here: SQL Server Express and MySQL. Once you have downloaded and installing the databases the last step you’ll need to take is to grab the mysql_schema.sql file and run it in your MySQL database. If you are not familiar with MySQL you can run the file from the command line by running this:
mysql –user=username –password=password < mysql_schema.sql
This will create a database and three tables: Master, Schools and PlayerSchools. The three tables will be migrated into a new SQL Server database. These tables are from a database created by the group baseball-databank.org who have collected baseball statistics from the beginning of professional baseball to the present. I find using this data more exciting then the usual demo data. Anyway, once you have the loaded the schema into your MySQL instance you are ready to start building the user interface.
UI.clj
Surprisingly enough the ui.clj file is where the user interface code lives. Here’s what it looks like when it is first started.
I won’t bore you with a line by line analysis of the source but I will show you highlights of the UI creation process, walking you through the steps below. Creating Windows.Forms apps follow the same process without regard to the language that they are being creating in. So there aren't any magic steps here. However, creating the UI in Clojure was a lot of fun.
Step 1. Including the necessary assembly and classes
The first step in creating the UI is to bring in the System.Windows.Forms assembly which I did by calling LoadWithParitalName. Next, I used :require to bring in the classes I needed create the UI with. Since I want to be able to run this app outside of the REPL, I call :gen-class to generate ui.exe.
Step 2. Instantiating the UI Objects
After I have loaded and required everything I need I can start creating objects. As you can see the creating objects is pretty straight forward, all I do is new up all the objects. In the next step is where the configuration of the objects and the event handlers are added.
Step 3. Setting Properties
The snippet above contains a sampling of the property setting code for the title label, the group box, the CheckedListBox and the 'Load Table' button. If you’d like to see the entire file you can do so here. You may notice all the calls to methods with names like set_Text and wonder where the methods come from since you do not see them when you look at the Label class in the Object Browser. In ClojureCLR we access the .NET objects at the CLR 'layer' which is different than the we access them through C#. The set_Text method is the CLR representation of the C# code Label.Text = "Some Text". If you’d like to see what other methods are available you can use the ildasm tool, which is a part of the .NET SDK. Here is an example of some of the set_ methods for the Label class:
Step 4. Adding the UI Objects to the Form and displaying the it
Now that I have all the objects customized I would like to show them to the user. In order to do that I need to add them to the Form object. The Form class has a Controls collection where all of its child objects are stored. I added the objects to the collection by calling the Controls.Add method for each object. When all of the objects have been added I call the ShowDialog method to display the UI.
Step 5. Adding Click Event Handlers to the Buttons.
A snazzy UI is great but if it doesn’t do anything what good is it? Handling the Button.Click events for both of the buttons is where all the action happens. In order to wire up the Button.Click event handlers I needed to use the gen-delegate macro. The macro’s signature looks like this:
gen-delegate
([type argVec & body])
The Load Table Click Event Handler
Here's what the 'Load Table' button's click handler looks like:
In my call to gen-delegate the parameters begin with passing the type System.EventHandler since we want a EventHandler delegate created. The next argument is the a vector which in this case contains the usual Click event parameters: sender and args. The last parameter is where the code of the event handler reside. In this case when the 'Load Table' button is clicked all of the table names from my bdb_post_2010 database are returned and are added to the CheckedListBox. After clicking the 'Load Table' button the app looks like this for me. Your table listing will look different. The SQL File I provide only has three tables.
The tables are retrieved by creating a MySQL connection which is then passed to a function that retrieves the list of tables in the specified database.
The returned lazy sequence I passed through doseq so the tables can be added to the table list. The table names are added by making a call to CheckedListBox.Items.Add method. Once all of the table names have been added I close the database connection.
The Migrate Tables Event Handler
The ‘Migrate Tables…’ button’s event handler does a similar task as the ‘Load Table’ handler does, except it interacts with both MySQL and SQL Server databases.
First it creates a connection to the MySQL database and uses the value from the 'New DB Name' TextBox to create the new database on the SQL Server instance. Next, doseq is called to process the values in the CheckedListBox.CheckedItems collection. Each of the selected table names is passed to mysql/get-columns to retrieve a sequence of maps that contains information about each column in the table. The returned sequence is then passed with the table name and SQL Server database object to sql/create-table which to creates the table in the SQL Server database. After all of the selected tables have been migrated to SQL Server the MySQL connection is closed and a MessageBox is displayed to inform the use that the migration is complete.
db/core.clj and db/sqlserver.clj
Since this is a blog post about creating a UI I will not be delving into the minute details of how the database related code works. However, I do think it is worth giving you a quick overview of the code. The DB code in db/mysql.clj, db/core.clj and db/sqlserver.clj are based on the code I wrote in Intro to Clojure-clr: Connecting to SQL Server and MySQL.
The db/core.clj file contains one function named run-sql which does what you might expect, it executes a query. In my previous database connectivity post I used (.Read reader) to loop through the results of a query but here I've switched to using the System.Data.DataTable class. Using the DataTable class allows me to close the reader right away and retrieve the column names for the query very easily. I used the column names with the zipmap function to map the column name to its values for each of the returned rows. This allows me to return a sequence of maps that represented the results of the query.
The db/mysql.clj file has three methods: get-connection, get-tables and get-columns. Each of these functions does what their names imply. get-tables uses 'show tables;' and get-columns uses 'describe [table name];'.
On the migration side of the app, the db/sqlserver.clj file makes use of the Microsoft.SqlServer.Smo assembly to create the new database, tables and columns. If you want more information on the visit the Microsoft.SqlServer.Management.Smo MSDN Page.
Running the App
Starting the application from the REPL is straight forward. Start the REPL in the same directory that as the ui.clj file. When the REPL is ready enter:
You should then see the UI appear with an empty CheckListBox. Once the app is up and running click on the ‘Load Table’ button. If are used the SQL file I mentioned above you should see three tables: master, schools, and schoolsplayers.
Go ahead and click all three tables. You will need to click on the table once to highlight it and then either click on the check box or press the space bar to put a check in the box.
Next, enter the new database name. My database will be named clr-intro-4. Finally, I kick off the migration click on the ‘Migrate Tables…’ button and within a few seconds you will have a new database with the three tables added. When the migration has completed you will see something similar to the image below.
Summary
Building a UI with System.Windows.Forms in ClojureCLR is straight forward. You only need to bring in the System.Windows.Forms assembly and follow the typical process of creating a Forms based application.
Adding event handlers is as easy as making use of the gen-delegate macro passing it the ‘guts’ of what you want to do when a particular event is fired.
About the Data
The data I’m using for this blog post comes from the Baseball Databank project. The project has gathered all the baseball status from previous season and offers the data in many different formats.
Resources
Previous Posts: Getting Started with Clojure-clr, Intro to Clojure-clr: Connecting to SQL Server and MySQL
Database Downloads: SQL Server Express, MySQL
Data: mysql_schema.sql
My Source: https://github.com/rippinrobr/clojure-clr-intro/zipball/master
More Examples and Information: Another Windows Forms Example, Microsoft.SqlServer.Management.Smo MSDN Page, and Ildasm.exe page on MSDN
