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

Here you have more details [ntext-in-servicestack-ormlite][1] [1]: http://stackoverflow.com/questions/11281726/ntext-in-servicestack-ormlite — Adam Łepkowski, Jun 20 '13 at 12:57
I would be interested in the reason behind using StringColumnDefinition = UseUnicode ? "NVARCHAR(4000)" : "VARCHAR(8000)"; — mosesfetters, Jun 20 '13 at 22:50
@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. — nikib3ro, Jan 30 '14 at 21:50
No it was meant more of why not use max as the default unless you put a limit in eg StringLength(6789) — mosesfetters, Jan 31 '14 at 00:02

score 3 · Answer 1 · answered Jun 28 '13 at 18:34

3

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)");
 }

answered Jun 28 '13 at 18:34

Antony Denyer

1,541
1
15
31

I liked this answer, but it's probably worth pointing out that this is MS SQL-specific, no? – Dale Holborow Nov 28 '16 at 19:26

Chris Marisic · Answer 2 · 2014-09-08T17:19:50.810

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.

score 2 · Answer 3 · answered Nov 28 '16 at 19:42

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.

score 1 · Answer 4 · answered Nov 28 '16 at 19:32

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.

FYI Also [see my answer](http://stackoverflow.com/a/40852209/85785), where you can also use a [Custom Type Converter](https://github.com/ServiceStack/ServiceStack.OrmLite/wiki/OrmLite-Type-Converters) to modify the Column definitions for each data type per RDBMS. — mythz, Nov 28 '16 at 19:43

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

4 Answers4

Linked