Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Changing Connection String at runtime when using Entity Framework Model First

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

783 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