none
Querying data across two databases using Linq RRS feed

  • Question

  • Hi all

    I have two databases, one hosted in-house, the other with our webserver.  The webserver db is updated by client submissions.  These submissions are then access by our intranet system and the details are downloaded into our in-house database.  I want to add a report page in aspx format that will compare the two tables in the two databases and show any exceptions whereby records are on the remote server but not on the local.  This will then show which records haven't yet been processed.

    In the past we have used Access databases and we would simply have used linked tables.  However, I'm unsure how to do this in asp.net.  Linq looks like it would be suitable, i.e. with me adding both tables into a datacontext but I'm having trouble finding any examples.

    Any help appreciated

    Tuesday, October 5, 2010 10:49 AM

Answers

  • I don't think you can have multiple databases in a single dbml file, because then the datacontext will not be able to resolve results from two databases, plus in a single dbml you can only have connection to only one database. 

    One way to do that would be to create a stored procedure in one of the database that would be access the other database through server object, (I am sure that can be done with regards to two SQL Server databases through server objects, for access I am not so sure). 

    Other way could be to create separate dbml for each database and then you can use them in your business objects for reporting purpose. 

     


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, October 5, 2010 7:40 PM
  • Hi,

    As Habib mentioned, there's no direct way to include two separate dbs in one dbml file. A few workarounds are:

    • If the other database reside on another server, you need to create a linked server and some SQL views pointing to the other db. Then you can include those views in your dbml file.
    • If the other database reside on same server, you can either create a view or do a manual modification in your dbml file described by Damien here: http://damieng.com/blog/2010/01/11/linq-to-sql-tips-and-tricks-3
    • Finally, you can always create two separate dbml files, retrieve the required data and combine results by using LINQ on in-memory collections.

     

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles
    Wednesday, October 6, 2010 6:56 AM

All replies

  • I don't think you can have multiple databases in a single dbml file, because then the datacontext will not be able to resolve results from two databases, plus in a single dbml you can only have connection to only one database. 

    One way to do that would be to create a stored procedure in one of the database that would be access the other database through server object, (I am sure that can be done with regards to two SQL Server databases through server objects, for access I am not so sure). 

    Other way could be to create separate dbml for each database and then you can use them in your business objects for reporting purpose. 

     


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, October 5, 2010 7:40 PM
  • Hi,

    As Habib mentioned, there's no direct way to include two separate dbs in one dbml file. A few workarounds are:

    • If the other database reside on another server, you need to create a linked server and some SQL views pointing to the other db. Then you can include those views in your dbml file.
    • If the other database reside on same server, you can either create a view or do a manual modification in your dbml file described by Damien here: http://damieng.com/blog/2010/01/11/linq-to-sql-tips-and-tricks-3
    • Finally, you can always create two separate dbml files, retrieve the required data and combine results by using LINQ on in-memory collections.

     

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles
    Wednesday, October 6, 2010 6:56 AM