page_type	languages	products	description	urlFragment
sample	tsql sql aspx-csharp json	azure dotnet aspnet aspnet-core azure-app-service vs-code azure-sql-database	Creating a modern REST API with .NET Core and Azure SQL, using Dapper and Visual Studio Code	azure-sql-db-dotnet-rest-api

Creating a REST API with .NET Core and Azure SQL

Thanks to native JSON support, creating a REST API with Azure SQL and .NET Core is really a matter of a few lines of code:

    var qr = await conn.ExecuteScalarAsync<string>(
        sql: procedure,
        param: parameters,
        commandType: CommandType.StoredProcedure
    );
    
    var result = JsonDocument.Parse(qr);

This is possible thanks to Azure SQL native support to JSON format and the MicroORM Dapper that removes all the plumbing code and returns not tables and columns but a fully deserialized object. Object that can be a POCO object or just JSON for maximum flexibilty.

Install Sample Database

In order to run this sample, the WideWorldImporters database is needed. Install WideWorldImporters sample database:

Restore WideWorldImporters Database

Add Database Objects

Once the sample database has been installed, you need to add some stored procedures that will be called from .NET. The SQL code is available here:

./SQL/WideWorldImportersUpdates.sql

If you need any help in executing the SQL script, you can find a Quickstart here: Quickstart: Use Azure Data Studio to connect and query Azure SQL database

Run sample locally

Make sure you have .NET Core 6.0 SDK installed on your machine. Clone this repo in a directory on your computer and then configure the connection string in appsettings.json.

If you don't want to save the connection string in the appsettings.json file for security reasons, you can just set it using an environment variable:

Linux:

export ConnectionStrings__DefaultConnection="<your-connection-string>"

Windows (Powershell):

$Env:ConnectionStrings__DefaultConnection="<your-connection-string>"

Your connection string is something like:

SERVER=<your-server-name>.database.windows.net;DATABASE=<your-database-name>;UID=DotNetWebApp;PWD=a987REALLY#$%TRONGpa44w0rd!

Just replace <your-server-name> and <your-database-name> with the correct values for your environment.

To run and test the REST API locally, just run

dotnet run

.NET will start the HTTP server and when everything is up and running you'll see something like

Now listening on: https://localhost:5001

Using a REST Client (such as Visual Studio, Insomnia, Curl or PowerShell's Invoke-RestMethod), you can now call your API, for example:

curl -k -X GET https://localhost:5001/customer/123

and you'll get info on Customer 123:

[
    {
        "CustomerID": 123,
        "CustomerName": "Tailspin Toys (Roe Park, NY)",
        "PhoneNumber": "(212) 555-0100",
        "FaxNumber": "(212) 555-0101",
        "WebsiteURL": "http://www.tailspintoys.com/RoePark",
        "Delivery": {
            "AddressLine1": "Shop 219",
            "AddressLine2": "528 Persson Road",
            "PostalCode": "90775"
        }
    }
]

Check out more samples to test all implemented verbs here:

cUrl Samples

Deploy to Azure

Now that your REST API solution is ready, it's time to deploy it on Azure so that anyone can take advantage of it. A detailed article on how you can that that is here:

• Create an ASP.NET Core app in App Service on Linux

The only thing you have do in addition to what explained in the above article is to add the connection string to the Azure Web App configuration. Using AZ CLI, for example:

appName="azure-sql-db-dotnet-rest-api"
resourceGroup="my-resource-group"

az webapp config connection-string set \
    -g $resourceGroup \
    -n $appName \
    --settings DefaultConnection=$ConnectionStrings__DefaultConnection \
    --connection-string-type=SQLAzure

Just make sure you correctly set $appName and $resourceGroup to match your environment and also that the variable $ConnectionStrings__DefaultConnection as also been set, as mentioned in section "Run sample locally".

An example of a full script that deploys the REST API is available here: azure-deploy.sh.

Learn more

If you're new to .NET and want to learn more, there are a lot of tutorial available on the Microsoft Learn platform. You can start from here, for example:

• https://docs.microsoft.com/en-us/learn/modules/build-web-api-net-core/?view=aspnetcore-3.1

If you also want to learn more about Visual Studio Code, here's another resource:

Using .NET Core in Visual Studio Code

Contributing

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.