By Doron Farber - 1/19/2009
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
|
By Trent L. Taylor - 1/20/2009
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.
|
By Doron Farber - 1/22/2009
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
|
By Trent L. Taylor - 1/22/2009
Why do you need this to be a single BO?
|
By Edhy Rijo - 1/22/2009
Trent L. Taylor (01/22/2009)
Why do you need this to be a single BO?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?
|
By Doron Farber - 1/22/2009
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
|
By Trent L. Taylor - 1/22/2009
Do you want to update back to the server with this same result set?
|
By Doron Farber - 1/22/2009
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
|
By Trent L. Taylor - 1/25/2009
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.
|
By Edhy Rijo - 1/25/2009
Trent L. Taylor (01/25/2009)
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. 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?
|
By Trent L. Taylor - 1/26/2009
we have to enter the Field Name of the returned query? 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.
|
By Edhy Rijo - 1/26/2009
Thanks for clearing this up, I just hope Doron can also understand this 
|
By Doron Farber - 1/26/2009
I hope you understood that too Mr. Rijo.
|
By Doron Farber - 1/26/2009
Hi Trent,Thanks for your reply and let me try that and see how it comes... Regards. Doron
|
By Greg McGuffey - 1/26/2009
I'm not quite sure if I understand the first method you indicated. When you say:
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}.
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?
|
By Trent L. Taylor - 1/26/2009
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! 
|
By Greg McGuffey - 1/27/2009
wow...I'll give that a try!
|
By Doron Farber - 2/2/2009
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
|
By Doron Farber - 2/9/2009
Hi Trent,Still waiting for your reply. Regards, Doron
|
By Trent L. Taylor - 2/12/2009
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. 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 So now when I call my stored procedure that retrieves all of my result sets, I will dump it into this BO like this: 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 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: 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 This should get you going in the right direction.
|
By Doron Farber - 2/14/2009
Hi Trent,Thanks for the informative answer. Regards, Doron
|