Using localdb in development

1 Reply

LocalDB is an instance of SQL Express. It is easy to use it in development from Visual Studio. I want to have localdb database files included into Visual Studio solution for easy connect to database wherever I work.

Sample solution can be downloaded here https://github.com/mchudinov/LocalDbExample
db

1. How to create a new localdb database

– Add new item to a project.
Choose item template called “SQL Server Database”
newdatabase
– Visual Studion with an ASP.NET solution will ask: “You are attempting to add a special file type (.mdf) to an ASP.NET Web site. Do you want to place the file in the ‘App_Data’ folder?”
youareattempting
Answer Yes.

Database file .mdf will be added to the solution under App_Data folder:
app_data

– Database file can be included in to source control. Then it will follow solution wherever a developer opens it.

– Double click on the newly created .mdf file in Solution Explorer and it will be opened in Server Explorer.

– Right click on Tables folder under database and choose Add New Table from context menu.
serverexplorer

– Define columns you need. Click on Update button to update database.
– Define table key and auto increment on it if needed
Right click on a key column and choose Properties.
Visual Studio will show column’s properties in Properties panel.
Set Identity Specification to True.
Choose Identity Increment and Seed.
This does not work for Guid uniqueidentifier type of keys.
identityspecification

– Add a connection string to solution

Data Source must be (LocalDB)\MSSQLLocalDB for Visual Studio 2015 and later.
For Visual Studio prior to 2015 it must be (LocalDB)\v11.0

AttachedDBFilename is where the .mdf file located. It must be |DataDirectory|\{filename}.mdf. |DataDirectory| definition is a solution folder or an App_Data folder under ASP.NET solution.

  <connectionStrings>
    <add name="DefaultConnection" 
         providerName="System.Data.SqlClient" 
         connectionString="Data Source=(LocalDb)\MSSQLLocalDB;
          Initial Catalog=WebAppTest;
          Integrated Security=True; 
          AttachDBFilename=|DataDirectory|\WebAppTest.mdf"/>
  </connectionStrings>

Here is documentation about SqlConnection.ConnectionString Property.

2. How to add an existing database to solution

Just copy the database .mdf file and _log.ldf file to App_Data folder and then drug and drop .mdf under App_Data folder in solution in Visual Studio.

Do not forget to add a connection string.

3. How to create a localdb outside a solution

– Start Visual Studio and open a SQL Server Object Explorer window in it. You do not need to open a solution.
– Find an instance of localdb you need. Use MSSQLLocalDB for VS 2015.
– Right click on Databases folder and use Add New Database from context menu.

serverobjectexplorer

Visual Studio asks about where to place database files. After the files are created they can be moved to another location. Just edit the AttachDBFilename parameter in connection string according to location.

4. Delete existing localdb database

– Right-click on .mdf file in Solution Explorer and choose delete. Rebuild solution.
– Open SQL Server Object Explorer and right-click on database and choose delete. This will delete connection.

deletelocaldb

5. How to trace localdb activity

Trace can be done using Profiler tool from Microsoft SQL Server Management Studio (MSSMS). The same way as for an ordinary SQL Express.

To connect to localdb we need a server address. To find an address for localdb instance a command line tool SqlLocalDB.exe can be used. Note! Visual Studio with a project must be running in order to localdb instances are sure to be active.
Use the following command to list all available localdb instances:
SqlLocalDb info
Instance named MSSQLLocalDB is for Visual Studio 2015, v11.0 is for VS prior to 2015.
Then use command SqlLocalDb info {instance name} to find the Instance pipe name – this is the address to connect in Profiler tool.

C:\>SqlLocalDB info
MSSQLLocalDB
ProjectsV12
v11.0

C:\>SqlLocalDB info MSSQLLocalDB
Name:               MSSQLLocalDB
Version:            12.0.2000.8
Shared name:
Owner:              AD-ONE\mchudino
Auto-create:        Yes
State:              Running
Last start time:    20.02.2016 10:05:03
Instance pipe name: np:\\.\pipe\LOCALDB#A25F3240\tsql\query

Address looks like np:\\.\pipe\LOCALDB#A25F3240\tsql\query

Copy-past it into Profiler login screen as Server name and use the Profiler as normal.

profiler

Note! Avoid to connect to this address from MSSMS or connection from Visual Studio will be lost. It is not possible to connect to the same localdb instance from different programs.

6. Troubleshooting

Changes in the database are not saved.
Do not worry, this is normal. VS copies the database file to output build folder and operates over the this copy. Then the original file in the project stays untouched.
This default behavior can be changed:
– right click the database .mdf file and select Properties
– set Copy To Output Directory to Do Not Copy
Then VS will operate on the original file.

copyfile

Solution build error: database file can not be copied.
This is because database file is locked by Visual Studio. Just close studio and open solution again.