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

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "create or replace stage emptyStage";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "create or replace table testCopy (cola string)";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "copy into testCopy from @emptyStage";
                int updateCount = cmd.ExecuteNonQuery();
                Assert.AreEqual(0, updateCount);

                // test rows_loaded exists
                cmd.CommandText = "copy into @%testcopy from (select 'test_string')";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "copy into testcopy";
                updateCount     = cmd.ExecuteNonQuery();
                Assert.AreEqual(1, updateCount);

                // clean up
                cmd.CommandText = "drop stage emptyStage";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "drop table testCopy";
                cmd.ExecuteNonQuery();

                conn.Close();
            }
        }
        public void TestRetrieveSemiStructuredData()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "create or replace table testsemi(cola variant, colb array, colc object) " +
                                  "as select '[\"1\", \"2\"]', '[\"1\", \"2\"]', '{\"key\": \"value\"}'";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "select * from testsemi";
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    Assert.AreEqual(true, reader.Read());
                    Assert.AreEqual("[\n  \"1\",\n  \"2\"\n]", reader.GetString(0));
                    Assert.AreEqual("[\n  \"1\",\n  \"2\"\n]", reader.GetString(1));
                    Assert.AreEqual("{\n  \"key\": \"value\"\n}", reader.GetString(2));
                }

                conn.Close();
            }
        }
Example #3
0
        public async Task <DataTable> GetTableColumns(IDictionary <string, object> config, string tableName)
        {
            try
            {
                using (var conn = new SnowflakeDbConnection())
                {
                    string connectionString = string.Format("scheme=https;ACCOUNT={0};HOST={1};port={2};ROLE={3};WAREHOUSE={4};USER={5};PASSWORD={6};DB={7};SCHEMA={8}", (string)config[SnowflakeConstants.KeyName.Account], (string)config[SnowflakeConstants.KeyName.Host], (string)config[SnowflakeConstants.KeyName.PortNumber], (string)config[SnowflakeConstants.KeyName.Role], (string)config[SnowflakeConstants.KeyName.Warehouse], (string)config[SnowflakeConstants.KeyName.Username], (string)config[SnowflakeConstants.KeyName.Password], (string)config[SnowflakeConstants.KeyName.DatabaseName], (string)config[SnowflakeConstants.KeyName.Schema]);
                    conn.ConnectionString = connectionString;

                    conn.Open();
                    var cmd = conn.CreateCommand();
                    cmd.CommandText = "select * from " + tableName;
                    var reader    = cmd.ExecuteReader();
                    var dataTable = new DataTable();
                    dataTable.Load(reader);
                    conn.Close();
                    return(await Task.FromResult(dataTable));
                }
            }
            catch (Exception)
            {
                return(new DataTable());
            }
        }
        public void testDataSourceError()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = connectionString;

                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from table_not_exists";
                try
                {
                    IDataReader reader = cmd.ExecuteReader();
                    Assert.Fail();
                }
                catch (SnowflakeDbException e)
                {
                    Assert.AreEqual(2003, e.ErrorCode);
                    Assert.AreNotEqual("", e.queryId);
                }

                conn.Close();
            }
        }
Example #5
0
        public void testReadOutNullVal()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();

                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select null";
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        reader.Read();
                        object nullVal = reader.GetValue(0);
                        Assert.IsNull(nullVal);
                        Assert.IsTrue(reader.IsDBNull(0));

                        reader.Close();
                    }
                }

                conn.Close();
            }
        }
        static void Main(string[] args)
        {
            // string connectionString = "Host=jo69808.us-central1.gcp.snowflakecomputing; ROLE=sysadmin;WAREHOUSE=John; USER=johns; PASSWORD=Water123;DB=JOHN;SCHEMA=EM";
            string connectionString = "scheme=https;host=jo69808.us-central1.gcp.snowflakecomputing.com;ROLE=sysadmin;WAREHOUSE=John;user=johns;password=Water123;DB=JOHN;account=jo69808.us-central1.gcp.*;";


            //Scenario 1. Get the list of employee from Snowflake View which returns Json data  ///Read The data using snow flake
            using (var conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = "select * from testtable1;";
                var     reader = cmd.ExecuteReader();
                dynamic employeeList;
                while (reader.Read())
                {
                    Console.WriteLine(reader.GetString(0));
                    employeeList = reader.GetString(0);
                }
                conn.Close();
                Insert();
            }
        }
Example #7
0
        public void TestSimpleCommand()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString;

                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select 1";

                // command type can only be text, stored procedure are not supported.
                Assert.AreEqual(CommandType.Text, cmd.CommandType);
                try
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    Assert.Fail();
                }
                catch (SnowflakeDbException e)
                {
                    Assert.AreEqual(270009, e.ErrorCode);
                }

                Assert.AreEqual(UpdateRowSource.None, cmd.UpdatedRowSource);
                try
                {
                    cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
                    Assert.Fail();
                }
                catch (SnowflakeDbException e)
                {
                    Assert.AreEqual(270009, e.ErrorCode);
                }

                Assert.AreSame(conn, cmd.Connection);
                try
                {
                    cmd.Connection = null;
                    Assert.Fail();
                }
                catch (SnowflakeDbException e)
                {
                    Assert.AreEqual(270009, e.ErrorCode);
                }

                Assert.IsFalse(((SnowflakeDbCommand)cmd).DesignTimeVisible);
                try
                {
                    ((SnowflakeDbCommand)cmd).DesignTimeVisible = true;
                    Assert.Fail();
                }
                catch (SnowflakeDbException e)
                {
                    Assert.AreEqual(270009, e.ErrorCode);
                }

                object val = cmd.ExecuteScalar();
                Assert.AreEqual(1L, (long)val);

                conn.Close();
            }
        }
        public void testGetNumber()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString;
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "create or replace table testGetNumber(cola number)";
                int count = cmd.ExecuteNonQuery();
                Assert.AreEqual(0, count);

                int   numInt   = 10000;
                long  numLong  = 1000000L;
                short numShort = 10;

                string insertCommand = "insert into testgetnumber values (?),(?),(?)";
                cmd.CommandText = insertCommand;

                var p1 = cmd.CreateParameter();
                p1.ParameterName = "1";
                p1.Value         = numInt;
                p1.DbType        = DbType.Int32;
                cmd.Parameters.Add(p1);

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

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

                count = cmd.ExecuteNonQuery();
                Assert.AreEqual(3, count);

                cmd.CommandText = "select * from testgetnumber";
                IDataReader reader = cmd.ExecuteReader();

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(numInt, reader.GetInt32(0));

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(numLong, reader.GetInt64(0));

                Assert.IsTrue(reader.Read());
                Assert.AreEqual(numShort, reader.GetInt16(0));

                Assert.IsFalse(reader.Read());
                reader.Close();

                cmd.CommandText = "drop table if exists testgetnumber";
                count           = cmd.ExecuteNonQuery();
                Assert.AreEqual(0, count);

                conn.Close();
            }
        }
        public void TestResultSetMetadata()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "create or replace table meta(c1 number(20, 4), c2 string(100), " +
                                  "c3 double, c4 timestamp_ntz, c5 variant not null, c6 boolean) ";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "select * from meta";
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    var dataTable = reader.GetSchemaTable();
                    dataTable.DefaultView.Sort = SchemaTableColumn.ColumnName;
                    dataTable = dataTable.DefaultView.ToTable();

                    DataRow row = dataTable.Rows[0];
                    Assert.AreEqual("C1", row[SchemaTableColumn.ColumnName]);
                    Assert.AreEqual(0, row[SchemaTableColumn.ColumnOrdinal]);
                    Assert.AreEqual(20, row[SchemaTableColumn.NumericPrecision]);
                    Assert.AreEqual(4, row[SchemaTableColumn.NumericScale]);
                    Assert.AreEqual(SFDataType.FIXED, (SFDataType)row[SchemaTableColumn.ProviderType]);
                    Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]);

                    row = dataTable.Rows[1];
                    Assert.AreEqual("C2", row[SchemaTableColumn.ColumnName]);
                    Assert.AreEqual(1, row[SchemaTableColumn.ColumnOrdinal]);
                    Assert.AreEqual(100, row[SchemaTableColumn.ColumnSize]);
                    Assert.AreEqual(SFDataType.TEXT, (SFDataType)row[SchemaTableColumn.ProviderType]);
                    Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]);

                    row = dataTable.Rows[2];
                    Assert.AreEqual("C3", row[SchemaTableColumn.ColumnName]);
                    Assert.AreEqual(2, row[SchemaTableColumn.ColumnOrdinal]);
                    Assert.AreEqual(SFDataType.REAL, (SFDataType)row[SchemaTableColumn.ProviderType]);
                    Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]);

                    row = dataTable.Rows[3];
                    Assert.AreEqual("C4", row[SchemaTableColumn.ColumnName]);
                    Assert.AreEqual(3, row[SchemaTableColumn.ColumnOrdinal]);
                    Assert.AreEqual(0, row[SchemaTableColumn.NumericPrecision]);
                    Assert.AreEqual(9, row[SchemaTableColumn.NumericScale]);
                    Assert.AreEqual(SFDataType.TIMESTAMP_NTZ, (SFDataType)row[SchemaTableColumn.ProviderType]);
                    Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]);

                    row = dataTable.Rows[4];
                    Assert.AreEqual("C5", row[SchemaTableColumn.ColumnName]);
                    Assert.AreEqual(4, row[SchemaTableColumn.ColumnOrdinal]);
                    Assert.AreEqual(SFDataType.VARIANT, (SFDataType)row[SchemaTableColumn.ProviderType]);
                    Assert.AreEqual(false, row[SchemaTableColumn.AllowDBNull]);

                    row = dataTable.Rows[5];
                    Assert.AreEqual("C6", row[SchemaTableColumn.ColumnName]);
                    Assert.AreEqual(5, row[SchemaTableColumn.ColumnOrdinal]);
                    Assert.AreEqual(SFDataType.BOOLEAN, (SFDataType)row[SchemaTableColumn.ProviderType]);
                    Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]);
                }

                conn.Close();
            }
        }
Example #10
0
        public DynamicDataRow[] QuerySnowFlake(string query, string host, string account, string username, string password, string database, string schema, string warehouse, string role)

        {
            try

            {
                // Defined for use as the output handler.

                DataTable outputTable = new DataTable();

                List <DynamicDataRow> dynamicRows = new List <DynamicDataRow>();



                using (IDbConnection conn = new SnowflakeDbConnection())

                {
                    if (host.StartsWith("http", StringComparison.OrdinalIgnoreCase))

                    {
                        host = host.Split(':').Last();

                        host = host.Replace("//", string.Empty);
                    }

                    // Create the connection string and connect to Snowflake instance.

                    conn.ConnectionString = "host=" + host + ";account=" + account + ";role=" + role + ";user="******";password="******";db=" + database + ";schema=" + schema + ";warehouse=" + warehouse;

                    conn.Open();



                    IDbCommand cmd = conn.CreateCommand();

                    cmd.CommandText = query;

                    IDataReader reader = cmd.ExecuteReader();



                    // Get the column names in the result set.

                    DataTable schemaTable = reader.GetSchemaTable();



                    // Dynamically generate the outputTable using the columns from above.

                    foreach (DataRow row in schemaTable.Rows)

                    {
                        outputTable.Columns.Add(row["ColumnName"].ToString(), Type.GetType(row["DataType"].ToString()));
                    }



                    int tableColumns = schemaTable.Rows.Count;



                    // Read all resultset rows into outputTable.

                    while (reader.Read())

                    {
                        DataRow row = outputTable.NewRow();



                        for (int i = 0; i < tableColumns; i++)

                        {
                            row[i] = reader[i];
                        }



                        outputTable.Rows.Add(row);

                        dynamicRows.Add(new DynamicDataRow(row));
                    }

                    conn.Close();
                }



                // Create the output result set back to Decisions.

                //Dictionary<string, object> resultData = new Dictionary<string, object>();

                //resultData.Add("Result", LDR.ToArray());

                return(dynamicRows.ToArray());
            }



            catch (Exception ex2)

            {
                throw;
            }
        }
        public void Run(ConfigurationDataset configBoard)
        {
            try
            {
                var geckoboardClient = new GeckoboardClient(configBoard.GeckoBoadApiKey);
                var canConnect       = geckoboardClient.Ping();

                var client = geckoboardClient.Datasets();
                try
                {
                    client.Delete(configBoard.DataSetName);
                    Console.WriteLine($"Deleted {configBoard.DataSetName} successfully : {DateTime.Now}");
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    var original = Console.BackgroundColor;
                    Console.BackgroundColor = ConsoleColor.Red;
                    Console.WriteLine(e);
                    Console.BackgroundColor = original;
                }

                var cleanBoard = new GeckoDataset()
                {
                    Fields   = configBoard.Fields,
                    UniqueBy = configBoard.UniqueBy,
                };
                try
                {
                    string jsonFields1 = cleanBoard.JSon();
                    var    dataset     = client.FindOrCreateByJson(configBoard.DataSetName, jsonFields1);
                    Console.WriteLine($"created {configBoard.DataSetName} successfully : {DateTime.Now}");
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    var original = Console.BackgroundColor;
                    Console.BackgroundColor = ConsoleColor.Red;
                    Console.WriteLine(e);
                    Console.BackgroundColor = original;
                }

                var objupd = new GeckoDataset()
                {
                    Data = new List <Dictionary <string, object> >()
                };

                using (IDbConnection conn = new SnowflakeDbConnection())
                {
                    conn.ConnectionString = configBoard.SnowflakeConnection;

                    conn.Open();

                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = configBoard.DataStatement;
                    IDataReader reader   = cmd.ExecuteReader();
                    var         dbObject = new ExpandoObject();
                    var         cols     = reader.GetSchemaTable().Rows;
                    for (int i = 0; i < cols.Count; i++)
                    {
                        object v = cols[i][0];
                        ((IDictionary <string, object>)dbObject).Add(v.ToString(), v);
                    }
                    List <dynamic> personList = new List <dynamic>();
                    var            allSales   = reader.DataReaderMapToList(dbObject);
                    conn.Close();

                    foreach (ExpandoObject saleOrder in allSales)
                    {
                        Dictionary <string, object> item = new Dictionary <string, object>()
                        {
                        };

                        foreach (var cleanBoardField in cleanBoard.Fields)
                        {
                            var entry = saleOrder.SingleOrDefault(p => p.Key.ToUpper().Equals(cleanBoardField.Value.Name,
                                                                                              StringComparison.InvariantCultureIgnoreCase));
                            var colValue   = entry.Value;
                            var hasNoValue = ReferenceEquals(colValue, null) || colValue == DBNull.Value;
                            if (hasNoValue)
                            {
                                if (cleanBoardField.Value.Type == DatasetFieldType.datetime || cleanBoardField.Value.Type == DatasetFieldType.date)
                                {
                                    if (cleanBoardField.Value.DefaultValue == "now")
                                    {
                                        item.Add(cleanBoardField.Value.Name.ToLower(), DateTime.Now);
                                    }
                                    else
                                    {
                                        item.Add(cleanBoardField.Value.Name.ToLower(), DateTime.Parse(cleanBoardField.Value.DefaultValue));
                                    }
                                }
                                else
                                {
                                    item.Add(cleanBoardField.Value.Name.ToLower(), "");
                                }
                            }
                            else
                            {
                                item.Add(cleanBoardField.Value.Name.ToLower(), colValue);
                            }
                        }
                        objupd.Data.Add(item);
                    }
                }
                // Act
                var jsonFields = objupd.JSon();
                var result     = client.UpdateDataset(jsonFields, configBoard.DataSetName);
                Console.WriteLine($"uploaded successfully: {configBoard.DataSetName} on: {DateTime.Now}");
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                var original = Console.BackgroundColor;
                Console.BackgroundColor = ConsoleColor.Red;
                Console.WriteLine(e);
                Console.BackgroundColor = original;
            }
        }
        public string QuerySnowFlake(string query, string host, string account, string username, string password, string database, string schema, string warehouse, string role)

        {
            try

            {
                // Defined for use as the output handler.

                DataTable outputTable = new DataTable();

                List <DynamicDataRow> dynamicRows = new List <DynamicDataRow>();



                using (IDbConnection conn = new SnowflakeDbConnection())

                {
                    if (host.StartsWith("http", StringComparison.OrdinalIgnoreCase))

                    {
                        host = host.Split(':').Last();

                        host = host.Replace("//", string.Empty);
                    }

                    // Create the connection string and connect to Snowflake instance.

                    conn.ConnectionString = "host=" + host + ";account=" + account + ";role=" + role + ";user="******";password="******";db=" + database + ";schema=" + schema + ";warehouse=" + warehouse;

                    conn.Open();



                    IDbCommand cmd = conn.CreateCommand();

                    cmd.CommandText = query;

                    IDataReader reader = cmd.ExecuteReader();


                    rows = ConvertToDictionary(reader);



                    conn.Close();
                }



                // Create the output result set back to Decisions.

                //Dictionary<string, object> resultData = new Dictionary<string, object>();

                //resultData.Add("Result", LDR.ToArray());

                return(JsonConvert.SerializeObject(rows));
            }



            catch (Exception ex2)

            {
                throw;
            }
        }