Undocumented SQL Server 2012 Express LocalDB

As a developer of Microsoft Visual Studio, SQL Server 2012 Express LocalDB probably has gotten onto your machine without your notice. I will document some of my exploration on LocalDB in this post.

Installation and Location

There are three ways to get LocalDB onto your machines:

  1. Install together when installing Microsoft Visual Studio 2013 (this is my case);
  2. Install by using SqlLocalDB.msi found in SQL Server 2012 Express
  3. Install by downloading from Microsoft Download Centre directly (here).

The installation location is default to C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn where sqlserv.exe is the main application.

The tools (utilities) to operate the LocalDB are SqlLocalDB, SQLCMD and bcp which are located at C:\Program Files\Microsoft SQL Server\110\Tools\Binn. Make sure to include it into your PATH.

SqlLocalDB

This is the utility to administrate the localdb instances.

  • to get help: sqllocaldb -?
  • to print the version: sqllocaldb versions
  • to create an instance: sqllocaldb create “YourInstanceName”
  • to delete an instance: sqllocaldb delete “YourInstanceName”
  • to start an instance: sqllocaldb start “YourInstanceName”
  • to stop an instance: sqllocaldb stop “YourInstanceName”
  • to share an instance: sqllocaldb share “YourInstanceName”
  • to unshared an instance: sqllocadbl unshare “YourInstanceName”
  • to list all your instances: sqllocaldb info
  • to list the status of an instance: sqllocaldb info “YourInstanceName”
  • to set the trace on and off: sqllocaldb trace on|off

If you’ve used VS 2013 to connect to LocalDB, VS 2013 would have created an instance for you (in my case is v11.0).

Even your instance is stopped, it will be auto-started when you try to connect to it first time either via VS 2013 or SQLCMD.

SQLCMD, BCP, process ‘sqlservr’

Both SQLCMD and BCP are well documented. The only difference between LocalDB and SQL server is that we need to put a bracket ‘()’ to indicate it is a LocalDB instead of hostname for the named instance like:

sqlcmd -S ‘(LocalDB)\v11.0’

This is also applied to SSMS and VS 2013 connections. There is only one process “sqlservr” related to LocalDB. It is very lightweighted by using about 12MB RAM on my machine.

Some Examples

The following is executed in powershell:

Windows PowerShell
Copyright (C) 2013 Microsoft Corporation. All rights reserved.

PS C:\Users\henry> sqllocaldb info
Projects
v11.0
PS C:\Users\henry> sqllocaldb info "v11.0"
Name:               v11.0
Version:            11.0.3000.0
Shared name:
Owner:              PolarBear\henry
Auto-create:        Yes
State:              Stopped
Last start time:    12/31/2013 2:37:39 PM
Instance pipe name:
PS C:\Users\henry> sqllocaldb start "v11.0"
LocalDB instance "v11.0" started.
PS C:\Users\henry>  ps | where-object {$_.ProcessName -match 'sqlservr'}

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName
-------  ------    -----      ----- -----   ------     -- -----------
    492      20    67780      17140   311     3.83   2248 sqlservr


PS C:\Users\henry> sqllocaldb stop "v11.0"
LocalDB instance "v11.0" stopped.
PS C:\Users\henry> sqlcmd -S "(LocalDB)\v11.0"
1> use test
2> go
Changed database context to 'test'.
1> select count(*) from HR.Employees
2> go

-----------
          9

(1 rows affected)
1> shutdown
2> go
Server shut down by request from login PolarBear\henry.
1> exit
PS C:\Users\henry>

Sharing or Not

From A TechNet Article
When sharing a SqlLocalDB instance with a non-owner, you must re-start the instance for the other users to be able to see the instance you have shared. A non-owner cannot start an instance, so if you are going to share an instance with other users who can access your machine, you also need to be sure it has been started. When you create an instance you can do this as follows:

sqllocaldb create “MyInstance”
sqllocaldb share “MyInstance” “OurInstance”
sqllocaldb start “MyInstance”

You should add users explicitly when connected to the instance as the owner, e.g.

CREATE LOGIN [Domain\User] FROM WINDOWS;
GRANT CONNECT TO [Domain\User];
— other permissions…

In general, though, the purpose of SqlLocalDB is to serve as a sandbox for an individual developer on a machine, not to serve as a development environment for multiple users. Each user should be able to create, start, administer and use his/her own LocalDB instances.

Working With Model in Entity Framework

In the previous post, I showed how to reversed-engineer an entity framework model from a Microsoft SQL Server 2012 database, and query the model in a C# console program. In the following, I will expand it further on how to work on the model generated to perform the standard CRUD (Create, Read, Update, Delete) operations.

Model

The model generated in the last post was in a file (testContext.cs) where the database context (testContext) was derived from DbContext, in which the property Employees was exposed as an entity (DbSet). In the simple RDBMS terms, the physical database (test) is mapped to DbContext (testContext) while a physical table (employees) was mapped to an entity DbSet (Employees). The entity model is like a conceptual model in the database design process.

Little notes on database design process
In a normal database design process, we design a conceptual model first by using designer like Visual Studio EF Designer or Oracle SQL developer/modeler, which will produce an ER-Diagram (Entity-Relationship Diagram) as a result. From the model, we can generate the physical model which is a set of DDLs, to run on a specific RDBMS like Oracle, MS SQL Server, etc. and produce a database.

In our previous example, we reversed the design process from bottom up, and generated the conceptual model into Entity Framework in Visual Studio from the physical database structures in Microsoft SQL Server (Database: test; Table: HR.Employees). This process is called reversed engineering.

namespace EmployeeConsole.Models
{
    public partial class testContext : DbContext
    {
        ......
        public DbSet<Employee> Employees { get; set; }
        ......
    }
}

Work on Model

To work on the model, the first is to include your model, then instantiate a dbContext so that the CRUD ops can be performed within the instance lifetime.

Here is the code snippets. Refer to last post for details.

using EmployeeConsole.Models;
...
      using (var db = new testContext())
          {
             // TODO YOUR CRUD Ops
          }

How to Read (Find) a Record

            using (var db = new testContext())
            {

                var myemp = db.Employees.Find(3);
                Console.WriteLine("Locate and Display empid=3");
                Console.WriteLine("EmpID\t{0}",myemp.empid);
                Console.WriteLine("FName\t{0}", myemp.firstname);
                Console.WriteLine("LName\t{0}", myemp.lastname);
                Console.WriteLine("HDate\t{0}", myemp.hiredate);
                Console.WriteLine("Phone\t{0}", myemp.phone);
                Console.WriteLine("Country\t{0}", myemp.country);
                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }

How to Update a Record

            using (var db = new testContext())
            {

                // Find the record first
                var myemp = db.Employees.Find(3);
                Console.WriteLine("EmpID\t{0}",myemp.empid);
                Console.WriteLine("Firstname\t{0}", myemp.firstname);
                Console.WriteLine("Lastname\t{0}", myemp.lastname);

                // Update DbSet in memory
                myemp.lastname = "Smith";

                // Write to db
                db.SaveChanges();
                
                // db.SaveChanges is the same as the following RAW T-SQL
                //db.Database.ExecuteSqlCommand("update hr.employees set lastname='Smith' where empid=3");

                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }

How to Add a New Record

            using (var db = new testContext())
            {
                var newemp = new Employee();
                newemp.firstname = "Adam";
                newemp.lastname = "Smith";
                newemp.title="Economist";
                newemp.titleofcourtesy="";
                newemp.birthdate=Convert.ToDateTime("01/01/1980");
                newemp.hiredate=Convert.ToDateTime("01/01/2001");
                newemp.address="16 Bay Street";
                newemp.city="Toronto";
                newemp.region="Mr.";
                newemp.postalcode="B2C 2V7";
                newemp.country="CANADA";
                newemp.phone="(416) 822-2288";

                // Add to DbSet
                db.Employees.Add(newemp);
                // Write to db
                db.SaveChanges();

                // db.SaveChanges is the same as the following RAW T-SQL
                //db.Database.ExecuteSqlCommand("insert into hr.employees (..) values (...)");

                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }

How to Delete a Record

            using (var db = new testContext())
            {
                // identify the record first, (empid=10) is the new record inserted in last code snippet.
                var empToBeDeleted=db.Employees.Find(10);

                // remove from DbSet
                db.Employees.Remove(empToBeDeleted);

                // Write to db
                db.SaveChanges();

                // db.SaveChanges is the same as the following RAW T-SQL
                //db.Database.ExecuteSqlCommand("delete from hr.employees where empid=10");

                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();
            }

Further Reading

  • Working with Entity Data from MSDN
  • Working with Objects
  • Working with Data (Entity Framework Tutorial)
  • Generate model from database using Entity Framework Power Tool

    The software I am using are:

    • Microsoft Visual Studion 2013 Premium
    • Microsoft SQL Server 2012 Enterprise
    • Entity Framework 6.0.1
    • Entity Framework Power Tools 1.0
    • Windows Server 2008 R2

    Create Database Objects

    I created a test database with a table HR.Employees as the following in my local Microsoft SQL 2012 instance (full T-SQL script is here):

    CREATE TABLE HR.Employees
    (
      empid           INT          NOT NULL IDENTITY,
      lastname        NVARCHAR(20) NOT NULL,
      firstname       NVARCHAR(10) NOT NULL,
      title           NVARCHAR(30) NOT NULL,
      titleofcourtesy NVARCHAR(25) NOT NULL,
      birthdate       DATETIME     NOT NULL,
      hiredate        DATETIME     NOT NULL,
      address         NVARCHAR(60) NOT NULL,
      city            NVARCHAR(15) NOT NULL,
      region          NVARCHAR(15) NULL,
      postalcode      NVARCHAR(10) NULL,
      country         NVARCHAR(15) NOT NULL,
      phone           NVARCHAR(24) NOT NULL,
      mgrid           INT          NULL,
      CONSTRAINT PK_Employees PRIMARY KEY(empid),
      CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid)
        REFERENCES HR.Employees(empid),
      CONSTRAINT CHK_birthdate CHECK(birthdate <= CURRENT_TIMESTAMP)
    );
    

    Reversed Engineering DB Objects to Classes

    I created a simple C# console project called ‘EmployeeConsole’ in visual studio, which will create two files: Program.cs and App.config. Right Click on the project in Solution Explorer, and choose ‘Entity Framework – Reversed Engineering Code First’. Then connect to the instance (local) and database (test), and generate the model from test database for table ‘HR.Employees’.

    What Does Entity Framework Power Tool Do?

    1. Visual Studio first will install Entity Framework (runtime) into the project by creating packages.config as the following:

    <?xml version="1.0" encoding="utf-8"?>
    <packages>
      <package id="EntityFramework" version="6.0.1" targetFramework="net45" />
    </packages>
    

    2. Create model: a database context (testContext.cs)

    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using EmployeeConsole.Models.Mapping;
    
    namespace EmployeeConsole.Models
    {
        public partial class testContext : DbContext
        {
            static testContext()
            {
                Database.SetInitializer(null);
            }
    
            public testContext()
                : base("Name=testContext")
            {
            }
    
            public DbSet Employees { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Configurations.Add(new EmployeeMap());
            }
        }
    }
    

    3. Create model: a employees class in the model (Employee.cs)

    using System;
    using System.Collections.Generic;
    
    namespace EmployeeConsole.Models
    {
        public partial class Employee
        {
            public Employee()
            {
                this.Employees1 = new List();
            }
    
            public int empid { get; set; }
            public string lastname { get; set; }
            public string firstname { get; set; }
            public string title { get; set; }
            public string titleofcourtesy { get; set; }
            public System.DateTime birthdate { get; set; }
            public System.DateTime hiredate { get; set; }
            public string address { get; set; }
            public string city { get; set; }
            public string region { get; set; }
            public string postalcode { get; set; }
            public string country { get; set; }
            public string phone { get; set; }
            public Nullable mgrid { get; set; }
            public virtual ICollection Employees1 { get; set; }
            public virtual Employee Employee1 { get; set; }
        }
    }
    

    4. Create ORM: mapping POJO Employees class to physical table (EmployeeMap.cs) using Fluent API

    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.ModelConfiguration;
    
    namespace EmployeeConsole.Models.Mapping
    {
        public class EmployeeMap : EntityTypeConfiguration
        {
            public EmployeeMap()
            {
                // Primary Key
                this.HasKey(t => t.empid);
    
                // Properties
                this.Property(t => t.lastname)
                    .IsRequired()
                    .HasMaxLength(20);
    
                this.Property(t => t.firstname)
                    .IsRequired()
                    .HasMaxLength(10);
    
                this.Property(t => t.title)
                    .IsRequired()
                    .HasMaxLength(30);
    
                this.Property(t => t.titleofcourtesy)
                    .IsRequired()
                    .HasMaxLength(25);
    
                this.Property(t => t.address)
                    .IsRequired()
                    .HasMaxLength(60);
    
                this.Property(t => t.city)
                    .IsRequired()
                    .HasMaxLength(15);
    
                this.Property(t => t.region)
                    .HasMaxLength(15);
    
                this.Property(t => t.postalcode)
                    .HasMaxLength(10);
    
                this.Property(t => t.country)
                    .IsRequired()
                    .HasMaxLength(15);
    
                this.Property(t => t.phone)
                    .IsRequired()
                    .HasMaxLength(24);
    
                // Table & Column Mappings
                this.ToTable("Employees", "HR");
                this.Property(t => t.empid).HasColumnName("empid");
                this.Property(t => t.lastname).HasColumnName("lastname");
                this.Property(t => t.firstname).HasColumnName("firstname");
                this.Property(t => t.title).HasColumnName("title");
                this.Property(t => t.titleofcourtesy).HasColumnName("titleofcourtesy");
                this.Property(t => t.birthdate).HasColumnName("birthdate");
                this.Property(t => t.hiredate).HasColumnName("hiredate");
                this.Property(t => t.address).HasColumnName("address");
                this.Property(t => t.city).HasColumnName("city");
                this.Property(t => t.region).HasColumnName("region");
                this.Property(t => t.postalcode).HasColumnName("postalcode");
                this.Property(t => t.country).HasColumnName("country");
                this.Property(t => t.phone).HasColumnName("phone");
                this.Property(t => t.mgrid).HasColumnName("mgrid");
    
                // Relationships
                this.HasOptional(t => t.Employee1)
                    .WithMany(t => t.Employees1)
                    .HasForeignKey(d => d.mgrid);
    
            }
        }
    }
    

    5. Update App.config to include entity framework config and SQL connection string.

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <configSections>
        <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
      </configSections>
      <connectionStrings>
        <add name="testContext" connectionString="Data Source=(local);Initial Catalog=test;Integrated Security=True;MultipleActiveResultSets=True"
          providerName="System.Data.SqlClient" />
      </connectionStrings>
      <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
      </startup>
      <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
          <parameters>
            <parameter value="v11.0" />
          </parameters>
        </defaultConnectionFactory>
        <providers>
          <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
        </providers>
      </entityFramework>
    </configuration>
    

    Utilize your model

    The following is a simple program to display a list of employee records by using EmployeeConsole.Models which was defined in testContext.cs.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using EmployeeConsole.Models;
    
    namespace EmployeeConsole
    {
        class Program
        {
            static void Main(string[] args)
            {
    
                using (var db = new testContext())
                {
                    // Get all the rows into var query by using LINQ if you want
                    //var query = from el in db.Employees orderby el.firstname select el;
    
                    // Get all the rows into var query by using SQL
                    var query = db.Employees.SqlQuery("select * from hr.employees");
    
                    // Display Listing Header
                    Console.WriteLine("LIST OF EMPLOYEES WITH PHONES:");
    
                    // just display firstnamelastnamephone
                    foreach (var item in query)
                    {
                        Console.WriteLine("{0}\t{1}\t{2}", item.firstname, item.lastname, item.phone);
                    }
    
                    Console.WriteLine("Press any key to exit...");
                    Console.ReadKey();
                }
            }
        }
    }
    

    Limitation on Processing Raw T-SQL queries

    What if when the SQL query changed to the following:

                select * from employees
                select firstname,lastname,phone from hr.employees
    

    Both are valid T-SQL queries, they won’t work from the model generated by Entity Framework because the model mapping has the following:

                this.ToTable("Employees", "HR");
                this.Property(t => t.empid).HasColumnName("empid");
    

    The execution will complain either no schema object ’employees’ in the first case or requiring ’empid’ in the latter.

    To use the model generated, it’s better to use either LINQ-TO-ENTITY or ENTITY-SQL. There are differences between Entity SQL and T-SQL.

    Further Reading: ADO.NET Entity Framework & Entity SQL

    Entity Framework 6 and Visual Studio 2013

    Entity Framework 6 has been included in Visual Studio 2013. It is also available as a NuGet Package which can be installed by using NuGet Package Manager.

    There are three ways you can work with data models in the Entity Framework: Code First, Model First, and Database First .

    Code First

    Whether you have an existing database or not, you can use the Entity Framework without using the Entity Framework Designer or an .edmx file. If you don’t have a database, you can code your own classes and properties that correspond to tables and columns. If you do have a database, Entity Framework tools can generate the classes and properties that correspond to existing tables and columns. The mapping between the store schema and the conceptual model represented by your code is handled by convention and by a special mapping API. If you let Code First create the database, you can use Code First Migrations to automate the process of deploying the database to production. Migrations can also automate the deployment of database schema changes to production when your data model changes. The following are details:

    Model First

    If you don’t have a database yet, you can begin by creating a model in an .edmx file by using the Entity Framework graphical designer in Visual Studio. When the model is finished, the Entity Framework designer can generate DDL (data definition language) statements to create the database. As in Database First, the .edmx file stores model and mapping information.

    The detail of how to use model first is here.

    Database First

    If you already have a database, the Entity Framework designer built into Visual Studio can automatically generate a data model that consists of classes and properties that correspond to existing database objects such as tables and columns. The information about your database structure (store schema), your data model (conceptual model), and the mapping between them is stored in XML in an .edmx file. The Entity Framework designer provides a graphical UI that you can use to display and edit the .edmx file.

    The detail of how to use database first is here.

    Some tricks on Visual Studio 2010

    •Suggestion mode [CTRL]+[ALT]+[SPACE]
    •Find all references [SHIFT]+[F12]
    •Navigating Highlights   [CTRL]+[SHIFT]+[UP|DOWN]
    •Code: ZOOM Mouse and Keyboard   [CTRL]+[SHIFT]+[<|>]
    •Outline Hide [CTRL]+[M],[H]
    •Outline Show [CTRL]+[M],[U]
    •Smart Tag Shortcut [CTRL]+[.]
    Code Snippets
    •Double [TAB] Expansion
    •Insert Snippet

    [CTRL]+[K] and [CTRL]+[X]