Bug 43795 - Wrong max. size for System.Data.SqlClient.SqlParameter of type NVarchar
Summary: Wrong max. size for System.Data.SqlClient.SqlParameter of type NVarchar
Status: RESOLVED FIXED
Alias: None
Product: Class Libraries
Classification: Mono
Component: System.Data (show other bugs)
Version: unspecified
Hardware: Macintosh Mac OS
: --- normal
Target Milestone: Untriaged
Assignee: Bugzilla
URL:
Depends on:
Blocks:
 
Reported: 2016-08-26 16:07 UTC by Kai M. Wadsack
Modified: 2018-02-22 22:21 UTC (History)
2 users (show)

Tags:
Is this bug a regression?: ---
Last known good build:


Attachments
StackTrace (2.75 KB, text/plain)
2016-08-26 16:07 UTC, Kai M. Wadsack
Details


Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.


Please create a new report on GitHub or Developer Community with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:
Status:
RESOLVED FIXED

Description Kai M. Wadsack 2016-08-26 16:07:22 UTC
Created attachment 17222 [details]
StackTrace

Microsoft .NET supports a max size of a SqlParameter of type NVarChar up to 2147483647.
Max. size in Mono for a SqlParameter of type NVarChar is 1073741823.

This incompatibility leads to a problem when using an OR Mapper like e.g. LLBLGen as they use the max size defined by Microsoft in the SqlParameter declaration. So any attempt to update or insert a table field of type NVarChar(max) leads to a TDS parser exception.

Steps to reproduce the bug:

First of all a small SQL Script to create a DB and a table, so that we've got something to test with:

USE master
GO

CREATE LOGIN DebugUser WITH PASSWORD=N'monotest', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE USER DebugUser FOR LOGIN DebugUser
GO

ALTER ROLE [db_owner] ADD MEMBER [DebugUser]
GO

CREATE TABLE NVarCharTest
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[LongName] [nvarchar](MAX) NOT NULL
)
GO

ALTER TABLE NVarcharTest ADD CONSTRAINT PK_NVarcharTest PRIMARY KEY ([Id])
GO

Next create a console application, name it "TestNVarCharMax", add a reference to System.Data to the project and overwrite the content of Program.cs with the following code:

Code:

using System.Data.SqlClient;
using System.Data;
using System;

namespace TestNVarCharMax {
    class MainClass {
        static string ConnectionString =
            @"data source=localhost;initial catalog=TestDB;User ID=DebugUser;Password=monotest;persist security info=False;packet size=4096;Connection Timeout=300";

        public static void Main() {

            const string commandText = @"INSERT INTO NVarCharTest (LongName) VALUES (@LongName)";
            const int maxNVarCharLengthDotNet = 2147483647;
            const int maxNVarCharLengthMono = 1073741823;

            using (var connection = new SqlConnection(ConnectionString)) {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection)) {
                    var longNameParameter = new SqlParameter("@LongName", SqlDbType.NVarChar, maxNVarCharLengthDotNet);
                    longNameParameter.Value = @"Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.";

                    command.Parameters.Add(longNameParameter);

                    try {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex) {
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }

                    longNameParameter.Size = maxNVarCharLengthMono;

                    try {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex) {
                        Console.WriteLine(ex.Message);
                        Console.WriteLine(ex.StackTrace);
                    }
                }
            }
        }
    }
}

Replace "localhost" in the connection string with ip address, name or instance name of your sql server.

Running the code under mono, line #25 of above code will give you the following error message:

Der eingehende Tabular Data Stream (TDS) für das RPC-Protokoll (Remote Procedure Call) ist nicht richtig. Parameter 3 ('@LongName'): Der 0xE7-Datentyp hat eine ungültige Datenlänge oder Metadatenlänge.

English translation: 

The incoming Tabular Data Stream (TDS) for the RPC protocol (Remote Procedure Call) is not correct. Parameter 3 ('@LongName'): The 0xE7-datatype has an invalid data length or metadata length.

Stack trace is attached.

Running the same code under .NET on Windows doesn't throw any errors.
Comment 1 Marek Safar 2018-02-22 22:21:44 UTC
Mono 5.10 has significantly improved System.Data implementation which should resolve this issue. If you can still reproduce it please reopen the issue.