locked
LINQ To SQL Connection String?

    Question

  • Guys/Gals i need a little help here which is probably a simple question for a lot of you with regard to connection strings. I'm using LINQ To SQL. I have a dll that i'm using LINQ TO SQL in. I have a dll where i'm using a linq to sql linq to sql object (.dbml) and told it which database to use it does the following:
    1) Creates a connection string in App.config
    2) Puts the following entry in settings.designer.vb:
    Global.System.Configuration.DefaultSettingValueAttribute("Data Source=MyDBServer;Initial Catalog=MyDB;User ID=MyId;Password=MyPass" & _
                "5")>

    OK i'm current developing this in a dev environment and, of course, as it moves between environments the connection striing will have to change. I tried to change the connection string in app.config and it had no effect. I then changed the connection string in the settings.designer.vb and that changed the server linq was connection to. What gives? Why does it put the connection string in two places? I want an administrator to be able to change the connection string without having to have Visual Studio and i don't want to have to recompile ever time the connection string is changed. I would think changing the connection string in app.config would have the desired effect but apparently the hardcodeed connection string in settings.designer takes precedence. Can anyone help me out here?

    thanks,
    Ncage
    Wednesday, April 15, 2009 4:12 PM

Answers

  • I have now been playing around with it for while and this is what ive found out.

    1) It also inserts the connectionstring in the .dbml file (you can see this if you edit it with a text viewer instead of the designer) which i think is screwy in itself but you can tell it to use the application settings connection string in the properties off of the designer for the dbml file

    2) If i change the connectionstring through the settings property window then linq to sql will connect to whatever DB i specify but if i just change the app.config file and save it then LINQ To SQL will connect to the old database (hence my problem). People without visual studio installed should be able to change connection info through the app.config.

    For some reason i thought in the past you couldn't set connection info in a dll. You had to go the application to set it but it seems to be working just as long as you go through the Application Settings window.

    Any help would be much appreciated.

    thanks,
    Ncage
    Wednesday, April 15, 2009 7:53 PM
  • Hi Ncage,

    In some place in your source code you must create an instance of the DataConext object Right? something like this:

    Dim Db As New BaseDatosDataContext()

    When you create a new DataContext instance you have the possibility to specify the connection string to use when connect, so, you can use something like this:

    In the .config file:

      <appSettings>
        <add key="Conexion" value ="Data Source=someserver;Initial Catalog=someDB;Persist Security Info=True;User ID=someUser;Password=somePassword"/>
      </appSettings>


    In the dll

    Dim Conexion As String = ""
    Conexion = ConfigurationManager.AppSettings("Conexion")
    Dim dB As New BaseDatosDataContext(ConexionGeneral)

    In this Way, when you deploy your app you have to change the connection string specified in the appSettings section inside the .config file,

    There it is.
    Friday, April 17, 2009 4:51 PM

All replies

  • I have now been playing around with it for while and this is what ive found out.

    1) It also inserts the connectionstring in the .dbml file (you can see this if you edit it with a text viewer instead of the designer) which i think is screwy in itself but you can tell it to use the application settings connection string in the properties off of the designer for the dbml file

    2) If i change the connectionstring through the settings property window then linq to sql will connect to whatever DB i specify but if i just change the app.config file and save it then LINQ To SQL will connect to the old database (hence my problem). People without visual studio installed should be able to change connection info through the app.config.

    For some reason i thought in the past you couldn't set connection info in a dll. You had to go the application to set it but it seems to be working just as long as you go through the Application Settings window.

    Any help would be much appreciated.

    thanks,
    Ncage
    Wednesday, April 15, 2009 7:53 PM
  • Hi Ncage,

    In some place in your source code you must create an instance of the DataConext object Right? something like this:

    Dim Db As New BaseDatosDataContext()

    When you create a new DataContext instance you have the possibility to specify the connection string to use when connect, so, you can use something like this:

    In the .config file:

      <appSettings>
        <add key="Conexion" value ="Data Source=someserver;Initial Catalog=someDB;Persist Security Info=True;User ID=someUser;Password=somePassword"/>
      </appSettings>


    In the dll

    Dim Conexion As String = ""
    Conexion = ConfigurationManager.AppSettings("Conexion")
    Dim dB As New BaseDatosDataContext(ConexionGeneral)

    In this Way, when you deploy your app you have to change the connection string specified in the appSettings section inside the .config file,

    There it is.
    Friday, April 17, 2009 4:51 PM
  • Hi everyone!

    Here is another simple solution.

    Put you connection string in the web.config of your Web-Project:

     <connectionStrings>
      <add name="DokumaxDatabaseConnectionString" connectionString="....." providerName="System.Data.SqlClient" />
     </connectionStrings>

    Then, change the constructor of the DataContext object in the the .dbml file:

    public DokumaxDataContext() :
                base(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString.ToString(), mappingSource)
      {
       OnCreated();
      }
    Monday, May 04, 2009 2:23 PM
  • Hey Thanx.. It really helped a lot.
    Will send u the link of our website for feedback.

    Abhishek
    Monday, October 05, 2009 11:27 AM