Support for joins between databases

5 posts, 1 answers
  1. Hessner
    Hessner avatar
    189 posts
    Member since:
    Mar 2003

    Posted 27 Jul 2009 Link to this post

    Hi,

    I am in a design face, and are about to move all my member related tables to a separate database.

    Now, I want to "cross over" and join to the new database.

    What I really are asking for is the ability to refer another DAL layer and somehow make them "know" each other.
    I will have "really" foreign keys stored in one database and data in another.

    In the future(or is it now?) data are stored in several databases, and if they can be laid out in OpenAccess
    it would be natural that they could communicate with each other,- agree?

    Does OpenAccess allow this kind of activity?
  2. Answer
    Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 28 Jul 2009 Link to this post

    Hello Hessner,
    As long as you use SQLServer you do not have to think about connections between different scopes. That makes it too complicated. You should have the persistent model for each db in a different assembly but with OpenAccess you can communicate via one sql server connection with several databases and maybe different servers. The trick is to prefix the table names by their schema and database name and we will tunnel all request via a single connection to the server and the server is doing the rest. Forward mapping is working as well, but the databases must be already there.

    Please have a look here:
    http://www.telerik.com/help/openaccess-orm/using-multiple-databases-with-openaccess-(mssql-specific).html

    Best wishes,
    Jan Blessenohl
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. DevCraft banner
  4. Hessner
    Hessner avatar
    189 posts
    Member since:
    Mar 2003

    Posted 28 Jul 2009 Link to this post

    Hi Jan,

    Thanks,- I will look into this.

    You are way out there, as usual :-)


  5. Samer
    Samer avatar
    1 posts
    Member since:
    Jan 2014

    Posted 04 Jun 2014 in reply to Jan Blessenohl Link to this post

    It seems this is no longer available in the current (free) version of OpenAccess?
    I'm using the fluent mappings and defined by table name as a string "'DatabaseName'.'SchemaName'.'TableName'" but I get an exception when trying to run a query.
    It looks like the current version only supports schema, but not databases.
    I am trying to query multiple SQL 2005 databases that are on the same database instance. If I were to execute a plain SQL query it would be possible by just prefixing the table name with the database and schema names... But I'd love to do it through Telerik instead!
  6. Kaloyan Nikolov
    Admin
    Kaloyan Nikolov avatar
    118 posts

    Posted 06 Jun 2014 Link to this post

    Hi Samer,

    This approach is still valid with Telerik Data Access. You can have a single model targeting multiple databases. Please find attached sample application demonstrating how you can achieve it with fluent mapping. 

    Here are the key aspects:
    1. You should have the databases created upfront 
    2. In your fluent mapping you should map the tables with the following constructs: 
    MappingConfiguration<Table1> configuration1 = new MappingConfiguration<Table1>();
    configuration1.MapType(x => new { }).ToTable(new TableName("Table1", "Db1'.'dbo"));
     
    MappingConfiguration<Table2> configuration2 = new MappingConfiguration<Table2>();
    configuration2.MapType(x => new { }).ToTable(new TableName("Table2", "Db2'.'dbo"));
    the important part is in the second parameter of the TableName constructor.

    The sample application includes the DB create scrips. You should execute them and set your connection string accordingly first.

    I hope this helps. Should you have any further questions do not hesitate to get back to us.


    Regards,
    Kaloyan Nikolov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner