>>We are planning to rewrite a VFP application which uses DBFs to .NET and SQL Server. The system is extremely large and is comprised of a large number of subsystems invoked from a main PRG and menu. It is not considered feasible to convert everything at once. Instead the plan is to rewrite a piece at a time as resources and budget become available. Has anyone done something like this? To me it seems like the data is going to have to reside in both VFP and SQL Server until everything is converted. Any tips on good ways to do that?
>>
>>Thanks in advance.
>
>There isn't a simple/easy/straightforward way to do this (which I'm sure you already knew <g>). From a .NET perspective you're going to be much happier if the data is in SQL Server. Even worse, generally there isn't even one approach that works for all modules. While you can use VFP tables from .NET it's not nearly as nice/straightforward as using SQL and you'll probably end up giving up a lot of things from the .NET side to get it to work (and realize that the VFP OLE-DB driver isn't always the most stable thing in the world). For example, LinqToSQL or Entity Framework. nHibernate may be worth a look, although I don't have experience with using it for DB integration with VFP.
>
>Ideally, you'd migrate the existing system to SQL Server first, then work on converting the application into .NET only once that has been completed. If that can't be done, here are some ways you can approach the problem:
>
>- For modules that have fairly isolated sets of data, do a straight conversion of the data into SQL Server and modify the existing VFP code to use the data their (views, etc.). Then you can rewrite the module in .NET.
>- Write new modules in .NET, read/write what data you can into SQL Server and use VFP via COM to pass XML back and forth for everything else. This works OK but getting it all installed and debugging is a pain. Basically try to start using SQL Server for everything new. If the old system needs access to it, call a COM component which keeps it in sync.
>- In some places you can also use triggers in SQL Server (with your existing data connected as a linked server) to sync changes back to VFP.
>- Write batch routines which convert some of the VFP table data into SQL Server (or vice-versa) on a regular basis for .NET modules that don't need the absolute latest data (or for data that doesn't change very often; you can also kick off the batch conversion from VFP at key points).
>- Modify the VFP code to make changes to both the VFP tables and then you mirror those same changes in SQL Server.
>- I'm sure there are others I'm missing
>
>Resist the urge to modify the database structure in SQL Server (ex. "Let's clean this up and normalize it since we have to convert it anyway"). Unless it's a new module, use the existing (usually really ugly) tables as-is in SQL Server (well, you may need to add primary keys if it doesn't already have them). Having to deal with two different schemas while doing a in-place write gets hairy quickly.
>
>Expect the conversion to take a long time.

Great stuff, Paul. Thanks.

I'm pretty sure we'll want the .NET modules to work directly with SQL Server from the start. What we will need is a way to mirror those data changes back to DBFs which are still in use on the unconverted VFP side.

I don't know if modifying "unconverted" VFP modules to use remove views of SQL Server is going to fly. The recoding to RVs and regression testing would not be trivial. I don't sense any enthusiasm for doing further work on the VFP code other than keeping it running until they can take it out back and bury it.