Kevin,

What you´ve found of course is about the only way you can solve the problem in .NET, execute it on the server. However, this really is going backwards compared to the possibility you have in VFP. You´re limiting your options. To Put it blunlty in a .NET solution you´ve got one database engine, The SQL sever on the backend. With VFP you´ve got two or more. The SQL Server on the backend plus the local database engine on each layer. A real powerfull thought isn´t it?

For example. I´ve got a translation table embedded into my executable, to make my application multi lingual. Whenever a resultset needs to merged with the translation table, I can do it on the front end, very easily. Transferring this table to the backend, again as I pointed out before, is making things worse:

1. First of all. A static META data table is best kept on the front end, so you have the best performance. If displaying a form, I need to translate a lot of captions, the resultset needs to be at the front end (Whether a cursor or dataset).

2. OTOH, when merging contents in the database with the translation table, you´ll need to do it on the backend.

3. I don´t like the idea having such tables stored in a backend database since they potentially can be changed, possibly causing problems. In fact it should be totally hidden as I does not have anything to do with the actual buisiness data the application is storing.

Going back to the idea that all DML has to be processed on the backend, indeed is going backwards. In VFP you´ve got a choice, On the backend or the front end, optimizing performance and flexibility. If you read a hardcore magazine about developments in the database world, you´ll discover there is a trend that you´ll process the data where it is best. And believe me when I look at some of the reports I´ve got, generating a large table of data out of numerous other tables in both a record oriented and set oriented way, it gives me the horrors when I have to move all that processing over to the backend. Even SQL server is no match when it comes to data munging especially when record oriented operations come into play. You cannot beat the horsepower of 100 modern desktops processing their reports on the frontend, with a full equiped SQL server running 100 complex reports simultaneously.

As you stated, you were alarmed you could not do this, (while you should have known this at forehand when you move to non data centric language), and found a work arround. Well it is fine you found a workarround, but anyway you put it, it is a workarround with its own problems such as complexity and maintainability, and more difficult to implement.

Don´t get me wrong. I see the direction where .NET is going to. I see a great future for it succesors, but in the current version there is a lack of addressing these kind of problems. In many respects it is going backward from a technical point of view. I think the .NET team already is working on this (As KenL has stated the the .NET is working on implementing VFP features into .NET). The trend is to implement everything with databases. If you look at the specs of longhorn you´ll that the database is going to be a central part.

That beeing said. Look at VFP applications where forms, classes and reports are just tables (META data) build into the executable that drive the application. This is where other languages could learn a lot from, because the database engine in VFP really the key component, as it should (Look at modern ERP/CRM solutions). I´m not sure about .NETs internal implementation, but I would not surprise me they really learned this lesson from VFP.

And actually this really is nothing new. IF I´m not mistaken it was Chris Date, stating that the ideal OS should be based on a database, in the early ´80. Well it is taken up to more than 20 years to really accept this idea, which is a shame. When ever I open the Windows explorer, and waiting for all my folders to show up, or I wait for a file to be found, I every time say to myself: "I´ve got have done this much better with a database solution". Now Longhorn with WinFs is going to address this issues (I Hope), but it is at least 10 years too late. Luckely MS had no real competition....

>But gradually, I realized that all the subsequent joins and data-munging that I had been doing against result sets could be redesigned so that the initial queries/stored procs would bring back all that I needed in the first place.

Ah yes, stored procedures. Another pig. In VFP I seldom use stored procedures, apart from the RI for DBF databases. It sincerely is my feeling that stored procedures are misused for things that are not possible on the client or middle tier. Lots of what you write in stored procedures from an architectual point of view really does not belong there. It belongs in a middle tier or the front end (traditional C/S). Keep in mind that using stored procedures is very back-end specific and clients are not always allowing you to specify stored procedures.

>The key is to re-think the entire data access strategy and what groupings you need to bring back, and how you can leverage stored procs to do this work for you.

You indeed HAVE to rethink your entire data access strategy, but this surely does not mean this is not going backwards, comparing to what you can do in VFP. The process you´ve been through is indeed looking how you can transfer your application logic into .NET, but forgot to think how it idealy should be handled. If you look at it from this perspective, you´re really going backward.

VFP is called a data centric development tool. .NET is not positioned that way. Have you ever wondered why ?


Walter,