How to programatically set database filename?

Question

Following this tutorial, I've created my DbConfiguration class:

class ImgSigDbConfig : DbConfiguration
{
    public ImgSigDbConfig()
    {
        SetDefaultConnectionFactory(new LocalDbConnectionFactory("v11.0")); 
    }
}

And told my DbContext to use it:

[DbConfigurationType(typeof(ImgSigDbConfig))] 
class ImgSimContext : DbContext 
{

Which seems to connect fine, but I have no idea where it's storing my data, and I don't like that. I would like it to save everything to a file in the App_Data folder. How can I specify that from inside my ImgSigDbConfig constructor? (Assume I know nothing about SQL server connections)

Did some more digging and came up with this:

public ImgSigDbConfig()
{
    var sqlConnBuilder = new SqlConnectionStringBuilder();
    sqlConnBuilder.DataSource = ".";
    sqlConnBuilder.InitialCatalog = "ImgSim.ImgSimContext";
    sqlConnBuilder.IntegratedSecurity = true;
    sqlConnBuilder.AttachDBFilename = @"App_Data\database.mdf";

    SetDefaultConnectionFactory(new LocalDbConnectionFactory("v11.0", sqlConnBuilder.ToString()));
}

But now it throws:

The provider did not return a ProviderManifestToken string

Per haim and gunther's suggestions I removed the superfluous connection settings:

class ImgSigDbConfig : DbConfiguration
{
    public ImgSigDbConfig()
    {
        var sqlConnBuilder = new SqlConnectionStringBuilder();

        sqlConnBuilder.IntegratedSecurity = true;
        sqlConnBuilder.MultipleActiveResultSets = true;

        SetDefaultConnectionFactory(new LocalDbConnectionFactory("v11.0", sqlConnBuilder.ToString()));
        SetManifestTokenResolver(new DefaultManifestTokenResolver());
    }
}

And moved them into Program.cs:

var conn = new SqlConnectionStringBuilder
{
    AttachDBFilename = @"App_Data\database.mdf",
    //InitialCatalog = "ImgSim.ImgSimContext",
};

using (var db = new ImgSimContext(conn.ToString()))

But even so, I get a System.Data.Entity.Core.ProviderIncompatibleException with InnerException:

The provider did not return a ProviderManifestToken string.

Even though I set a SetManifestTokenResolver -- I guess the default one doesn't work??

I'm not an EF guru so I may be well off hear on what you've understood - but typically you provide a connectionstring in the web.config or app.config who's name matches that of your context (ImgSimContext). This will be the DB that get's used so you know exactly where the data is going. If that's what you're after I'll flesh out an answer. — Ian
– Ian, Commented Jun 14, 2014 at 19:28
@Ian I'd prefer not to write XML if I can do it directly in C#. I don't understand any of this -- I'm used to MySQL + linux. In that environment, I start the mysql daemon, programatically set a few connection settings, and connect to it. I can see the daemon running in my processes, and I can find the data on disk. I can view my data with SqlYog or PMA. I have no idea what Visual Studio is doing for me. Is it starting up an SQL Express server every time I run my app? Is "localdb" some equivalent of sqlite? Where's the data on disk? How do I view it? — mpen
– mpen, Commented Jun 14, 2014 at 19:40
localDB is some sort of light SQL (not sure exactly what). Have you got Sql Server Management Studio? If so launch a connection to (localdb)\v11.0 I think and you'll see the database listed — Ian
– Ian, Commented Jun 14, 2014 at 19:44
Why do you initialize the context with an invalid connection-string? — haim770
– haim770, Commented Jun 14, 2014 at 21:16

Avneesh · Accepted Answer · 2014-06-14 21:02:39Z

2

LocalDbConnectionFactory constructor v11.0 allows to connect to sqlserver 2012 and by default it tries to find the name of the connection string in app.config as context full name. Say you have not specified the connection string in config and want to create a database named Test ( In a datadirectory specified below) then you need to set the constructor like this:

   [DbConfigurationType(typeof(ImgSigDbConfig))]
class MyDbContext : DbContext
{
    public MyDbContext(string nameOrConnectionString)
        : base(nameOrConnectionString)
    {
    }

    public DbSet<Person> Persons { get; set; }
}

Now you can call the context :

AppDomain.CurrentDomain.SetData("DataDirectory", @"c:\App\DataDirectory");
        MyDbContext myDbContext = new MyDbContext("test"); //this is the name
        myDbContext.Persons.Add(new Person() { Id = 1, Name = "Name1" });
        myDbContext.SaveChanges();

The configuration will remain same:

class ImgSigDbConfig : DbConfiguration
{
    public ImgSigDbConfig()
    {
        SetDefaultConnectionFactory(new LocalDbConnectionFactory("v11.0"));
    }
}

Watch that I am setting the datadirectory manually as it is not a web app. You need to give permissions to this directory so that a new database can be created and attached. My app.config is like this:

    <configuration>  <configSections>    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />  </configSections>  <connectionStrings>
  </connectionStrings>
  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration>

answered Jun 14, 2014 at 21:02

Avneesh

6544 silver badges6 bronze badges

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

4 Comments

mpen Over a year ago

Well, that did it. I'm not quite sure why that was so complicated.

mpen Over a year ago

How do I give the folder permissions? Anything other than my project folder / App_Data fails?

Avneesh Over a year ago

You should be able to select the folder, right click properties and then set the user to have write permission under which the app will be running.

Avneesh Over a year ago

Under what user is your sql server running? Does it also have permissions to this folder?

Ian · Accepted Answer · 2014-06-14 19:51:35Z

So by default Entity Framework will look for a connection string to determine which database it should open, the current one it's using is a a version of SQL Expres (see here). This is just the default setup making development easy for you.

A typical website will have a web.config file that you can find. In there you'd expect to see something like:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
          <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=testdb;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\testdb.mdf" providerName="System.Data.SqlClient" />
  </connectionStrings>

For a windows forms/console application there will be an app.config instead, these serve the same purpose however.

This connection string needs to be modified to use the database you're interested in, at this point I'd recommend connectionstrings.com. You can make it point at SQL, Oracle or whatever you like pretty much.

grabthefish · Accepted Answer · 2014-06-14 20:15:05Z

1

You can create a database based on a connectionstring you give to your ImgSigContext

example:

public class ImgSigContext : DbContext{
   public ImgSigContext(string connectionString) : base(connectionString)
}

and create it by using

string connString = "..."; //here comes a valid connectionstring
bool created = new ImgSigContext(connString).Database.CreateIfNotExists();
//returns true if database didn't exist and is succesfully created
//works from Entity FrameWork 5 and up

or like this

// will automatically create if migrations is enabled
using(var context = new ImgSigContext(connString))
{
   // some logic comes here
}

edited Jun 14, 2014 at 20:15

answered Jun 14, 2014 at 20:00

grabthefish

91814 silver badges32 bronze badges

Comments

haim770 · Accepted Answer · 2014-06-14 20:23:15Z

1

The documentation of the baseConnectionString parameter on MyLocalDbConnectionFactory constructor explicitly states that:

The connection string to use for options to the database other than the 'Initial Catalog', 'Data Source', and 'AttachDbFilename'. The 'Initial Catalog' and 'AttachDbFilename' will be prepended to this string based on the database name when CreateConnection is called. The 'Data Source' will be set based on the LocalDbVersion argument.

Try this instead:

var sqlConnBuilder = new SqlConnectionStringBuilder();
sqlConnBuilder.IntegratedSecurity = true;
sqlConnBuilder.MultipleActiveResultSets = true;

If you're running a Console Application make sure your DataDirectory is actually set. Use: AppDomain.CurrentDomain.GetData("DataDirectory").

answered Jun 14, 2014 at 20:23

haim770

49.2k7 gold badges113 silver badges139 bronze badges

3 Comments

mpen Over a year ago

Aha. Didn't notice that. It is indeed a console app. AppDomain.CurrentDomain.GetData("DataDirectory") returns null. AppDomain.CurrentDomain says "There are no context policies".

haim770 Over a year ago

Try to set it to your App_Data or rewrite the original LocalDbConnectionFactory to accommodate your needs (source: entityframework.codeplex.com/SourceControl/latest#src/…).

haim770 Over a year ago

Also, if you'll watch myDbContext.Database.Connection.ConnectionString (using your current code) you'll find it invalid.

Collectives™ on Stack Overflow

How to programatically set database filename?

4 Answers 4

4 Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related