Lesson 2: Importing Data
In this lesson we're going to cover the basics of a very powerful feature of the RestApiGenerator: resolving identifiers when importing data. Inside a database you want to choose an primary key based on performance or cost, you don't want to chose it based on how readable it is or how it was used in a legacy database. A key chosen for performance -- particularly auto-generated keys -- aren't compatible with a key used to importing data from files or external systems that have no knowledge of your internal primary keys. 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 REST 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 Shelley" },
{ "mnemonic": "EH", "name": "Ernest 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", "price": 15.99 },
{ "mnemonic": "YBUCKET", "name": "Yellow Bucket", "price": 8.49 },
{ "mnemonic": "GDTAPE", "name": "Gray Duct Tape", "price": 3.99 },
{ "mnemonic": "PF", "name": "Pitch Fork", "price": 28.59 },
{ "mnemonic": "LADDER12", "name": "12' Ladder", "price": 67.99 },
{ "mnemonic": "POST844", "name": "8' x 4 x 4 Post", "price": 8.99 },
{ "mnemonic": "BPAINT1", "name": "Blue Paint - 1 Gal.", "price": 23.49 },
{ "mnemonic": "LBULB40W", "name": "40 W Light Bulb", "price": 2.99 }
]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 that was started by your IDE and navigating to the products URL from the exercise above. Your browser should look like this:
Now that we have unique external keys on our customers and products, we can import orders from the outside world. For this step we're going to import from scripted JSON records via Postman. Open up a new tab in Postman, set the request type to POST, copy the URL for the orders into the form: https://localhost:PORT#/api/orders. Click on the Body tab and select RAW and JSON (application/json). Then select the text below and past it into the body of the message:
[
{
"customerId": { "customerExternalKey": { "mnemonic": "MS" } },
"productId": { "productExternalKey": { "mnemonic": "DBRUSH" } },
"quantity": 1
},
{
"customerId": { "customerExternalKey": { "mnemonic": "MS" } },
"productId": { "productExternalKey": { "mnemonic": "YBUCKET" } },
"quantity": 3
},
{
"customerId": { "customerExternalKey": { "mnemonic": "MS" } },
"productId": { "productExternalKey": { "mnemonic": "LBULB40W" } },
"quantity": 5
}
]Your Postman form should look like this:
Hit the Send button. We've now added three items to the order for Mary Shelley: one dog brush, three yellow buckets and five light bulbs.
Let's take a look at the JSON we used to import these records. The internal key is an auto-generated integer, chosen for speed and because it increases monotonically. Because it's auto-generated, we have no idea what the key for any give record will be ahead of time. We won't know until we actually create the record and that doesn't help an external system or a script.
{
"customerId": { "customerExternalKey": { "mnemonic": "MS" } },
"productId": { "productExternalKey": { "mnemonic": "DBRUSH" } },
"quantity": 1
},The API will examine the incoming records. If the value for the "customerId" is the native key, then nothing extra is done; the API will attempt to use the raw value when looking up parent records. However, if you provide the name of a unique key on the parent table as the value, then the lookup is performed on that table and the internal key replaces incoming parameter for the, say, customer identifier. So even though we didn't know the internal identifier of the customer or product, we can reference them symbolically in a script.
This is a powerful, extensive mechanism that can be used to import and script data. Best of all, the translation is generated for you. All that you need to focus on is providing unique keys on your table that translate the outside world for you.
In our next lesson we'll put all this together and query the in-memory data model for a complete order.