ASP.NET Web API 2迁移至Azure：图片存储与API返回格式实现咨询

Hey Ahmed, let's walk through exactly how to get your ASP.NET Web API 2 setup handling images with Azure SQL and Blob Storage (the best practice for cloud) to return that exact JSON format you need. I’ve tackled this exact scenario before, so here’s a step-by-step guide from start to finish:

First off, don’t store raw image binaries directly in Azure SQL unless you’re dealing with tiny icons (<10KB). For most cases, Azure Blob Storage is the way to go—it’s cheaper, faster, and designed for large file storage. We’ll store the image file in Blob Storage, then save the image’s URL (or relative path) in your Azure SQL table. This aligns perfectly with the image: "/john.png" format you want.

If you absolutely must store binaries in SQL (e.g., strict compliance rules), I’ll cover that at the end, but Blob Storage is the recommended path.

Let’s get your blob container ready:

• Log into the Azure Portal, create a Storage Account (choose Standard V2, LRS redundancy for cost-effectiveness).
• Inside the storage account, create a Blob Container. Set the access level to Blob (anonymous read access for blobs only) if you want public access to images (great for your use case). If you need private access, you’ll have to handle SAS tokens later, but public access simplifies the "/john.png" path.
• Note down your container’s base URL (e.g., https://yourstorageaccount.blob.core.windows.net/yourcontainer/).

You’ll need a column to store the image’s URL/path. Run this SQL command to add it to your existing table:

ALTER TABLE YourUserTableName
ADD image_url NVARCHAR(255) NULL;

We’ll use this column to store values like "/john.png" or the full blob URL (e.g., "https://yourstorageaccount.blob.core.windows.net/yourcontainer/john.png").

Next, add an API endpoint to accept image uploads, save them to Blob Storage, and update the SQL table with the image path.

First, install the Azure Blob Storage NuGet package compatible with .NET Framework (since you’re using Web API 2):

Install-Package Azure.Storage.Blobs -Version 12.20.0

Here’s a code snippet for the upload endpoint (using ADO.NET):

[HttpPost]
[Route("api/users/{id}/upload-image")]
public async Task<IHttpActionResult> UploadUserImage(int id)
{
    if (!Request.Content.IsMimeMultipartContent())
    {
        return BadRequest("Unsupported media type");
    }

    var provider = new MultipartMemoryStreamProvider();
    await Request.Content.ReadAsMultipartAsync(provider);

    var file = provider.Contents.FirstOrDefault();
    if (file == null) return BadRequest("No file uploaded");

    // Generate a filename (use username or GUID to avoid conflicts)
    var fileName = $"john.png"; // Or use user's name from DB: GetUserNameById(id) + ".png"
    var storageConnString = "Your_Azure_Storage_Connection_String";
    var containerName = "your-container-name";

    // Upload to Blob Storage
    var blobServiceClient = new BlobServiceClient(storageConnString);
    var containerClient = blobServiceClient.GetBlobContainerClient(containerName);
    var blobClient = containerClient.GetBlobClient(fileName);

    using (var stream = await file.ReadAsStreamAsync())
    {
        await blobClient.UploadAsync(stream, overwrite: true);
    }

    // Update SQL with the image path (use relative path or full URL)
    var sqlConnString = "Your_Azure_SQL_Connection_String";
    var updateQuery = "UPDATE YourUserTableName SET image_url = @ImageUrl WHERE pK_ID = @UserId";

    using (var conn = new SqlConnection(sqlConnString))
    {
        var cmd = new SqlCommand(updateQuery, conn);
        cmd.Parameters.AddWithValue("@ImageUrl", $"/{fileName}"); // Or use full blob URL
        cmd.Parameters.AddWithValue("@UserId", id);

        conn.Open();
        await cmd.ExecuteNonQueryAsync();
    }

    return Ok("Image uploaded successfully");
}

Modify your existing GET endpoint to include the image field from the image_url column. Here’s how it looks with ADO.NET:

[HttpGet]
[Route("api/users/{id}")]
public IHttpActionResult GetUser(int id)
{
    var sqlConnString = "Your_Azure_SQL_Connection_String";
    var query = "SELECT pK_ID, name_EN, count, phone, image_url FROM YourUserTableName WHERE pK_ID = @UserId";

    using (var conn = new SqlConnection(sqlConnString))
    {
        var cmd = new SqlCommand(query, conn);
        cmd.Parameters.AddWithValue("@UserId", id);

        conn.Open();
        var reader = cmd.ExecuteReader();

        if (reader.Read())
        {
            var user = new
            {
                pK_ID = reader.GetInt32(0),
                name_EN = reader.GetString(1),
                count = reader.GetInt32(2),
                phone = reader.GetString(3),
                image = reader.IsDBNull(4) ? null : reader.GetString(4)
            };

            return Ok(new[] { user });
        }

        return NotFound();
    }
}

This will return exactly the JSON format you requested:

[ { "pK_ID": 1, "name_EN": "John", "count": 0, "phone": "52525", "image": "/john.png" }]

If you’re using a relative path like "/john.png", you’ll need to make sure frontend apps can resolve it to the actual blob URL. Options include:

• Configure your frontend to prepend the blob container base URL (e.g., https://yourstorageaccount.blob.core.windows.net/yourcontainer/ + /john.png).
• Set up a custom domain for your blob storage (e.g., https://yourdomain.com/john.png maps to the blob), so relative paths work directly in browsers.

If you have to store binaries in SQL, here’s how to do it:

1. Add a binary column to your table:

   ALTER TABLE YourUserTableName
   ADD image_data VARBINARY(MAX) NULL;
   

2. Modify the upload endpoint to save the image as a byte array in SQL.
3. Add a separate endpoint to serve the image:

   [HttpGet]
   [Route("api/users/{id}/image")]
   public IHttpActionResult GetUserImage(int id)
   {
       var sqlConnString = "Your_Azure_SQL_Connection_String";
       var query = "SELECT image_data FROM YourUserTableName WHERE pK_ID = @UserId";
   
       using (var conn = new SqlConnection(sqlConnString))
       {
           var cmd = new SqlCommand(query, conn);
           cmd.Parameters.AddWithValue("@UserId", id);
   
           conn.Open();
           var imageData = cmd.ExecuteScalar() as byte[];
   
           if (imageData == null) return NotFound();
   
           return new FileContentResult(imageData, "image/png"); // Adjust MIME type for your images
       }
   }
   

4. Update your main API to return the endpoint URL: image: "/api/users/1/image"

1. Use Postman or curl to upload an image to the /api/users/{id}/upload-image endpoint.
2. Verify the image exists in your blob container and the image_url column is updated in SQL.
3. Call the GET endpoint and confirm the JSON matches your desired format.
4. Test the image URL to ensure it loads correctly in a browser.

内容的提问来源于stack exchange，提问作者ahmed