NashTech Blog

Get in touch

Integrating Snowflake with ASP.NET Core Web API Using Swagger

In this tutorial, we'll walk through the process of setting up an ASP.NET Core Web API project that connects to Snowflake, a popular cloud-based data warehousing platform. We'll also integrate Swagger for easy API documentation and testing.

Prerequisites

Before we begin, make sure you have the following:
- .NET Core SDK installed (version 3.1 or later) - Basic knowledge of ASP.NET Core and C# - Access to a Snowflake account with necessary credentials (account, username, password, database, schema, warehouse)

Step 1: Create a New ASP.NET Core Web API Project

Open your terminal or command prompt and run the following commands to create a new ASP.NET Core Web API project: 
				
					dotnet new webapi -n SnowflakeWebAPI
cd SnowflakeWebAPI

Step 2: Install Required NuGet Packages

Next, install the necessary NuGet packages for Snowflake integration and Swagger: 
				
					dotnet add package Snowflake.Data
dotnet add package Swashbuckle.AspNetCore

Step 3: Configure Snowflake Connection and Swagger

First, configure the Program.cs file to set up the web host: 
				
					using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.OpenApi.Models;
using Snowflake.Data.Client;
using System.Data;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();

// Register SnowflakeService
builder.Services.AddTransient();

// Configure Snowflake connection
builder.Services.AddSingleton(provider =>
{
    var connectionString = builder.Configuration.GetConnectionString("Snowflake");
    var conn = new SnowflakeDbConnection();
    conn.ConnectionString = connectionString;
    return conn;
});

// Register Swagger services
builder.Services.AddSwaggerGen(c =>
{
    c.SwaggerDoc("v1", new OpenApiInfo { Title = "Snowflake API", Version = "v1" });
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseDeveloperExceptionPage();
}

// Enable middleware to serve generated Swagger as a JSON endpoint
app.UseSwagger();

// Enable middleware to serve swagger-ui (HTML, JS, CSS, etc.), specifying the Swagger JSON endpoint
app.UseSwaggerUI(c =>
{
    c.SwaggerEndpoint("/swagger/v1/swagger.json", "Snowflake API V1");
    c.RoutePrefix = string.Empty; // Set Swagger UI at the root URL
});

app.UseRouting();

app.UseAuthorization();

app.MapControllers();

app.Run();

Step 4: Create SnowflakeService Class

Create a service class (SnowflakeService.cs) to manage Snowflake connections and data retrieval: 
				
					using Microsoft.Extensions.Configuration;
using Snowflake.Data.Client;
using System.Data;
using System.Threading.Tasks;

public class SnowflakeService
{
    private readonly SnowflakeDbConnection _connection;

    public SnowflakeService(SnowflakeDbConnection connection)
    {
        _connection = connection;
    }

    public async Task GetDataFromTableAsync(string tableName)
    {
        await _connection.OpenAsync();

        using (var cmd = _connection.CreateCommand())
        {
            cmd.CommandText = $"SELECT * FROM {tableName}";

            using (var adapter = new SnowflakeDbDataAdapter(cmd))
            {
                var dataTable = new DataTable();
                adapter.Fill(dataTable);
                return dataTable;
            }
        }
    }
}

Step 5: Create SnowflakeController

Create a controller (SnowflakeController.cs) to expose API endpoints: 
				
					using Microsoft.AspNetCore.Mvc;
using System.Data;
using System.Threading.Tasks;

[ApiController]
[Route("[controller]")]
public class SnowflakeController : ControllerBase
{
    private readonly SnowflakeService _snowflakeService;

    public SnowflakeController(SnowflakeService snowflakeService)
    {
        _snowflakeService = snowflakeService;
    }

    [HttpGet]
    public async Task GetDataFromTable(string tableName)
    {
        try
        {
            DataTable data = await _snowflakeService.GetDataFromTableAsync(tableName);
            return Ok(data);
        }
        catch (SnowflakeDbException ex)
        {
            return StatusCode(500, $"Snowflake Error: {ex.Message}");
        }
    }
}

Step 6: Configure Snowflake Connection String

Add your Snowflake connection string to appsettings.json: 
				
					{
  "ConnectionStrings": {
    "Snowflake": "account=.snowflakecomputing.com;user=;password=;db=;schema=;warehouse=;role="
  }
}

Step 7: Run the Application

Run your ASP.NET Core Web API project using Visual Studio or with the following command: 
				
					dotnet run

Step 8: Access Swagger UI

Navigate to https://localhost:/swagger in your web browser to access Swagger UI. You can use Swagger UI to document and test your API endpoints, including the GET /Snowflake/GetDataFromTable endpoint.

Conclusion

By following this guide, you've successfully set up an ASP.NET Core Web API project that connects to Snowflake and integrated Swagger for API documentation and testing. This setup allows you to dynamically fetch data from any table in your Snowflake database through a RESTful API.
Scroll to Top