In today’s lesson we will continue developing backend Web API and our goal is database access. We will be creating CRUD API entry points handling GET, POST, PUT and DELETE requests.
As you probably remember that last time we simply hardcoded objects (Users) in the code. Today we will solve this issue.

Prerequisites

First of all, as we are about to store information in database, we need to prepare a new database to work with.
If you haven’t installed SQL Server Management Studio (SSMS) yet, it is time to do it. I recommend to download it from official Microsoft website.
Once it is ready – open it and create new database from ‘Object Explorer’ tree. For now, it is enough to provide only new database name: ‘webapi-db’

Now you can open your previously created project in Visual Studio.

Step 1 – Setting up connection string to SQL server

Pretty easy step, open ‘web.config’ (which is in root folder of your project) and add (or update) the following section:

If you use a remote SQL server, instead of localhost you need to specify your remote server (e.g. its IP address).

Step 2 – A little bit of theory

CRUD abbreviation stands for the four base functions that a model should be able to do – Create, Read, Update, and Delete.

Microsoft introduced a bunch of approaches to work with database: LINQ, Entity Framework (EF) Core, EF using String Path, EF with Lambda Expression, ORM …
You are free to use any of the methods, but need to note that methods have slightly different syntax, own strengths and weaknesses.

LINQ (Language Integrated Query) is a model and certain methodology which adds formal query capabilities into .NET languages. It provides wide and convenient ways for data manipulation.

Entity Framework is an Object Relational Mapper (ORM). Allows to work with database records as with ordinary objects.

Lambda Expression is an anonymous function that you can use to create delegates or expression tree types. By using lambda expressions, you can write local functions that can be passed as arguments or returned as the value of function calls. One of usages of Lambda expressions is writing LINQ query expressions.

Step 3 – database table

We are ready to build a table in our database. You can do it either in Microsoft SQL Server Management Studio or in your Visual Studio. Both ways are pretty straight forward. Lets create the table in Visual Studio. On the left, find ‘Server Explorer’ and select ‘Connect to Database’.
Once the database is connected, select ‘Add New Table’ in ‘Tables’. Here we can define fields of our Users table: id, name, email, phone and role.

Step 4 – Model

We already had the basic User model. In order to link the model with db table, I used LINQ Mapping properties (Table, Column ..). Find below the updated version of the Users model. This is basic model to keep users information.

using System;
using System.Data.Linq.Mapping;
namespace webapi.Models
{
    [Table(Name="dbo.WebApiUsers")]
    public class User
    {
        private Guid _id;
        private string _name;
        private string _email;
        private string _phone;
        private int? _role;
        [Column(Storage = "_id", AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
        public Guid id { get => _id; set => _id = value; }
        [Column(Storage = "_name", DbType = "NChar(32)")]
        public string name { get => _name; set => _name = value; }
        [Column(Storage = "_email", DbType = "NChar(32)")]
        public string email { get => _email; set => _email = value; }
        [Column(Storage = "_phone", DbType = "NChar(32)")]
        public string phone { get => _phone; set => _phone = value; }
        [Column(Storage = "_role", DbType = "int(2)")]
        public int? role { get => _role; set => _role = value; }
    }
}

Here you can see exactly same fields we prepared in our db table. Pay attention that the id field is Guid, which is primary key in this table.

Step 5 – Repository

Repository represents a pattern to control access to the data source. The class implementation thisdoes not contain business logic, does not manage business processes, it only contains operations on data. As a rule, the repository implements a CRUD interface – operations to extract, add, edit, and delete data.

In short, the repository serves to retrieve data from the database. At this stage it is pretty important to understand how it works

All API requests sent to IIS go to API Controller, the controller works via repository.

Now we need to declare a set of CRUD methods in interface

public interface IRepo
{
    T Create(T item);
    T GetOne(Guid id);
    T[] GetAll();
    T Update(UserModel model);
    int Delete(Guid id);
}

UserModel, an another model is used in Update method.

public class UserModel
{
    public Guid id { get; set; }
    public string field { get; set; }
    public string value { get; set; }
}

I decided to make a quick update method (which updates/patches only one field) instead of updating all user fields (which could be heavier).

Now we can start building a repo.

Entity Framework Code First data access approach consider a data access context that is inherited from the DbContext class.
In the beginning I already mentioned various approaches of creation the data contexts and repositories. Let’s review implementations of various approaches.

Linq to Entities/Objects

Linq to Entities offers a simple and intuitive approach for retrieving data using expressions that are close to SQL language.

Working with database, we operate with LINQ queries, but the database understands only queries in the SQL language. Therefore, there is a data provider between LINQ to Entities and the database that allows them to interact. This data provider is EntityClient. It creates an interface for interacting with the ADO.NET provider for SQL Server.

We can use LINQ operators and extensions. Refer to the following realization:

public class UsersDataContextLinq
{
    private DataContext dataContext;
    public DbConnection dbConnection;
    public User[] Users { get; set; }
    public Table usersTable { get; set; }
    public UsersDataContextLinq()
    {
        SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["webapiConnectionStrings"].ConnectionString);
        dbConnection.Open();
        var commandText = $@"
            IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.WebApiUsers') and OBJECTPROPERTY(id, N'IsTable') = 1)
            BEGIN
                CREATE TABLE [dbo].WebApiUsers(
                    [id] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
                    [name] [nvarchar](128) NOT NULL,
                    [email] [nvarchar](128) NOT NULL,
                    [phone] [nvarchar](128) NOT NULL,
                    [company] [nvarchar](128) NOT NULL,
                    [role] [int] NULL,
                    [status] [int] NULL
                )
            END
        ";
        using (var command = dbConnection.CreateCommand())
        {
            command.CommandText = commandText;
            command.ExecuteNonQuery();
        }
        dataContext = new DataContext(dbConnection);
        usersTable = dataContext.GetTable();
    }
    public User[] GetAll()
    {
        IQueryable users =
            from user in usersTable
            select user;
        return users.ToArray();
    }
    public User Create(User newEntry)
    {
        usersTable.InsertOnSubmit(newEntry);
        dataContext.SubmitChanges();
        return newEntry;
    }
    public int DeleteEntry(Guid id)
    {
        IQueryable deleteUser =
            from user in usersTable
            where user.id == id
            select user;
        usersTable.DeleteOnSubmit(deleteUser.Single());
        try
        {
            dataContext.SubmitChanges();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            return 0;
        }
        return 1;
    }
    public User UpdateField(Guid entryId, String field, String value)
    {
        User user = usersTable.FirstOrDefault(p => p.id == entryId);
        if (user != null)
        {
            Type userModelType = typeof(User);
            FieldInfo editFieldInfo = userModelType.GetField("_" + field, BindingFlags.NonPublic | BindingFlags.Instance);
            if (editFieldInfo != null)
            {
                Type editFieldType = Nullable.GetUnderlyingType(editFieldInfo.FieldType) ?? editFieldInfo.FieldType;
                object safeValue = (value == null) ? null : Convert.ChangeType(value, editFieldType);
                editFieldInfo.SetValue(user, safeValue);
                dataContext.SubmitChanges();
            }
        }
        return user;
    }
}

Using our webapiConnectionStrings we create a new SqlConnection and open it.
In case if the table was not created earlier (Step 3), it will be added automatically executing commandText in constructor.
Pay attention to SQL like syntax in GetAll and DeleteEntry methods – this is how we can use LINQ operators to work with db table.
System.Reflection is used in ‘PatchField’ method in order to update any of User field (providing just name of the field and new value).
However, using this approach (via System.Reflection) is pretty a big hit to performance. I don’t recommend to use it.

Repository using Core Entity Framework

Quite easy to create the data context

public class UsersDataContext : DbContext
{
    public DbSet Users { get; set; }
    public UsersDataContext() : base(ConfigurationManager.ConnectionStrings["webapiConnectionStrings"].ConnectionString)
    {
    }
}

You only need to pass our connection string into DbContext.
The repository class became more readable and understandable.

public class UsersRepository : IRepo
{
    public UsersDataContext _dataContext { get; set; }
    public DbSet Users { get; set; }
    public UsersRepository(UsersDataContext context) : base()
    {
        _dataContext = context;
        Users = _dataContext.Set();
    }
    public User Create(User newUser)
    {
        newUser.id = Guid.NewGuid();
        Users.Add(newUser);
        _dataContext.SaveChanges();
        return newUser;
    }
    public User[] GetAll()
    {
        return Users.ToArray();
    }
    public User GetOne(Guid id)
    {
        return Users.SingleOrDefault(s => s.id.Equals(id));
    }
    public User Update(UserModel model)
    {
        var user = GetOne(model.id);
        if (model.field.Any())
        {
            switch (model.field)
            {
                case "name":
                    user.name = model.value;
                    break;
                case "email":
                    user.email = model.value;
                    break;
                case "phone":
                    user.phone = model.value;
                    break;
                case "company":
                    user.company = model.value;
                    break;
            }
            _dataContext.SaveChanges();
        }
        return user;
    }
    public int Delete(Guid id)
    {
        Users.Remove(GetOne(id));
        return _dataContext.SaveChanges();
    }
}

We defined DbSet for Users, binded it with the users data context, and now we can operate with it across all CRUD functions.

As you can see, this is applicable for basic single level queries. If you need to include multiple levels, you can use an eager loading – process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the Include method.
There are several methods of achieving it:

Using Lambda Expression

var extraData = Users /* or _dataContext.Users depending on your context */
    .Include(i => i.ExtraData.Select(it => it.Items))
    .ToList();

The Include method allows the required depth of eager loading to be specified by providing Select expressions to the appropriate depth.

Using String Path

var extraData = Users
    .Include("ExtraData.Items")
    .ToList();

Entity Framework Core

var extraData = Users
    .Include(i => i.ExtraData)
        .ThenInclude(it => it.Items))
    .ToList();

Entity Framework ThenInclude method allows include multiple levels of related data.

Step 6 – UsersController

Finally we need to update our controller to finalize the CRUD:

public class UsersController : ApiController
{
    private readonly UsersRepository _usersRepo;
    public UsersController() : base()
    {
        _usersRepo = new UsersRepository(new UsersDataContext());
    }
    [Route("api/Users/Add"), HttpPost]
    public IHttpActionResult AddUser(User newUser)
    {
        User newUserReturn = _usersRepo.Create(newUser);
        return Json(newUserReturn);
    }
    [Route("api/Users/GetAll"), HttpGet]
    public IHttpActionResult GetObjects()
    {
        var allUsers = _usersRepo.GetAll();
        return Json(allUsers);
    }
    [Route("api/Users/Get/{id}"), HttpGet]
    public IHttpActionResult Get(Guid id)
    {
        var users = _usersRepo.GetOne(id);
        if (users == null)
        {
            return NotFound();
        }
        return Json(users);
    }
    [Route("api/Users/Update"), HttpPut]
    public IHttpActionResult UpdateUser(UserModel updatedUser)
    {
        User user = _usersRepo.Update(updatedUser);
        return Json(user);
    }
    [HttpDelete, Route("api/Users/Delete/{id}")]
    public IHttpActionResult DeleteUser(Guid id)
    {
        int res = _usersRepo.Delete(id);
        return Json("OK");
    }
}

All the methods linked with appropriated repository methods. By this we prepared the following API entry points:

• api/Users/Add
• api/Users/GetAll
• api/Users/Get/{id}
• api/Users/Update
• api/Users/Delete/{id}

After you build and run the server, you can test all the created methods (e.g. you can use jQuery):

$.ajax({
    type: 'POST', url: '/api/Users/Add',
    data: {name: 'username', email: 'email', phone: 'phone', company: 'company', role: 1, status: 1},
    success: function(data, success) {
        console.log(data);
    },
    xhrFields: {
        withCredentials: true
    }
});
$.ajax({
    type: 'GET', url: '/api/Users/GetAll',
    success: function(data, success) {
        console.log(data);
    },
    xhrFields: {
        withCredentials: true
    }
});
$.ajax({
    type: 'GET', url: '/api/Users/Get/26f72318-63f4-4a22-a9fb-c04ddea6a6f4',
    success: function(data, success) {
        console.log(data);
    },
    xhrFields: {
        withCredentials: true
    }
});
$.ajax({
    type: 'PUT', url: '/api/Users/Update',
    data: {id: '26f72318-63f4-4a22-a9fb-c04ddea6a6f4', field: 'name', value: 'new name'},
    success: function(data, success) {
        console.log(data);
    },
    xhrFields: {
        withCredentials: true
    }
});
$.ajax({
    type: 'DELETE', url: '/api/Users/Delete/26f72318-63f4-4a22-a9fb-c04ddea6a6f4',
    success: function(data, success) {
        console.log(data);
    },
    xhrFields: {
        withCredentials: true
    }
});

Some hints and conclusions

Speaking about context and performance, fetching objects to the context. What if there are no further actions to change and save them.
If we are not going to modify these objects, AsNoTracking() should be called for this collection

Example:
Users[] ReadonlyUsers = context.Users.AsNoTracking().ToList();

It also can happen that you need to modify your existing db table.
If you create new fields, modify existing, or remove fields, you will need to update your database in Visual Studio.
Next three screenshotes explain how you can do it:

Happy coding!