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?

Answer 1

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) 
    {

    }

Answer 2

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.