本文介绍了SubSonic 2.x现在支持TVP的SQL Server 2008的SqlDbType.Structure / DataTables的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

对于那些感兴趣的人,我现在修改了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功能的更多信息:

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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-16 21:26