c# - dbType NVarChar 对于此构造函数无效

Question

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void MyMethod()
    {
        string connectionString = "context connection=true";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlMetaData[] metaData = {
                                         new SqlMetaData("Column1", System.Data.SqlDbType.NVarChar)
                                         ,new SqlMetaData("Column1", System.Data.SqlDbType.NVarChar)
                                     };
            SqlDataRecord record = new SqlDataRecord(metaData);
            record.SetString(0,"hello world");
            SqlContext.Pipe.SendResultsRow(record);
        }
    }

当我在 SQL 中运行该方法时

执行我的方法

错误

Msg 6522, Level 16, State 1, Procedure MyMethod, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "MyMethod": System.ArgumentException: The dbType NVarChar is invalid for this constructor. System.ArgumentException: at Microsoft.SqlServer.Server.SqlMetaData.Construct(String name, SqlDbType dbType, Boolean useServerDefault, Boolean isUniqueKey, SortOrder columnSortOrder, Int32 sortOrdinal) at Microsoft.SqlServer.Server.SqlMetaData..ctor(String name, SqlDbType dbType) at WcfClrApps.MyNamespace.MyMethod()

如何返回我自己创建的记录？我不想运行任何 SQL。项目构建是为 .NET 3.5 设置的。 MSDN 指出 SQL 2008 R2 不支持 4.0。

Answer 1

问题有两方面。 1. 需要最大长度。 2. SendResultsStart()/SendResultsEnd() 是必需的。

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void MyMethod()
    {
        string connectionString = "context connection=true";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlMetaData[] metaData = {
                                         new SqlMetaData("Column1", System.Data.SqlDbType.NVarChar, 100)//Max length has to be specified
                                         ,new SqlMetaData("Column1", System.Data.SqlDbType.NVarChar, 100)//same story
                                     };
            SqlDataRecord record = new SqlDataRecord(metaData);
            SqlContext.Pipe.SendResultsStart(record);//SendResultsStart must be called

            //create a row and send it down the pipe
            record.SetString(0,"hello world");
            SqlContext.Pipe.SendResultsRow(record);

            SqlContext.Pipe.SendResultsEnd();//End it out

        }
    }

Iterative example