StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Best Practice to Use Views or Tables with SQL Databases and BOs

Best Practice to Use Views or Tables with SQL Databases and BOs

Russell Scott Brown — Wed, 05 Nov 2008 04:26:17 GMT

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!

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Aaron Young — Wed, 05 Nov 2008 04:26:17 GMT

I still have to use (but not for much longer :)) VPME and xCase2VPM and I will not miss either. I will certainly not miss the quirks or the extremely tedious view generation process that takes forever to run for a simple change :) Or the massive list of views which contain many more fields than I actually want to query or the generation of "SELECT *" type statements which causes VFP to throw an error when the field order changes.....

To be honest, I was worried initially about SF as you code your own queries and I assumed this would be hard work. However, after using SF, it is a MUCH better approach and is extremely easy to do. Now the SF version of my VPME application is only quering the fields I need rather than some expanded list of fields created in a view generator.

I have been using SF every day now for months and I haven't found anything yet to grumble about :) I am really angry with myself for not finding it years ago but I am determined to find something wrong with it so I can grumble :) However, I suspect it will be a spelling mistake somewhere rather than a design flaw.

The best advice I can give anyone coming from a VPME background to SF is to totally forget about VPME and views. SF is in a different league and comparisions are pointless.

Just my 18 cents worth (sorry inflation).

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Edhy Rijo — Mon, 03 Nov 2008 16:21:41 GMT

[quote][b]Russell Scott Brown (11/03/2008)[/b][hr]Thanks. I am not going to miss using Views, that is for sure.[/quote]

Humm, don't think so. :) I still use VPME with the PSP Libraries and xCase2VPM which takes care of creating all needed views for me, and even though I have been working with views for a long time, so far I don't miss anything from VFP while working with .NET/SF.

I do miss some VPME features I was used to, like the form's builder and others but, I am pretty comfortable with the way things are going between me and SF/.NET.

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Russell Scott Brown — Mon, 03 Nov 2008 12:39:55 GMT

Thanks. I am not going to miss using Views, that is for sure.

I definitely plan to go with SF for the future. VFP/VPME for only those apps already developed but over time even those will use SF.

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Russell Scott Brown — Mon, 03 Nov 2008 12:35:44 GMT

Thanks for all the very helpful comments.

I am glad I asked!

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Ivan George Borges — Sat, 01 Nov 2008 07:05:39 GMT

Hola Juan.

Me alegro de haber ayudado. :cool:

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Juan Carlos Pazos — Fri, 31 Oct 2008 20:28:30 GMT

Ivan

Thanks a lot for the sample. I find it very usefull.

I'm sorry for wrote in spanish, I just don't think and wrote as come to my mind. Glad to see that you can understand me.

Kindest regards

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Ivan George Borges — Fri, 31 Oct 2008 16:58:54 GMT

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:

[codesnippet]

    Private Sub MyBrowseDialog1_BrowseDialogClosed(ByVal e As MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogClosedEventArgs) Handles MyBrowseDialog1.BrowseDialogClosed
        '-- check if the BrowseDialog was canceled by user
        If e.Reason = MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogClosedReasons.UserCancelled Then
            Exit Sub
        End If

        '-- establish locals
        Dim lnCount As Integer
        Dim lnChosenPk As Integer

'-- get the chosen row PK
lnChosenPk = Me.MyViewInfoBO1.myview_pk

        '-- copy the data from the populated BO to another one without the duplicates
        '-- (I used to do something a lot more complicate at this point, thanks to Paul for the next line)
        Me.MyViewInfoBO2.CopyDataFrom(Me.MyViewInfoBO1.CurrentDataTable.DefaultView.ToTable _
          (True, New String() {"myview_pk"}), _
           MicroFour.StrataFrame.Business.BusinessCloneDataType.ClearAndFillFromCompleteTable)

'-- get the number of unique rows
lnCount = Me.MyViewInfoBO2.Count()

'-- establish an array dimensioned with the number of rows in the BO
Dim arPk(lnCount) As Integer

        '-- populate the array of PKs, so we can use it to populate the form BO with the found records
        For Each row As MyViewInfoBO In MyViewInfoBO2.GetEnumerable()
            arPk(MyViewInfoBO2.CurrentRowIndex) = MyViewInfoBO2.myview_pk
        Next

        '-- fill the Parent BO given the array of PKs
        Me.MyParentBO1.FillByPrimaryKey(arPk)
        '-- position the chosen row
        Me.MyParentBO1.NavigateToPrimaryKey(lnChosenPk)
    End Sub

[/codesnippet]

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Ivan George Borges — Fri, 31 Oct 2008 15:18:23 GMT

Hey Edhy.

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

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Ivan George Borges — Fri, 31 Oct 2008 15:16:27 GMT

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:

[codesnippet]

'-- get the number of unique rows
lnCount = Me.MyViewInfoBO2.Count()

'-- establish an array dimensioned with the number of rows in the BO
Dim arPk(lnCount) As Integer

        '-- fill the Parent BO given the array of PKs
        Me.MyParentBO.FillByPrimaryKey(arPk)
    End Sub
[/codesnippet]

That should be it. ;)

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Edhy Rijo — Fri, 31 Oct 2008 15:11:03 GMT

[quote][b]Ivan George Borges (10/31/2008)[/b][hr]Hola Juan.

Si, prepararé un ejemplo para usted y pondré aquí.[/quote]

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

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Ivan George Borges — Fri, 31 Oct 2008 14:53:48 GMT

Hola Juan.

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

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Juan Carlos Pazos — Fri, 31 Oct 2008 13:37:03 GMT

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,

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Ivan George Borges — Fri, 31 Oct 2008 09:02:11 GMT

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.

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Aaron Young — Fri, 31 Oct 2008 06:25:08 GMT

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.

RE: Best Practice to Use Views or Tables with SQL Databases and BOs

Trent L. Taylor — Thu, 30 Oct 2008 21:36:49 GMT

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.