Creating an application programming interface using .net Minimal APIs returning data from a stored procedure

Minimal APIs are a relatively new feature introduced in .net 6 architected to create APIs with minimal dependencies. They have a minimal set of features and dependencies and are much faster than traditional methods.

I resurrect an old project to implement a simple affiliate shopping site using minimal API's reading data from a SQL Server stored procedure.

I have been playing with Minimal APIs for a little while now, and I wanted to create something which utilised them. Actually building something that works is a much better way of working for me. 

Over the years, I have purchased lots of website domains and like most developers, haven't done anything with them. In 2010, I registered christmaspanicbutton.co.uk as I was doing a lot of affiliate work and wanted a quirky little site which would give you a random gift if you were stuck for a seasonal present. At the time, I did have a little site but it was built in classic ASP, I kept it up for one Christmas but gave up on it probably because I had another 27 interesting 'personal projects' to tend to.

Back to the present day and I still have my affiliate account with affiliate window, which has a number of gift-type e-commerce websites on its feed including the likes of firebox and IWOOT, so I could download a product feed which I could import to my SQL server.

I wanted a project to allow me to gain some experience in using the new(ish) minimal APIs feature in .net so I decided to resurrect www.christmaspanicbutton.co.uk built with more modern technologies. 

To do this, the website would have four elements to it:

1. The database which holds all the gift data
2. A stored procedure used to get a random gift from the database
3. An API, written using the minimal APIs framework within Microsoft .net which executes the stored procedure to get data, and format the output as JSON
4. The www.christmaspanicbutton.co.uk website consumes the API and displays the data in a nice graphical format

I could have the API code query the database directly, but when you have access to stored procedures it is much better to utilise them instead of writing dynamic queries which could potentially leave my site open to SQL injection if I don't remember to validate everything properly, especially when adding new features.

Let's start with the database work

I created a database to hold the gift data and I created a stored procedure which would output a single random gift every time it was called. The stored procedure is called sp_GetRandomItem and does not accept any parameters and outputs a single row with the following data:

1. itemTitle - string
2. itemDescription - string
3. itemURL (including the affiliate id embedded) - string
4. itemImage (Url of image) - string
5. itemPrice (in pounds including the postage) - decimal

I'm not going to go into any details about the database or stored procedure other than explaining the data structure above because this article is mainly about the minimal API framework and SQL databases as well as stored procedures have been around for years and fully documented

The Minimal API Code

Microsoft has made building a minimal API so easy, I literally have a fully working API endpoint which accesses the database within a handful of lines of code, the code is below, and does all the work required) 

I have documented the code below but essentially it's a very simple c# program which uses SqlClient to return a random item from the stored procedure, it populates a record (a record is essentially a simplified class) called giftItem and then outputs that data as JSON. 

using System.Data.SqlClient;
using System.Data.Sql;
using System.Data;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

// Set up the mapping/routing
app.MapGet("/getrandomgift", () => {

// Create a database connection
using (SqlConnection connection = new SqlConnection("Server=database.domain.com;Database=db_ourdatabasename;User Id=usr_ouruser; Password=Password123;Trusted_Connection=false"))
{
    // Tell the database we'll be using a stored procedure
    using (SqlCommand command = new SqlCommand("sp_GetRandomItem"))
    {      
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Connection = connection;
        connection.Open();

        // Execute the stored procedure
        SqlDataReader reader = command.ExecuteReader();

            // Check the database has returned something and if so then populate the gift item with the data (the stored procedure should only ever return 1 row)
            if (reader.HasRows)
            {
                reader.Read();

                var randomGiftItem = new giftItem
                (
                    reader["itemTitle"].ToString(),
                    reader["itemDescription"].ToString(),
                    reader["itemURL"].ToString(),
                    reader["itemImage"].ToString(),
                    Convert.ToDecimal(reader["itemPrice"])
                    );
                
                // Return the random gift item as a JSON string using the giftItem model
                return Results.Ok(randomGiftItem);
            }

            //if no data is returned then assume there's an error and return "No data"
            else

            {
                return Results.Problem("No Data");
            }
        }
    }
});

app.Run();

public record giftItem(
    
    string title,
    string description,
    string url,
    string image,
    decimal price
    
);

The API endpoint that the website will call is called /getrandomgift and is set up very simply using MapGet

// Set up the mapping/routing
app.MapGet("/getrandomgift", () => {

This tells the code to set up a Mapping or route and executes the code below it when called. The API will run from https://api.christmaspanicbutton.co.uk/getrandomgift and when called returns something like this:

{
   "title":"Mission Impawsible Game",
   "description":"Mission Impawsible is the purrfect game for cat lovers! You can play with as little as 2 players, so team up with the whole family or go head to head with one adversary. All you’ve got to do is pull the sticks out with the least amount of cats falling as possible. Sounds easy? What if we told you the cats were real?* \u00A0 *They’re not, we couldn’t fit the real ones in the box.",
   "url":"https://www.awin1.com/pclick.php?p=34087625483&a=84252&m=550",
   "image":"https://images2.productserve.com/?w=200&h=200&bg=white&trim=5&t=letterbox&url=ssl%3Afirebox.com%2Fmedia%2Fcatalog%2Fproduct%2Fm%2Fi%2Fmission-impawsible-lifestyle-v4.jpg&feedId=45795&k=4f38319898723a7bcceab9c615797b1d9163a87d",
   "price":16.9800
}

Consuming the API

On its own, the API is not of much use. We need to consume the data from the API and render it to a website to create anything useful from it.

So, I have created the Christmas Panic Button website using only raw HTML and Javascript which is something I have never done before (I have previously only really consumed bespoke APIs at the back end using c#)

I will leave the website code for another article but you can have a play with the site at: https://www.christmaspanicebutton.co.uk

I really could do with your help! 😃 If you find this article interesting, could you please do me a favour by either sharing it or commenting below, I would love to hear yours and other peoples' thoughts on this subject. And if this or any other content on the site has helped you and you would like to show your appreciation, then you can always buy me a coffee ☕️ It would make the time I put into this more than worthwhile! Thank you 😃

Author: Craig Pickles (YorkshireTechy)