SubSonic 2.x现在支持TVP的SQL Server 2008的SqlDbType.Structure / DataTables [英] SubSonic 2.x now supports TVP's - SqlDbType.Structure / DataTables for SQL Server 2008

问题描述

对于那些感兴趣的人，我现在修改了SubSonic 2.x代码以识别和支持DataTable参数类型。

For those interested, I have now modified the SubSonic 2.x code to recognize and support DataTable parameter types.

您可以在这里阅读有关SQL Server 2008功能的更多信息： http://download.microsoft.com/download/4/9/0/4906f81b-eb1a-49c3-bb05-ff3bcbb5d5ae/ SQL％20SERVER％202008-RDBMS / T-SQL％20增强％20 with％20SQL％20Server％202008％20-％20Praveen％20Srivatsav.pdf

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

什么现在，这个增强功能现在可以让您创建一个部分StoredProcedures.cs类，并使用覆盖存储过程包装器方法的方法。

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.

有点好的形式：
我的DAL没有直接的表访问，我的数据库只有该用户对我的sprocs的执行权限。因此，SubSonic仅生成AllStructs和StoredProcedures类。

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.

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

TVP： p>

The TVP:

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

auto gen工具运行，它创建以下错误方法：

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;
    }

它将UserDetails设置为字符串类型。

It sets UserDetails as type string.

由于为SubSonic DAL（自定义和生成）拥有两个文件夹，所以在Custom中创建了一个StoredProcedures.cs部分类，如下所示：

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 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;
    }

如您所见，方法签名现在包含一个DataTable，并与我的对SubSonic框架的修改，现在可以完美地工作。

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

我想知道SubSonic的人是否可以修改auto-gen来识别sproc签名中的TVP，如以避免重写包装？

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 wrapper?

SubSonic 3.x是否支持结构化数据类型？

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?

谢谢。

推荐答案

我已经发布了一个包含整个解决方案，源代码和说明的CodePlex项目。

I've posted a CodePlex project with the entire solution, source code and instructions.

项目可以在此处找到。

这篇关于SubSonic 2.x现在支持TVP的SQL Server 2008的SqlDbType.Structure / DataTables的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！