Filtering while using AdoTypeConverters

5 posts, 0 answers
  1. Musashi
    Musashi avatar
    20 posts
    Member since:
    Jan 2014

    Posted 14 Jan Link to this post

    I have an enum on my entity. In the database, it is represented by a char. Naturally, I made an AdoTypeConverter that maps the chars to the enum. For reading, this seems to work just fine. Writing I haven't had a chance to test  yet.

    But when I query, i get an exception saying "Conversion failed when converting the varchar value 'G' to data type int", for example.

    Since all I have are blog posts and github code samples to go off of instead of actual documentation, I'm at a loss to why querying doesn't work.

    So, to set up a full example:

    Below, EnumDbValueConverter is a class that maps enums values to database values (string).

    A side note: CreateLiteralSql is never called.

    public enum CompanyType
    {
        General,
        LawFirm
    }
     
    public class Company
    {
        public int PrimaryKey { get; set; }
        public string Name { get; set; }
        public CompanyType Type { get; set; }
    }
     
    public class CompanyTypeConverter : AdoTypeConverter
    {
        public override Type DefaultType
        {
            get { return typeof(CompanyType); }
        }
     
        public override object Read(ref DataHolder holder)
        {
            bool isNull = holder.Reader.IsDBNull(holder.Position);
     
            holder.NoValue = isNull;
     
            if (isNull)
            {
                holder.ObjectValue = EnumDbValueConverter.ConvertDbValue<CompanyType>(null);
            }
            else
            {
                holder.ObjectValue = EnumDbValueConverter.ConvertDbValue<CompanyType>(holder.Reader.GetValue(holder.Position).ToString());
            }
            return (holder.Box) ? holder.ObjectValue : null;
        }
     
        public override void Write(ref DataHolder holder)
        {
            holder.Parameter.DbType = System.Data.DbType.String;
            if (!holder.NoValue)
            {
                string s = EnumDbValueConverter.ConvertEnumValue((CompanyType)holder.ObjectValue);
                holder.Parameter.Size = s.Length;
                holder.Parameter.Value = s;
            }
        }
         
        public override bool CreateLiteralSql(ref DataHolder holder)
        {
            if (holder.NoValue)
            {
                holder.StringValue = this.NullValueSql;
                return false;
            }
            else
            {
                holder.StringValue = EnumDbValueConverter.ConvertEnumValue((CompanyType)holder.ObjectValue);
                return true;
            }
        }
    }
     
    //  this fails with the message "Telerik.OpenAccess.RT.sql.SQLException: Conversion failed when converting the varchar value 'G' to data type int."
    return Repository.GetCompanies().Where(c => c.Type == CompanyType.LawFirm);

  2. Musashi
    Musashi avatar
    20 posts
    Member since:
    Jan 2014

    Posted 19 Jan Link to this post

    Am I SOL here?

    If no one can help me with the original post, maybe someone can point me to a tutorial, or some kind of documentation for AdoTypeConverters?
  3. DevCraft banner
  4. Musashi
    Musashi avatar
    20 posts
    Member since:
    Jan 2014

    Posted 25 Jan Link to this post

    Ok, the questions still stand, but I seem to have gotten somewhere.

    I remembered one issue I had with variables vs constants in a Linq statement. On a whim I changed my query to this:

    CompanyType ct = CompanyType.LawFirm;
    return Repository.GetCompanies().Where(c => c.Type == ct);
    I'm no longer getting the exception and I get back the results I expect.

    But, the breakpoint is never hit in CreateLiteralSql. Is that normal?

    Is anyone else using AdoTypeConverters?

  5. Musashi
    Musashi avatar
    20 posts
    Member since:
    Jan 2014

    Posted 19 Feb Link to this post

    Are there any admins that can chime in on this?

    I'm running into another issue where an artificial property with a converter on it is causing issues in concurrency.

    A nullable datetime column is mapped to an artificial nullable datetime property with a converter that is supposed to convert nulls and dates before 1899 in the database to nulls in the application.

    I think this is causing an issue where when I try to update an artificial datetime property, the optimistic concurrency verification is attempting to find the row by using 'null', when in the database it's '1899-1-1'. So the row is not found, exception is thrown.

    Some code to aid in troubleshooting.

    mappingConfiguration.HasArtificialPrimitiveProperty(udf.FieldName, typeof(Nullable<DateTime>)).HasFieldName(fieldName).ToColumn(udf.FieldName).WithConverter<DateTimeConverter>();

    public class DateTimeConverter : AdoTypeConverter
    {
        public override Type DefaultType
        {
            get { return typeof(DateTime?); }
        }
     
        public override object Read(ref DataHolder holder)
        {
            //check if the value is dbnull
            bool isNull = holder.Reader.IsDBNull(holder.Position);
     
            //set wheather or not there is a value
            holder.NoValue = isNull;
     
            //if the value is null, then we need to return default values
            DateTime? value = null;
            if (!isNull)
            {
                //the value is not null, so here we want to pull the value out, and convert it
                value = holder.Reader.GetValue(holder.Position) as DateTime?;
     
                //if the value is nullabel, or needs to be boxed, we set the ObjectValue Property of the DataHolder
                if (value.RemoveTime() <= InteumDate.EMPTY_DATE)
                    value = null;
            }
            holder.ObjectValue = value;
     
            //now we return our value
            return value;
        }
     
        public override void Write(ref DataHolder holder)
        {
            //set the db type
            holder.Parameter.DbType = System.Data.DbType.DateTime;
     
            //if there is no value we could specify what to set the db field to
            if (holder.NoValue)
            {
                holder.Parameter.Value = InteumDate.EMPTY_DATE;
            }
            else
            {
                holder.Parameter.Value = holder.ObjectValue == null ? InteumDate.EMPTY_DATE : holder.ObjectValue as DateTime?;
            }
        }
     
        public override bool CreateLiteralSql(ref DataHolder holder)
        {
            //If there is no value, then we just want to query against null.
            if (holder.NoValue)
            {
                holder.StringValue = "NULL";
     
                //returning false indicates that no quotes are required. We want NULL instead of 'NULL'
                return false;
            }
            else
            {
                holder.DateTimeValue = (holder.ObjectValue as DateTime?).GetValueOrDefault(InteumDate.EMPTY_DATE);
                // return true indicates that quotes are needed around the value
                return true;
            }
        }
    }

    Exception message:

    "Row not found: GenericOID@50ac1abb TECHNOL PRIMARYKEY=4210
    UPDATE [TECHNOL] SET [RUPDATEDD] = ?, [UDFG6EDDT] = ? WHERE [PRIMARYKEY] = ? AND [RUPDATEDD] = ? AND [UDFG6EDDT] is null"

     The value for column UDFG6EDDT in the database is (SQL Server) '1899-1-1'. So natually, there is no row found with the given filter. How is this fixed?

  6. Ady
    Admin
    Ady avatar
    588 posts

    Posted 25 Feb Link to this post

    Hello Mushashi,

    In the 'Changed' optimistic concurrency mode DataAccess checks the old values of the fields that are to be updated to detect whether the object has changed in the meantime. To do this it maintains a copy of the old values internally. Since your converter does not load the values at all DataAccess has only 'NULL' as the original value. It cannot know the original value in the database.

    You can switched from 'Changed' concurrency to using an explicit version field or load the value of the field without using the converter and in the property implementation is your class you can return 'NULL' if the value is before 1899.

    Do get back in case you need further assistance.

    Regards,
    Ady
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top
DevCraft banner