Create a column with varchar(max) rather than varchar(8000)

Question

How can I create a column in a table and specify it as varchar(max) when using servicestack ormlite?

Currently I execute some sql after the table create to get what I want. I've seen the StringLength attribute but was wondering if there's a nicer way of doing it?

Something like

StringLength(Sql.VarcharMax)

Thanks

Bine

Here you have more details [ntext-in-servicestack-ormlite][1] [1]: #11282226 – Acropolis

I would be interested in the reason behind using StringColumnDefinition = UseUnicode ? "NVARCHAR(4000)" : "VARCHAR(8000)"; – Decastro

@FettMo If you mean why 4000 & 8000 - varchar fields in SQL Server Express are maxed at 8000 (unless you use max). nvarchar is twice as big as varchar so it's maxed at 4000. – Creuse

No it was meant more of why not use max as the default unless you put a limit in eg StringLength(6789) – Decastro

Answer 1

Worked around the issue by doing the following

 if (!db.TableExists(typeof(Entity).Name))
 {
   db.CreateTableIfNotExists<Entity>();
   db.ExecuteSql("ALTER TABLE Entity ALTER COLUMN Column VARCHAR(MAX)");
 }

Bine

I liked this answer, but it's probably worth pointing out that this is MS SQL-specific, no? – Sousa

Answer 2

Decorate your domain model with System.ComponentModel.DataAnnotations.StringLengthAttribute

such as

[StringLengthAttribute(8001)]
public string Markdown { get;set; }

or

[StringLength(Int32.MaxValue)]
public string Markdown { get;set; }

using any length greater than 8000 to exceed to maximum length of Sql Server varchar/nvarchar column types that required the MAX declaration.

Use a custom dialect provider the understands the MAX declaration.

public class MaxSqlProvider : SqlServerOrmLiteDialectProvider
{
  public new static readonly MaxSqlProvider Instance = new MaxSqlProvider ();

  public override string GetColumnDefinition(string fieldName, Type fieldType, 
            bool isPrimaryKey, bool autoIncrement, bool isNullable, 
            int? fieldLength, int? scale, string defaultValue)
  {
     var fieldDefinition = base.GetColumnDefinition(fieldName, fieldType,
                                    isPrimaryKey, autoIncrement, isNullable, 
                                    fieldLength, scale, defaultValue);

     if (fieldType == typeof (string) && fieldLength > 8000)
     {
       var orig = string.Format(StringLengthColumnDefinitionFormat, fieldLength);
       var max = string.Format(StringLengthColumnDefinitionFormat, "MAX");

       fieldDefinition = fieldDefinition.Replace(orig, max);
     }

     return fieldDefinition;
  }
}

Use the provider when you construct the database factory

var dbFactory = new OrmLiteConnectionFactory(conStr, MaxSqlProvider.Instance);

Note this illustration is specifically targeting SqlServer but it would be relevant for other data providers with minor alterations after inheriting from the desired provider.

Sherise

Answer 3

The column definition of each type can be controlled using OrmLite's Type Converters where the default SqlServerStringConverters will use VARCHAR(MAX) for properties attributed with [StringLength(StringLengthAttribute.MaxText)], e.g:

public class PocoTable
{
    public int Id { get; set; }

    [StringLength(StringLengthAttribute.MaxText)]
    public string MaxText { get; set; }

    [StringLength(256)]
    public string SmallText { get; set; }
}

Using StringLengthAttribute.MaxText (or its alias int.MaxValue) will automatically use the most appropriate datatype for storing large strings in each RDBMS.

Looney

Answer 4

It appears that ServiceStack has addressed this with a feature to further customize the creation type of fields, see: https://github.com/ServiceStack/ServiceStack.OrmLite#custom-field-declarations

or as detailed at that link:

The [CustomField] attribute can be used for specifying custom field declarations in the generated Create table DDL statements, e.g.:

public class PocoTable
{
    public int Id { get; set; }

    [CustomField("CHAR(20)")]
    public string CharColumn { get; set; }

    [CustomField("DECIMAL(18,4)")]
    public decimal? DecimalColumn { get; set; }
}

Where

db.CreateTable<PocoTable>(); 

Generates and executes the following SQL:

CREATE TABLE "PocoTable" 
(
  "Id" INTEGER PRIMARY KEY, 
  "CharColumn" CHAR(20) NULL, 
  "DecimalColumn" DECIMAL(18,4) NULL 
);  

Again however, as per my comment o one of the previous answers, I am guessing that this is probably db-specific, sorry, I don't have easy access to several db servers to test.

Sousa

FYI Also see my answer, where you can also use a Custom Type Converter to modify the Column definitions for each data type per RDBMS. – Looney