• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1688
  • Last Modified:

Changing Connection String at runtime when using Entity Framework Model First

I am new to Entity Framework and have created a simple program using Entity Framework and MVC to learn EF.  I created the EF Model from my database (Model First).  When building the model, I used the database on our Development server.

Now that everything is running as expected, I need to make the app more flexible by setting the connection string at runtime so if the app is running on the Development server it will use the Development database; running on the UAT server will use the UAT database; and running on Production server will use the Production database.

I have a DLL that is included in the project that will return the correct connection string based on the server the app is running on.  The string return looks like the following (with security fields replaced with XXX):

"Data Source=pl-wdhsqlpp02;Initial Catalog=aigdirectnet;Persist Security Info=True;User ID=XXXXXXX;Password=XXXXXXXX;MultipleActiveResultSets=true;"

The connection string in the web.config looks like the following (with security fields replaced with XXX):

  <connectionStrings>
    <add name="aigdirectnetEntities" connectionString="metadata=res://*/Models.ENL.csdl|res://*/Models.ENL.ssdl|res://*/Models.ENL.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=PL-WDHSQLPP02;initial catalog=aigdirectnet;persist security info=True;user id=XXXXXXX;password=XXXXXXXX;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

I have a class, ENL_MetaData.cs, that is used to add Metadata to the fields in the model and in that class I am adding another method to instantiate my model using a connection string:

namespace ENL.Models
{
    public partial class aigdirectnetEntities : DbContext
    {
        public aigdirectnetEntities(string connectionString)
            : base(connectionString)
        {
            
        }
    }

    // ENL_GroupNames
    [MetadataType(typeof(ENL_GroupNamesMetadata))]
    public partial class ENL_GroupName { }

    public class ENL_GroupNamesMetadata
    {
        [UIHint("PrimaryKey")]
        [DisplayName("ID")]
        public int ENLGroupNameID { get; set; }
        [ENL.HtmlProperties(Size = 40, MaxLength = 30)]
        [Required(ErrorMessage = "* Group Name is required")]
        [DisplayName("Group Name")]
        public string ENLGroupName { get; set; }
        [DisplayName("Active?")]
        public bool IsActive { get; set; }
    }
}

Open in new window


In my controller I added a constructor to instantiate the model.  The automatic code generated line is commented out:

namespace ENL.Controllers
{
    public class ENL_GroupNamesController : Controller
    {
        //private aigdirectnetEntities db = new aigdirectnetEntities();
        private aigdirectnetEntities db;

        public ENL_GroupNamesController()
        {
            // Create reference to model using connection string
            // Used to establish connection strings based on server that site is running on
            string connectionString;
            i21Login Login = new i21Login();

            // Get SQL Server Connection String
            connectionString = "metadata=res://*/Models.ENL.csdl|res://*/Models.ENL.ssdl|res://*/Models.ENL.msl;" + Login.GetConnectionString("SQL");
            db = new aigdirectnetEntities(connectionString);
        }

        public ActionResult Index()
        {
            return View(db.ENL_GroupName.ToList().OrderBy(e => e.ENLGroupName));
        }
    }
}

Open in new window


When I run the app, I get an error in the Index method on the return View:
Keyword not supported: 'metadata'.

If I replace db = new aigdirectnetEntities(connectionString); with db = new aigdirectnetEntities(); everything works but it uses the connection string in the web.config which is what I don't want.

Any help is greatly appreciated!
0
dyarosh
Asked:
dyarosh
1 Solution
 
jasonduanCommented:
Things can be simpler.

1. Create separate web.config file for each environment.
2. The deployment will be a 2 steps process
    a. Deploy code package
    b. Deply the appropriate web.config file
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now