Lesson 2: Importing Data
In this lesson we're going to cover the basics of a very powerful feature of the RestApiGenerator: resolving external identifiers. Inside the database, you want to chose an primary key based on performance or cost, you don't want to chose it based on how readable it is. This is at odds with importing data from files or external systems that likely have no idea what you're using in your database for a primary key. The solution to this problem is external identifiers as unique keys. Each table that loads data from an outside source has a key that is native to that source, or a human-readable key for scripted files. Our generated API will make the translation between the outside key and the internal key for you!
If you haven't already installed the Data Model Generator extension, go to Lesson 1 and follow steps 1 and 2.
Create a new project. File > New > Project
In the New Project form, select Installed > Visual C# > .NET Core. We're going to create a new ASP.NET Core Web Application project called WebApplication2.
Use the API template to create the service. Take all the default settings.
At this point you should have a solution that looks like this:
Select the ValuesController.cs that was added by the template and delete it.
Add the Microsoft.EntityFrameworkCore and GammaFour.Data packages using the Tools > NuGet Package Manager > Package Manager Console.
Right-Click on the WebApplication2 project in the Solution Explorer pane and Add > New Item to the project.
Select ASP.NET Core > Data > XML File and change the name to DataModel.xsd.
Hit the Add button.
Copy and paste this source into the DataModel.xsd file that you just created.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="DataModel" targetNamespace="http://tempuri.org/DataModel.xsd" xmlns="http://tempuri.org/DataModel.xsd" xmlns:mstns="http://tempuri.org/DataModel.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:gfdata="urn:schemas-gamma-four-com:xml-gfdata">
<xs:element name="DataModel">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Customer" gfdata:verbs="Delete,Get,Post,Put">
<xs:complexType>
<xs:sequence>
<xs:element name="CustomerId" gfdata:AutoIncrement="true" type="xs:int" />
<xs:element name="Mnemonic" type="xs:string"/>
<xs:element name="Name" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Order" gfdata:verbs="Delete,Get,Post,Put">
<xs:complexType>
<xs:sequence>
<xs:element name="CustomerId" type="xs:int" />
<xs:element name="OrderId" gfdata:AutoIncrement="true" type="xs:int" />
<xs:element name="ProductId" type="xs:int" />
<xs:element name="Quantity" type="xs:decimal" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Product" gfdata:verbs="Delete,Get,Post,Put">
<xs:complexType>
<xs:sequence>
<xs:element name="Mnemonic" type="xs:string"/>
<xs:element name="Name" type="xs:string"/>
<xs:element name="Price" type="xs:double" />
<xs:element name="ProductId" gfdata:AutoIncrement="true" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="CustomerKey" gfdata:IsPrimaryKey="true">
<xs:selector xpath=".//mstns:Customer" />
<xs:field xpath="mstns:CustomerId" />
</xs:unique>
<xs:unique name="CustomerExternalKey">
<xs:selector xpath=".//mstns:Customer" />
<xs:field xpath="mstns:Mnemonic" />
</xs:unique>
<xs:unique name="OrderKey" gfdata:IsPrimaryKey="true">
<xs:selector xpath=".//mstns:Order" />
<xs:field xpath="mstns:OrderId" />
</xs:unique>
<xs:unique name="ProductKey" gfdata:IsPrimaryKey="true">
<xs:selector xpath=".//mstns:Product" />
<xs:field xpath="mstns:ProductId" />
</xs:unique>
<xs:unique name="ProductExternalKey">
<xs:selector xpath=".//mstns:Product" />
<xs:field xpath="mstns:Mnemonic" />
</xs:unique>
<xs:keyref name="CustomerOrderdKey" refer="CustomerKey">
<xs:selector xpath=".//mstns:Order" />
<xs:field xpath="mstns:CustomerId" />
</xs:keyref>
<xs:keyref name="ProductOrderKey" refer="ProductKey">
<xs:selector xpath=".//mstns:Order" />
<xs:field xpath="mstns:ProductId" />
</xs:keyref>
</xs:element>
</xs:schema>Note that in the Customer and Product tables, we've added a Mnemonic property. Also, we've created unique indices for these properties that will allow us to import data using the mnemonics.
Save the file with Ctrl+S.
Right-Click on the DataModel.xsd in the Solution Explorer and click Copy. Now right-click on the Controllers folder and click Paste.
Give each of the schema files a custom tool to execute. Select the DataModel.xsd in the root directory and enter ServerDataModelGenerator as the Custom Tool:
Select the DataModel.xsd in the Controllers folder and enter RestApiGenerator as the Custom Tool. Your project should now look like this:
At this point we have a complete Web Service with a RESTful API. The next step is to connect it to a database. For this step, you'll need access to a local or remote version of SQL Server.
Cut and paste the following code into Startup.cs:
namespace WebApplication2
{
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<DataModelContext>(
options => options.UseSqlServer(this.Configuration.GetConnectionString("SqlServerConnection")),
ServiceLifetime.Transient,
ServiceLifetime.Transient);
services.AddSingleton<DataModel>();
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
app.UseHsts();
}
app.UseHttpsRedirection();
app.UseMvc();
}
}
}This instructs the Web Service's Dependency Injection container how to build and instantiate our data model and the DbContext we'll use to communicate with the database. We'll also need an SQL connection string. Place the following in your appSettings.json file (this assumes you have a local Db):
{
"ConnectionStrings": {
"SqlServerConnection": "Data Source=.;Initial Catalog='WebApplication2';Integrated Security=True"
},
"Logging": {
"LogLevel": {
"Default": "Warning"
}
},
"AllowedHosts": "*"
}Build the solution using Ctrl+Shift+B.
The generated code contains a complete description of the entities and their relations that Entity Framework can use to build the persistent database. Open up the Package Manager Console with Tools > NuGet Package Manager > Package Manager Console and type:
When that task has completed, update the database with this new migration with:
Now, update your debugger settings to display the Customer records by default when you start debugging. Right click on your WebApplication1 project in the Solution Explorer and click on Properties. Select the Debug tab on the right and enter "api/customers" in the Launch browser setting:
Run the Web Application. Hit hit F5. You should get an empty JSON array in the browser.
Just like in the first lesson, we're going to import some data using our generated RESTful API. Open up Postman and set the HTTP verb to be PUT this time. We're using the REST concept of a RESOURCE to locate our customers and, because we can reference an unambigous customer using our external identifiers, these calls will be idempotent. Copy the URL from the browser that the debugger opened (https://localhost:PORT#/api/customers) and paste it into the Enter request URL box.
Click on the Body tab and select RAW and JSON (application/json) from the drop-down. Now take the following JSON data and paste it into the body of the message:
[
{ "mnemonic": "MS", "name": "Marry Shelly" },
{ "mnemonic": "EH", "name": "Earnest Hemingway" },
{ "mnemonic": "WG", "name": "William Gibson" },
{ "mnemonic": "HM", "name": "Henry Miller" },
{ "mnemonic": "RLC", "name": "R. L. Corey" },
{ "mnemonic": "GRRM", "name": "George R. R. Martin" }
]Your Postman screen should look like this:
Hit the Send button. You'll get back a 200 OK status. Now go to your web browser and hit F5 and you'll see your records stored in the cache and on the database server. Now add our set of products. Select a new tab in PostMan and select POST for the request type using the products URL: https://localhost:PORT#/api/products. Click on the Body tab and select RAW and JSON (application/json). Cut and past this batch of products into the body.
[
{ "mnemonic": "DBRUSH", "name": "Dog Brush" },
{ "mnemonic": "YBUCKET", "name": "Yellow Bucket" },
{ "mnemonic": "GDTAPE", "name": "Gray Duct Tape" },
{ "mnemonic": "PF", "name": "Pitch Fork" },
{ "mnemonic": "LADDER12", "name": "12' Ladder" },
{ "mnemonic": "POST844", "name": "8' x 4 x 4 Post" },
{ "mnemonic": "BPAINT1", "name": "Blue Paint - 1 Gal." },
{ "mnemonic": "LBULB40W", "name": "40 W Light Bulb" }
]Your PostMan form should look like this:
Hit the Send button and you should get back a 200 OK again. Verify this by going to the browser started by the IDE and selecting the products URL from the exercise above. Your browser should look like this: