locked
How to change EDMX ConnectionString from Dev. to Prod RRS feed

  • Question

  • I got a class library dll (C#) which has EDMX with a connection to my dev sql server.
    I did compile and test fine.
    I create a setup deploy file for installation on client which includes the compile dll file and SQL Script to create the SQL database. (myassemblyfile.dll, mydatabase.sql)
    Obvioulsy the client will have the same database coming from the instalation but different password and SQL server name.
    How do I change the edmx connectionstring to point to the customer sql server with proper credential.
    Where should and how to store those infos for the fastest retreival. I may have few dozen iteration call to this dll at the same time?
    And this dll could install on many different customer

    This is from my app.config but this is not accessible when deployed on the client. Is it?
    <

    connectionStrings>
    <
    add name="myOBJEntities" connectionString="metadata=res://*/myOBJ.csdl|res://*/myOBJ.ssdl|res://*/myOBJ.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=mySQLServerSourceName;Initial Catalog=myOBJ;User ID=*****;Password=*****;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
    </
    connectionStrings>
    Friday, March 11, 2011 10:18 PM

Answers

  • Hi,

    First of all, you can safely change the connection string on the production server. What you need to change is the information after the provider connection string part of the entity connection string (between the &quot; you see there).

    But if you don't want to do this you'll have several different approaches. Here's some examples:

    So what you could do is to define a copy of your myOBJEntities in your connectionstring table and define it like this:

    <add name="myOBJEntitiesTempl" connectionString="metadata=res://*/myOBJ.csdl|res://*/myOBJ.ssdl|res://*/myOBJ.msl;provider=System.Data.SqlClient;provider connection string=&quot;{0}&quot;" providerName="System.Data.EntityClient" /> 
    
    

    You could then in your code get this from your ConfigurationManager.ConnectionStrings and do a string.Format on it with the connection string to your database.

    Like this:

    string conn = string.Format(ConfigurationManager.ConnectionStrings["myOBJEntitiesTempl"].ConnectionString, databaseConnection);
    
    using(myOBJEntities context = new myOBJEntities(conn))
    {
      //.... 
    }
    

    There is also possible for you to modify the ((EntityConnection)context.Connection).StoreConnection.ConnectionString, but remember, you have to do this as soon as your context is created. 

    using(myOBJEntities context = new myOBJEntities(conn))
    {
      ((EntityConnection)context.Connection).StoreConnection.ConnectionString = databaseConnection;
    }
    

    On the question where you should store it, well that is a bit difficult to answer and may be different from environment to environment. If this is an application that only uses one connection, I would recommend you just to change your connection information in myOBJEntities, since this is the cleanest way. If you don't want to do this, you could add additional connectionstrings to the connectionStrings section of your file and databaseConnection in my examples above from there.

    Hope this helps!


    --Rune
    • Marked as answer by Jackie-Sun Tuesday, March 22, 2011 8:28 AM
    Saturday, March 12, 2011 5:00 PM
  • Hi Energie Design,

     

    Thanks for your feedbaack.

    As I know, This article describes how to programmatically specify the client network library in the connection string when you connect to a SQL Server database.

    In Microsoft Data Access Components (MDAC) 2.6 and later, you can specify the client access library by using the <var>server name</var> parameter in connection string. Therefore, you can specify a specific client access library when you are prompted by an application for a server name to which to connect. This behavior can be very useful when you are testing and troubleshooting connectivity issues for SQL Server.

    For example, you can use the Osql command-line utility to connect to SQL Server and to force it to use the TCP/IP network library:

    osql -Stcp:myServer,portNumber -E
    
    Code Sample:
    The following Microsoft Visual C# .NET code sample demonstrates how to set the connection string. The connection string has the same format irrespective of the language that you use: 
     
    using System;
    using System.Data;
    using System.Data.SqlClient;

    namespace getCurrentProtocol
    {
    /// <summary>
    /// Main Application Driver Class
    /// </summary>
    class Driver
    {
    static void Main(string[] args)
    {
    string sCxn = "server=myServer;Integrated Security=SSPI; database=master";
    //string sCxn = "server=np:myServer;Integrated Security=SSPI; database=master";
    //string sCxn = "server=tcp:myServer;Integrated Security=SSPI; database=master";
    //string sCxn = "server=rpc:myServer;Integrated Security=SSPI; database=master";
    //string sCxn = "server=lpc:myServer;Integrated Security=SSPI; database=master";
    string sCmd = "SELECT net_library from sysprocesses where spid=@@spid";
    SqlConnection cxn = new SqlConnection(sCxn);
    SqlCommand sqlCmd = new SqlCommand(sCmd, cxn);
    SqlDataAdapter sqlDa = new SqlDataAdapter(sCmd, cxn);
    DataTable dt = new DataTable();
    try
    {
    sqlDa.Fill(dt);
    Console.WriteLine("Hit ENTER to continue ...");
    Console.ReadLine();
    foreach (DataRow dr in dt.Rows)
    Console.WriteLine(dr["net_library"]);
    }
    catch (SqlException e)
    {
    Console.WriteLine(e.StackTrace);
    Console.WriteLine("SQL Error Number: " + e.Number);
    Console.WriteLine("SQL Error Message: " + e.Message);

    }
    }
    }
    }
     
    Hope this helps.
    Have a ncie day,

    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Jackie-Sun Tuesday, March 22, 2011 8:27 AM
    Wednesday, March 16, 2011 6:26 AM

All replies

  • Hi,

    First of all, you can safely change the connection string on the production server. What you need to change is the information after the provider connection string part of the entity connection string (between the &quot; you see there).

    But if you don't want to do this you'll have several different approaches. Here's some examples:

    So what you could do is to define a copy of your myOBJEntities in your connectionstring table and define it like this:

    <add name="myOBJEntitiesTempl" connectionString="metadata=res://*/myOBJ.csdl|res://*/myOBJ.ssdl|res://*/myOBJ.msl;provider=System.Data.SqlClient;provider connection string=&quot;{0}&quot;" providerName="System.Data.EntityClient" /> 
    
    

    You could then in your code get this from your ConfigurationManager.ConnectionStrings and do a string.Format on it with the connection string to your database.

    Like this:

    string conn = string.Format(ConfigurationManager.ConnectionStrings["myOBJEntitiesTempl"].ConnectionString, databaseConnection);
    
    using(myOBJEntities context = new myOBJEntities(conn))
    {
      //.... 
    }
    

    There is also possible for you to modify the ((EntityConnection)context.Connection).StoreConnection.ConnectionString, but remember, you have to do this as soon as your context is created. 

    using(myOBJEntities context = new myOBJEntities(conn))
    {
      ((EntityConnection)context.Connection).StoreConnection.ConnectionString = databaseConnection;
    }
    

    On the question where you should store it, well that is a bit difficult to answer and may be different from environment to environment. If this is an application that only uses one connection, I would recommend you just to change your connection information in myOBJEntities, since this is the cleanest way. If you don't want to do this, you could add additional connectionstrings to the connectionStrings section of your file and databaseConnection in my examples above from there.

    Hope this helps!


    --Rune
    • Marked as answer by Jackie-Sun Tuesday, March 22, 2011 8:28 AM
    Saturday, March 12, 2011 5:00 PM
  • Thanks,

    This application would be a stand alone dll, so I still have to find a way of getting the SQL sever name from the client (the SQL may be on the internal network as well or local host).

    I like the approach of making a Template connectionstring wich allow me to create my EDMX without breaking it at design time. and only when on run time to switch to the template.

    I still have to find a solution for this SQL Server name to be retreive from somewhere effienciently If I cannot send it as parameter on the new myOBJEntities(***))
    Anymore idea would be appreciated?

    Thanks

    Monday, March 14, 2011 8:08 PM
  • Hi Energie Design,

     

    Thanks for your feedbaack.

    As I know, This article describes how to programmatically specify the client network library in the connection string when you connect to a SQL Server database.

    In Microsoft Data Access Components (MDAC) 2.6 and later, you can specify the client access library by using the <var>server name</var> parameter in connection string. Therefore, you can specify a specific client access library when you are prompted by an application for a server name to which to connect. This behavior can be very useful when you are testing and troubleshooting connectivity issues for SQL Server.

    For example, you can use the Osql command-line utility to connect to SQL Server and to force it to use the TCP/IP network library:

    osql -Stcp:myServer,portNumber -E
    
    Code Sample:
    The following Microsoft Visual C# .NET code sample demonstrates how to set the connection string. The connection string has the same format irrespective of the language that you use: 
     
    using System;
    using System.Data;
    using System.Data.SqlClient;

    namespace getCurrentProtocol
    {
    /// <summary>
    /// Main Application Driver Class
    /// </summary>
    class Driver
    {
    static void Main(string[] args)
    {
    string sCxn = "server=myServer;Integrated Security=SSPI; database=master";
    //string sCxn = "server=np:myServer;Integrated Security=SSPI; database=master";
    //string sCxn = "server=tcp:myServer;Integrated Security=SSPI; database=master";
    //string sCxn = "server=rpc:myServer;Integrated Security=SSPI; database=master";
    //string sCxn = "server=lpc:myServer;Integrated Security=SSPI; database=master";
    string sCmd = "SELECT net_library from sysprocesses where spid=@@spid";
    SqlConnection cxn = new SqlConnection(sCxn);
    SqlCommand sqlCmd = new SqlCommand(sCmd, cxn);
    SqlDataAdapter sqlDa = new SqlDataAdapter(sCmd, cxn);
    DataTable dt = new DataTable();
    try
    {
    sqlDa.Fill(dt);
    Console.WriteLine("Hit ENTER to continue ...");
    Console.ReadLine();
    foreach (DataRow dr in dt.Rows)
    Console.WriteLine(dr["net_library"]);
    }
    catch (SqlException e)
    {
    Console.WriteLine(e.StackTrace);
    Console.WriteLine("SQL Error Number: " + e.Number);
    Console.WriteLine("SQL Error Message: " + e.Message);

    }
    }
    }
    }
     
    Hope this helps.
    Have a ncie day,

    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Jackie-Sun Tuesday, March 22, 2011 8:27 AM
    Wednesday, March 16, 2011 6:26 AM