Explicit connection string for EF

Article
12/09/2008

The default constructor for the ObjectContext class in the Entity Data Model (EDM) retrieves the construction string from the web.config or app.config file. If you have multiple data models or need to pass in the the construction string at run time you must use the ObjectContext constructor that takes a construction string. (Note: Be sure to see the bug/work-around at the end of this article.) The follow snippet shows how to build the string for integrated security.

 //  copy connection string from app.config or web.config  

    // connectionString="metadata=res://*;
    // provider=System.Data.SqlClient;
    // provider connection string=&quot;
    // Data Source=ricka0;Initial Catalog=Northwind;Persist Security Info=True;
    // User ID=sa;Password=*(IU89iu;MultipleActiveResultSets=True&quot;"
    // providerName="System.Data.EntityClient" 
    

    public static string UglyConStr() {

        return "metadata=res://*;"
          + "provider=System.Data.SqlClient;"
          + "provider connection string=';"  // Replace &quot with ' (single quote)
        + "Data Source=ricka0;"
        + "Initial Catalog=Northwind;"
        + "Persist Security Info=True;"
        + "User ID=sa;Password=*(IU89iu;"
        + "MultipleActiveResultSets=True';"  // Replace &quot with ' (single quote)
            //    + "providerName=\"System.Data.EntityClient\""
        ;
    }

While the construction string above works, it's very ugly. The hair pulling trick to get it working is replacing &quot with a single quote ' as shown in the comments. Using the raw connection string from the config file (using &quot ) results in a misleading error message Keyword not supported: 'data source' .

A much more elegant construction string using SQL connection is shown below.

  public static string getConStrSQL() {

        string connectionString = new System.Data.EntityClient.EntityConnectionStringBuilder
        {
            Metadata = "res://*",
            Provider = "System.Data.SqlClient",
            ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder
            {
                InitialCatalog = "Northwind",
                DataSource = "ricka0",
                IntegratedSecurity = false,
                UserID = getUID(),                 // User ID such as "sa"
                Password = getPWD(),               // hide the password
            }.ConnectionString
        }.ConnectionString;

        return connectionString;
    }

In VB:

 Public Shared Function getConStrSQL() As String
     Dim connectionString As String = New System.Data.EntityClient.EntityConnectionStringBuilder() _
         With {.Metadata = "res://*", _
               .Provider = "System.Data.SqlClient", _
               .ProviderConnectionString = New System.Data.SqlClient.SqlConnectionStringBuilder() _
                 With {.InitialCatalog = "Northwind", _
                       .DataSource = "ricka0", _
                       .IntegratedSecurity = False, _
                       .UserID = getUID(), _
                       .Password = getPWD()}.ConnectionString}.ConnectionString
     Return connectionString
 End Function

The integrated security approach is slightly different.

 public static string getConStrIntegrated() {

        string conStrIntegratedSecurity = new System.Data.EntityClient.EntityConnectionStringBuilder
           {
               Metadata = "res://*",
               Provider = "System.Data.SqlClient",
               ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder
               {
                   InitialCatalog = "NorthwindEF",
                   DataSource = "bing0",
                   IntegratedSecurity = true,
               }.ConnectionString
           }.ConnectionString;

        return conStrIntegratedSecurity;
    }

 For Dynamic Data, simply pass the construction string to the MetaModel RegisterContext as follows.

  public static void RegisterRoutes(RouteCollection routes) {
        MetaModel model = new MetaModel();


        model.RegisterContext(() => new NorthwindModel.NorthwindEntities(getConStrIntegrated()),
            new ContextConfiguration()
            {
                ScaffoldAllTables = true
            });

        // Routes omitted for clarity
    }

Unfortunately, the current version of Dynamic Data doesn't support this approach with EF (L2S does work). To get the page templates to use the connection string you must add the following line to the Page_Load method in the page templates.

 GridDataSource.ContextCreating += delegate(object ceSender, System.Web.UI.WebControls.EntityDataSourceContextCreatingEventArgs ceArgs) {
            ceArgs.Context = (System.Data.Objects.ObjectContext)table.CreateContext();
        };

The complete Page_Load is below

 protected void Page_Load(object sender, EventArgs e) {
        table = GridDataSource.GetTable();

         GridDataSource.ContextCreating += delegate(object ceSender, System.Web.UI.WebControls.EntityDataSourceContextCreatingEventArgs ceArgs) {
            ceArgs.Context = (System.Data.Objects.ObjectContext)table.CreateContext();
        }; 

        Title = table.DisplayName;
        GridDataSource.Include = table.ForeignKeyColumnsNames;
        InsertHyperLink.NavigateUrl = table.GetActionPath(PageAction.Insert);

        // Disable various options if the table is readonly
        if (table.IsReadOnly) {
            GridView1.Columns[0].Visible = false;
            InsertHyperLink.Visible = false;
        }
    }

Comments

Anonymous
January 16, 2009
Rick Anderson Rick’s blog focuses on Dynamic Data, including a FAQs and Dynamic Data samples. Most current
Anonymous
February 18, 2009
Please post corrections/new submissions to the Dynamic Data Forum . Put FAQ Submission/Correction in
Anonymous
November 23, 2010
this method can be useful: protected static string createEFConnectionString(string connectionString, string edmxPathAndName) { if (connectionString.Contains("metadata")) return connectionString; if (connectionString.StartsWith("name=")) { var s = connectionString.Replace("name=", string.Empty).Trim(); return createEFConnectionString(ConfigurationManager.ConnectionStrings[s].ConnectionString, edmxPathAndName); } var efConnection = new System.Data.EntityClient.EntityConnectionStringBuilder { Metadata = string.Format("res:///{0}.csdl|res:///{0}.ssdl|res://*/{0}.msl", edmxPathAndName), Provider = "System.Data.SqlClient", ProviderConnectionString = connectionString }; return efConnection.ConnectionString; }
Anonymous
January 04, 2011
What about something like that: private static string EntityConnectionString2008 { get { // Specify the provider name, server and database. string providerName = "System.Data.SqlClient"; string serverName = @"localhostsql2008express"; string databaseName = "SiteDB"; // Initialize the connection string builder for the // underlying provider. SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(); // Set the properties for the data source. sqlBuilder.DataSource = serverName; sqlBuilder.InitialCatalog = databaseName; sqlBuilder.IntegratedSecurity = true; // Build the SqlConnection connection string. string providerString = sqlBuilder.ToString(); // Initialize the EntityConnectionStringBuilder. EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder(); //Set the provider name. entityBuilder.Provider = providerName; // Set the provider-specific connection string. entityBuilder.ProviderConnectionString = providerString; // Set the Metadata location. entityBuilder.Metadata = "res:///ArticlesEntities.csdl|res:///EFModel.ArticlesEntities2008.ssdl|res://*/ArticlesEntities.msl"; return entityBuilder.ToString(); } }
Anonymous
April 05, 2011
string connectionforEmodel = "metadata=res://*;provider=System.Data.SqlClient;provider connection string='" + _EPSConnection + "'";
Anonymous
August 09, 2011
The comment has been removed

Partager via

Explicit connection string for EF

Comments

Ressources supplémentaires