This project is read-only.

For those interested, I have now modified the SubSonic 2.3 code to recognize and support DataTable parameter types, in order to make use of SQL Server 2008's TVP's (table valued parameters). This is a very powerful data structure that performs better than bulk inserts. You can read a solid review and discussion about the advantages of TVP's here .

More information about SQL Server 2008 features can be found here.

What this enhancement will now allow you to do is to create a partial StoredProcedures.cs class, with a method that overrides the stored procedure wrapper method.

A bit about good form: My DAL has no direct table access, and my DB only has execute permissions for that user to my sprocs. As such, SubSonic only generates the AllStructs and StoredProcedures classes.

The SPROC:

 

ALTER PROCEDURE dbo.testInsertToTestTVP
@UserDetails TestTVP READONLY,
@Result INT OUT
AS BEGIN SET NOCOUNT ON;

SET @Result = -1

--SET IDENTITY_INSERT dbo.tbl_TestTVP ON

INSERT INTO dbo.tbl_TestTVP
   ( GroupInsertID, FirstName, LastName )
   SELECT GroupInsertID, FirstName, LastName
   FROM @UserDetails

IF @@ROWCOUNT > 0
   BEGIN
      SET @Result = 1
      SELECT @Result
      RETURN @Result
   END
--SET IDENTITY_INSERT dbo.tbl_TestTVP OFF
END

 

 

The TVP:

CREATE TYPE dbo.TestTVP AS TABLE(
   GroupInsertID varchar(50) NOT NULL,
   FirstName varchar(50) NOT NULL,
   LastName varchar(50) NOT NULL
) 
GO;



At this point the work on the SQL server is complete.

Next execute the SubSonic code generation tool. Once complete, the following erroneous method is created:

/// <summary>
/// Creates an object wrapper for the testInsertToTestTVP Procedure
/// </summary>
public static StoredProcedure TestInsertToTestTVP(string UserDetails, int? Result)
{
   SubSonic.StoredProcedure sp = new SubSonic.StoredProcedure("testInsertToTestTVP", DataService.GetInstance("MyDAL"), "dbo"); 
   sp.Command.AddParameter("@UserDetails", UserDetails, DbType.AnsiString, null, null);
   sp.Command.AddOutputParameter("@Result", DbType.Int32, 0, 10); 
   return sp;
}



The ORM misinterprets UserDetails as type string, so we override this method with the appropriate signature we expect.

As it's good form to have two folders for a SubSonic DAL - Custom and Generated, I created a StoredProcedures.cs partial class in Custom that looks like this:

/// <summary>
/// Creates an object wrapper for the testInsertToTestTVP Procedure
/// </summary>
public static StoredProcedure TestInsertToTestTVP(DataTable dt, int? Result)
{
   SubSonic.StoredProcedure sp = new SubSonic.StoredProcedure("testInsertToTestTVP", DataService.GetInstance("MyDAL"), "dbo");

   // TODO: Modify the SubSonic code base in sp.Command.AddParameter to accept
   // a parameter type of System.Data.SqlDbType.Structured, as it currently only System.Data.DbType.
   // sp.Command.AddParameter("@UserDetails", dt, System.Data.SqlDbType.Structured null, null);

   sp.Command.AddParameter("@UserDetails", dt, SqlDbType.Structured);
   sp.Command.AddOutputParameter("@Result", DbType.Int32, 0, 10);

   return sp;
}


As you can see, the method signature now contains a DataTable, and with my modification to the SubSonic framework, this now works perfectly.

I have used this in a production environment and works flawlessly. Any further modifications are welcome, especially implementing these changes to the .T4 templates.

What wasn't fixed in this release was the issue of parameters with sprocs.  If you have an output parameter in your sproc, you still need to send null for those parameters when calling the sproc.  See this project's issue tracker for more details.

 

One-Click DAL Generation with SubSonic

From Visual Studio, you can configure an external command to generate your DAL.

In Visual Studio, click on Tools / External Commands.  Create a new command and set the following:

Title: SubSonic DAL
Command: C:\Frameworks\SubSonic2.3-TVPSupport\SubCommander\bin\Release\sonic.exe     <-- whatever the location is to your sonic.exe
Arguments: generate /out Generated
Initial Directory: $(ProjectDir) 

You can configure this as a button in VS as well, for a one-click DAL generation solution for whenever you modify you database.

 

Final Notes

This is a great utility for use with active records, especially if you want to generate complex object that retain object relationships.  I have used this in an enterprise-level MVC application, but some warnings about how much data you transfer with WCF:

Set binding and reader quota max values LARGE to avoid the following error message:    

The maximum message size quota for incoming messages (65536) has been exceeded.

65536 is the default quota set when adding a new WCF endpoint.

To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.    

Be aware that the larger the buffer size, the longer the initial load will be.        

Increase the maxStringContentLength in readerQuotas to allow for longer content:

Your Web.config basic bindings can be modified as such:

      <basicHttpBinding>
        <binding name="BasicHttpBinding_IFeederService" closeTimeout="00:01:00"
          openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
          allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
          maxBufferSize="2000000" maxBufferPoolSize="524288" maxReceivedMessageSize="2000000"
          messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
          useDefaultWebProxy="true">
          <readerQuotas maxDepth="32" maxStringContentLength="15000" maxArrayLength="16384"
            maxBytesPerRead="4096" maxNameTableCharCount="16384" />
          <security mode="None">
            <transport clientCredentialType="None" proxyCredentialType="None"
              realm="" />
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>
        </binding>
      </basicHttpBinding>

 

 

 

 

Last edited Sep 5, 2011 at 5:42 PM by elhaix, version 4

Comments

No comments yet.