Windows API with JSON and SQL |
API Backend in Windows with JSON and SQL Serverπ₯οΈ Create a software solution with Windows API with JSON responses and accessing SQL Server Databases.
|
Windows API BackendThis project built a Windowsβhosted REST API that connects to SQL Server and delivers JSON data. Includes the next features: - Windows REST API with SQL and JSON Data This example uses an order management system with REST API with SQL Backend. The solution implements an API for Windows .NET (C#) accesing storage in SQL Server databases. This is a Rest API using ADO.NET with SqlConnection, SqlCommand, and SqlDataReader. This uses ASP.NET Core Web API with GET, POST, PUT and DELETE endpoints. The response is in JSON: Returns the order as a JSON object, or 404 Not Found if no match. |
API EndpointsThis is a REST API using use ASP.NET Core Web API. βοΈ How the API works: The API to support commands to insert, delete, list, update orders with POST, GET, DELETE endpoints This ASP.NET Core Web API solution integrates the most common endpoints: - POST /orders β for inserting new orders - GET /orders/{orderId} β Retrieves a singe order by ID. Will accept an orderId as input and return the order details. - PUT /orders/{orderId} β Update order by ID - DELETE /orders/{orderId} β Delete order by ID - GET /orders/user/{userId} β List all orders for a given user |
SQL Connectionπ Change the connection string to use your credentials and configuration: string dbHost = "mySQLHost"; string dbDatabase = "mySQLDatabase"; string dbUserId = "mySQLUserId"; string dbUserPassword = "mySQLUserPass"; Notes: Update your credentials: The API uses mySQLUserId and mySQLUserPass to connect to the SQL Server database. Connection String: Replace mySQLHost and mySQLDatabse with your actual SQL Server instance and database name. Authentication: That configuration uses Windows Authentication (Integrated Security=True). If you use SQL authentication, replace with User Id=...;Password=...;. Security note: In production, credentials should be stored securely (e.g., environment variables, secrets manager). |
Table ordersπ» This example uses a table called orders with field names: orderId, orderTitle, orderDate, orderItemsIds, orderDescription, orderPrice, orderUserId You can adjust the SQL query to match your table schema. Example orders table:
CREATE TABLE orders (
orderId INT PRIMARY KEY IDENTITY,
orderTitle NVARCHAR(100) NOT NULL,
orderDate NVARCHAR(100) NOT NULL,
orderItemsIds NVARCHAR(100) NOT NULL,
orderDescription NVARCHAR(100) NOT NULL,
orderPrice NVARCHAR(100) NOT NULL,
orderUserId NVARCHAR(100) NOT NULL
);
|
API example in C#π οΈ Here's the source code:
// Rest API in Windows Core
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using System.Data.SqlClient;
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddEndpointsApiExplorer();
var app = builder.Build();
// Fixed database connection values
string dbHost = "mySQLHost";
string dbDatabase = "mySQLDatabase";
string dbUserId = "mySQLUserId";
string dbUserPassword = "mySQLUserPass";
// Connection string
string connectionString = $"Server={dbHost};Database={dbDatabase};User Id={dbUserId};Password={dbUserPassword};";
// POST: Insert new order
app.MapPost("/orders", async (Order newOrder) =>
{
string insertQuery = @"INSERT INTO Orders
(orderTitle, orderDate, orderItemsIds, orderDescription, orderPrice, orderUserId)
VALUES (@orderTitle, @orderDate, @orderItemsIds, @orderDescription, @orderPrice, @orderUserId);
SELECT SCOPE_IDENTITY();";
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
using var command = new SqlCommand(insertQuery, connection);
command.Parameters.AddWithValue("@orderTitle", newOrder.orderTitle);
command.Parameters.AddWithValue("@orderDate", newOrder.orderDate);
command.Parameters.AddWithValue("@orderItemsIds", newOrder.orderItemsIds);
command.Parameters.AddWithValue("@orderDescription", newOrder.orderDescription);
command.Parameters.AddWithValue("@orderPrice", newOrder.orderPrice);
command.Parameters.AddWithValue("@orderUserId", newOrder.orderUserId);
var insertedId = await command.ExecuteScalarAsync();
return Results.Created($"/orders/{insertedId}", new { orderId = insertedId });
});
// GET: Get order by ID
app.MapGet("/orders/{orderId}", async (int orderId) =>
{
string query = @"SELECT * FROM Orders WHERE orderId = @orderId";
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
using var command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@orderId", orderId);
using var reader = await command.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
var orderObj = new
{
orderId = reader["orderId"],
orderTitle = reader["orderTitle"],
orderDate = reader["orderDate"],
orderItemsIds = reader["orderItemsIds"],
orderDescription = reader["orderDescription"],
orderPrice = reader["orderPrice"],
orderUserId = reader["orderUserId"]
};
return Results.Json(orderObj);
}
return Results.NotFound(new { Error = "Order not found" });
});
// PUT: Update order by ID
app.MapPut("/orders/{orderId}", async (int orderId, Order updatedOrder) =>
{
string updateQuery = @"UPDATE Orders SET
orderTitle=@orderTitle, orderDate=@orderDate, orderItemsIds=@orderItemsIds,
orderDescription=@orderDescription, orderPrice=@orderPrice, orderUserId=@orderUserId
WHERE orderId=@orderId";
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
using var command = new SqlCommand(updateQuery, connection);
command.Parameters.AddWithValue("@orderId", orderId);
command.Parameters.AddWithValue("@orderTitle", updatedOrder.orderTitle);
command.Parameters.AddWithValue("@orderDate", updatedOrder.orderDate);
command.Parameters.AddWithValue("@orderItemsIds", updatedOrder.orderItemsIds);
command.Parameters.AddWithValue("@orderDescription", updatedOrder.orderDescription);
command.Parameters.AddWithValue("@orderPrice", updatedOrder.orderPrice);
command.Parameters.AddWithValue("@orderUserId", updatedOrder.orderUserId);
int rowsAffected = await command.ExecuteNonQueryAsync();
return rowsAffected > 0 ? Results.Ok(new { Updated = true }) : Results.NotFound(new { Error = "Order not found" });
});
// DELETE: Delete order by ID
app.MapDelete("/orders/{orderId}", async (int orderId) =>
{
string deleteQuery = @"DELETE FROM Orders WHERE orderId=@orderId";
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
using var command = new SqlCommand(deleteQuery, connection);
command.Parameters.AddWithValue("@orderId", orderId);
int rowsAffected = await command.ExecuteNonQueryAsync();
return rowsAffected > 0 ? Results.Ok(new { Deleted = true }) : Results.NotFound(new { Error = "Order not found" });
});
// GET: List orders by userId
app.MapGet("/orders/user/{userId}", async (int userId) =>
{
string query = @"SELECT * FROM Orders WHERE orderUserId=@userId";
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
using var command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@userId", userId);
using var reader = await command.ExecuteReaderAsync();
var ordersList = new System.Collections.Generic.List
|
GET request βοΈ Example with a GET request to retrieve order details by id:
|
POST requestβ‘οΈ You can also test the POST endpoint so you can insert new orders into the database via JSON input. This is an example with a POST request:
POST http://myserver.com:5000/orders
Content-Type: application/json
{
"orderTitle": "New Laptop",
"orderDate": "2026-03-22T00:00:00",
"orderItemsIds": "301,302",
"orderDescription": "MacBook Pro with accessories",
"orderPrice": 2200.00,
"orderUserId": 10
}
|
DELETE RequestβοΈ Example HTTP Request DELETE http://myserver.com:5000/orders/3 This request will attempt to delete the order with orderId = 3. Example with curl curl -X DELETE http://myserver.com:5000/orders/3 Example Response (Success) { "Deleted": true } Example Response (Not Found) { "Error": "Order not found" } How it works - The API receives the orderId from the URL path. - It runs DELETE FROM Orders WHERE orderId=@orderId. - If a row is deleted, you get { "Deleted": true }. - If no row matches, you get { "Error": "Order not found" }. This makes it easy to manage orders directly via REST calls. Would you like me to also show a full workflow example (insert β list β update β delete) so you can see the lifecycle of an order through the API? |
List ordersπ Use the GET /orders/user/{userId} endpoint to list all orders for a given user: Example HTTP Request GET http://myserver.com:5000/orders/user/12 This request will return all orders where orderUserId = 12. Example with curl curl http://myserver.com:5000/orders/user/12 Example Response (Success) [ { "orderId": 4, "orderTitle": "Office Furniture", "orderDate": "2026-03-22T00:00:00", "orderItemsIds": "401,402", "orderDescription": "Desk and chair set", "orderPrice": 850.00, "orderUserId": 12 }, { "orderId": 5, "orderTitle": "Printer Purchase", "orderDate": "2026-03-23T00:00:00", "orderItemsIds": "501", "orderDescription": "HP LaserJet Pro", "orderPrice": 300.00, "orderUserId": 12 } ] Example Response (No Orders Found) [] How it works - The API takes the userId from the URL path. - It queries the database: SELECT * FROM Orders WHERE orderUserId=@userId; - Returns all matching rows as a JSON array. This endpoint is useful for retrieving all orders belonging to a specific user. |
Test Workflowβ»οΈ Extended end-to-end workflow (insert β list by user β update β delete) so you can see how these endpoints work together in practice. Hereβs a full workflow example showing how to use the API endpoints we built for orders. This demonstrates the lifecycle: insert β list β update β delete. β’ Insert a new order (POST /orders) Request: curl -X POST http://myserver.com:5000/orders \ -H "Content-Type: application/json" \ -d '{ "orderTitle": "Office Furniture", "orderDate": "2026-03-22T00:00:00", "orderItemsIds": "401,402", "orderDescription": "Desk and chair set", "orderPrice": 850.00, "orderUserId": 12 }' Response: { "orderId": 4 } β’ List orders for a user (GET /orders/user/{userId})
β’ Update an order (PUT /orders/{orderId})
|
API Test PowerShellπ€ Hereβs a simple PowerShell script that automates testing all your Orders API endpoints in sequence: insert β list β get β update β delete β verify. It uses Invoke-RestMethod to send requests and parse JSON responses. # Base URL of your API $baseUrl = "http://myserver.com:5000/orders" Write-Host "=== 1. Insert new order ===" $insertBody = @{ orderTitle = "Test Order" orderDate = "2026-03-22T00:00:00" orderItemsIds = "101,102" orderDescription= "Automated test order" orderPrice = 123.45 orderUserId = 99 } | ConvertTo-Json $insertResponse = Invoke-RestMethod -Uri $baseUrl -Method Post -Body $insertBody -ContentType "application/json" Write-Host "Insert Response:" ($insertResponse | ConvertTo-Json -Depth 3) $orderId = $insertResponse.orderId Write-Host "`n=== 2. List orders by userId ===" $listResponse = Invoke-RestMethod -Uri "$baseUrl/user/99" -Method Get $listResponse | ConvertTo-Json -Depth 3 | Write-Host Write-Host "`n=== 3. Get order by orderId ===" $getResponse = Invoke-RestMethod -Uri "$baseUrl/$orderId" -Method Get $getResponse | ConvertTo-Json -Depth 3 | Write-Host Write-Host "`n=== 4. Update order by orderId ===" $updateBody = @{ orderTitle = "Updated Test Order" orderDate = "2026-03-22T00:00:00" orderItemsIds = "101,102,103" orderDescription= "Updated automated test order" orderPrice = 200.00 orderUserId = 99 } | ConvertTo-Json $updateResponse = Invoke-RestMethod -Uri "$baseUrl/$orderId" -Method Put -Body $updateBody -ContentType "application/json" Write-Host "Update Response:" ($updateResponse | ConvertTo-Json -Depth 3) Write-Host "`n=== 5. Delete order by orderId ===" $deleteResponse = Invoke-RestMethod -Uri "$baseUrl/$orderId" -Method Delete Write-Host "Delete Response:" ($deleteResponse | ConvertTo-Json -Depth 3) Write-Host "`n=== 6. Verify deletion (list orders by userId again) ===" $verifyResponse = Invoke-RestMethod -Uri "$baseUrl/user/99" -Method Get $verifyResponse | ConvertTo-Json -Depth 3 | Write-Host π What this script does - Insert a new order for userId = 99. - List all orders for that user. - Get the inserted order by its orderId. - Update the order with new details. - Delete the order. - Verify deletion by listing orders again. This script gives you a quick automated test of all endpoints directly in PowerShell. |
API Test in BashAutomate Test in bash You can chain these calls in a script (e.g., a simple Bash or PowerShell script) so you can automate the workflow end-to-end. To automate testing of all the endpoints in your Orders API, you can write a simple test script. Hereβs an example using Bash + curl that runs through the full workflow: insert β list β update β delete β list again. #!/bin/bash BASE_URL="http://myserver.com:5000/orders" echo "=== 1. Insert new order ===" INSERT_RESPONSE=$(curl -s -X POST $BASE_URL \ -H "Content-Type: application/json" \ -d '{ "orderTitle": "Test Order", "orderDate": "2026-03-22T00:00:00", "orderItemsIds": "101,102", "orderDescription": "Automated test order", "orderPrice": 123.45, "orderUserId": 99 }') echo "Insert Response: $INSERT_RESPONSE" ORDER_ID=$(echo $INSERT_RESPONSE | jq -r '.orderId') echo "=== 2. List orders by userId ===" curl -s "$BASE_URL/user/99" | jq . echo "=== 3. Get order by orderId ===" curl -s "$BASE_URL/$ORDER_ID" | jq . echo "=== 4. Update order by orderId ===" UPDATE_RESPONSE=$(curl -s -X PUT "$BASE_URL/$ORDER_ID" \ -H "Content-Type: application/json" \ -d '{ "orderTitle": "Updated Test Order", "orderDate": "2026-03-22T00:00:00", "orderItemsIds": "101,102,103", "orderDescription": "Updated automated test order", "orderPrice": 200.00, "orderUserId": 99 }') echo "Update Response: $UPDATE_RESPONSE" echo "=== 5. Delete order by orderId ===" DELETE_RESPONSE=$(curl -s -X DELETE "$BASE_URL/$ORDER_ID") echo "Delete Response: $DELETE_RESPONSE" echo "=== 6. Verify deletion (list orders by userId again) ===" curl -s "$BASE_URL/user/99" | jq . π How this works - Step 1: Inserts a new order and captures the orderId from the JSON response. - Step 2: Lists all orders for userId = 99. - Step 3: Fetches the order by its orderId. - Step 4: Updates the order with new details. - Step 5: Deletes the order. - Step 6: Lists orders again to confirm deletion. Requirements - curl installed. - jq installed (for parsing JSON easily). This script gives you a quick automated check that all endpoints are working correctly. Technology Windows API SQL 2026 SQL Server on Linux: How? Introduction Microsoft Whatβs new in Microsoft SQL Server 2025 on AWS Amazon Web Services (AWS) How an old Drawbridge helped Microsoft bring SQL Server to Linux Ars Technica Announcing Microsoft SQL Server 2025: Enterprise AI-ready database from ground to cloud Microsoft |