Posts Tagged

api

How to setup SQL server with Entity Framework using .NET CORE

This article is the first of a series that is aimed to explain how to use familiar technologies with .NET CORE.

The aim of this post is to create a Database from scratch using Entity Framework. We will configure SqlServer to run with our core app and finally touch upon table relationship in Entity Framework.

Disclaimer: This is the first time I use entity Framework and .NET Core, so I just trying to share my “findings” and it is by no mean the “best” solution available. I am more than open to suggestion if I can make the code below faster and cleaner.

Getting Started

To be able to follow along with this article, you will need to have a project set up and ready. If you do not have one, I would suggest you to create a new project (Web Api preferred) by using the newly released Templates available cross platform. To enable this you will need to install the newest core package for visual studio.

In our case we are going to use a newly created Web API build on Core 2.0.

Tables

One of the hardest task to complete, before you can create a full set of tables in a relationship database ( like Sql and MySql) it is to create a full map of the tables. It may seem clean in your head what your app needs, but I greatly suggest you to create a complete map of all the tables, columns and its relationship on paper to make sure that you have thought at everything.

You will be able to change things later on, but database architecture it is very important for the performance of your application.

In our case we are going to create a player table and an inventory table.

The tables will have the following columns

Player

  • (int) PlayerId
  • (string) Name
  • (decimal) health

Inventory

  • (int) InventoryId
  • (int) PlayerId
  • (string) Name

The first step is to create a Model class for each of the above table. It is good practice to have all models together within a folder placed in the Root of our application called Models.

Now that the folder is in place, we can two files, and call them Player.cs and Inventory.cs.

Player.cs
 
namespace My_game
{
    public class Player
    {
        public long PlayerId { get; set; }
        public string Name { get; set; }
        public decimal health { get; set; }
    }
}

The above model is quite simple. If you are familiar with c# at all, you have surely created a file that looked like this in the past. The power Entity Framework will use the above class to create the table and support us in mapping our future database queries.

Now we need to create another file that will include the Inventory class. This is going to be slightly different than the one above, as we this class is expecting to have a “relationship” with the player class, because each player will be able to have many pieces of inventory.

Inventory.cs
 
namespace My_game
{
    public class Inventory
    {
        public long InventoryId { get; set; }
        public decimal Name { get; set; }
        virtual Player Player { get; set; }
    }
}

As shown by the above code, to add a relationship you just need to use a “virtual” entry with the use of the recently created “player” model.

As you may have noticed, we have not specified any unique identified when creating the models, and not any specification on where the relationship of the tables lies.

The magic is in the names. Entity Framework, if not specified, will expect the Unique Identifier to be either called Id, or the class name appended by Id (eg. InventoryId, PlayerId).

Relationship are handled a very similar way, it assume the tables are connected by using its unique identifier, and as with the above case, we are free to change the default ( this will not be covered in this article).

Context

Now that we have a couple of tables in action, it is time to fit them together we need to use System.Data.Entity.DbContext class (often refereed as context). This class is responsible of creating a complete picture of the database.

For this example we are going to create a context file called dbContext.cs. This file will include the models created above and will look like the following snippets:

dbContext.cs
 
namespace My_game.Models
{
    public class dbContext : DbContext
    {
        public dbContext(DbContextOptions<dbContext> options)
            : base(options)
        {
        }
        public DbSet<Player> Player { get; set; }
        public DbSet<Inventory> Inventory { get; set; }
    }
}

The above class is going to be used in the next few chapter with the use of Entity Framework tools to create a migrations script that will eventually create our database tables.

Configure SQL server

Now that all our models and the context have been fully developed, we are ready to configure SQL server. This article is not going to explain how to set up an SQL server and create a Database, and it is out of scope, but plenty or resources can easily be found on this topic.

Assuming that you have a server and a database setup, we will need to add a connection string in our appsettings.json file. This connection string will provide our application with the correct credential to connect to our database.

To add a local server called mssqllocaldb and a database called my_app_db,  a connection string would look something like this:

appsettings.json
 
"ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=my_app_db;Trusted_Connection=True;MultipleActiveResultSets=true"
  }

The connection string vary depending from the setting, authentication and location of the server, so the one above has just been shared to give you an idea. You can have multiple connection strings ( for development, and live environment). In our case the connection string is going to be called DefaultConnection.

Now that we have added the above entry in our appsettings.json file, we are ready to link the database to our app.

This time we are going to insert some code in the Startup.cs file. This file includes all the services and configuration that are going to be made available within the app.

Our database connection is going to be a service, and as such, our code is going to be inserted within the ConfigureServices method.

SQL server service configuration
 
            services.AddDbContext<dbContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Adding the service is quite straight forward, you just need to add the context name that we have previously defined dbContext, and specify the connection string that we want to use to connect to our DB DefaultConnection. This is all we need to be able to then access the DB within our application.

The example above will connect to SQL server, but there are already options within the .net framework to connect to the most used database servers.

Query the database

Now that everything is linked, query the database is going to be very simple. For example the file below shows the code required to do a select statement (get) and an insert statement (add).

Basic Database operations
 
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Threading.Tasks;
  5. using Microsoft.AspNetCore.Mvc;
  6. using My_app.Models;
  7. namespace My_app.Controllers
  8. {
  9. [Route("api/[controller]")]
  10. public class dbController : Controller
  11. {
  12. private readonly dbContext _context;
  13. public dbController(dbContext context)
  14. {
  15. _context = context;
  16. }
  17. [HttpGet]
  18. public IEnumerable<Resource> GetPlayer()
  19. {
  20. return _context.Player.ToList();
  21. }
  22. [HttpGet]
  23. public IEnumerable<Resource> AddPlayer(string name)
  24. {
  25. var player = new Player {
  26. Name = name,
  27. health = 100
  28. };
  29. return _context.Player.Add(player);
  30. }
  31. }
  32. }

Conclusion

In the next few articles we are going to explain how to create a migration script and how to use them to create your tables, we will also explain how to set you database to seed our tables with static data on start up.

This article has covered the basic set up required to get you up an running with SQL server on a .NET Core appto enable you to get started. I really hope you find any of the information shared above useful and I am happy to get any comments to support me in tailor the above information for future readers.

 

Transactional email made easy – Postmark

Every developer in their career has been asked to build a transactional email at least once. It usually starts with a simple request like:

Please could you send a simple email when a contact form is submitted, something very simple, nothing special.

A few minutes search on Google or Stackoverflow and “snap”, we find perfectly what we need, the php mail function just does what we need right? The description says “Sends an email” what can go wrong?! Well LOTS.

So we have then starter to create our own implementation of a safe mail sender, we start to create templates, we improve our knowledge of crazy workaround needed for HTML formatting in emails and of course they end in the spam, no matter how clear the content is and our previous excitement just turned into our biggest nightmare.

Don’t reinvent the wheel

Unfortunately the reason why I am writing this post, is because I have been there. I have gone through all the nightmares explained above, and trust my words when I say the word nightmares.

There are company that with great product and the right marketing campaigns, have been able to became the leading providers in the industry (eg. WordPress, Mailchimp, etc..).

Today I am going to share with you what I found to be one of the best provider in transactional email that maybe and hopefully be part of that “right marketing campaigns” that can helps them.

Mail sent and delivered.. hassle free!

Over a year ago, I was ask to implement an easy to use way to create Email templates, that would be reliable, easy to change (code free) and with added analytics. (the conversation started with the sentence shared above.. it was supposed to be an easy job they said 🙂 ).

Postmark was my final choice and I have no regret as it has not only delivered all it was required for me, but it also provides fantastic customer service ( fast and reliable) with affordable plans (They provide initial 25000 email free, plus extra free token that are provided as the service get used more, that is more than enough for a small/medium company).

See to believe

I am going to post below a small example taken directly form my project by creating a very simple email template and show the code required to deliver it. There will be no explanation on how to create an account, and how to fetch the unique key, as the documentation of the website is just great on that.

Step 1 – Create the template

Login into your account and then click on the “Add a new template” button situated in the template section.

postmark crreate templace
Postmark create template screenshots

 

Postmark provides you with a list of templates. Each of the templates provides a very details post with all the information required to create your own version of the template with tons of tips around email requirements, but it also give you the ability to use the open source templates provided by them, or of course start from a blank canvas.

For the purpose of this example we are going to use the existing Welcome template.

Step 2 – Modify the template

Postmark template example
Postmark template example

The Web App provides a very easy to use IDE, everything is within reach, and the template provides you with all the basic structure and example needed.

This editor provides you with a Template name, Subject line, HTML editor, live preview, a variables object, ability to fill the email with test data to see how the email layout will react, a “Send Test” button to see what an email really look like and to finish off, an “API snippets” .

 

postmakr api snippets
postmark api snippets

The snippets provide you with everything you need, all the code has been dynamically filled with your account information, the template id and also all the variables.

The code is provided in 5 different languages curl, Ruby, C#, PHP and Node.js.

If this snippets is not enough, you can be assured to be supported by a great customer service ( I had a lovely personal experience and I deals with 3 different people).

 

 

Step 3 – The code

After creating your HTML template and setting the right variables and small details, there is nothing else left than copy this snippets into the portion of your code than handles the email and replace the variable with actual values.

PHP snippets
 
// Create Client
$client = new PostmarkClient("000000000000-0000-00000000000");
// Make a request
$sendResult = $client->sendEmailWithTemplate(
  "sender@example.com",
  "recipient@example.com", 
  1526526,
  [
  "name" => $_POST["name"],
"username" => $_POST["username"]
]);
?>

That is it, no more code required.

Conclusion

I am usually reluctant to use external tools or API as it makes your product fragile, but there are some cases where the risk is worth while, and this is one of them.

The tons of hours I saved after introducing Postmark and clear way to use it has made this a must have for every of my project small or big.

In this post I have really just touched the surface, as the Web App offers many more features and benefits ( for example low possibility of spam because they make sure no one uses the APP for spams emails and they do any effort to keep the servers clears), and hope after trying it out, you will share my same view.

Reference:

Postmark – https://postmarkapp.com/

image from postmarkapp website

"