String Parameters default to ZERO length

[dibley1973]

Currently string parameters default to a length of zero. It has been suggested that string parameters default to VARCHAR(MAX) rather than VARCHAR(0).

However although this will be a good default if a stored procedure has a VARCHAR(MAX) parameters, it will still cause issues with VARCHAR(AnyOtherNumber).

Current Agreed Behaviour will be:

• If no Size annotation is set and the value of the parameter is longer than SQL Stored Procedure Parameter length then an exception should be thrown
• If a Size annotation is set and the value of the parameter is longer than the Size annotation value then an exception should be thrown
• If a Size annotation is set and the Size annotation value is larger than the SQL Stored Procedure parameter size then an exception should be thrown.
• In all other cases no exception should be thrown

[dibley1973]

There will be a problem in implementing the following cases:

• If no Size annotation is set and the value of the parameter is longer than SQL Stored Procedure Parameter length then an exception should be thrown
• If a Size annotation is set and the Size annotation value is larger than the SQL Stored Procedure parameter size then an exception should be thrown.

This will require an additional round trip to the DB possible using SqlCommand.DeriveParameters. this is known to be unwise in a production environment.

[jadjare]

How about setting the parameter length to simply match the string.length. That way you'll have a length to use for the VARCHAR and if it exceeds the sql parameter length you'll also get an exception.

[dibley1973]

@yoboj - That would be a great solution, but I am not sure where I am going to implement it. Currently the parameters are built and populated in separate method calls from the DbConnectionExtensions.BuildProcedureParameters(...) method, as each method has different concerns. I can't see a clean way of implementing this without havening an OverrideStringParameterLengthsFromParameters(...) type of method call sitting between the following lines...

var sqlParameters = SqlParameterHelper.CreateSqlParametersFromPropertyInfoArray(mappedProperties);

// Populate parameters from storedProcedure parameters
PopulateSqlParametersFromProperties(sqlParameters, mappedProperties, procedure);

Unless I pass the procedure into SqlParameterHelper.CreateSqlParametersFromPropertyInfoArray(mappedProperties); and override the Size using the string value length in that procedure? What are your thoughts?

[dibley1973]

As a dirty workaround, to allow consumers of this project to not have to set a SizeAttribute on a string parameter and have it default to 8000 - the maximum number of characters for a standard varchar data type VARCHAR(8000), I have implemented a DEFAULT length in the TrySetSqlParameterSizeFromAttribute(...) method of the SqlParameterHelper class...

    private static void TrySetSqlParameterSizeFromAttribute(PropertyInfo propertyInfo, SqlParameter sqlParameter)
    {
        var sizeAttribute = propertyInfo.GetAttribute<SizeAttribute>();
        sqlParameter.Size = sizeAttribute != null 
            ? sizeAttribute.Value 
            : DefaultStringSize;
    }

This will do as a stop gap until a better solution can be found.

[dibley1973]

A temporary fix has been applied. closing this issue for now.

[jadjare]

Sounds like a sensible solution. If it's not within needs users must annotate the parameter property. This gives a reasonable default behaviour whilst allowing for flexibility.