Programmatically creating a connection string for mapping an Entity Framework Code-First model with an existing Sql Server Compact database

Question

I've successfully mapped an Entity Framework Code-First data model with an existing Sql Server Compact database by a declarative approach using app.config but it would be great if I could build such connection programmatically with perhaps the help of the EntityConnectionStringBuilder (System.Data.EntityClient) class. Unfortunately this approach is not working as the [DbContext].Connection.ConnectionString is not accepting most of its properties.

Here's the actual working code with the faulty one commented out:

Book.cs

public class Book
{
    [Key]
    public string Isbn { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public string Publisher { get; set; }
    public DateTime Published { get; set; }
    public int Pages { get; set; }
    public bool InStock { get; set; }
    public string Description { get; set; }
}

Catalog.cs

public class Catalog : DbContext
{
    public DbSet<Book> Books { get; set; }
}

app.config

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add
      name="Catalog"
      providerName="System.Data.SqlServerCE.4.0"
      connectionString="Data Source=res/Catalog.sdf"
  />
  </connectionStrings>
</configuration>

Main()

static void Main()
{
    // var res = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "res");
    // var str = new EntityConnectionStringBuilder();
    // str.Name = "Catalog";
    // str.Provider = "System.Data.SqlServerCe.4.0";
    // str.ProviderConnectionString = String.Format("Data Source {0}", Path.Combine(res, "Catalog.sdf"));

    try
    {
        using (var catalog = new Catalog())
        {
            // catalog.Database.Connection.ConnectionString = str.ConnectionString;

    // remaining code not relevant - skipped

I've tried using other builder classes such as SqlCeConnectionStringBuilder (System.Data.SqlServerCe), SqlConnectionStringBuilder (System.Data.SqlClient) and even DbConnectionStringBuilder (System.Data.Common) but apparently none of them seem to match what [DbContext].Connection.ConnectionString is expecting.

Should I conclude there is no programmatic way to achieve this? Any advice will be surely appreciated. Thanks much in advance for your contributions.

Ladislav Mrnka · Accepted Answer · 2011-07-02 20:25:48Z

2

If you are using SQL Server Compact 4.0 and DbContext with code first you cannot use EntityConnectionStringBuilder - it builds connection string for EF with EDMX file. You need SqlCeConnectionStringBuilder from System.Data.SqlServerCe assembly with version 4.0.0.0!

You should also pass the connection string to the context instance through the constructor - DbContext has constructor accepting name of the connection string (from configuration) or connection string itself.

answered Jul 2, 2011 at 20:25

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nano Taboada Over a year ago

Thanks for the contribution Ladislav. I've tried creating the connection string with SqlCeConnectionStringBuilder but it results in an exception: The provider did not return a ProviderManifestToken string. Would you mind providing us with a sample code snippet that compiles?

Tom · Accepted Answer · 2011-09-29 22:27:16Z

2

Open a DbConnection and pass it to the DbContext constructor or use a DefaultConnectionFactory:

Database.DefaultConnectionFactory
    = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");

using (var catalog = new Catalog("Catalog.sdf"))
{
}

SqlCeConnectionFactory is a ready-to-use connection factory provided by Microsoft, but you can also implement your own factory.

answered Sep 29, 2011 at 22:27

Tom

7997 silver badges13 bronze badges

Collectives™ on Stack Overflow

Programmatically creating a connection string for mapping an Entity Framework Code-First model with an existing Sql Server Compact database

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related