Snowflake.Data.Xt 1.7.0

Snowflake.Data.Xt ❄️

Nuget package for fluent snowflake access.

Usage

dotnet add package Snowflake.Data.Xt

Example

This package provides multiple attributes and extension methods to try to make it easier querying data from snowflake.
This is an example for the table EXAMPLE_COST joined with COST_CENTER.
It uses a left join. Check the attribute implementation for all join options.

Example.cs

using System;
using Snowflake.Data.Xt;

namespace SnowflakeApplication;

// Define the main table to read from. Attribute is only allowed once.
[SnowflakeTable(
  name: "EXAMPLE_COST",     // Name is optional. If not provided, it would be parsed from the class name.
  alias: "cost")]           // Alias has to be set for joins, otherwise is optional.
// Define a table to join from.
[SnowflakeJoin(
  table: "COST_CENTER",              // The table to join from.
  alias: "costCenter",               // The alias to use.
  type: SnowflakeJoinAttribute.Left, // The join type (Cross, FullOuter, Inner (default), Left, LeftOuter, Natural, Right or RightOuter).
  condition: "cost.COST_CENTER_ID = costCenter.ID")] // The join condition, aliases have to be used.
public class Example
{
  [SnowflakeColumn(name: "VALUE")] // Defines the column to map from. Uses the default table if not provided otherwise.
  public decimal Value { get; set; }

  [SnowflakeColumn(name: "ADDRESS", table: "COST_CENTER")] // Uses the joined table.
  public string Address { get; set; }

  [SnowflakeColumn] // If no name is provided, it will be parsed from the property name. Here: IS_READY
  public bool IsReady { get; set; }
}

ExamplesRepository.cs

using System.Collections.Generic;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Snowflake.Data.Xt;

namespace SnowflakeApplication;

public class ExamplesRepository : IExamplesRepository
{
  public async Task<IList<Example>> GetByAddressAsync(string address, CancellationToken cancellationToken = default)
  {
    var snowflakeCommand = new SnowflakeDbCommand<Example>() // Creates a default select statement
      .Select(item => item.Value)               // Only select the value
      .Where(item => item.Address == address)   // Filter for the address
      .OrderByAsc(item => item.Value);          // Sort by value ascending

    var list = await snowflakeCommand
      .ToListAsync(                             // Returns a list
        new List<(string, DbType, object)> { }, // Parameter list if required, here empty
        cancellationToken)
      .ConfigureAwait(false);

    return list;
  }
}

You can also join a table on itself (if you provide an table alias):

using System;
using Snowflake.Data.Xt;

namespace SnowflakeApplication;

[SnowflakeTable(
  name: "COST_CENTER",
  alias: "costCenter1")]
[SnowflakeJoin(
  table: "COST_CENTER",
  alias: "costCenter2",
  type: SnowflakeJoinAttribute.Left,
  condition: "costCenter1.COST_CENTER_ID = costCenter2.COST_CENTER_ID")]
public class Example
{
  [SnowflakeColumn(name: "VALUE")]
  public decimal Value { get; set; }

  [SnowflakeColumn(name: "ADDRESS", table: "COST_CENTER")]
  public string Address { get; set; }

  [SnowflakeColumn(name: "IS_READY", table: "COST_CENTER", tableAlias: "costCenter2")] // Alias must be provided
  public bool IsReady { get; set; }
}

... and reuse same column names (if you provide an column alias):

using System;
using Snowflake.Data.Xt;

namespace SnowflakeApplication;

[SnowflakeTable(
  name: "COST_CENTER",
  alias: "costCenter1")]
[SnowflakeJoin(
  table: "COST_CENTER",
  alias: "costCenter2",
  type: SnowflakeJoinAttribute.Left,
  condition: "costCenter1.COST_CENTER_ID = costCenter2.COST_CENTER_ID")]
public class Example
{
  [SnowflakeColumn(name: "VALUE")]
  public decimal Value { get; set; }

  [SnowflakeColumn(name: "ADDRESS", table: "COST_CENTER")]
  public string Address { get; set; }

  [SnowflakeColumn(name: "ADDRESS", columnAlias: "ADDRESS2", table: "COST_CENTER", tableAlias: "costCenter2")] // Table and column alias must be provided
  public string AddressMapped { get; set; }
}

You can also reuse a snowflake database connection:

using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Snowflake.Data.Client;
using Snowflake.Data.Xt;

namespace SnowflakeApplication;

public class ExamplesRepository : IExamplesRepository
{
  public async Task<Owner> GetByAddressAsync(string address, CancellationToken cancellationToken = default)
  {
    // Setup of the snowflake database connection
    using var snowflakeDbConnection = new SnowflakeDbConnection
    {
      ConnectionString = EnvironmentExtensions.GetSnowflakeConnectionString(),
    };

    // If you do not open the connection here, the first command which will run, will open it for you
    await snowflakeDbConnection
      .OpenAsync(cancellationToken)
      .ConfigureAwait(false);

    var example = new SnowflakeDbCommand<Example>(snowflakeDbConnection) // Provide the snowflake database connection in the constructor
      .Where(item => item.Address == address)
      .FirstOrDefaultAsync(cancellationToken)
      .ConfigureAwait(false);

    var owner = new SnowflakeDbCommand<Owner>(snowflakeDbConnection) // Provide the snowflake database connection in the constructor and it will be reused
      .Where(item => item.Name == example.OwnerName)
      .FirstOrDefaultAsync(cancellationToken)
      .ConfigureAwait(false);

    // Do not forget to close the connection
    await snowflakeDbConnection
      .CloseAsync(cancellationToken)
      .ConfigureAwait(false);

    return owner;
  }
}

Modifier

You can add multiple modifiers to you command:

Methods

You can query for a single entry or a list - sync or async:

Environment Variables

License

Snowflake.Data.Xt is distributed under the MIT license. See LICENSE for details.

MIT License

Copyright (c) 2023-2024 Jonas Schubert

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.