SubSonic and Stored Procedures

Question

When using SubSonic, do you return the data as a dataset or do you put that in a strongly typed custom collection or a generic object?

I ran through the subsonic project and for the four stored procs I have in my DB, it gave me a Sps.cs with 4 methods which return a StoredProcedure object.

If you used a MVC, do you usually use the StoredProcedure object or wrap that around your business logic and return a dataset, list, collection or something else?

Are datasets still the norm or is that replaced by something else?

Answer 1

If the results of the stored procedure has the same schema as one of your tables, you can build a collection using this code (SubSonic 2.1):

ProductCollection coll = new ProductCollection();
coll.LoadAndCloseReader(SPs.GetProducts(1).GetReader());

Answer 2

If my stored procedure returns all the fields from one of the tables for which I have a SubSonic object then I do a LoadAndCloseReader on the result of the stored procedure. If my stored procedure returns data that does not match a SubSonic object then I just work with it as a dataset.

Answer 3

my stored procedures return data from a few different tables and the data is massaged a bit too.

Answer 4

Perhaps return a datareader and then iterate it to populate some custom objects. Alternatively the quick and dirty way (since you're not using domain driven design) create a view in the DB with the same structure as the stored proc, then load the result into your ViewObjectCollection similar to John's code.

Answer 5

You can do data readers, but that's so 1999. Returning objects is a breeze with SubSonic, and easier to use than a data reader. You can retrieve objects like so:

Dim Charts As Generic.List(Of MusicDB.Billboard) = _
    New SubSonic.Select(MusicDB.DB.Repository.Provider, New String() _
    {"Prefix", "Artist", "Track", "ArtistNarrowToken", "TrackNarrowToken", "ArtistId", "TrackId", "TrackYear"}). _
    From(MetadataTagger.MusicDB.Tables.Billboard). _
    Where(MusicDB.Billboard.Columns.ArtistNarrowToken).IsLessThan(10). _
    Or(MusicDB.Billboard.Columns.TrackId).IsNull(). _
    OrderAsc(New String() {"TrackYear"}).ExecuteTypedList(Of MetadataTagger.MusicDB.Billboard)()

Answer 6

ExecuteTypedList<> is your best friend in this case:

IList list=SPs.GetProducts().ExecuteTypedList< Product >();