Sunday, May 27, 2012

Migrating from Visual FoxPro to .NET

From 1994 to 2004 Visual FoxPro was at the heart of many data oriented business application that ran in Windows.  And for good reason too; it allowed developers to quickly and effectively develop excellent business applications.  But alas, Visual FoxPro is at end of life.  Microsoft is recommending that all enterprise applications be written in .NET.  This is a good thing.  The Microsoft .NET framework is far superior to FoxPro in every way.  Unfortunately, business are still using FoxPro - and I say unfortunately because migrating from Visual FoxPro to .NET can be quite frustrating.

Microsoft has stopped releasing OLE DB and ODBC drivers for 64 bit systems.  Since Windows Server 2008R2 and the up and coming Windows "8" Server (which is available as a free beta right now) don't have 32 bit releases of these operating systems.  That means the need to migrate VFP to .NET can come very suddenly.

Fortunately, it is very possible to migrate each piece of software without suffering from any down-time in your network applications (keep in mind I said "possible", not guaranteed)  as you're building your .NET applications.

If your company does not make use of any virtualization software, now is the time to start.  Covering the benefits of virtualizing servers is not within the scope of this article, here is a link to one that lays out the benefits very well.  What you'll want to do is setup a virtual machine, one for Microsoft Server 2003 (32 bit version).  You won't need to allocate very much space to it, 20 gigabytes is probably sufficient for our purposes here.  It's not going to require much RAM either - 1 gigabyte should be more than sufficient.

Once you get the virtual machine setup, go ahead and install all the updates for it.  This is going to take a couple of hours, so make sure you have something else to do while this is going on.  After all the updates have installed, go ahead and install the Microsoft FoxPro OLE DB drivers.  You can find them here.  After you get the driver installed you only need one more piece of software: Microsoft SQL 2008 SQL Express.  It must be this version, the new 2012 will not work (believe me, I tried.).  Here is a link to the Server, and Management Studio; you will need both.

After you are finished installing the Server and Management Studio Express, what you'll want to do is configure a linked server to the FoxPro database.  I prefer to use scripts rather than the GUI, so here's what I executed:

EXEC master.dbo.sp_addlinkedserver 
@server = N'SERVERNAME', 
@srvproduct=N'', 
@provider=N'VFPOLEDB', 
@datasrc=N'E:\PATH\TO\FOXPRO\DIRECTORY

Please note that the datasource will be the directory of the FoxPro data files, not the actual datafile themselves.  With this configuration, you can call read and write to your FoxPro tables from this SQL database.  The easiest way to do this is with the OPENQUERY function:

SELECT * FROM OPENQUERY(SERVERNAME, 'SELECT * FROM MyFoxProTable')

Well, there you have it!  It's not the most elegant solution out there, but it's not too hard and very inexpensive to go this route.  Good luck in your applications migration!