.NET Integration

Connect to Universql using Snowflake's .NET driver, providing seamless integration with .NET applications and frameworks.

Installation

Install via NuGet Package Manager:

dotnet add package Snowflake.Data

Or via Package Manager Console:

Install-Package Snowflake.Data

Quick Start

Basic ADO.NET usage:

using Snowflake.Data.Client;
using System.Data;
 
public class SnowflakeExample
{
    private readonly string _connectionString;
 
    public SnowflakeExample(IConfiguration configuration)
    {
        _connectionString = $"account={configuration["Snowflake:Account"]};user={configuration["Snowflake:User"]};password={configuration["Snowflake:Password"]}";
    }
 
    public async Task<IEnumerable<UserStats>> GetMonthlyUserStatsAsync()
    {
        // Create connection with Universql server
        using var connection = new SnowflakeDbConnection(
            $"host={universql_server};user=your_username;password=your_password"  // Universql server
        );
        await connection.OpenAsync();
 
        using var command = connection.CreateCommand();
        command.CommandText = @"
            SELECT 
                DATE_TRUNC('month', created_at) as month,
                COUNT(*) as user_count,
                COUNT(DISTINCT country) as countries
            FROM users
            GROUP BY 1
            ORDER BY 1 DESC
            LIMIT 12";
 
        var stats = new List<UserStats>();
        using var reader = await command.ExecuteReaderAsync();
        
        while (await reader.ReadAsync())
        {
            stats.Add(new UserStats
            {
                Month = reader.GetDateTime(0),
                UserCount = reader.GetInt32(1),
                CountryCount = reader.GetInt32(2)
            });
        }
 
        return stats;
    }
}
 
public class UserStats
{
    public DateTime Month { get; set; }
    public int UserCount { get; set; }
    public int CountryCount { get; set; }
}

Entity Framework Core Integration

First, install the EF Core provider:

dotnet add package EFCore.Snowflake

Configure your DbContext:

public class SnowflakeContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<Order> Orders { get; set; }
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSnowflake(
            $"host={universql_server};" +  // Universql server
            "user=username;password=password;db=database;schema=schema"
        );
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Price).HasColumnType("NUMBER(10,2)");
            entity.HasIndex(e => e.Category);
        });
 
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.HasOne(e => e.Product)
                  .WithMany(p => p.Orders)
                  .HasForeignKey(e => e.ProductId);
        });
    }
}
 
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Category { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
}
 
public class Order
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public DateTime OrderDate { get; set; }
    public virtual Product Product { get; set; }
}

ASP.NET Core Integration

Configure Snowflake in your web application:

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<SnowflakeContext>(options =>
            options.UseSnowflake($"host={universql_server};db=your_database"));  // Universql server
            
        services.AddScoped<IProductRepository, ProductRepository>();
    }
}

Repository implementation:

public interface IProductRepository
{
    Task<IEnumerable<Product>> GetTopSellingProductsAsync(int limit = 10);
    Task<Product> CreateProductAsync(Product product);
}
 
public class ProductRepository : IProductRepository
{
    private readonly SnowflakeContext _context;
 
    public ProductRepository(SnowflakeContext context)
    {
        _context = context;
    }
 
    public async Task<IEnumerable<Product>> GetTopSellingProductsAsync(int limit = 10)
    {
        return await _context.Products
            .Include(p => p.Orders)
            .Select(p => new
            {
                Product = p,
                TotalSold = p.Orders.Sum(o => o.Quantity)
            })
            .OrderByDescending(x => x.TotalSold)
            .Take(limit)
            .Select(x => x.Product)
            .ToListAsync();
    }
 
    public async Task<Product> CreateProductAsync(Product product)
    {
        _context.Products.Add(product);
        await _context.SaveChangesAsync();
        return product;
    }
}

Configuration

Configure connection settings in appsettings.json:

{
  "Snowflake": {
    "Host": "{universql_server}",  // Universql server
    "User": "your_username",
    "Account": "your_account",
    "Password": "your_password",
    "Warehouse": "your_warehouse",
    "Database": "your_database",
    "Schema": "your_schema"
  }
}

Bulk Operations

Perform bulk operations efficiently:

public async Task BulkInsertProductsAsync(IEnumerable<Product> products)
{
    using var connection = new SnowflakeDbConnection(
        $"account={universql_server};user=your_username;password=your_password"  // Universql server
    );
    await connection.OpenAsync();
 
    using var transaction = connection.BeginTransaction();
    try
    {
        using var command = connection.CreateCommand();
        command.CommandText = @"
            INSERT INTO products (name, price, category)
            VALUES (@name, @price, @category)";
 
        var nameParam = command.CreateParameter();
        nameParam.ParameterName = "@name";
        command.Parameters.Add(nameParam);
 
        var priceParam = command.CreateParameter();
        priceParam.ParameterName = "@price";
        command.Parameters.Add(priceParam);
 
        var categoryParam = command.CreateParameter();
        categoryParam.ParameterName = "@category";
        command.Parameters.Add(categoryParam);
 
        foreach (var product in products)
        {
            nameParam.Value = product.Name;
            priceParam.Value = product.Price;
            categoryParam.Value = product.Category;
            await command.ExecuteNonQueryAsync();
        }
 
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

Error Handling

Handle Snowflake-specific errors:

using Snowflake.Data.Client;
using Snowflake.Data.Core;
 
public async Task<T> ExecuteWithRetryAsync<T>(Func<Task<T>> operation)
{
    var retryCount = 0;
    var maxRetries = 3;
 
    while (true)
    {
        try
        {
            return await operation();
        }
        catch (SnowflakeDbException ex)
        {
            if (retryCount >= maxRetries || !IsRetryableError(ex.ErrorCode))
                throw;
 
            retryCount++;
            await Task.Delay(TimeSpan.FromSeconds(Math.Pow(2, retryCount)));
        }
    }
}
 
private bool IsRetryableError(int errorCode)
{
    return errorCode switch
    {
        390144 => true, // Connection timeout
        390187 => true, // Network error
        390195 => true, // Session expired
        _ => false
    };
}

Best Practices

Use Connection Pooling

services.AddDbContextPool<SnowflakeContext>(options =>
    options.UseSnowflake(
        $"host={universql_server};db=your_database"  // Universql server
    ),
    poolSize: 128);

Implement Retry Logic

services.AddTransient<ISnowflakeRetryPolicy>(sp =>
    new SnowflakeRetryPolicy
    {
        MaxRetries = 3,
        DelayBetweenRetries = TimeSpan.FromSeconds(2)
    });

Use Parameters

// Good
command.CommandText = "SELECT * FROM users WHERE id = @id";
command.Parameters.AddWithValue("@id", userId);
 
// Avoid
command.CommandText = $"SELECT * FROM users WHERE id = {userId}";