R – SubSonic and Stored Procedures

stored-proceduressubsonic

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?

Best Answer

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

Related Solutions

C# – data access layer – subsonic, C# 2.0

What are you planning to do with the Datasets that you cannot do with the ILists? IMHO, you'll want to be strongly typed as much as possible.

On my team, we connect our middle tier to ObjectDataSources and then all of our GridViews, ListViews, etc... use them to fetch data:

Business Layer / Middle Tier

namespace Project
{
    public class BusinessLayer
    {
        public IList<Product> GetProducts()
        {
            return new Select().From( Products.Schema ).Where( Products.Columns.Status ).IsEqualTo( true ).ExecuteTypedList<Product>();
        }
    }
}

On Page

<asp:ObjectDataSource id="odsProducts" runat="server" TypeName="Project.BusinessLayer" SelectMethod="GetProducts()"></asp:ObjectDataSource>

Then from there, you can connect any of your data-view controls (Gridview, Listview, etc...) to the data source. It's very clean and requires no code in the code-behind.

I also came across that article by Rob that you posted and have found it helpful in trying to figure out how to write de-coupled apps with SubSonic.

Sql – Insert results of a stored procedure into a temporary table

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Best Answer

Related Solutions

C# – data access layer – subsonic, C# 2.0

Sql – Insert results of a stored procedure into a temporary table

Related Topic