Porting from Windows & SQL Server Express to MySQL & Tomcat

Written by Mark Cafazzo, a two-time winner of the Race To Linux porting competition. Mark is an expert C# and Java developer, as well as a Grasshopper enthusiast. Here, he shares his experience porting the open source Blog Starter Kit to Java EE and running the application on Linux using VMWare.

Porting the Blog Starter Kit from Windows & SQL Server Express to Tomcat & MySQL

Download project files.

Consider this scenario: You are an experienced C# programmer who's logged thousands of hours coding Web applications using the Visual Studio IDE. Now, you're facing a new challenge: you've been asked to run an ASP.NET 2.0 application on Linux and migrate the data from SQL Server Express to MySQL. You are free to use any toolkit necessary to deliver an application that looks identical to the original and works the same on Linux as on Windows. And just for fun, you're racing against the clock.

This is the challenge I faced when I entered the Race to Linux 2.0, a .NET programming competition that encouraged developers to explore cross-platform development technologies. In the three-series race, participants downloaded pre-determined .NET applications and ported them to run on Linux. I chose to port the Blog Starter Kit to Java Enterprise Edition using Mainsoft's Grasshopper – a Visual Studio extension that enables .NET developers to cross-compile an ASP.NET Web application project to a Java EE application – and run it under Linux. Here's how I won the Race to Linux 2.0, along with a few lessons I learned along the way.

The Blog Starter Kit

The Blog Starter Kit is an open source ASP.NET 2.0 application developed by Shanku Niyogi that provides a quick way to start developing a personal blog site with Visual Studio. It works out-of-the-box with a small amount of configuration, and has interesting features like a rich text editor for formatting posts, the ability to expose the blog as an RSS feed, and an email notification for posted comments. The application works with the IIS 6 Web server running on Windows 2003, or IIS on Windows XP.

Using Mainsoft's Grasshopper, I was able to port the Blog Starter Kit to run on Java under Linux and migrate the database in less than a day.

Grasshopper: Behind the scenes

If you're a pro at Visual Studio programming you've no doubt invested a significant amount of time and effort to learn your craft. And, if you are part of a development team that uses .NET technology extensively, your company may find it too costly and time-consuming to switch to a new development toolkit like Java. Mainsoft, Developer Edition (a.k.a. Grasshopper) bridges the gap between .NET and Java EE by extending the Visual Studio IDE with a robust code porting toolkit. Before we enter the world of cross-platform development, let's take a brief look behind the scenes of Grasshopper:

When you build an ASP.NET application, the C# compiler compiles the program code into an assembly containing Microsoft Intermediate Language (MSIL). The assembly containing these MSIL instructions is then run by the .NET Common Language Runtime (CLR), which compiles the assembly into a platform-specific instruction set, and executes it. When you convert and build the same project with Grasshopper, the MSIL code is cross-compiled into Java bytecode. Grasshopper rehosts the Mono .NET Framework libraries on Java to deliver key .NET functionality on the Java Enterprise Edition platform. The Mono project is an open source, cross-platform implementation of the .NET Framework, of which Mainsoft is a major contributor. Grasshopper deploys your applications to a Tomcat application server, which it has customized with this Java implementation of the .NET namespaces, and is utilized by the cross-compiled .NET applications running inside it. With very few changes to their applications, Grasshopper enables Visual Studio developers to "hop" into the world of Java.

The right tools

There are several tools you will need during our Linux porting expedition, so I'll introduce them before we get started.

Visual Studio 2008 or 2005 with Visual C# or Visual Basic. The code samples in this article are in C#.
Visual Studio 2005 Service Pack 1: If you are working with Visual Studio 2005, you will need to download and install this service pack. This may take some time, so be patient. You will need to install this upgrade to enable the Visual Studio 2003 Web Application Project model, which is the only project compatible with Grasshopper.
Grasshopper: If you want to make the process of porting your ASP.NET applications to Linux easy, there's nothing like it! Go ahead and install this Visual Studio plug-in so you're ready to start the tutorial.
SQL Server 2005 Express: Although the Blog.mdf data file is all that's needed for the Blog Starter Kit's data store, you will not be able to access it using a database migration tool unless it is attached to a running instance of SQL Server Express.
Microsoft SQL Server Management Studio Express: This tool is a light version of the SQL Server 2005 MMC, and comes in handy for attaching SQL Server Express to the Blog.mdf data file in your project's App_Data directory. You can also follow the instructions to do this using the command line tool.
VMware Server 1.0.3: This virtual machine manager will prove indispensable when testing your ported ASP.NET application on a Linux operating system. You will also need to register for a free serial number.
Apache Tomcat Virtual Appliance: This virtual machine server appliance will be your Tomcat test server in a box. This is an excellent approach when you don't have the time or resources to set up a Linux machine and install Tomcat. Be sure to download the VMware version.
MySQL Virtual Appliance: This appliance comes with everything you need to run MySQL on your machine! Testing with a separate database server ensures that your application can get connectivity regardless of the infrastructure. Again, be sure to download the VMware machine.
MySQL Connector for Java: Once your application has been ported to Java EE, you will need these drivers to connect to MySQL.
Blog Starter Kit sample project : I've already ported the Blog application so you can see what it looks like. It also contains the JDBC driver and database script you'll be using in the tutorial. If you'd like to try your hand at porting it from scratch, there's enough information in this article to help you accomplish this as well.

Prepare to hop!

Let's get started with your porting project. First, we will step through the process of preparing to port the Blog Starter Kit. In order to open the sample project, you'll need to install the Grasshopper extension into Visual Studio. Click on the link in the section above to download and install Grasshopper.

Next, download the Blog Starter Kit sample project, unzip it to a directory of your choice, and then open Visual Studio. Select File > Open > Web Site, and browse to the project folder that you unzipped (BlogWebApplication folder). Once you've loaded the Web application project, look for a file called Welcome.html, which outlines the functionality of the Blog Starter Kit. Follow the instructions for creating an administrative user. This is accomplished by using ASP.NET 2.0's new MembershipProvider. Familiarize yourself with this tool since we'll be using it in the port.

Before you begin the port, it's a good idea to run the Web site to make sure it functions as expected. Use F5 or the play button to run the Web site using Visual Studio's built-in Web server.

I initially found the ASP.NET 2.0 Web site project a bit difficult to get used to. When I realized that the Blog Starter Kit was a Web site project, my first instinct was to convert it to a Web application project. Visual Studio enabled me to convert the Blog Web site into the example Web application project I'll be referencing in this article. Follow these steps if you'd like to try it for yourself:

Create a new Web application project in your Visual Studio solution, and then copy the Web site files into it. Name the project whatever you like.
Right-click the solution, and select Add > New Project. Since we're using C#, select a new ASP.NET Web Application from the Visual C# menu. Delete the default.aspx and web.config files in the new project.
Copy all the files from the root of the Web site project and paste them into the new Web application project. Set the new project as the startup project.
Right-click the new Web application project, and select Convert to Web Application.
(Perform this step only if you are working with Visual Studio 2005:) Once the conversion is complete, you will have to set the Build Action property on all files in the Old_App_Code directory to Compile.
Remove the original Web site project from the solution. Start the Web application, and create some posts via the administration page of the Blog Starter Kit (e.g. http://localhost/BlogWebApplication/admin/default.aspx). Log in using the account you created in Step 1. This step gives you a good idea of how the different elements of the site should look once they are populated with data.

One approach to porting the application is connecting to an SQL Server in a hybrid Windows/Linux network. I chose to change the C# code to access MySQL using the OleDb ADO.NET provider and the MySQL JDBC driver. It turns out that MySQL is a good alternative to SQL Server for .NET applications.

Enter MySQL

Figure 1 – The VMware server console

The Blog Starter Kit uses an SQL Server 2005 Express database file for saving posts, categories, feedback, and other data. To make a complete shift to the Linux platform, we will use the latest version of MySQL, one of the most popular databases used with Linux.

Instead of installing MySQL 5 on your local computer, implement VMware Server and run a virtual MySQL server. This emulates the separate database/Web server infrastructure that is common in many hosted environments. To get started, download VMware Server and run the installer.

If using Windows XP:

When you run the installer, a warning message will appear informing you of potential problems running VMware server on a non-server operating system.

Figure 2 – Loading the MySQL Virtual Machine

You can ignore this message and continue with the install. Another warning will appear about running the Management Interface manually from IIS, but this also does not adversely affect our use of VMware Server.

Download the MySQL virtual appliance to a common folder, which is accessible to your VMware installation. Run VMware server, and select local as the host environment.

In VMware Server Console Home, select Open Existing Virtual Machine (see Figure 1).

Select Browse (see Figure 2).

Navigate to the folder where you downloaded and unzipped the MySQL Virtual Appliance, and select the VirtualAppliancesMySQL.vmx file. The server will load the virtual machine into the management console, and display a details page (see Figure 3).

If you're using a server like Windows 2003 as your development machine, VMware should be automatically configured to map virtual machines to your network. If you're using Windows XP, follow the instructions below.

Figure 3 – The MySQL virtual appliance in the VMware Server inventory

If using Windows XP Pro:

In the details page, select Edit virtual machine settings, select Ethernet from the Device list, and click the NAT radio button. Click OK, and then start the virtual machine using the green start button (see Figure 4).

Next, fire up the MySQL appliance by clicking the start button. What appears is VMware booting up Linux as a hardware-abstracted, virtual server utilizing the same network resources as your host (local) machine. (See Figure 5.)

After starting, if the server does not appear to have obtained an IP address like the one shown in the above screenshot, you will have to follow the Windows XP instructions above. The second-last line of the boot screen refers to the administration Web site for the MySQL appliance. (In the next section, you will need to log in to this site using 'admin' as the username and 'admin' as the password.)

Figure 4 – Setting up the NAT connection

Figure 5 – The MySQL virtual appliance running on VMware Server

Figure 6 – Attaching SQL Express to the database file

Another hop: Migrating from SQL Server Express

Our next task is to migrate the SQL Server Express database to MySQL.

If you haven't already, download and install SQL Server Express and the Management Studio. Once that's done, start Management Studio Express, load the local database instance, right-click the Databases folder, and select Attach from the list (see Figure 6).

On the next screen, click the Add button, browse to the Blog.mdf file located in the sample Visual Studio project's App_Data folder, then click OK once you have selected the file. SQL Express will attach to the data file, which you can now access through the Management Studio. This enables data migration tools to access the Blog.mdf file as a database.

Figure 7 – The MySQL virtual appliance administration page

There are a number of tools you can download and use to migrate the SQL Express database to MySQL. A common approach is to specify the target and source databases, and output a database script to run in MySQL, or synchronize the databases directly from the tool. I recommend using Spectral Core's product – you can use the trial version for this step if you would like to experiment. To make things easy, I have provided a MySQL setup script, which you can run on your virtual MySQL server.

Go to the administration Web site for MySQL that was specified during the MySQL virtual server startup, and click the link to phpMyAdmin (see Figure 7).

Log in to the phpMyAdmin interface as 'root', and leave the password blank. You will be directed to the phpMyAdmin tool, a Web administration interface for MySQL. At the top of the left sidebar, click the icon with the red SQL text. This will take you to a new window where you can enter SQL queries that will run on you MySQL virtual server (see Figure 8).

Figure 8 – The phpMyAdmin home page

Select the Import files tab, click the browse button and go to the blogMySQL.sql script found in the App_Data folder of the example project (see Figure 9).

Click the Go button, and the script will create the blog database and tables. In order to create a new user and assign it access rights to the new database, go back to the phpMyAdmin homepage and select the Privileges link (see Figure 10).

Add a new user to the database (See Figure 11).

Assign the new user global privileges (you can also do this at the database and table level, but this will do for test purposes). See Figure 12.

Figure 9 – Importing script file into phpMyAdmin

Figure 10 – Location of the Privileges link

Figure 11 – Add new database user

Figure 12 – Assigning global privileges

Database dialects

Migrating from one database platform to another can be a daunting task. The original idea of ISO/ANSI SQL, a standard introduced in 1992 for structured query languages, guaranteed portability from one database vendor to another. The reality is that database vendors like Oracle, Microsoft, IBM, PostgreSQL, MySQL, and others have implemented their own flavor of the ANSI SQL language, creating barriers to cross-platform development. You can think of these "flavors" as different dialects that exist within a particular language like Spanish, French, Chinese, or English. It's the same mother tongue, but each dialect has its own nuance.

In light of this, some code changes are required so the application works with the MySQL database. The application's SqlDatabase class (located in the Old_App_Code\Utilites directory) contains methods that use the System.Data.SqlClient provider to access the SQL Express database. In order to use MySQL, the System.Data.OleDb provider will be used instead. As a generic ADO.NET provider, OleDb can run on top of any JDBC (Java DataBase Connectivity) driver including the MySQL JDBC driver.

Now that we're using MySQL in place of SQL Express, the previous way of populating parameters with field values for the INSERT and UPDATE statements will no longer work. Here's an example:

The INSERT statement for adding the Categories is created by first passing the Category object from the ObjectDataSource to the Insert() method:

public void Insert(object item, string tableName) {...

An array is loaded using the GetFields() method, which accesses the database to get the Category table's field names based on the Category object's properties:

PropertyInfo[] fields = GetFields(item.GetType(), tableName);

The SQL is then constructed by iterating through the attributes in the PropertyInfo array:

StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO "); … for (int i = 1; i < fields.Length; i++) { if (i > 1) { sb.Append(','); } sb.Append(fields[i].Name); }

Here is where the parameters are created:

sb.Append(") VALUES ("); for (int i = 1; i < fields.Length; i++) { if (i > 1) { sb.Append(','); } sb.Append("@"); sb.Append(fields[i].Name); } sb.Append("); SELECT @@IDENTITY");

The resulting SQL is as follows:

INSERT INTO Categories (Name, Description) VALUES (@Name, @Description); SELECT @@IDENTITY

This is in turn passed into the CreateCommand object on which the ExecuteScalar() method is executed:

private object ExecuteScalar(string commandText, object[] parameters) { OleDbConnection conn = CreateConnection(); OleDbCommand command = CreateCommand(conn, commandText, parameters); object returnValue = command.ExecuteScalar(); conn.Close(); return returnValue; }

That was all well and good for SQL Express, but will not be understood by MySQL (especially @@IDENTITY - you might think to use MySQL's LAST_INSERT_ID(), which also returns the last inserted record's ID, but it does not work in this context) and it's simply too much code to re-write for a simple SQL statement. So I thought fast (because time is everything when you're in a race), and resorted to CommandType.Text, and concatenated the proper INSERT statement by hand. This way, I was able to manage the SQL syntax more efficiently, and know that it complied with MySQL-speak:

using (OleDbConnection conn = new OleDbConnection(connectionString())) { conn.Open(); OleDbCommand cmd = new OleDbCommand(@"INSERT INTO Categories (CategoryID, Name, Description) VALUES (LAST_INSERT_ID(), '" + category.Name + "','" + category.Description + "')", conn); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); conn.Close(); }

If you take a look at the code in the BlogEngine class, you'll see where I've made the necessary changes to other data access methods. If you want to experiment with switching back to SQL Express, the code will still be available for you to use. The point is that even though I didn't use all the code in the SqlDatabase class for MySQL database access, Grasshopper was still able to compile it correctly. All you have to do is change the OleDb provider back to SqlClient, and change the connection string to match.

Another change proved necessary for supporting the MySQL DateTime data type. In SQL Express, the DateTime field looks like this:

5/1/2007 12:00:00 AM

This format is supported when using the .NET DateTime data type to populate the corresponding database type. In MySQL, however, a DateTime field is formatted like this:

2007-04-01 12:00:00

If a .NET DateTime value is used to populate a MySQL DateTime column, MySQL registers an exception. To solve this communication breakdown, I added a new property called DatePostedMySQL to the Post class, which returns a formatted string that complies with MySQL's DateTime. Note how the existing DatePosted property is utilized:

public string DatePostedMySQL { get { string _datePostedMySQL; DatePosted = DateTime.Now; StringBuilder sb = new StringBuilder(); sb.Append(DatePosted.Year); sb.Append(@"-"); sb.Append(DatePosted.Month); sb.Append(@"-"); sb.Append(DatePosted.Day); sb.Append(" "); sb.Append(DatePosted.Hour); sb.Append(":"); sb.Append(DatePosted.Minute); sb.Append(":"); sb.Append(DatePosted.Second); string _datePostedMySQL = sb.ToString(); _datePostedMySQL.Replace("{", ""); _datePostedMySQL.Replace("}", ""); return _datePostedMySQL; } }

The ObjectDataSource control

Now that the methods in the SqlDatabase class have been dealt with, let's take a look at the pages that utilize them. If you open the default.aspx page in the root of the project, you'll notice it contains an ObjectDataSource control:

<asp:ObjectDataSource ID="PostsDataSource" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetRecentPosts" TypeName="BlogStarterKit.BlogEngine"> </asp:ObjectDataSource>

The ObjectDataSource is part of the declarative data binding model brought to you by the .NET Framework. The ObjectDataSource TypeName attribute specifies the class containing the method used for populating the page's DataGrid control. The parameters for the ObjectDataSource methods are dynamically set in the default.aspx page's OnLoad event handler. For example, on line 32 of the default.aspx.cs code-behind class:

PostsDataSource.SelectMethod = "GetRecentPosts"; PostsDataSource.SelectParameters.Add(new QueryStringParameter("count", "1"));

The QueryStringParameter method, which is binding to the "count" query string field, will not return a value because there is nothing in the query string at this point. I fixed this bug, and added a new feature: setting the maximum posts to display on the page by adding a key to the web.config, and retrieving its value. I then added the code for retrieving the configuration setting as both a string and an integer to the BlogEngine class:

private string _postsMaxCount = ConfigurationManager.AppSettings.Get("postsMaxCount"); public int PostsMaxCount { get { return int.Parse(_postsMaxCount); } set { _postsMaxCount = value.ToString(); } } public string GetPostsMaxCountAsString() { return PostsMaxCount.ToString(); }

To implement my change and fix the bug at the same time, I modified the code in the default.aspx page's OnLoad event:

BlogStarterKit.BlogEngine blogEngine = new BlogEngine(); PostsDataSource.SelectMethod = "GetRecentPosts"; PostsDataSource.SelectParameters.Add("count", blogEngine.GetPostsMaxCountAsString());

Generate and configure the Java EE project

Figure 13 – Adding a Java reference

Now that the tour of the code changes is complete, you're ready to port the Blog Starter Kit to Java bytecode. Remove the original Java EE project from the solution. Right-click the Visual Studio project and select Generate Java EE Project from the menu. The Generate Java EE Project wizard will appear:

The next screen will give you the option to save the original ASP.NET solution file. Select this option, and continue on to the next screen. Click OK and Grasshopper will hop into action, creating a new Java EE project.

In order to access your MySQL server, the ported Java application will use the MySQL JDBC driver. If it's not already pre-configured in your Tomcat server, add the JDBC driver as a reference to your project. If you're wondering how to add a Java assembly to a Visual Studio project, Grasshopper takes care of this through the tools it has integrated with your IDE. Right-click the References folder in your Java EE project, and select Add Java Reference... from the list (see Figure 13).

Select Browse, and go to the Java References folder in the Visual Studio BlogStarterKit example project. Select the mysql-connector-java-5.0.5-bin.jar file, and click Open (see Figure 14).

Figure 14 – Selecting a JAR file

Grasshopper will process the reference, and add it to the list. Change the "Blog" connection string in the project's web.config to specify this driver, the MySQL server IP address, database, and the login details:

<add name="Blog" connectionString=" JdbcDriverClassName=org.gjt.mm.mysql.Driver; Provider=SQLOLEDB.1; JdbcURL=jdbc:mysql://192.168.91.128/blog; user=<<your username>>; password=<<your password>>" />

Figure 15 - Apache Tomcat running on Windows

Build the Java EE project

The next step is to actually compile the project into Java bytecode. Before the build starts, Tomcat needs to be running since Grasshopper will actually deploy all the necessary files for the Blog Starter Kit to the Tomcat Web application folder. You can start Tomcat from the Mainsoft for Java EE program group (see Tomcat running in Figure 15).

As an added bonus, I have provided an alternative to running Tomcat from the Start menu by utilizing the Pre-Build events tool in Grasshopper. Right-click the Java EE Project and select Properties. Select Build Events from the left sidebar, and enter the following lines in the Pre-build event command line textbox:

cd $(ProjectDir)\Utilities
TomcatInvoker –default

Figure 16 - Pre-build event command line configuration

This runs these command line arguments before proceeding with the compilation process. I created the TomcatInvoker executable as an intermediary console application to run Grasshopper's Tomcat startup file and then exit normally, allowing the build to continue. The problem I encountered with targeting the Tomcat batch file directly was that the process will not terminate until Tomcat has stopped running. Grasshopper will not start building until the pre-build event has exited successfully (see Figure 16).

Right-click the Java EE project, and select Build. Grasshopper will compile the .NET code into Java bytecode using a Java version of the Mono Framework assemblies:

Figure 17 - Grasshopper's Java EE build output window – deploying to Tomcat server.

Switch the startup project in Visual Studio to the Java EE project, make sure that the MySQL virtual machine is running, and start the application in Debug_Java mode. What you see is the Blog Starter Kit home page running in Tomcat. If you want to set a break point and step through some of the code, Grasshopper makes this process transparent by mapping the C# source code to the bytecode currently executing in the Java EE runtime.

The Membership provider

Figure 18 - Set the administrator role.

Do you remember setting up the administrative user and role using Visual Studio's built-in Configuration Manager? Grasshopper has this covered as well. Grasshopper implements its own default Membership provider, and it creates a Derby database that does the same job. Derby is an embedded, pure Java database server, which is now included in your new Java EE project. Select the Java EE project in Visual Studio, and click the Configuration Manager icon to go to Grasshopper's Web Site Administration Tool (WSAT). Add an administrative role and a user for that role. This will enable you to log in to the admin section of the Blog Starter Kit at http://localhost:8080/BlogWebApplication/admin/default.aspx, so you can add posts and categories. Keep in mind that the Administration Tool is a Grasshopper application coded entirely in Java, so it can run on Linux natively, should you need to access it again (see Figures 18 and 19).

Figure 19 - Add the admin user

Your Java EE application needs to know which MembershipProvider to use, so add the following to the system.web section of your project's web.config:

<membership defaultProvider="AspNetDerbyMembershipProvider"/>

We also want to enable remote access to the Grasshopper WSAT, so we can manage our users from a browser running remotely. Mainsoft defined an application setting key that enables remote access for the WSAT, which, by default, is disabled for security reasons. So, add the following entry in your web.config file:

<appSettings> (...) <add key="allowRemoteConfiguration" value="true" /> </appSettings>

Another change that is required for the WSAT is in global.asax. The Blog Starter Kit actually redirects all requests to the post.aspx page. Since Grasshopper WSAT application is packaged within the application itself and not as a separate application, we need to ensure that requests to the WSAT won’t be redirected to the post.aspx page. Therefore, we add in the global.asax another condition before proceeding to the redirect, just to ensure that the request is not to the WSAT application:

if (!File.Exists(physicalPath) && physicalPath.EndsWith(".aspx", StringComparison.InvariantCultureIgnoreCase) && !physicalPath.Contains("aspnetconfig"))

Running the Blog Starter Kit in Linux

Figure 20 - Java build properties

The next step is to create the Java deployment package. Stop the running application by pressing the stop button, or closing the browser. Switch the project to Release_Java, and then go to the Java EE project's properties page. Select Java Build from the left-hand menu, and change the Deployment method to Full deployment package (WAR). Then build the project once this property has been set (see Figure 20).

Now for the fun part: running the starter kit on a Linux server. Follow the same steps outlined for the MySQL virtual appliance to download and install the Tomcat appliance in the VMware Server. Start the Tomcat virtual appliance, and go to the Tomcat administration site as specified in the Linux command prompt. Log in to the Tomcat administration section as 'admin' with 'admin' as password (if needed, check the Virtual Appliances download page for the up-to-date login details), and scroll to the bottom of the page. In the Import section, browse to the WAR file that Grasshopper generated in the project's bin directory and select it (see Figure 21).

Tomcat will install the Blog Starter Kit, and provide a link to the site (see Figure 22).

The outcome is the Blog Starter Kit running on the Tomcat virtual appliance (see Figure 23).

There you have it – a complete port of an ASP.NET 2.0 application that began in .NET running on Windows and SQL Express, and ended up being served up by a Linux web server and MySQL! I hope this experience has added some hop to your development step, and better enabled you to enter the world of cross-platform .NET development.

Figure 21 - Deploying the WAR file

Figure 22 - The link to the Tomcat-hosted Blog site

Figure 23 - The Blog Starter Kit homepage running in Linux