?
Solved

Changing Connection String at runtime when using Entity Framework Model First

Posted on 2014-01-29
1
Medium Priority
?
1,610 Views
Last Modified: 2014-01-29
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
Comment
Question by:dyarosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 11

Accepted Solution

by:
jasonduan earned 1500 total points
ID: 39818207
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question