IT:AD:LocalDB

Summary

LocalDB is a lightweight version of the SQL Server Express Database Engine that is targeted for program development requirements. ## Note ## Not intended for production.

  • Default is to create “*.mdf” files directly under the %userprofile% directory.
  • The connection string that will work in a dev environment is (notice it does not have *.mdf after it):

"ConnectionStrings": {
  "MyProject": "Server=(localdb)\\mssqllocaldb;Database=MyProject;Trusted_Connection=True;MultipleActiveResultSets=true"
}

  • To connect to the server, from Visual Studio, specify:
    • (LocalDB)\v11.0 (the old way)
    • (LocalDB)\mysqllocaldb (the current correct way)

There is another way to connect to the database, using AttachDbFilename:


<add name="FooProject.Db.CN" providerName="System.Data.SqlClient" connectionString="Data Source=(localdb)\mssqllocaldb;AttachDbFilename=Z:\Foo\Bar\FFooProjectDb.mdf;Integrated Security=True" />

But if using the IT:AD:DataDirectory tag, there are several issues need consideration:

  • Advantages:
    • You can more easily delete/rebuild the database if it is local, or at least in a directory where you expect it (eg: you don't need to issue commands to the Sql Server instance to kill it).
  • Considerations:
    • In all likelihood, AttachDbFilename is not what you will be using in prod, so it's not a great habit to get into.
  • It offers portability and makes it easy to delete/recreate …but not easily with Code First, due to the issues raised here:

    Finally, because the default behavior was a bit unclean, I did try – to no avail – AttachDbFilename=%userprofile%\Databases\FFooProjectDb.mdf.

For all the above reasons, I've moved on from AttachDbFileName= to database=

Resources