Different DB data type

[marco]

Hi, i'm totaly new in the orm world.

I'm evaluating OpenAcces as ORM for a project that have to be DB agnostic.
My concern is:
in the object User i have a property isActive that is boolean type.
in ms sql server this property is mapped on a bit type column
in oracle i have not the bit type so i map this property to a CHAR(1) "T" / "F" or to a INT 0 / 1

If i start developing the application using MS SQL what i have to change in the code and where to support a different field tipe on oracle?
Obviusly the User.isActive need to continue to be boolean type.

Thank you
Marco

[IT-Als]

Hi Marco,

You're  right you don't change anything in your code. The type of the IsActive property will still be bool.

However, what you change is how OpenAccess map this bool to the correct column type in the table. You can do this for each supported backend (database vendor) in the configuration file. Here's a snippet, for the "mssql" backend:

    <connections>
      <connection id="MyConnection">
        <databasename>MyDB</databasename>
        <servername>myserver</servername>
        <integratedSecurity>False</integratedSecurity>
        <user>xxxx1</user>
        <password>yyyy1</password>
        <backendconfigurationname>mssqlConfiguration</backendconfigurationname>
      </connection>
    </connections>
    <backendconfigurations>
      <backendconfiguration id="mssqlConfiguration" backend="mssql">
        <mappingname>mssqlMapping</mappingname>
        <typeMap>
          <typeMapping>
            <ADOType>LONGVARCHAR</ADOType>
            <SQLType>NTEXT</SQLType>
          </typeMapping>
          <typeMapping>
            <ADOType>CLOB</ADOType>
            <SQLType>NTEXT</SQLType>
          </typeMapping>
          <typeMapping>
            <CLRType>System.Char</CLRType>
            <ADOType>CHAR</ADOType>
            <SQLType>NCHAR</SQLType>
          </typeMapping>
          <typeMapping>
            <CLRType>System.String</CLRType>
            <ADOType>VARCHAR</ADOType>
            <SQLType>NVARCHAR</SQLType>
          </typeMapping>
        </typeMap>
      </backendconfiguration>
    </backendconfigurations>

As you can see..in the <connection> element (within the <connections>) you refer to a <backendconfigurationname> which has to be defined in a <backendconfiguration> element (within the <backendconfigurations>). In this element you define the backend attribute as "mssql" (in my example above)... and how types should be mapped from CLR types (the bool) and ADO and SQL types (the backend specific type).
So, to change what a bool CLR type is mapped to in column type in Oracle you just define another <backendconfiguration> that uses Oracle as value in the backend attribute of the element.

All of these settings can also be done from the Telerik menu in Visual Studio under Configuration->Backend Configuration in the section Type Mapping

Regards
Henrik

[marco]

Hi Henrik

so if I map the CLR boolean to DB int

in my application i work with boolean and in the DB it will be saved 0 for false and 1 for true value?

Thank you.

[IT-Als]

Hi Marco,

Yes, this should do it. But remember to use the backend "Oracle" instead of "mssql".

BTW, it is a whole lot easier to use the menu Configuration to set these types.

Regards

Henrik

[marco]

Hi

i create a table in Oracle
the table have a field of type CHAR(1) that act like a boolean (contain T or F)
When i stat the mapping wizart it non let me map the oracle char field to a boolean field.

[Serge]

Hello Marco and Hello Henrik,

Henrik, you are correct, thank you. 
Marco, I will suggest that you read the Type Mapping section of this help article.

Do not hesitate to ask if you need further assistance.

Regards,
Serge
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[marco]

Hi i read the page that you suggest but i don't find the solution.
I want map a class boolean property on a db char(1) T/F field.

I see that it' is possible to map a boolean to a char(1) but it save 1/0 not T/F

[Serge]

Hi marco,

Telerik OpenAcces ORM provides a mechanism for customizing the way that CLR types are mapped to ADO types, but it is not possible to customize the values that are inserted automatically. 

I would suggest mapping the boolean field to an int column with 1/0 inserted and that you perform a forward mapping scenario. This means that OpenAccess will create and update your tables from your classes. This way OpenAccess has the responsibility of managing your database and your code is in fact database agnostic. More on forward mapping can be found here.

Hope this helps.

Kind regards,
Serge
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.