Creating LocalDB for testing from a Visual Studio SQL project

Question

I am trying to create integration tests for my project. I need to test a controller that makes a call to a stored procedure through a repository. An empty database must be created in each run of a specific test area. Therefore, I am going to follow these steps:

• Create LocalDB
• Run some preliminary scripts (to add test data)
• Launch test
• Run some post-scripts (if necessary to run another test in this database)
• Delete LocalDB

In my solution, I have .sqlproj with all tables and SP. How can I create LocalDB with the same structure as in .sqlproj from C # code? Or how can I generate a script with all objects to run it on LocalDB ?

.sqlproj exists in the bin .sqlproj folder , but it does not start in cmd.ExecuteNonQuery();

Any help / suggestion would be appreciated. Thanks

PS Related question How to disable deployment and postdeployment scripts in DacServices.Deploy ()

Answer 1

In the build folder of your sqlproj, you can also find the [DBName] .dacpac file. You can use sqlpackage.exe to deploy dacpac to localdb. Ben Day explains this blog quite nicely. You can find sqlpackage.exe in your sql server installation folder if it is not directly accessible from the command line.

 SqlPackage /Action:Publish /SourceFile:"Benday.SampleApp.Database.dacpac" /TargetDatabaseName:BendaySampleAppDb /TargetServerName:"demo2012util"