Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Morten B 13 posts 66 karma points
    Jun 17, 2015 @ 14:10
    Morten B
    2

    Umbraco.Core.Persistence, ntext deprecated, nvarchar(MAX)

    I am trying to create a table on application start using PetaPoco. Since 2008 the column type ntext has been deprecated and replaced by nvarchar(MAX). ntext will be removed at some point. nvarchar allows for more operations on the column without having to cast or convert.

    With MS SQL Server using nvarchar(n), n can be 0-4000 and MAX.

    How do I create a nvarchar(MAX) column in Umbraco using PetaPoco? LengthAttribute is an int. https://github.com/umbraco/Umbraco-CMS/tree/7.2.4/src/Umbraco.Core/Persistence/DatabaseAnnotations

    SpecialDbTypes has ntext and nchar. I am (probably) missing something like a nvarcharmax special type or an enum allowing me to do:

    [Column("Description")]
    [SpecialDbType(SpecialDbTypes.NVARCHARMAX)]
    public string Description { get; set; }
    

    What is the Umbraco-way of creating a nvarchar column with more than 4.000 characters?

  • Taha 5 posts 25 karma points
    Jul 03, 2015 @ 09:07
    Taha
    0

    searching for the same.. Did u get any solution??

  • Morten B 13 posts 66 karma points
    Jul 03, 2015 @ 09:18
    Morten B
    0

    We are using nvarchar(4000) for now, but we thought of doing an "alter table" after the PetaPoco table creation as we cannot create a nvarchar(max) column.

  • Morten B 13 posts 66 karma points
    Jul 03, 2015 @ 09:27
    Morten B
    4

    Just did a quick test. The alter table workaround works.

    db.Execute("ALTER TABLE Test ALTER COLUMN Description NVARCHAR(MAX)");
    
  • Streety 358 posts 567 karma points
    Jan 17, 2018 @ 16:00
    Streety
    1

    I know this post is old but I thought I post the easier way:

    Decorate the class as follows:

        [Column("properties")]
        [Length(4000)]
        public string properties { set; get; }
    

    Does the same as nvarcharMAX

  • Jeroen Oostwouder 64 posts 160 karma points
    Aug 06, 2018 @ 09:24
    Jeroen Oostwouder
    0

    Having the same problem, I came accross this post.

    But according to the MS Documentation:

    nvarchar [ ( n | max ) ]

    Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^30-1 characters. The maximum storage size in bytes is 2 GB.

    2^30 = 1,073,741,824 says my calculator. Which seems like a lot more than 4000.

  • Flavio Spezi 128 posts 313 karma points
    Jan 15, 2019 @ 17:25
    Flavio Spezi
    0

    I develop a little code to adjust table after creation.
    It convert any column of table where datatype is NVARCHAR(3999) in NVARCHAR(MAX).
    Instead of convert NTEXT, I choose to search and convert NVACHAR because PetaPoco need to know the real dbType.
    The length 3999 is a "key-value" to activate the conversion. So the 4000 size is free to use when necessary.

    public const int NVarcharMaxSizeKey = 3999;
    
    private void adjustNVarcharMaxFields(DatabaseContext dbContext, System.Type modelDtoType)
    {
        var sqlSyntaxProvider = ApplicationContext.Current.DatabaseContext.SqlSyntax;
        var sqlParts = new List<string>();
    
        var tableNameAttribute = modelDtoType.FirstAttribute<TableNameAttribute>();
        var tableName = tableNameAttribute.Value;
    
        var objProperties = modelDtoType.GetProperties().ToList();
    
        foreach (var propertyInfo in objProperties)
        {
            //If current property has an IgnoreAttribute then skip it
            var ignoreAttribute = propertyInfo.FirstAttribute<IgnoreAttribute>();
            if (ignoreAttribute != null) continue;
    
            //If current property has a ResultColumnAttribute then skip it
            var resultColumnAttribute = propertyInfo.FirstAttribute<ResultColumnAttribute>();
            if (resultColumnAttribute != null) continue;
    
            //Looks for ColumnAttribute with the name of the column, which would exist with ExplicitColumns
            //Otherwise use the name of the property itself as the default convention
            var columnAttribute = propertyInfo.FirstAttribute<ColumnAttribute>();
            string columnName = columnAttribute != null ? columnAttribute.Name : propertyInfo.Name;
    
            // Filtering for column where `SpecialDbTypeAttribute` attribute is not defined (or NVARCHAR value).
            var sqlColumnTypeAttribute = propertyInfo.FirstAttribute<SpecialDbTypeAttribute>();
            if (sqlColumnTypeAttribute != null) continue;
    
            // Filtering for column where `LengthAttribute` is 3999.
            var lengthAttribute = propertyInfo.FirstAttribute<LengthAttribute>();
            if (lengthAttribute == null || lengthAttribute.Length != NVarcharMaxSizeKey) continue;
    
            // Reading nullable column property.
            var nullSettingAttribute = propertyInfo.FirstAttribute<NullSettingAttribute>();
            var nullableColumn = nullSettingAttribute != null && nullSettingAttribute.NullSetting == NullSettings.Null;
    
            // Compiling SQL COMMAND for ALTER COLUMN
            sqlParts.Add(string.Format(sqlSyntaxProvider.AlterColumn,
                sqlSyntaxProvider.GetQuotedTableName(tableName),
                string.Format("{0} NVARCHAR(MAX) {1}NULL", columnName, (nullableColumn ? "" : "NOT "))
                ));
        }
    
        // Executing
        if (sqlParts.Count > 0)
        {
            var sql = string.Join("\r\n", sqlParts);
    
            dbContext.Database.Execute(sql);
        }
    }
    
  • David Armitage 347 posts 1373 karma points
    Jun 07, 2020 @ 04:47
    David Armitage
    0

    Hi Guys,

    I do a similar kind of thing as a couple of people above.

    My Model/Poco field looks something like this.

    [Column("Body")]
    
    public string Body { get; set; }
    

    So by default this would get inserted as NVARCHAR(255)

    I then run an update on that column straight after the migration. Here is an example of my Composition initialize method which I usually add this into.

    public class MigrationPlanComponent : IComponent
        {
            private readonly IScopeProvider _scopeProvider;
            private readonly IMigrationBuilder _migrationBuilder;
            private readonly IKeyValueService _keyValueService;
            private readonly ILogger _logger;
    
            public MigrationPlanComponent(IScopeProvider scopeProvider,IMigrationBuilder migrationBuilder,IKeyValueService keyValueService,ILogger logger)
            {
                _scopeProvider = scopeProvider;
                _migrationBuilder = migrationBuilder;
                _keyValueService = keyValueService;
                _logger = logger;
            }
    
            public void Initialize()
            {
                var upgrader = new Upgrader(new UMB8SMMMigrationPlan());
                upgrader.Execute(_scopeProvider, _migrationBuilder, _keyValueService, _logger);
    
                using (var scope = _scopeProvider.CreateScope())
                {
                    scope.Database.Execute("ALTER TABLE UMB8SMMEmailQueue ALTER COLUMN Body NVARCHAR(MAX)");
                    scope.Complete();
                }
            }
    
            public void Terminate()
            {
                throw new NotImplementedException();
            }
        }
    

    Once this is complete the column is updated to NVARCHAR(MAX) as desired.

    Hope this helps someone.

    Regards David

  • David Armitage 347 posts 1373 karma points
    Jun 07, 2020 @ 09:48
    David Armitage
    0

    Hi Guys

    Actually. A slightly modified version. I moved the column update into the migrationplan. Just after the create table.

    The code I supplied before would update the column every time the site is initialized which we dont want.

    Here is an example of my migrationplan.

    using Umbraco.Core.Migrations; 
    using Umbraco.Core.Scoping; 
    using Website.Core.Models;
    
        namespace UMB8SMM.Migrations {
            public class UMB8SMMMigrationPlan : MigrationPlan
            {
                public UMB8SMMMigrationPlan() : base("UMB8SMMCreateTables")
                {
                    From(string.Empty).To<MigrationCreateTables>("UMB8SMMCreateTables-first-migration");
                }
            }
    
            public class MigrationCreateTables : MigrationBase
            {
                private readonly IScopeProvider _scopeProvider;
    
                public MigrationCreateTables(IMigrationContext context, IScopeProvider scopeProvider) : base(context)
                {
                    _scopeProvider = scopeProvider;
                }
    
                public override void Migrate()
                {
                    if (!TableExists("UMB8SMMEmailQueue"))
                    {
                        Create.Table<UMB8SMMPocoEmailQueue>().Do();
    
                        using (var scope = _scopeProvider.CreateScope())
                        {
                            scope.Database.Execute("ALTER TABLE UMB8SMMEmailQueue ALTER COLUMN Body NVARCHAR(MAX)");
                            scope.Complete();
                        }
                    }
                }
            } }
    
Please Sign in or register to post replies

Write your reply to:

Draft