Different DB data type

8 posts, 1 answers
  1. marco
    marco avatar
    7 posts
    Member since:
    Apr 2010

    Posted 15 Apr 2010 Link to this post

    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
  2. Answer
    TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 15 Apr 2010 Link to this post

    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
  3. DevCraft banner
  4. marco
    marco avatar
    7 posts
    Member since:
    Apr 2010

    Posted 15 Apr 2010 Link to this post

    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.
  5. TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 15 Apr 2010 Link to this post

    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
  6. marco
    marco avatar
    7 posts
    Member since:
    Apr 2010

    Posted 20 Apr 2010 Link to this post

    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.
  7. Serge
    Admin
    Serge avatar
    375 posts

    Posted 20 Apr 2010 Link to this post

    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.
  8. marco
    marco avatar
    7 posts
    Member since:
    Apr 2010

    Posted 20 Apr 2010 Link to this post

    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
  9. Serge
    Admin
    Serge avatar
    375 posts

    Posted 22 Apr 2010 Link to this post

    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.
Back to Top
DevCraft banner