Click here to Skip to main content
14,393,257 members

Demystify LocalDB in ASP.NET with sqlcmd

Rate this:
5.00 (1 vote)
Please Sign up or sign in to vote.
5.00 (1 vote)
18 Jan 2016CPOL
Demystify LocalDB in ASP.NET with sqlcmd

LocalDB can be a bit magical to work with. Since you can't drop into a console, and treat it like SQL Server, I wondered, wouldn't it be nice if you could? In this take, I would like to start with a simple ASP.NET app and see what LocalDB is capable of. My initial struggle was to break free from Visual Studio. The tooling around LocalDB and Visual Studio is nice, but is there more to the story?

The Model

To keep it stupid simple, the data model is a secret message stored somewhere. One could conceptualize:

public class Secret
    public int Id { get; set; }
    [StringLength(16, ErrorMessage = "Max 16")]
    public string Message { get; set; }

Excellent, with code first, let's wire up this data model:

internal class ApplicationDatabaseInitialize
    : CreateDatabaseIfNotExists<ApplicationDbContext>
    protected override void Seed(ApplicationDbContext context)
        context.Secrets.AddOrUpdate(new Secret { ... });

I will not cover the ApplicationDbContext here since it is self explanatory. The idea is to leverage Entity Framework to create the database. The cool thing is we now have a solid data model from a conceptual C# abstraction.

The Data Layer

Using the repository pattern, here is the contract I chose:

public interface ISecretRepository : IDisposable
    Task<Secret> GetSecret();

With this in place, let's write up integration tests for the data access layer. I have opted to skip unit tests this time. I feel it's out of scope for now. The goal is to wire up a database in ASP.NET so I can explore LocalDB. Feel free to figure out what unit tests will look like.

[TestMethod, TestCategory("Integration")]
public async Task Repository_SecretGetSecretMessage()
    Database.SetInitializer(new ApplicationDatabaseInitialize());
    var context = new ApplicationDbContext();
    var repo = new SecretRepository(context);
    var result await repo.GetSecret();

I will let you imagine what the repository implementation will look like. With this awesome sauce, I get passing tests:

Passing tests of data access layer

Wrap Up the Controller

Onto the controller which returns the secret message:

public async Task<ActionResult> Index()
    var secret = await _secretRepository.GetSecret();
    return Content(secret.Message);

The test checks contracts and data:

[TestMethod, TestCategory("Unit")]
public async Task Controller_HomeIndex()
    var mock = new Mock<ISecretRepository>();
    mock.Setup(m => m.GetSecret()).ReturnsAsync(new Secret { Message = "secret content" });
    var controller = new HomeController(mock.Object);
    var result = await controller.Index() as ContentResult;

Managing LocalDB

Now that we've got a database in LocalDB, time to fire up a command line console. The command we need is sqllocaldb. Chances are it's not on the PATH, so one can search for it in explorer. Once this executable gets added, one can do:

> sqllocaldb i
> sqllocaldb s MSSQLLocalDB
LocalDB instance "MSSQLLocalDB" started.

The i flag lists all local instances. With Visual Studio 2015, you get the latest which is MSSQLLocalDB. This is a lightweight local version of SQL Server 2014. The s is self evident, it spins up the local instance so we can play with it. All outside Visual Studio. There is documentation on sqllocaldb that walks through the intent of this command. Or, type up the command without any arguments for a full list of options.

Let’s Descent into sqlcmd

With the instance fired up, type up:

> sqlcmd -S (localdb)\MSSQLLocalDB
1> select left(name, 60) from sys.databases;
2> go

This lists the databases available, and one of those is the database I just created. It will have a hash appended to the end of the name. To make it easy to work with it, let's rename it:

1> alter database DEMYSTIFYLOCALDBSQLCMD_474f4506f19b41eeb89418cfd78da823
     modify name = 'DemystifyLocalDbSqlcmd';
2> go

With an easy to use local database, let's play with it!

1> use DemystifyLocalDbSqlcmd;
2> select left(table_name, 15) from information_schema.tables;
3> go

1> select left(column_name, 15), left(data_type, 15)
     from information_schema.columns where table_name = 'Secrets';
2> go

1> select Id, Message from Secrets;
2> go

Like in SQL Server 2014, information_schema reveals all the schema information. sqlcmd unlocks the full power of T-SQL in LocalDB. I am free to explore T-SQL, unhindered by the tooling inside Visual Studio. For hacker points, this is what the console looks like:

Command line tooling through sqlcmd


LocalDB and sqlcmd are a powerful combination to play around with T-SQL. You get all the tooling you need when you install Visual Studio. This is not the only way. Both tools are available through a separate install. Getting to know the command line is a good way to break out of Visual Studio. With tools like Visual Studio Code and C# going cross platform. There is ample freedom to explore coding experiences outside the IDE. I feel Visual Studio is the ultimate coding tool and there is opportunity to use it in this way. LocalDB is not exclusive to sqlcmd as it is accessible through SQL Server Management Studio. But, my penchant is towards the command line.

If interested, the entire demo is up on GitHub.

The post Demystify LocalDB in ASP.NET with sqlcmd appeared first on BeautifulCoder.NET.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Camilo Reyes
United States United States
Husband, father, and software engineer living in Houston Texas. Passionate about JavaScript, C#, and webbing all the things.

Comments and Discussions

-- There are no messages in this forum --
Technical Blog
Posted 18 Jan 2016

Tagged as


8 bookmarked