SubSonic 2.x now supports TVP's - SqlDbType.Structure / DataTables for SQL Server 2008
- by ElHaix
For those interested, I have now modified the SubSonic 2.x code to recognize and support DataTable parameter types.
You can read more about SQL Server 2008 features here: http://download.microsoft.com/download/4/9/0/4906f81b-eb1a-49c3-bb05-ff3bcbb5d5ae/SQL%20SERVER%202008-RDBMS/T-SQL%20Enhancements%20with%20SQL%20Server%202008%20-%20Praveen%20Srivatsav.pdf
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
The the auto gen tool runs, it creates the following erroneous method:
/// <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;
}
It sets UserDetails as type string.
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)
{
DataSet ds = new DataSet();
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 accepts
// 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'm wondering if the SubSonic guys can modify the auto-gen to recognize a TVP in a sproc signature, as to avoid having to re-write the warpper?
Does SubSonic 3.x support Structured data types?
Also, I'm sure many will be interested in using this code, so where can I upload the new code?
Thanks.
