StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » Mapping a BO to a Stored Procedure...

Mapping a BO to a Stored Procedure...

StarkMike — Mon, 08 May 2006 16:04:42 GMT

I want to fill a listview using a stored procedure. I like the simplicity of the business object method, however I'm not sure you can base a BO on a stored procedure. I have it working...kind of. I created a view that is identical to the stored procedure so that I can create a BO based on the view and then I call the stored procedure in the fill method of the BO class. Am I able to base a business object on a stored procedure or could you suggest a better way? I don't necessarily want to create a view for every stored procedure that I want to base a BO on.

Thanks

RE: Mapping a BO to a Stored Procedure...

StrataFrame Team — Mon, 08 May 2006 16:04:42 GMT

No, that's really considered the best practice since it's the dynamic way of doing it. It becomes a maintenance nightmare if you have to create a new view for each stored procedure...

RE: Mapping a BO to a Stored Procedure...

StarkMike — Mon, 08 May 2006 14:22:10 GMT

So then, theoretically I could use one BO and fill one or more listviews using one or more stored procedures by piggybacking off that BO.

Is that considered best practices or would you suggest a better way to fill listviews based on stored procedures?

RE: Mapping a BO to a Stored Procedure...

StrataFrame Team — Mon, 08 May 2006 09:20:23 GMT

The piggy backing won't affect the operation of the business object... when the ListView builds the list, it uses the "Item" property on the business object to pull the data from the business object by field name. The item property goes through this methodology:

1. Search for the fieldname in the existing PropertyDescriptors collection. If found, return the value.

2. Search for the fieldname by reflecting the business object for the property name. If found, add a property descriptor, then return the value.

3. Search for the fieldname within the columns of the internal data table. If found, add a property descriptor, then return the value.

So, the only thing you're going to have is a few extra property descriptors in your PropertyDescriptors collection (which isn't a bad thing). And as you can see, once a field is found, the location of the data for that field name is cached, so the Item property doesn't have to go searching for it again. So, you have a couple microsecond delay when you reference the column the first time, but after that, it's just as fast as anything else.

This functionality was added mainly because when we build ListViews, we invariably end up adding extra columns to the return data for display purposes; generally the results of aggregate functions in SQL Server. I.e.: we create a list of the customers in the database, and add a cust_count field that counts the users by last name (COUNT(cust_lname) AS cust_count). When we do this, we can manually type in cust_count and don't have to create a view or remap the business object.

RE: Mapping a BO to a Stored Procedure...

StarkMike — Mon, 08 May 2006 09:07:20 GMT

So you're saying... create a method in the BO that executes the stored procedure I want to gather data from. Then, in the PopulationDataSourceSettings choose the BO that contains the method and choose the method to execute from the drop down. Then in the fields to display drop down i can manually type in the names of the fields and it will pull them from the table. Is that right?

If I understand what you're telling me then I can just piggy back off any other BO to get the job done. How does this piggy back affect the normal operation of the BO.

This is what the Data Retrieval region will look like in the BO.

[code]
Public Sub Fill()
Me.FillDataTable("SELECT * FROM Customers")
End Sub

Public Sub FillOrdersByCustomerID(ByVal CustomerID as Integer)
'--Establish locals
Dim loCmd As New SqlCommand()

'--Build the query
loCmd.CommandText = "uspGetOrdersByCustomerID"
loCmd.CommandType = CommandType.StoredProcedure

'--Add the parameter
loCmd.Parameters.Add("@CustomerID", SqlDbType.Int)
loCmd.Parameters("@CustomerID").Value = CustomerID

'--Execute the command to fill the business object
Me.FillDataTable(loCmd)
End Sub
[/code]

Is the FillOrdersByCustomerID going to affect the other Fill method? They are both using the same method... FillDataTable.

RE: Mapping a BO to a Stored Procedure...

StrataFrame Team — Mon, 08 May 2006 08:50:17 GMT

We are planning to eventually allow you to build a business object off of a stored procedure, but at the moment, we don't have that functionality.

RE: Mapping a BO to a Stored Procedure...

StrataFrame Team — Mon, 08 May 2006 08:49:35 GMT

If you are using the results of the stored procedure to create the list view, you can actually use any business object you want to. Here's how it works. When you set the PopulationDataSourceSettings for the list view, you'll notice that when adding a new Display Field, you have the drop down list of the available fields, however in that drop down list, you can type anything you need into the blank. So, you can use any business object, regarless whether is has the same structure as the stored procedure results, and manually type in the Display Fields. When the list builds, the list will determine that the fields do not belong on the business object, and will pull them from the internal DataTable (even though the properties don't exist). So, you can build ListViews all day long without a business object that matches the structure, however, if you want to work with the results of the stored procedure in code (unlikely, since you cannot save your changes to it), you won't have the strong typed properties to work with.

Hopefully that makes sense :)