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?

score 5 · Answer 1 · answered Oct 23 '08 at 01:27

5

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());

answered Oct 23 '08 at 01:27

John Sheehan

77,456
30
160
194

score 3 · Answer 2 · edited Jan 09 '18 at 10:09

3

ExecuteTypedList<> is your best friend in this case:

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

edited Jan 09 '18 at 10:09

nkr

3,026
7
31
39

answered Apr 04 '09 at 19:03

1

the top answer here doesn't work in SubSonic 3, the above does. Just for info. – Liam May 31 '12 at 16:12

score 2 · Answer 3 · answered Oct 23 '08 at 01:30

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.

score 1 · Answer 4 · answered Dec 09 '08 at 01:46

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.

score 0 · Answer 5 · answered Apr 04 '09 at 10:45

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)()

it's a lot like link2sql. it "may" not be easier, but it's easier to read, and it's strongly typed. — Rick Rat, May 20 '09 at 11:46

SubSonic and Stored Procedures

5 Answers5

Linked