none
Dynamically change SQL server RRS feed

  • Question

  • I'm 100% new to Linq although hopes for it are fading relatively quickly.

    I have a program that is a front end which can used to query multiple third party electronic medical records systems. These third party softwares are many and varied but all run SQL. I want the user to dynamcially assign which SQL server should be used depending on type of third party software, environment etc. 

    As I understand it Linq requires assignment of the specific server to the VS solution at the time of build?

    Is there anyway around this? Ideally the user would need to assign *.mdf path (SQL file format) and hit the tables for varaibles and proceed with mapping from there,..

    Help Obi Wan kenobi, you're my only hope,...
    Monday, October 19, 2009 7:34 AM

Answers

  • No. They are different databases from different softwares. I'm currently trying to retrieve the list of tables from the newly assigned data context but am having trouble with this. db.mapping.gettable(s) or db.gettables() seems not to be working in the first instance although others seems to have had some success: http://blogs.msdn.com/jomo_fisher/archive/2007/07/30/linq-to-sql-trick-get-all-table-names.aspx In principle am I going to be able to do what I want?

    Ah, no. The tables and their columns must be known at design time. Linq-to-SQL generates classes at design time that are mapped to the tables, but it doesn't have anything built in for runtime generating entity classes.

    You _could_ start off with a blank datacontext and then generate your classes at runtime but that would take away most of the advantage of using Linq-to-SQL since you wouldn't have any strong typing during development etc. In other words, if the tables are not known at design time then it will probably be easier to go with plain ADO.NET....
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by snoppann Tuesday, October 20, 2009 3:45 AM
    Tuesday, October 20, 2009 1:46 AM
    Answerer

All replies

  • You can pass a SqlConnection or a connection string to the datacontext when you create a new instance...

    string connectString = "Data Source=someServer;Initial Catalog=someDB;Integrated Security=True;";
    SomeDataContext myDataContext = new SomeDataContext(connectString);

    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Monday, October 19, 2009 7:49 AM
    Answerer
  • OK thanks. For SomeDataContext to exist I would first have to assign an arbitrary database at build time and then have the user reassign (as per your suggestions) to the SomeDataContext. If so then I have a way forward,..
    Monday, October 19, 2009 7:57 AM
  • Do all the MDF's share the same table names, structure, keys, column names and types etc.

    [)amien
    Monday, October 19, 2009 3:21 PM
    Moderator
  • No. They are different databases from different softwares. I'm currently trying to retrieve the list of tables from the newly assigned data context but am having trouble with this. db.mapping.gettable(s) or db.gettables() seems not to be working in the first instance although others seems to have had some success: http://blogs.msdn.com/jomo_fisher/archive/2007/07/30/linq-to-sql-trick-get-all-table-names.aspx In principle am I going to be able to do what I want?
    Monday, October 19, 2009 10:14 PM
  • No. They are different databases from different softwares. I'm currently trying to retrieve the list of tables from the newly assigned data context but am having trouble with this. db.mapping.gettable(s) or db.gettables() seems not to be working in the first instance although others seems to have had some success: http://blogs.msdn.com/jomo_fisher/archive/2007/07/30/linq-to-sql-trick-get-all-table-names.aspx In principle am I going to be able to do what I want?

    Ah, no. The tables and their columns must be known at design time. Linq-to-SQL generates classes at design time that are mapped to the tables, but it doesn't have anything built in for runtime generating entity classes.

    You _could_ start off with a blank datacontext and then generate your classes at runtime but that would take away most of the advantage of using Linq-to-SQL since you wouldn't have any strong typing during development etc. In other words, if the tables are not known at design time then it will probably be easier to go with plain ADO.NET....
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by snoppann Tuesday, October 20, 2009 3:45 AM
    Tuesday, October 20, 2009 1:46 AM
    Answerer
  • Thanks. I now understand what LINQ is about now and it is not for my current project. I'm already forging ahead with good old ADO. Maybe Linq will serve me well some other time. Once again thanks for your help.
    Tuesday, October 20, 2009 3:45 AM