StrataFrame Forum » StrataFrame Application Framework - V1 » WebForms (How do I?) » Need to create a BO for multiple tables

Need to create a BO for multiple tables

Doron Farber — Sat, 14 Feb 2009 17:08:18 GMT

Hi There,

1)
I need to search on multiple tables and the data from 20 TO 70GB

Since the data is pretty big I found out the best thing is to use a dynamic sql statement where I define on the fly the parameters to be used. I can NOT use a view with constant number of parameters since it will be too great of an impact on the execution plan.

I am trying to see how can I use a BO under this condition. For instant if the first sql gets 0 records, then in the same SP I will pursue with another SQL statement to grab from different set of tables.

How do you assign a BO to this kind of process.

2)
I need to use some audit trail. Like if the user enters FirstName, LastName and State for search purpose. I need to enter that as a record for an audit trail and then the results of the search as well. It could be up 100 records. Obviously that could be as a simple Insert.

How is the audit trail is coming along in the SF? Is that something that I can use?

Regards,

Doron

RE: Need to create a BO for multiple tables

Doron Farber — Sat, 14 Feb 2009 17:08:18 GMT

Hi Trent,

Thanks for the informative answer.

Regards,

Doron

RE: Need to create a BO for multiple tables

Trent L. Taylor — Thu, 12 Feb 2009 13:53:05 GMT

Doron,

This is exactly what I had expected to see when I was posting my previous responses. Here is what you need to do. Create a SPROC that will retrieve all of the results that you are wanting to see, even if they come back in more than one result set.

Let's keep this simple. Two tables:

Customers
CustomerPhoneNumbers

Now I want to show in the list you provided all of the phone numbers in a flat list. So the first thing I would do is get the query the way I like it, create a BO for each that may be used here (i.e. CustomersBO, CustomerPhoneNumbersBO). Then I might treat this like a report if this is going to be for display purposes and then add a link (or whatever mechanism you intend to use) to an editing page if they want to make changes. So in this case, I might inherit the CustomersBO and add all of the custom properties that I need for this display while not "messing up" the original BO since these custom properties will not be used everyone.

[codesnippet]Public Class MyCustomBo
Inherits CustomersBO

Private Shared _PhoneNumbers As New CustomerPhoneNumbersBO()

    Public Readonly Property PhoneNumbers() As CustomerPhoneNumbersBO
          Get
               Return _PhoneNumbers
          End Get
    End Property
End Class[/codesnippet]

So now when I call my stored procedure that retrieves all of my result sets, I will dump it into this BO like this:

[codesnippet]Private _MyCustomBO As New MyCustomBo()

Private Sub LoadData()
Dim cmd As New SqlCommand("dbo.MySproc")

cmd.CommandType = SqlCommandType.StoredProcedure

MicroFour.StrataFrame.Business.BusinessLayer.FillMultipleDataTables(cmd, _MyCustomBO, _MyCustomBO.PhoneNumbers)
End Sub[/codesnippet]

So now I have a single entry point with two BOs. This is the same type of logic you would use for a report. So now, go one further and create a custom property to show all of the phone numbers on the entry point BO:

[codesnippet]Public Class MyCustomBo
    Inherits CustomersBO
    Private Shared _PhoneNumbers As New CustomerPhoneNumbersBO()
    Public Readonly Property PhoneNumbers() As CustomerPhoneNumbersBO
          Get
               Return _PhoneNumbers
          End Get
    End Property

    Public Readonly Property My_PhoneNumbersAsHtml() As String
        Get
            Dim r As String = ""

            For Each bo As CustomerPhoneNumbersBO in _PhoneNumbers
                 r &= bo.PhoneNumberAsText & "<br>"
            Next

            Return r
        End Get
    End Property
End Class[/codesnippet]

This should get you going in the right direction.

RE: Need to create a BO for multiple tables

Doron Farber — Mon, 09 Feb 2009 13:52:37 GMT

Hi Trent,

Still waiting for your reply.

Regards,

Doron

RE: Need to create a BO for multiple tables

Doron Farber — Mon, 02 Feb 2009 09:24:04 GMT

Hi Trent,

I tried your suggestion using WinForm as a test and it worked just fine and thanks for that.

But in order to use a BO I had to map it at least to one table. I used this code: e.BusinessObject.CurrentRow.Item("MyField") for the list view. But I also need to do a lot of manipulation with the text, so I think if I have a BO with all fields involved in the query it could be easier. One thought is to use a dummy table with all fields involved in query and use that as the BO of the search, so this way I will have the Strong Type capability to process all fields.

Please advise if you have another or better idea for above.

I also would need to create a listview as shown in the below image. Do you know of any control that I can have such a look while I can create links within a cell etc... Or maybe there is a way of doing so using the SF ListView..

Please see this link to get the idea of how it has to be displayed: http://www.dfarber.com/joe_smith_data.png

Thanks,

Doron

RE: Need to create a BO for multiple tables

Greg McGuffey — Tue, 27 Jan 2009 10:35:20 GMT

wow...I'll give that a try! :D

RE: Need to create a BO for multiple tables

Trent L. Taylor — Mon, 26 Jan 2009 15:42:30 GMT

Just type it in. This is a combo box, not a drop down list, so you can type in whatever you want and it will try to use that field name. If that field name doesn't exist...BOOM! :D

RE: Need to create a BO for multiple tables

Greg McGuffey — Mon, 26 Jan 2009 11:06:02 GMT

I'm not quite sure if I understand the first method you indicated. When you say:

[quote]You would add the column on the left with the name of the column that has been returned in the query. You could then populate the column with a formatted value using the {index}.[/quote]

If the column isn't a strongly typed property, it doesn't appear on the left, so how do you do this? Is it just done in code?

RE: Need to create a BO for multiple tables

Doron Farber — Mon, 26 Jan 2009 10:15:15 GMT

Hi Trent,

Thanks for your reply and let me try that and see how it comes...

Regards.

Doron

RE: Need to create a BO for multiple tables

Doron Farber — Mon, 26 Jan 2009 10:13:28 GMT

I hope you understood that too Mr. Rijo.;)

RE: Need to create a BO for multiple tables

Edhy Rijo — Mon, 26 Jan 2009 09:50:24 GMT

Thanks for clearing this up, I just hope Doron can also understand this :w00t:

RE: Need to create a BO for multiple tables

Trent L. Taylor — Mon, 26 Jan 2009 09:25:01 GMT

[quote] we have to enter the Field Name of the returned query? [/quote]

You would add the column on the left with the name of the column that has been returned in the query. You could then populate the column with a formatted value using the {index}. You could also use the populated through even option and populate the column from within the RowPopulating method and use the e.BusinessObject.CurrentRow.Item("MyField") option as well.

RE: Need to create a BO for multiple tables

Edhy Rijo — Sun, 25 Jan 2009 18:48:43 GMT

[quote][b]Trent L. Taylor (01/25/2009)[/b][hr]It relates to a JOINed query. At this point, it is just a matter of creating the query, and then within the list, enter the name of the field in the column that you want to show. You don't even have to create custom properties in this case. The column name just has to be within the result set to prevent an error when the ListView tries to reference that column. Another option, if you so choose, is to create custom properties that will expose these columns. But this is something that we do all of the time within our applications and is very common practice.[/quote]

I am still a bit confused about how to build the listview to show the data from the query. Do you mean that when building the listview instead of using {Index} to set the value we have to enter the Field Name of the returned query? or we have to do this in any of the listview event manually?

RE: Need to create a BO for multiple tables

Trent L. Taylor — Sun, 25 Jan 2009 18:34:58 GMT

Great! So my logic is exactly what you want to do then as this is something that we do every day. We have hundreds of places within our medical software that does this very thing. It relates to a JOINed query. At this point, it is just a matter of creating the query, and then within the list, enter the name of the field in the column that you want to show. You don't even have to create custom properties in this case. The column name just has to be within the result set to prevent an error when the ListView tries to reference that column. Another option, if you so choose, is to create custom properties that will expose these columns. But this is something that we do all of the time within our applications and is very common practice.

RE: Need to create a BO for multiple tables

Doron Farber — Thu, 22 Jan 2009 14:55:15 GMT

Hi Trent,

When it comes to search no need to update the server. I will need to create some SPs that will do as follows: If the user enters First, Last and State and the search yield 100 records which is the Max.

I will store the one record in table which is called: SearchValues which is the parent where I enter the values that the user entered initially, and then in the child will store only the pk of each found record and the table name. I haven't tried that yet, but that's the idea...

That's it when it comes to search.

I will have many types of search and this link may get you the idea: http://www.PeopleFinders.com

Regards,

Doron

RE: Need to create a BO for multiple tables

Trent L. Taylor — Thu, 22 Jan 2009 14:40:02 GMT

Do you want to update back to the server with this same result set?

RE: Need to create a BO for multiple tables

Doron Farber — Thu, 22 Jan 2009 14:37:45 GMT

Hi Trent,

If you look that SP that was attached before it covers 6 tables (for the 2 sql statements) and I need to add more sql statements from other tables as well so this way maybe 12 tables will get involved. If one search in the first data set gets 0 records then it goes to next SQL statement and so forth...

Hundreds of users will be logged into that server and perform search, and then I need constantly to reload too many BOs for one search at time not to mention the over head with all these BOs.

I know that you reduced the foot print of each BO but that also is a concern as well.
I would rather do that in one BO...

Regards,

Doron

RE: Need to create a BO for multiple tables

Edhy Rijo — Thu, 22 Jan 2009 14:25:16 GMT

[quote][b]Trent L. Taylor (01/22/2009)[/b][hr]Why do you need this to be a single BO?[/quote]

Hi Trent,

Because Doron will be using a ListView to show the results, can we use multiple BO sources in a ListView and display fields from each BO in the ListView?

Also, would it be too much of overhead to have 4 BO to show the ListView result?

RE: Need to create a BO for multiple tables

Trent L. Taylor — Thu, 22 Jan 2009 13:26:04 GMT

Why do you need this to be a single BO?

RE: Need to create a BO for multiple tables

Doron Farber — Thu, 22 Jan 2009 10:57:08 GMT

Hi Trent,

Thanks for your reply.

I need to show the resulting data from the attached store procedure in a SF listview, I would like to use one single BO, but as you can see the SP is getting its data from 4 different tables, so I don't know how would I map the single BO to all those fields in the resulting SP cursor, it is this possible or is there any other approach I could use?

FYI, these are big tables with millions of records, so I must use the SP.

Regards,

Doron

RE: Need to create a BO for multiple tables

Trent L. Taylor — Tue, 20 Jan 2009 22:58:24 GMT

1. This is exactly where you would create a stored procedure to return a result set (or more) back to one or more BOs. The issue here is the search. If you want to traverse large data sets with high response, you are going to have to use a number of things in conjunction to make this happen. But the issue is not on the BO side, as you will just call a stored procedure to fill it. The issue is creating the query/queries necessary to accept the parameter(s) and then efficiently execute the search.

If you will recall, Ben gave a nice session during the training class on SQL Server Optimization and SPROCS. This is exactly where that lesson comes in very nicely.

As for the BO, you can map it to a pre-existing table or view or even create custom properties. In most cases that I have ever dealt with, you are generally searching data that represents more than one table in this type of scenario which may split into more than one table. This is fine, you would just make your SPROC return more than one result set and then populate the BOs in question. This is a great place to use the BusinessLayer.FillMultipleDataTables method.

2. You are going to want and do this server side. The SF auditing is mostly in place, but the issue here if you are trying to do verbose auditing is that you are going to impede performance. So in this case, you are probably beter off creating triggers on the SQL side to automatically handle the UPDATE/DELETEs to place the changes into your auditing table. You will want to be careful here too and just keep an eye on optimization so that things don't slow down server side.