Get Started

In this guide we will design the data access layer for at small database that tracks products, customers, addresses and orders. We will show you how to organize your code with the MVC pattern, compose SQL with Razor syntax and bind it all together with well-defined accessor interfaces performing the necessary mapping and exposing your data.

Our examples use the dotnet tools available in the .NET Core SDK, but feel free to go through the steps with Visual Studio, Visual Studio Code or any other editor. For a reference to the code written in this guide and other examples, you can clone our samples repo on GitHub.

Schema

Jerrycurl works in a database-first approach meaning that you as a developer need to design your database schema and then create a model representing that schema. In the following sections we use the schema below.

Installation

First step is to create an empty class library and add the Jerrycurl package.

>
>
>
dotnet new classlib -o Jerrystore
cd Jerrystore
dotnet add package Jerrycurl

This package contains a combination of the compiler and runtime targets required for our project to build and execute our Razor-based .cssql files. It requires only that your project is compatible with the .NET Standard 2.0 or higher.

Next up is creating a simple project structure with a few folder stubs.

>
>
>
>
>
mkdir Accessors
mkdir Commands
mkdir Models
mkdir Queries
mkdir Views

This represents the basic setup of any Jerrycurl project and separates code into the three components of the MVC pipeline in a way very similar to the setup of ASP.NET MVC applications. The main difference is the terminology

The organizational pattern of MVC is highly aligned with ASP.NET MVC This way of organizing your code is identical to that of ASP.NET MVC, but with a slightly different terminology. This means that for our code we have models in the Models and Views folders, accessors (controllers) in the Accessors folder and procedures (views) in Commands and Queries.

Tooling

Let's add our first code file which will be a database model that matches the schema above. We can do that easily with the CLI that is available to install as a .NET Global tool. We recommend that you do a one-time, global install.

>
>
>
dotnet tool install --global dotnet-jerry
You can invoke the tool using the following command: jerry
Tool 'dotnet-jerry' (version '1.0.0') was successfully installed.

Use the help command to get a list of commands and options.

> jerry help
Jerrycurl CLI v1.0.0
Usage: jerry [command] [options]

Commands:
    scaffold                    Generate C# classes from an existing database.
    info                        Show information about a specific vendor.
    help                        Show this information.

Options:
    -v, --vendor <moniker>      Vendor used to connect to database. Moniker can
                                    be 'sqlserver', 'sqlite', 'oracle', 'postgres'
                                    or 'mysql'.
    -c, --connection <cs>       Connection string used to connect to database.
    -ns, --namespace <ns>       Namespace to place scaffolded C# classes in.
    -o, --output <file>         Path to scaffold .cs files into. Writes one
                                    file per class unless specified with .cs
                                    extension. Defaults to Database.cs.

For now the only interesting command is scaffold with a few options available. So let's use that to generate (and later update) our model from a local SQL Server.

If you are using Visual Studio you can install the CLI support package in your project and instead call the Invoke-Jerry cmdlet directly from the Package Manager Console with identical arguments. Use the long-form, double-dash options to get around the default PowerShell argument parser.
>
>
>
>
>
jerry scaffold -v sqlserver -c "SERVER=.;DATABASE=jerrystore;TRUSTED_CONNECTION=true" -ns "Jerrystore.Database"
Fetching Jerrycurl.Tools.Vendors.SqlServer v1.0.0...
Connecting to database 'jerrystore'...
Generating...
Generated 5 tables and 22 columns in Database.cs.

This adds a bunch of classes to Database.cs that make up our model in the one-class-per-table style common to most ORMs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// Database.cs
namespace Jerrystore.Database
{
    [Table("dbo", "Customer")]
    public class Customer	
    {
	    [Id, Key("PK_Customer", 1)]
	    public int Id { get; set; }
	    [Ref("PK_CustomerAddress", 1, "FK_Customer_CustomerAddress")]
	    public int? AddressId { get; set; }
	    public DateTime CreatedDate { get; set; }
	    public string Email { get; set; }
	    public string Password { get; set; }
	    public string Name { get; set; }
    }
	
    // etc
}

The database model provides us with the first object-centric view of our database and in the next section we will expand on this design and create a domain model for our Models and Views folders.