Partager via


Explicit connection string for EF

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="
    // Data Source=ricka0;Initial Catalog=Northwind;Persist Security Info=True;
    // User ID=sa;Password=*(IU89iu;MultipleActiveResultSets=True""
    // 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