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

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

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.

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.

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

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

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.

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?

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...