Coming from a VFP and VPME background, I am wondering what is the best practice for applications that use SQL Databases.  In VFP applications I normally use Views for Forms and Reports and separate them into two or more databases.  Also, I would try to use Views instead of Filters most of the time.  Is the way I would do it in VFP still the best way for SQL database applications?

- Is it best to separate tables and views into separate databases?  I noticed the Strataflix and Strataframe samples both show views and tables together in the same DB but this may be just for simplification

- Should Maintenance Forms be based on views or tables?

- Should Reports use views or tables?

- When is it best to use a View as opposed to a table or a table with a filter?

- Also, I plan to develop applications using SF that I want to be able to sell just parts of as well as the entire application as a whole.  For example, I'm working on a Safety Review application that could be sold as a complete package or someone might just want to purchase the OSHA package, ADA package, Driver/Employee File package, Vehicle Inspections package, etc.  I might also have a Lite/Express version too for example.

- I'd be interested how others are doing it and what make the most sense in terms of flexibility, especially since I have the opportunity to start from "scratch" so to speak.

Thanks!

Russ Brown (Using C#2010/SQL Server 2008)

We have a long session in our training class that covers a lot of this.  But is short, you do not want your entire application to be view driven.  Views are slower and will have a drastic negative effect on your application if you were to have nothing but views.  Does this mean that you should never create a view? No.  Not at all.  There are times for views, for example reporting, or when you have the need to a more complex situation.  But you can never make views as fast as optimized SPROCS.  So the short answer here is, especially since you are just getting started here, to stay away from views unless an absolute specific need arises. 

Let me put it like this, in our medical application we have 250+ tables in one database, very large databases (millions of records in single tables) extremely complex real-time calculations, and through all of this, we never use a view.  We do have one or two views for RBS security, but past that, it is all tables, sprocs, and UDFs.

Trent is right.

I came from the same VFP/VPME background and while it may work on the principle that virtually every database access is through a view, the same (thankfully) is not the same with SF and .NET.

When I initially looked at SF I was nervous of losing the xCase2VPM view generator but I have never looked back since. I now have a large VPME application converted to SF and there are only 6 views now when there were more then 700 in VPME. Data access is so much easier, more flexible and faster than with VPME. I really can't say how much better SF's data access is over VPME/xCase2VPM.

The best advice I can give you is to forget about VFP/VPME when working with SF. Views are for special occasions - eventually you will know if and when you need one.

If you want to talk more about this feel free to contact me.

Hey Russel.

I think that a good time to use a view is when you need to use the BrowseDialog to search on different tables at the same time. So, if you have a form with a Parent BO and 1 or more Children BOs related, you could create a view to join all these tables and format the BrowseDialog to search on that view, so the user would get all the columns needed from the different tables. Once the BrowseDialog is Closed, you can deal with the view copying the data from the populated BO to another without duplicates on the Parent PK, get an array of the PKs populated and Fill your form Parent BO with the array of PKs you've just created.

I have found this a great way to extend the use of the BrowseDialogs.

Que tal,

Sería factible que pusieras un ejemplo de tu proceso, la parte del View la puedo manejar pero el proceso de filtrar duplicados y demás no se me ocurre como hacerlo.

Gracias,

Everything is possible, just keep trying...

Hola Juan.

Si, prepararé un ejemplo para usted y pondré aquí.

Aha, Ivan tu eres como una cajita de sorpresa, no sabia que hablabas y escribes en Español.

Edhy Rijo

Here you are.

• Get your view ready with the Parent and all its children, remember to have the parent PK as a column in it.
• Create a new BO in your application and use the Business Object Mapper to map it. Remember that if you are used to mapping selecting the DDT as a source, with a view you will need to go straight to the SQL Server, so deploy it first, and then map it.
• Rebuild your project and drop 2 instances of the view BO on your form.
• Drop a BrowseDialog and configure it to this BO type, use one of the instances of the BO as the BusinessObjectToPopulate.
• Configure your BrowseDialog as usual. SearchFields, BrowseResultsLayout and so on.
• Go to your BrowseDialog Events and create one for BrowseDialogClosed.

In it, put some code like the following:

That should be it.

Hey Edhy.

I can read it, but not really write it. I asked for help over here...

Hi Juan.

I read the code I posted to you and noticed I didn't code some important features in it, so I corrected a bit: