?
Solved

Changing Connection String at runtime when using Entity Framework Model First

Posted on 2014-01-29
1
Medium Priority
?
1,675 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
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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…

588 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