Home / Questions / 205391
Accepted
Wilmilcard
Asked: 2020-10-18 04:55:54 +0800 CST 2020-10-18 04:55:54 +0800 CST

How to declare (map) an SQL_VARIANT in a Model - C# [UWP]

  • 772

Hi, I have a table in SQL:

CREATE TABLE Parametros(
    Empid       int NOT NULL CONSTRAINT [FK_Parametros_Empid] REFERENCES Empresa(id),
    Codigo      varchar(25) NOT NULL,
    Valor       sql_variant NULL,
    CONSTRAINT [PK_Parametros] PRIMARY KEY (Empid,Codigo))

And then I have to create the model of that table to work, and I don't know how to declare the 'value' column in the model.

Thank you

c#

2 Answers

  1. I do not recommend using this type of data, at least not if you are going to use it in applications because it is clear that you will not know what type of data is persisted

    Otherwise you will have to return along with the value of what type of data was persisted, for that it is usedSQL_VARIANT_PROPERTY

    sql_variant (Transact-SQL)

    As you will see, you would return

    SELECT Empid, 
       Codigo,
       Valor, 
       SQL_VARIANT_PROPERTY(Valor,'BaseType') AS 'DataType'
FROM Parametros

    When you have the data type you will have to apply logic to know what specific type to cast in .net Having the data type you could see how to make a generic converter

    Convert or TryParse from string to T (generic) possible? work around? 

    public static T TryParse<T>(string inValue)
{
    TypeConverter converter =
        TypeDescriptor.GetConverter(typeof(T));

    return (T)converter.ConvertFromString(null,
        CultureInfo.InvariantCulture, inValue);
}

    but you have to see how you plan to use the data in the application, maybe using a string for all types of data is simpler

    • 0
  2. Best Answer

    I already found the solution, in the model I had to add it [Column(TypeName = "sql_variant")]to the SQL_Variant field in the Database to be able to map it, in my case the column is calledValor

    using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public partial class Parametro : Entidad
{
    public override string ToString() { return Codigo; }

    [Key]
    public int Empid { get; set; }

    public string Codigo { get; set; }

    [Column(TypeName = "sql_variant")]
    public object Valor { get; set; }
}
    • 0