Add a localdb for tests

Question

I use entity framework 6 code first.

At the moment I have a database for production. I want to add a different database for tests. How do I do that?

Do I need another connection string in my tests project?

What should be the difference between the two connection strings?

I tried copying the connection string and change the Catalog name but it results in mapping to the same database.

Please help me understand each part of the connection string in general

Thanks

Related - http://stackoverflow.com/questions/3090311/different-application-settings-depending-on-configuration-mode/27546685#27546685 — Eugene Podskal, Apr 10 '16 at 14:03
I don't see how it relates to my question. A guide to how to seperate config files? I just want to know how to add a different database — , Apr 10 '16 at 14:10
What problems does it cause if you change the catalog name? As long as schema of your database is same everything should work fine or you make use of database name in your code some where which breaks flow? — RBT, Apr 10 '16 at 14:11
@S.Peter, well as I understand you want to have separate database settings configured for testing and release? That's what different versions of the app.config are for, though I am not sure how to handle it between different projects (code and test). — Eugene Podskal, Apr 10 '16 at 14:12
@RBT After running the tests and than running the code, the production database is not created. Should the databases connection name be different? Should the catalog name be different? What attribute in the app config causes a different database to be created? — , Apr 10 '16 at 15:40

score 8 · Answer 1 · answered Apr 12 '16 at 18:32

Using local db in the local test environment

You can use localdb for your testing environment. Localdb is a file based database on your local development system which does not need a server to run but behaves like an SQL Server from your applications point of view.

I can describe the steps I would perform in Visual Studio 2013:

Open the "SQL Server Object Explorer" window
Expand the entry beginning with (localdb)v.11.0
Right click on "Database" and click "Add new Database" from the context menu
On my System this will create a localdb (mdf file) in my users folder e.g. C:\Users\USERNAME\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0
After the database is created you can create the tables in your localdb matching your live environment using scripts. For example, you can use SQL Server Management Studio to export the table structure (database -> Tasks -> Generate Scripts)
To get the connection string: Open the context menu of the database (still in Visual Studio SQL Server Object Explorer) and choose "Properties".
In the properties Windows locate the "Connection string" property and copy its value. Example: Data Source=(localdb)\v11.0;Initial Catalog=DATABASENAME;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False
Use this Connection string to create a new connectrion string in your web.config or app.config within the connectionStrings element. Put the value in the connectionString attribute. Do not change the providerName attribute.
```
 <connectionStrings>
   <add name="ConnectionName" connectionString="..." providerName="System.Data.SqlClient" />    
</connectionStrings>
```

Use that connection string name in your DbContext derived class:

public class YourDbContext : DbContext
{
    public YourDbContext() : base("ConnectionName")
    {        
    }
}

+1. Say I had two apps on my local PC (say WPF and WinForms). Could they both use the same LocalDB or should I create two separate LocalDBs that point to the same database? Thanks. — w0051977, Jul 01 '17 at 11:37
A local db file is similar to a SQL server instance. It may have more than one database (see answer above, there is a "Database" sub-entry that may contain several databases). Each database within the local db does have its own connection string which will contain the name of the database as "Initial catalog". So in your scenario you do not need to create more than one local db, just create two databases within the local db. — Martin, Jul 02 '17 at 00:00

Add a localdb for tests

1 Answers1