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. |