Sql Compact - string longer than 4000 characters

4 years ago
Hi,

This is a specific Sql Compact question - I am getting an error logged - not a big problem as it's just a debugging issue not a live site, but it's not logging the actual error as the logger itself throws a second error - the log message is over 4000 characters long - this throws Sql Compact as it can't store the string (I repeat this is Sql Compact not nopCommerce). I can put a wrap around it, but I was wondering if this could be solved another way - as Sql Compact is never going to support larger strings?

Thanks,
4 years ago
Hi Marc,

We haven't test it yet but please have a look at the approach described here (use "IsMaxLength" method). Does it help?
4 years ago
Thanks,

Looks good, I'll set up a test.
4 years ago
marc wrote:
Thanks,

Looks good, I'll set up a test.


Although - there is a Microsoft support case to do with ADO.NET Entity Framework in .NET 3.5. It says that:

https://support.microsoft.com/en-gb/kb/958478

microsoft wrote:

"Additionally, if the provider marks the parameter as the ntext data type or as the image data type, an error occurs if any equality operations, grouping operations, or sorting operations are being performed on the parameter."


I will have to find out what it does to other parts of nop.
4 years ago
seems ok for logging. I added:

this.Property(l => l.FullMessage).IsMaxLength(); // Uses n t e x t in s q l c o m p a c t, n v a r c h a r(max) in SQL Server


to Nop.Data/Mapping/Logging/LogMap.cs on a fresh install.

Thank you.
4 years ago
I was able to solve the problem (a general solution using refactoring). But faced with other constraint in SQL CE when using with "IsMaxLength" extension method:
field type ntext cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses. This makes it impossible to use NTEXT to overcome the limitation of 4000 characters for string fields.

But I will post here my solution, maybe in the future it will work.
    
First I added public function to NopEntityTypeConfiguration class:
/// <summary>
/// Configures all string properies (in addition to those fields for which clearly defined maximum length)
/// to allow the maximum length supported by the database provider
/// </summary>
public virtual void SetStringMaxLength()
{
    //get ignorable properties
    var filterConf = this.GetType().GetProperty("Configuration", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(this);
    var filter = filterConf.GetType().GetProperty("IgnoredProperties", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(filterConf) as IEnumerable<PropertyInfo>;
    var ignoredProperties = filter != null ? filter.Select(p => p.Name).ToList() : new List<string>();

    var names = typeof(T).GetProperties()
        .Where(p => p.PropertyType == typeof(string))
        .Where(p=>!ignoredProperties.Contains(p.Name))
        .Select(p => p.Name).ToArray();

    var expression = names.Select(name => Core.DynamicExpression.ParseLambda<T, string>(name, null));

    foreach (var expr in expression)
    {
        var prop = this.Property(expr);
        var temp = prop
            .GetType()
            .GetProperties(BindingFlags.NonPublic | BindingFlags.Instance).FirstOrDefault(p => p.Name == "Configuration");

        if (temp == null)
            continue;

        var obj = temp.GetValue(prop);
        var maxLength = obj.GetType().GetRuntimeProperty("MaxLength").GetValue(obj);

        if (maxLength == null)
            //uses ntext in sqlcompact, NVARCHAR(MAX) in SQL Server
            Property(expr).IsMaxLength();
    }
}


Then I added code to NopObjectContext.OnModelCreating in line 46:
//use this approach to determine the Sql CE is not necessary, may have a better solution.
var dataProviderInstance = EngineContext.Current.Resolve<BaseDataProviderManager>().LoadDataProvider() as SqlCeDataProvider;
if (dataProviderInstance != null)
    configurationInstance.SetStringMaxLength();

    
The file with the changes is attached to this work item details (see my comment)