Set Entity Framework connection string at runtime

Question

I have generated entity model from AdventureWorks database; now I want to delete the connection string in app.config and set it at runtime. In the Model1.Context.cs file I have chnaged the constructor to

public AdventureWorksEntities(string str)
        : base("name=AdventureWorksEntities")
    {
        this.Database.Connection.ConnectionString = str;
    }

and in the program.cs file

EntityConnectionStringBuilder ecsb = new EntityConnectionStringBuilder();
        ecsb.Metadata = @"res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl";
        ecsb.Provider = @"System.Data.SqlClient";
        ecsb.ProviderConnectionString =
            @"data source=.\sqlexpress;initial catalog=AdventureWorks;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework";

        using (var ent = new AdventureWorksEntities(ecsb.ConnectionString))
        {
            Console.WriteLine(ent.Database.Connection.ConnectionString);
            var add = ent.Addresses;
            foreach (var ad in add)
            {
                Console.WriteLine(ad.City);
            }


        }
        Console.ReadKey();

Now it says metadata keyword not found. How to set connectionstring for entityframework at runtime?

William Smith · Accepted Answer · 2013-08-13 11:41:50Z

This is an example using standard .aspx login information to set the UserID and Password information in the connection string. No connection string settings are stored in the web.config or app.config file.

Modify the Model.Designer.cs page as follows:

public partial class Entities : ObjectContext
{
    #region Constructors


    public static string getConStrSQL(string UID,string PWD)
    {

        string connectionString = new System.Data.EntityClient.EntityConnectionStringBuilder
        {
            Metadata = "res://*",
            Provider = "System.Data.SqlClient",
            ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder
            {
                InitialCatalog = "your_database_name",
                DataSource = "your_server",
                IntegratedSecurity = false,
                UserID = UID,                
                Password = PWD,              
            }.ConnectionString
        }.ConnectionString;

        return connectionString;
    }

    /// <summary>
    /// Initialize a new Entities object.
    /// </summary>
    public Entities(string UID,string PWD)
        : base(getConStrSQL(UID,PWD), "Entities")
    {
        this.ContextOptions.LazyLoadingEnabled = true;
        OnContextCreated();
    }
    ......

Then in your code behind page:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Mvc;
using System.Web.Security;


public partial class views_html_form : System.Web.UI.Page
{
public void Page_Load()
{
    if (currentUser() == null)
    {
        HttpContext.Current.Response.Redirect("~/login.aspx");
    }
}
public static MembershipUser currentUser()
{
    MembershipUser currentUser = Membership.GetUser();
    return currentUser;
}

public static string UID()
{
    string UID = currentUser().UserName;
    return UID;
}
public static string PWD()
{
    string PWD = currentUser().GetPassword();
    return PWD;
}
public static void SelectRecord()
{
    YourModel.Entities db = new YourModel.Entities(UID(), PWD());
    var query = from rows in db.Table_Name orderby rows.ID select rows;
   .....

That's it. No messing around with .config files. Alternatively you could send a database name, for example, as a parameter in the same way.

JochemKempe · Accepted Answer · 2014-01-15 15:25:09Z

2

I'd go with something like:

 public AdventureWorksEntities(string server, string databaseName, string user, string password)
        :base(new System.Data.EntityClient.EntityConnectionStringBuilder
        {
            Metadata = "res://*",
            Provider = "System.Data.SqlClient",
            ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder 
            {
                InitialCatalog = databaseName,
                DataSource = server,
                IntegratedSecurity = false,
                UserID = user,
                Password = password,

            }.ConnectionString
        }.ConnectionString) 
    {

    }

answered Jan 15, 2014 at 15:25

JochemKempe

2,8242 gold badges18 silver badges12 bronze badges

Collectives™ on Stack Overflow

Set Entity Framework connection string at runtime

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related