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.

Consume Web API in a .NET C# client

Recently I have written a console client in C# to consume those web services developed in my previous post by using APS.NET Web API 2 and Microsoft Entity Framework 6.

I started with a new Console Application (called WebApiClient) in C# in Visual Studio 2013, and then installed the Microsoft ASP.NET Web API Class Libraries using NuGet. Lastly, I added Employee.cs (model class) which was used by web services as my data structure.

The following is a web client program in C# (Program.cs):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Net.Http;
using System.Net.Http.Headers;

namespace WebApiClient
{
    class Program
    {
        static void Main(string[] args)
        {
            HttpClient client = new HttpClient();
            string baseUrl = "http://localhost:55142";
            client.BaseAddress = new Uri(baseUrl);
            
            // Add an Accept header for JSON format.
            client.DefaultRequestHeaders.Accept.Add(
            new MediaTypeWithQualityHeaderValue("application/json"));

            //New Employee Record URL
            int anEmpId = -1;
            string serviceUrl;


            // 1. Post a new employee
            serviceUrl="api/employee";
            Console.WriteLine("1. Add an employee by sending POST {0}/{1}  \r\n", baseUrl, serviceUrl);
            Console.WriteLine("The following new employee is added  \r\n");
            var anEmployee = new Employee() { 
                lastname = "Smith",
                firstname = "Adam",
                title = "Economist",
                titleofcourtesy = "Dr.",
                birthdate = Convert.ToDateTime("1958-12-05 "),
                hiredate = Convert.ToDateTime("1990-01-01"),
                address = "188 Bay Street",
                city = "London",
                region = "",
                postalcode = "A1B 2Z3",
                country = "UK",
                phone = "(71) 234-8228",
                mgrid = 1};
            

            HttpResponseMessage response = client.PostAsJsonAsync(serviceUrl, anEmployee).Result;
            if  (response.IsSuccessStatusCode)
            {

                // display the new employee
                Console.WriteLine("firstname={0}", anEmployee.firstname);
                Console.WriteLine("lastname={0}", anEmployee.lastname);
            }
            else
            {
                Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
            }

            Console.WriteLine("\r\nCheck your database table. Press any key to continue...\r\n");
            Console.ReadKey();


            // 2. Get all employees.
            serviceUrl="api/employee";
            Console.WriteLine("2. Get All Employee by sending GET {0}/{1} \r\n", baseUrl, serviceUrl);

            response = client.GetAsync(serviceUrl).Result;        // Blocking call!
            if  (response.IsSuccessStatusCode)
            {
                // Parse the response body. Blocking!
                var employees = response.Content.ReadAsAsync<IEnumerable<Employee>>().Result;
                foreach (var e in employees)
                    {
                         
                        if (e.firstname.Equals("Adam") & e.lastname.Equals("Smith"))
                        { 
                            anEmpId = e.empid;
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine("{0};\t{1};\t{2};\t{3}", e.empid, e.firstname, e.lastname, e.title);
                            Console.ForegroundColor = ConsoleColor.Yellow;
                            Console.WriteLine("\r\nThe new employee is with empid={0}\r\n", anEmpId);
                            Console.ForegroundColor = ConsoleColor.Gray;
                        }
                        else
                        {
                            Console.WriteLine("{0};\t{1};\t{2};\t{3}", e.empid, e.firstname, e.lastname, e.title);
                        }
                    }
            }
            else
            {
                    Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
            }
            Console.WriteLine("\r\nPress any key to continue...\r\n");
            Console.ReadKey();

            // 3. Get an employee by empid
            if (anEmpId>0)
            {
                serviceUrl = "api/employee/" + anEmpId.ToString();

            }
            else
            {
                serviceUrl = "api/employee/1";
                anEmpId = 1;
            }

            Console.WriteLine("3. Get the employee with empid={0} by sending GET {1}/{2} \r\n", anEmpId, baseUrl, serviceUrl);

            response = client.GetAsync(serviceUrl).Result;        // Blocking call!
            if (response.IsSuccessStatusCode)
            {
                // Parse the response body. Blocking!
                var a_employee = response.Content.ReadAsAsync<Employee>().Result;

                Console.WriteLine("emid={0}", a_employee.empid);
                Console.WriteLine("FirstName={0}", a_employee.firstname);
                Console.WriteLine("LastName={0}", a_employee.lastname);
                Console.WriteLine("Title={0}", a_employee.title);
            }
            else
            {
                Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
            }
            Console.WriteLine("\r\nPress any key to continue...\r\n");
            Console.ReadKey();

            // 4. Update an employee by empid
            Console.WriteLine("4. Update an employee with empid = {0} by sending PUT {1}/{2} \r\n", anEmpId, baseUrl, serviceUrl);

            response = client.GetAsync(serviceUrl).Result;        // Blocking call!
            if (response.IsSuccessStatusCode)
            {
                // Retrieve the record first
                var a_employee = response.Content.ReadAsAsync<Employee>().Result;
                Console.WriteLine("The record is retrieved before update: \r\n");
                Console.WriteLine("emid={0}", a_employee.empid);
                Console.WriteLine("FirstName={0}", a_employee.firstname);
                Console.WriteLine("LastName={0}", a_employee.lastname);
                Console.WriteLine("Title={0}", a_employee.title);

                // Update the tile field of the record
                a_employee.title = "Senior Economist";

                response = client.PutAsJsonAsync(serviceUrl, a_employee).Result;

                if (response.IsSuccessStatusCode)
                {
                    Console.WriteLine("\r\nThe record with empid={0} was updated with the following: \r\n", anEmpId);
                    Console.ForegroundColor = ConsoleColor.Yellow; 
                    Console.WriteLine("Title={0}", a_employee.title);
                    Console.ForegroundColor = ConsoleColor.Gray;
                }
                else
                {
                    Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
                }
            }
            else
            {
                Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
            }
            Console.WriteLine("\r\nCheck your database table. Press any key to continue...\r\n");
            Console.ReadKey();

            // 5. delete an employee added in step 1
            Console.WriteLine("4. Delete an employee with empid = {0} by sending DELETE {1}/{2} \r\n", anEmpId, baseUrl, serviceUrl);

            response = client.DeleteAsync(serviceUrl).Result;
             if (response.IsSuccessStatusCode)
             {
                 Console.ForegroundColor = ConsoleColor.Yellow;
                 Console.WriteLine("The record with empid={0} was deleted.", anEmpId);
                 Console.ForegroundColor = ConsoleColor.Gray;
             }
             else
             {
                 Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
             }
             Console.WriteLine("\r\nCheck your database table.\r\n");
        }
    }
}

Using Entity Framework with ASP.NET Web API 2 to build RESTful applications

ASP.NET Web API 2 is a framework for building RESTful applications on the .NET Framework. A broad range of clients, including browsers and mobile devices, can be built to process and present the data through HTTP web services.

In this post, I will build a basic web service by using ASP.NET Web API 2 template, reversed engineer the data model from existing database table by using Entity Framework 6, and finally scaffold and code the web service controller.

ASP.NET Web API template

Create a new project EmployeeWebAPI2 by using C# ASP.NET Application and Web API template in Microsoft Visual Studio 2013.

Reversed Engineering Model From Database

Here is the database schema in Microsoft SQL Server T-SQL.

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’. For more detail, please refer to my another post.

It will create testContext.cs, Employee.cs and EmployeeMap.cs. I commented out the key and relationship self reference to simply the object class.

Build Web Service Controller

Right Click on Controllers folder, and choose ‘Add->New Scaffold Item”. Choose ‘Web API 2 Controller with read/write Access’. Type ‘EmployeeController’ as controller name.

It will generate a EmployeeController.cs with a list of GET, POST, PUT and DELETE methods. The following steps show how to complete the various web URI methods using EF 6.

1. use the model generated from EF 6.

using EmployeeWebAPI2.Models;

2. GET api/employee

        // GET api/employee
        public IEnumerable<Employee> Get()
        {
            using (testContext entities = new testContext())
            {
                return entities.Employees.ToList<Employee>();
            }
        }

3. GET api/employee/5

        // GET api/employee/5
        public Employee Get(int id)
        {
            using (testContext entities = new testContext())
            {
                return entities.Employees.SingleOrDefault<Employee>(b => b.empid == id);
            }
        }

4. POST api/employee

        // POST api/employee
        public HttpResponseMessage Post(Employee value)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    using (testContext entities = new testContext())
                    {
                        entities.Employees.Add(value);
                        entities.SaveChanges();
                        return Request.CreateResponse(HttpStatusCode.OK);
                    }
                }
                else
                {
                    return Request.CreateResponse(HttpStatusCode.InternalServerError, "invalid New Value");
                }
            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message);
            }
        }

5. PUT api/employee/5

        // PUT api/employee/5
        public HttpResponseMessage Put(int id, Employee value)
        {
            try
            {
                using (testContext entities = new testContext())
                {
                    Employee myEmp = entities.Employees.SingleOrDefault<Employee>(b => b.empid == id);
                    // update all the fields
                    myEmp.empid = value.empid;
                    myEmp.lastname = value.lastname;
                    myEmp.firstname = value.firstname;
                    myEmp.title = value.title;
                    myEmp.titleofcourtesy = value.titleofcourtesy;
                    myEmp.birthdate = value.birthdate;
                    myEmp.hiredate = value.hiredate;
                    myEmp.address = value.address;
                    myEmp.city = value.city;
                    myEmp.region = value.region;
                    myEmp.postalcode = value.postalcode;
                    myEmp.country = value.country;
                    myEmp.phone = value.phone;
                    myEmp.mgrid = value.mgrid;
                    // flush to disk
                    entities.SaveChanges();
                    return Request.CreateResponse(HttpStatusCode.OK);
                }
            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message);
            }
        }

6. DELETE api/employee/5

        // DELETE api/employee/5
        public HttpResponseMessage Delete(int id)
        {
            try
            {
                using (testContext entities = new testContext())
                {
                    Employee myEmp = entities.Employees.SingleOrDefault<Employee>(b => b.empid == id);
                    entities.Employees.Remove(myEmp);
                    entities.SaveChanges();
                    return Request.CreateResponse(HttpStatusCode.OK);
                }
            }
            catch (Exception ex)
            {
                return Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message);
            }
        }

Here is the complete code for EmployeeController.cs.

Test the web service

Build the solution and run the web service. Point your localhost url to:
http://localhost:55142/api/employee/2

It will get the following JSON object from url for (empid=2):


{"empid":2,"lastname":"Funk","firstname":"Don","title":"Vice President, Sales","titleofcourtesy":"Dr.","birthdate":"1962-02-19T00:00:00","hiredate":"2002-08-14T00:00:00","address":"9012 W. Capital Way","city":"Tacoma","region":"WA","postalcode":"10001","country":"USA","phone":"(206) 555-0100","mgrid":1}

I also use a tool called ‘Fiddler Web Debugger’ to examine the JSON / XML response return from the web service.

In the next post, I will write a client application to consume all the URIs in the above web api services.

Using Entity Framework in an ASP.NET MVC5 Application

In my previous two posts, I built a C# console application using Entity Framework (EF) in Microsoft Visual Studio 2013, and investigated how to perform CRUD operations on EF.

Building an ASP.NET MVC5 application using EF can become a straightforward job in Visual Studio 2013. It is very similar to building a J2EE MVC application by using JPA in Netbeans 7. The steps can be summarized as three steps: 1) create a ASP.NET MVC project; 2) add M: reversed engineer an EF model(M) from database; 3) Add C with V: scaffolding MVC 5 Controller (C) with views (V) using EF.

1. Create a ASP.NET MVC Project

In Microsoft Visual Studio 2013, create a new project (Ctrl-Shift-N) called EmployeeMVC using template (Visual C#->ASP.NET Web Application) and also select a template ‘MVC‘ and add folders and core references for ‘MVC’.

What will happene?
Visual Studio will create a few folders (App_Data, App_Start, Content, Controllers, fonts, Models, Scripts, Views) and files (Global.asax, packages.config, Web.config, Project_Readme.html, Startup.cs), also download a list of js scripts in Scripts folder, create AccountViewModels.cs and IdentityModels.cs in Models folder, AccountController.cs and HomeController.cs in Controllers folder, a list of *.shtml in various view folders, site style sheets in Contents folder and a few config.cs in App_Start.

Press F5 to build and run the empty application now.

2. Add ADO.NET Entity Data Model

Following the steps to reversed engineer EF 5 model from a table in the database into ASP.NET MVC (for reversed engineer an EF 6 model class, please use Entity Framework Power Tools):

  • Right click on Models folder, and choose New Item-Add->Data->ADO.NET Entity Data Model (C#);
  • Provide a Name: EmployeeModel.edmx and Click ‘Add’;
  • Choose ‘generate from database’ and Click ‘Next’
  • Connect to a SQL instance (local), choose your database (test) and table (HR.Employee)
  • If you receive a security warning, select OK to continue running the template.
  • This will add EmployeeModel.edmx (EF 5) as a folder under Models, and also add relevant references.

    Build the solution.

    3. Generate Controller and Views

    To generate controller and views:

  • Add the new controller to the existing Controllers folder. Right-click the Controllers folder, and select Add – New Scaffolded Item; Select the MVC 5 Controller with views, using Entity Framework option. This option will generate the controller and views for updating, deleting, creating and displaying the data in your model.
  • Set the controller name to EmployeeController, select Employee (EmployeeMVC.Models) for the model class, and select the testEntities (EmployeeMVC.Models) for the context class, Click ‘Add’
  • This will create EmployeeController.cs in Controllers:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Entity;
    using System.Linq;
    using System.Net;
    using System.Web;
    using System.Web.Mvc;
    using EmployeeMVC.Models;
    
    namespace EmployeeMVC.Controllers
    {
        public class EmployeeController : Controller
        {
            private testEntities db = new testEntities();
    
            // GET: /Employee/
            public ActionResult Index()
            {
                var employees = db.Employees.Include(e => e.Employee1);
                return View(employees.ToList());
            }
    
            // GET: /Employee/Details/5
            public ActionResult Details(int? id)
            {
                if (id == null)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
                Employee employee = db.Employees.Find(id);
                if (employee == null)
                {
                    return HttpNotFound();
                }
                return View(employee);
            }
    
            // GET: /Employee/Create
            public ActionResult Create()
            {
                ViewBag.mgrid = new SelectList(db.Employees, "empid", "lastname");
                return View();
            }
    
            // POST: /Employee/Create
            // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
            // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
            [HttpPost]
            [ValidateAntiForgeryToken]
            public ActionResult Create([Bind(Include="empid,lastname,firstname,title,titleofcourtesy,birthdate,hiredate,address,city,region,postalcode,country,phone,mgrid")] Employee employee)
            {
                if (ModelState.IsValid)
                {
                    db.Employees.Add(employee);
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }
    
                ViewBag.mgrid = new SelectList(db.Employees, "empid", "lastname", employee.mgrid);
                return View(employee);
            }
    
            // GET: /Employee/Edit/5
            public ActionResult Edit(int? id)
            {
                if (id == null)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
                Employee employee = db.Employees.Find(id);
                if (employee == null)
                {
                    return HttpNotFound();
                }
                ViewBag.mgrid = new SelectList(db.Employees, "empid", "lastname", employee.mgrid);
                return View(employee);
            }
    
            // POST: /Employee/Edit/5
            // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
            // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
            [HttpPost]
            [ValidateAntiForgeryToken]
            public ActionResult Edit([Bind(Include="empid,lastname,firstname,title,titleofcourtesy,birthdate,hiredate,address,city,region,postalcode,country,phone,mgrid")] Employee employee)
            {
                if (ModelState.IsValid)
                {
                    db.Entry(employee).State = EntityState.Modified;
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }
                ViewBag.mgrid = new SelectList(db.Employees, "empid", "lastname", employee.mgrid);
                return View(employee);
            }
    
            // GET: /Employee/Delete/5
            public ActionResult Delete(int? id)
            {
                if (id == null)
                {
                    return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
                }
                Employee employee = db.Employees.Find(id);
                if (employee == null)
                {
                    return HttpNotFound();
                }
                return View(employee);
            }
    
            // POST: /Employee/Delete/5
            [HttpPost, ActionName("Delete")]
            [ValidateAntiForgeryToken]
            public ActionResult DeleteConfirmed(int id)
            {
                Employee employee = db.Employees.Find(id);
                db.Employees.Remove(employee);
                db.SaveChanges();
                return RedirectToAction("Index");
            }
    
            protected override void Dispose(bool disposing)
            {
                if (disposing)
                {
                    db.Dispose();
                }
                base.Dispose(disposing);
            }
        }
    }
    
    

    It will also create a folder ‘Employee‘ in Views with a list of cshtml (Create, Delete, Details, Edit, Index)

    Build and run by pressing F5, and go to the similar link in the browser like:
    http://localhost:49481/employee

    I removed some of unwanted items in index.cshtml. My index page looked like:

    ASP.NET MVC 5 using EF

    ASP.NET MVC 5 using EF

    Summary

    Using Entity Framework, Visual Studio 2013 can easily generate a model from database, and scaffold a controller and generate all the views based on default templates and standard CRUD operations on EF.

    The advantage of this process is that the model, controller and view can be easily re-generated by repeating the above step 2 and 3 if the table structure in the underlying physical table is changed.

    The disadvantage is also obvious. Whatever customization made after the generation on (M. V. C) will have to be repeated after re-generation.

    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)
  • Design a site like this with WordPress.com
    Get started