How to query Snowflake data in C#

Question

Hi there,

How to query Snowflake data in C# ?

My code:

public static async Task<DataTable> ExecuteSnowflakeQuery(string connectionString, string query)
{
    DataTable dt = new DataTable();
    try
    {
        using (var connection = new SnowflakeDbConnection())
        {
            connection.ConnectionString = connectionString;
            connection.Open();

            using (IDbCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "USE WAREHOUSE WAREHOUSE";
                cmd.ExecuteNonQuery();
                cmd.CommandText = query;   // sql opertion fetching 
                                           //data from an existing table
                IDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                { 
                    dt.Load(reader);
                }
                connection.Close();

            }
        }
    }
    catch (Exception exc)
    {

        Console.WriteLine("Error Message: {0}", exc.Message);
    }
    return dt;
}

I want to return the results of the snowflake query as a List, any suggestions?

Thanks&Regards, zmsoft

Answer 1

@zmsoft ,

Thanks for reaching out. Regarding your query on how to return the results of a Snowflake query as a List in C#, could you please change the code like below:

First, define a custom class to represent the data:

public class SnowflakeData
{
    public int Column1 { get; set; }
    public string Column2 { get; set; }
    // Add properties for other columns as needed
}

Then, modify your method to return a List<SnowflakeData>:

public static async Task<List<SnowflakeData>> ExecuteSnowflakeQuery(string connectionString, string query)
{
    List<SnowflakeData> resultList = new List<SnowflakeData>();
    try
    {
        using (var connection = new SnowflakeDbConnection())
        {
            connection.ConnectionString = connectionString;
            connection.Open();

            using (IDbCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "USE WAREHOUSE WAREHOUSE";
                cmd.ExecuteNonQuery();
                cmd.CommandText = query;   // sql operation fetching 
                                           //data from an existing table
                IDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    SnowflakeData data = new SnowflakeData
                    {
                        Column1 = reader.GetInt32(0),
                        Column2 = reader.GetString(1)
                        // Map other columns as needed
                    };
                    resultList.Add(data);
                }
                connection.Close();
            }
        }
    }
    catch (Exception exc)
    {
        Console.WriteLine("Error Message: {0}", exc.Message);
    }
    return resultList;
}

In this example, SnowflakeData is a custom class with properties that match the columns of your query result. The ExecuteSnowflakeQuery method reads the data from the IDataReader and populates a list of SnowflakeData objects, which is then returned.

Feel free to adjust the properties and data types in the SnowflakeData class to match your actual query results. If you have any further questions or need additional assistance, please don't hesitate to reach out.