Solved

Changing Connection String at runtime when using Entity Framework Model First

Posted on 2014-01-29
1
1,524 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 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

828 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