I'm looking for honest feedback on my content. Please comment or reach out to me on Twitter!
Have you ever seen one of those dropdowns that populates the city based on what ZIP code you put in? This is a small but neat user interface element, and can be handy for making user input forms simpler and faster to use.
What we'll build
We are going to build an application that takes a ZIP code as an input, and returns city information for that ZIP code that looks like this:
It will be a .NET Core Web API application, that you can use for web pages, mobile, whatever. It's kind of a Microservice but I hate to call it that. It's a simple RESTful application.
What You'll Need To Get Started
For this tutorial I'm using .NET Core 2.2. You'll need to download the .NET Core Installer from here. You could easily do this project with a text editor and CLI, but I'm using Visual Studio for this tutorial to show off how easy it is to use.
You should also download DB Browser for importing data into the SQLite database.
Step 1: Create a new project.
Load up Visual Studio and create a new project.
Select "ASP.NET Core Web Application" and click next. Name the project whatever you'd like.
Next you'll be presented with a screen that looks like this. Select "API" and click "Create".
This will scaffold out a new API application. The first thing we'll do is delete the "values" controller.
Then, we'll install some tools so we can use SQLite and Entity Framework Core with this application:
At a command prompt or package manager console, run the following:
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design
This will add SQLite support and the design namespace, which has some design components that will help us out.
The reason we're using SQLite here is that we want something that will be a self-contained database without a server, and run on all the three platforms. SQLite serves this purpose well, at least for small databases like this.
Don't forget to restore the project:
dotnet restore
NOTE: EF Core is not required here at all. It would likely be faster with direct access or using something like Dapper. But for simplicity we're using EF Core.
2. Create Your Model
Next, create a folder called models, and create a new class. Our class will look like this:
public class City
{
[Key]
public string Zip { get; set; }
public float Lat { get; set; }
public float Lng { get; set; }
public string CityName { get; set; }
public string StateId { get; set; }
public string StateName { get; set; }
public string TimeZone { get; set; }
public ICollection<City> Cities { get; set; }
}
This class represents the "City" that will be the data type we're working with. As you can see it has the ZIP code, which we're going to use here for an ID. It contains the Latitude/Longitude of the city, name, state id (like OR), state name and time zone.
We'll add a collection for "cities" in here as well for returning a collection of cities. Save the file.
3. Define The Context
Next, we're going to create a context for the database that these cities can live in. Create a new class in models that looks like this:
public class CityContext : DbContext
{
public CityContext(DbContextOptions<CityContext> options) : base(options){ }
public DbSet<City> Cities { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite(@"Data Source=Database/zipcodes.db"); // windows
}
}
This is the context we'll use for Entity Framework Core so we can easily manipulate the data.
Here we allow the options to be injected in the class, and on configuration we'll override it with an optionsbuilder. This is one place where will direct the context to use a specific file, in this case, zipcodes.db. This will be the hosted database with our city information in it.
Next, open up startup.cs and add the following lines to the top:
using Microsoft.EntityFrameworkCore;
using Zippy.Models;
Make sure the 2nd line is a path to your Models namespace (My project is named Zippy).
Then in the Configureservices method, we will add the reference to our database again, and set EF options to use SQLite.
var connection = @"Data Source=Database\zipcodes.db"; // Windows
services.AddDbContext<CityContext>(options => options.UseSqlite(connection));
Notice where it's commented out and says "Windows". If you want to run this application in Linux or OSX you need to reverse the slash so it can be found on the file system:
var connection = @"Data Source=Database/zipcodes.db"; // Linux
Now let's create that database.
4. Create Our Database
Create a new folder in the project named "Database".
Open up DB Browser and create a new database.
Create a filename for it to be saved as. You don't need to create any tables or do anything else with it at this time.
5. Create a Migration
Our database will only have a single table in it, but we want to create a migration in EF Core for it. Migrations are important for keeping track of historical changes in your database and helping to restore it when needed. Note I'm only talking about schema (layout) information here, not the data stored in it.
dotnet ef migrations add Initial
Now we have an initial schema setup. Let's update Entity Framework Core:
dotnet ef database update
Now our database and table are created.
6. Import the Data
This will be populated with data from a CSV from Simple Maps. If you're planning on using this for a website, make sure and give them credit with a link back to their website.
Now that your table is created we're going to populate it with the data from the CSV file downloaded from Simple Maps. For this project I stripped out some of the columns so it looks like this:
As Derek pointed out, you will need to add a blank field at the end so it matches the columns in your EF generated table.
Now it's ready to be imported.
Go to file -> import -> table from CSV File:
Load up the CSV file you created earlier. It should look like this:
And import the file. You should now have a fully populated database.
Save the database file. Make sure it's set to be copied with the project.
Now let's make our app interact with it.
7. Create a Controller
Now we need to create a cities controller. This is a fairly simple process, but Visual Studio makes it even easier.
Right click on your controllers folder and select add -> controller
We want to select an API Controller with Actions using Entity Framework
Select City for the Model class, and CityContext for the Data Context Class and generate it.
Visual Studio will generate a set of actions automatically to:
- Get a list of cities in the DB
- Get a particular city from the ZIP
- Add a city
- Remove a city
- Update information
While these things are pretty trivial to build, it's nice to have it all scaffolded out. For this to be a useable service you'd want to remove the actions to modify the data, and only keep the GET functionality.
8. Set Default Route
The last step is to set our default route. Open up launchSettings.json and modify the two lines marked "launchUrl":
Change
"launchUrl": "api/values",
to
"launchUrl": "api/cities",
Save the file and build the project. Press F5 to launch it.
The Finished Product
After pressing F5 you'll see the project come up in your web browser.
For this service to work as intended, it must take a zipcode as input, this is easily done by appending the zip code to the end of the URL:
This looks better when formatted in something like POSTMan:
You could use this service in many ways for a user interface in forms. This is an easy call from a JavaScript application or Mobile app.
Conclusion
It's really easy to create simple microservices and applications with .NET Core. They are quick to put together and run very fast and lean. Plus, they can run on anything. If you want to learn more about .NET Core check out some of these great courses or hit up the .NET Core help site from Microsoft.
In a future tutorial, I'll show you how to deploy this application to multiple server and cloud environments.
Note: This application uses data from Simple Maps (free version). If you intend to use this on your site you should give them a link back or consider the professional version as the data will be more accurate and updated.
What's your .NET Core IQ??
My ASP.NET Core Skill IQ is 200. Not bad, can you beat it? Click here to try
Top comments (6)
A few errors here:
In your screenshot, you name the DB "zipcodes.db", then you have us run a migration, which creates a "cities.db", created from the Context.
Startup.cs references "zipcodes.db", instead of the "cities.db" references in CityContext.
If we change everything to "cities.db" (or "zipcodes.db") and then try to import the data from a CSV, you can't override the existing Cities table because DB Browser won't allow you to import the CSV into an existing table (the DB created from the migration) if it doesn't have the same number of matching columns. You need to add the "CityZip" column to the CSV file first.
If we create a new DB from DB Browser and import the CSV into it, EF will connect, but throw an error because the "CityZip" column that EF adds doesn't exist.
You name your table on import "zips", but your app is looking for the "Cities" table.
Make sure to not have your DB open in DB Browser, or EF will report that the DB is Locked.
Once you change all occurrences of "zipcode.db" in your code to "cities.db", add the blank CityZip column to the CSV and import it into the DB created from the migration, all should run nicely ;) You don't need to manually create the DB, the migration will output the file for you.
Thank you for the helpful feedback. I clearly missed a few things while putting it together. I have made corrections to the article and will have the project hosted up on GitHub soon so people can take a deeper look at it.
Thanks again!
No problem! I was looking for some resources to learn .NET Core (I'm a front end dev at a .NET shop) and I'd saved your tutorial a while back to try out. The errors actually forced me to learn the material better, since I had to do some troubleshooting to get everything running :P
It's not really about the service, but learning how to build it that's important. There may also be cases where people want to build their own to match certain requirements and this is a good place to start.
Nice tutorial, what about the data quality of simple map? I know zipcodes for instance in the UK are quite complex, I never found a provider (even paid service) that had good quality data.
I don't know much about it, my initial spot checks of ones in this area were pretty accurate. From what I understand the most accurate and updated version of it is the one you pay for.
I have not researched any UK databases, it sounds like a good business opportunity for someone who gets it right!