Snowflake Connector for .NET

The Snowflake .NET connector supports most core functionality. Currently, the PUT and GET commands are not supported. All other query types are supported.

Library target is under .NET Framework 4.6 and .NET Standard 2.0.

Building the Package

Prerequisites

This project is developed under Visual Studio 2017. All other versions of Visual Studio are not supported.

Steps

1. Check out the source code from GitHub:

git clone [email protected]:snowflakedb/snowflake-connector-net snowflake-connector-net

2. Pull down the dependency:

cd snowflake-connector-net
nuget restore

3. Build the solution file

msbuild snowflake-connector-net.sln /p:Configuration=Release

Installing the Package

Package ID for Snowflake Connector for .Net is Snowflake.Data.

Packages can be directly downloaded from nuget.org.

It can also be downloaded using Visual Studio UI (Tools > NuGet Package Manager > Manage NuGet Packages for Solution and search for "Snowflake.Data")

Alternatively, packages can also be downloaded using Package Manager Console:

PM> Install-Package Snowflake.Data

Testing the Connector

Before running tests, create a parameters.json file under Snowflake.Data.Tests\ directory. In this file, specify username, password and account info that tests will run against. Here is a sample parameters.json file

{
  "testconnection": {
    "SNOWFLAKE_TEST_USER": "snowman",
    "SNOWFLAKE_TEST_PASSWORD": "XXXXXXX",
    "SNOWFLAKE_TEST_ACCOUNT": "TESTACCOUNT",
    "SNOWFLAKE_TEST_WAREHOUSE": "TESTWH",
    "SNOWFLAKE_TEST_DATABASE": "TESTDB",
    "SNOWFLAKE_TEST_SCHEMA": "TESTSCHEMA",
    "SNOWFLAKE_TEST_ROLE": "TESTROLE"
  }
}

Command Prompt

The build solution file builds the connector and tests binaries. Issue the following command from the command line to run the tests. The test binary is located in the Debug directory if you built the solution file in Debug mode.

cd Snowflake.Data.Tests
dotnet test -f netcoreapp2.0

Tests can also be run under code coverage:

OpenCover.4.6.519\tools\OpenCover.Console.exe -target:"dotnet.exe" -returntargetcode -targetargs:"test -f netcoreapp2.0" -register:user -filter:"+[Snowflake.Data]*" -output:"netcoreapp2.0_coverage.xml" -oldStyle 

Visual Studio 2017

Tests can also be run under Visual Studio 2017. Open the solution file in Visual Studio 2017 and run tests using Test Explorer.

Usage

Create a Connection

To connect to Snowflake, specify a valid connection string composed of key-value pairs separated by semicolons, i.e "<key1>=<value1>;<key2>=<value2>...". The following table lists all valid connection properties:

Connection Property	Required	Comment
ACCOUNT	Yes
DB	No
HOST	No	If no value specified, driver will use <ACCOUNT>.snowflakecomputing.com
PASSWORD	Yes
ROLE	No
SCHEMA	No
USER	Yes
WAREHOUSE	No
CONNECTION_TIMEOUT	No	Total timeout in seconds when connecting to Snowflake. Default to 120 seconds

Sample code to open a connection to Snowflake:

using (IDbConnection conn = new SnowflakeDbConnection())
{
    conn.ConnectionString = "account=testaccount;user=testuser;password=XXXXX;db=testdb;schema=testschema";

    conn.Open();
    
    conn.Close();
}

Run a Query and Read Data

using (IDbConnection conn = new SnowflakeDbConnection())
{
    conn.ConnectionString = connectionString;
    conn.Open();

    IDbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "select * from t";
    IDataReader reader = cmd.ExecuteReader();
                
    while(reader.Read())
    {
        Console.WriteLine(reader.GetString(0));
    }
    
    conn.Close();
}

Bind Parameter

using (IDbConnection conn = new SnowflakeDbConnection())
{
    conn.ConnectionString = connectionString;
    conn.Open();

    IDbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "insert into t values (?),(?),(?)";
    IDataReader reader = cmd.ExecuteReader();
                  
    var p1 = cmd.CreateParameter();
    p1.ParameterName = "1";
    p1.Value = 10;
    p1.DbType = DbType.Int32;
    cmd.Parameters.Add(p1);

    var p2 = cmd.CreateParameter();
    p2.ParameterName = "2";
    p2.Value = 10000L;
    p2.DbType = DbType.Int32;
    cmd.Parameters.Add(p2);

    var p3 = cmd.CreateParameter();
    p3.ParameterName = "3";
    p3.Value = (short)1;
    p3.DbType = DbType.Int16;
    cmd.Parameters.Add(p3);

    var count = cmd.ExecuteNonQuery();
    Assert.AreEqual(3, count);             
    
    conn.Close();
}

Logging

The Snowflake Connector for .NET uses log4net as the logging framework.

Here is a sample app.config file that uses log4net

  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
  </configSections>
  
  <log4net>
    <appender name="MyRollingFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="snowflake_dotnet.log" />
      <appendToFile value="true"/>
      <rollingStyle value="Size" />
      <maximumFileSize value="10MB" />
      <staticLogFileName value="true" />
      <maxSizeRollBackups value="10" />
      <layout type="log4net.Layout.PatternLayout">
        <!-- <header value="[DateTime]  [Thread]  [Level]  [ClassName] Message&#13;&#10;" /> -->
        <conversionPattern value="[%date] [%t] [%-5level] [%logger] %message%newline" />
      </layout>
    </appender>

    <root>
      <level value="ALL" />
      <appender-ref ref="MyRollingFileAppender" />
    </root>
  </log4net>