>My company has a legacy system running pure VFP8.
>One of our client wants to move to sql server as a back end.
>We thought about rewriting the app using c#.
>But have decided to use the same VFP code and simply adding SQL server capability...... with same table structures.
>
>So, the app can run in full VFP mode. or full SQL SERVER mode.
>
>Can anyone make an argument AGAINST doing such thing?
>
>Thanks in advance..

Food for thought, there is always some space for another viewpoint when such a question arises :) This is going to be a long post, if you can bear reading to the end.

First, I have done it 9-10 years ago and still the newest version of the application works with either VFP tables or SQL server depending on user's choice of backend. Since, all personally, I didn't like views, I have done it with CursorAdapters. All the table operations now run through a few routines like SqlQueryInto, SqlExecuteScalar, SqlNonQuery, SqlInsert, SqlOpenTable ... (maybe less than 10 such routines) + CA classes per table that are based on one CABase class. This all works quite well and application can still expand with little effort on adding new tables, fields etc. The supporting routines and CA classes do their job so good that even you could now take a form with native tables in its DataEnvironment and just by adding a single method call to BeforeOpenTables (namely UpdateDE(this)) make it work against VFP or SQL server data. I thought releasing my work and routines to public, but that would also need documenting it for others and I was lazy. Just to show part of it here is a sample code that I find interesting, it demonstrates handling some backend specific pieces + the need to pass local data to server to make the query itself run on server (typically an "IN" query with say sending a bunch of IDs to server) - code written with extra blank lines just to make it easy to see the parts + it would run with or without those extra lines:

local lcSQL
text to m.lcSQL textmerge noshow

select itemId, startDate, owner,         

<SQL> 
         case itemType
                   when 1 then 'Type 1'
                   when 2 then 'Type 2'
                   when 3 then 'Type 3'
                   else 'Unknown'
          end
</SQL>
<VFP>
         icase(itemType=1 ,'Type 1',
                   itemType=2 ,'Type 2',
                   itemType=3 ,'Type 3',
                   'Unknown')
</VFP>
        AS itemType 
 
from myTable
where 
   startDate > 
       <SQL> getdate() </SQL>
       <VFP> datetime() </VFP> 

   AND endDate < ? 
   
   AND cast( IsCancelled as int ) = 0

   AND customerId in (?)

endtext

local array laIDs[1]
select ID from localCursor into array laIDs && we have a local cursor from some source

* ? in query are parameter placeholders, with parameter values here bound in same order
* Note the use of 
* customerId in (?)
* is bound to an array parameter.

SqlQueryInto(m.lcSQL, "resultsCursor", Date(2018,1,1), @laIDs)  

SqlQueryInto and some helper functions parse and PARAMETERIZE this query according to chosen backend and return the result cursor (in this case it is readonly cursor, as if you did a plain select query in VFP).


Cool IMHO, and thus, I should also say, yes I am not against it and go for it. BUT, there is other side of the coin. This has pros and also cons as well. Pros are obvious, I can still expand the application with VFP and the backend switching is super simple, it is also not limited to VFP and MS SQL server, could also use say postgreSQL, Oracle ...

However, the truth is the transition is not something that you could do overnight. AND it could be a nightmare if your code does too much xBase style data processing, and\or rely on tables in DE (with MS SQL server it is not very realistic to have them in DE, though you could have filtered versions, I wouldn't suggest).

Cons: Time is passing and while VFP's capabilities and understanding of SQL is what it was back in year 2007 (I might be remembering the year wrong) other databases are evolving, many backends are based on ANSI SQL and ANSI SQL itself is evolving. They are supporting more and more data types and new ways of querying the data is emerging. Any query you write against VFP tables is likely to work against MS SQL but not the otherwise. A query with some correlated subqueries for example, would work in MS SQL flawlessly while it is likely to be too complex for the VFP's query engine. When VFP needs to bee supported, you lock yourself to a bunch of datatypes and somewhat limited queries.

An application would also be likely to expand by some external modules, not necessarily written with VFP, and/or it may be on a platform other than windows. That would call for other languages and thus connecting to MS SQL server wouldn't be a problem but the case is different for VFP tables (it also causes 32/64 bit problems).

Along the lines, I would ask, why not a separate VFP application that supports only MS SQL server. The reply would generally be "they can't afford". In practice, most VFP applications' data fit into 10 Gb and MS SQL server express is free up to 10 Gb. Then I also would ask, why MS SQL Server? postgreSQL is the most advanced open source database out there. It works on Linux, BSD, Solaris and Mac too. It is free, has much more cool data types and much more built-in functions, operators ... you name it. You might choose a separate application with postgreSQL.

It is going to be not so easy job, good luck.