Example #1
0
        public void TestCrlCheckSwitchConnection()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString + ";INSECUREMODE=true";
                conn.Open();
                Assert.AreEqual(ConnectionState.Open, conn.State);
            }

            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString;
                conn.Open();
                Assert.AreEqual(ConnectionState.Open, conn.State);
            }

            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString + ";INSECUREMODE=false";
                conn.Open();
                Assert.AreEqual(ConnectionState.Open, conn.State);
            }

            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString;
                conn.Open();
                Assert.AreEqual(ConnectionState.Open, conn.State);
            }

            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString + ";INSECUREMODE=false";
                conn.Open();
                Assert.AreEqual(ConnectionState.Open, conn.State);
            }

            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString + ";INSECUREMODE=true";
                conn.Open();
                Assert.AreEqual(ConnectionState.Open, conn.State);
            }
        }
    /// <summary>
    /// Ensures that the database specified in the connection string exists.
    /// </summary>
    /// <param name="supported">Fluent helper type.</param>
    /// <param name="connectionString">The connection string.</param>
    /// <param name="logger">The <see cref="DbUp.Engine.Output.IUpgradeLog"/> used to record actions.</param>
    /// <param name="timeout">Use this to set the command time out for creating a database in case you're encountering a time out in this operation.</param>
    /// <param name="collation">The collation name to set during database creation</param>
    /// <returns></returns>
    public static void SnowflakeDatabase(
        this SupportedDatabasesForEnsureDatabase supported,
        string connectionString,
        IUpgradeLog logger,
        int timeout      = -1,
        string collation = null)
    {
        using (var connection = new SnowflakeDbConnection())
        {
            connection.ConnectionString = connectionString;
            try
            {
                connection.Open();
            }
            catch (SnowflakeDbException)
            {
                //// Failed to connect to master, lets try direct
                //if (DatabaseExistsIfConnectedToDirectly(logger, connectionString, connection.Database))
                //    return;

                throw;
            }

            var sqlCommandText = $@"create database IF NOT EXISTS ""{connection.Database}"";";

            // Create the database...
            using (var command = new SnowflakeDbCommand()
            {
                CommandText = sqlCommandText,
                Connection = connection,
                CommandType = CommandType.Text
            })
            {
                if (timeout >= 0)
                {
                    command.CommandTimeout = timeout;
                }

                command.ExecuteNonQuery();
            }

            logger.WriteInformation(@"Created database {0}", connection.Database);
        }
    }
 public void TestValidOAuthExpiredTokenConnection()
 {
     try
     {
         using (var conn = new SnowflakeDbConnection())
         {
             conn.ConnectionString
                 = ConnectionStringWithoutAuth
                   + ";authenticator=oauth;token=ETMsDgAAAXcmdv0gABRBRVMvQ0JDL1BLQ1M1UGFkZGluZwCAABAAECPcGqa/QJd3BMw5z2V/Fn8AAABQdCCJgbhZpzzIrh2j/ej8rXZBODsPIwM6oODfWZ3a2PNP91PdMadOoUh5NjWanGfUQdZNkVFLzh6BJdAT5XaaQdTiszkqtOao9QaWhtarKVoAFAQ+KiE/CavTBhURVKjXmSfe7k6N";
             conn.Open();
             Assert.Fail();
         }
     }
     catch (SnowflakeDbException e)
     {
         // Token is expired
         Assert.AreEqual(390318, e.ErrorCode);
     }
 }
Example #4
0
            override public int RunCommand()
            {
                using (var conn = new SnowflakeDbConnection())
                {
                    conn.ConnectionString = configuration.GetConnectionString("SnowFlake");

                    conn.Open();
                    var cmd          = conn.CreateCommand();
                    var CommandText  = "SELECT * from DB_APPS.PUBLIC.BOOKINGS as b ORDER BY b.CREATED_AT DESC LIMIT 10";
                    var reservations = conn.Query <Reservation>(CommandText);

                    foreach (var reservation in reservations)
                    {
                        console.WriteLine("= " + reservation.Id);
                    }
                }

                return(0);
            }
Example #5
0
        public async Task <SnowflakeDbConnection> GetConnection(IDictionary <string, object> config)
        {
            try
            {
                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]);

                var result = new SnowflakeDbConnection();

                result.ConnectionString = connectionString;

                result.Open();

                return(await Task.FromResult(result));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public void TestConnectWithDifferentRole()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                var host = testConfig.host;
                if (string.IsNullOrEmpty(host))
                {
                    host = $"{testConfig.account}.snowflakecomputing.com";
                }

                string connStrFmt = "scheme={0};host={1};port={2};" +
                                    "user={3};password={4};account={5};role=public;db=snowflake_sample_data;schema=information_schema;warehouse=WH_NOT_EXISTED";

                conn.ConnectionString = string.Format(
                    connStrFmt,
                    testConfig.protocol,
                    testConfig.host,
                    testConfig.port,
                    testConfig.user,
                    testConfig.password,
                    testConfig.account
                    );
                conn.Open();
                Assert.AreEqual(conn.State, ConnectionState.Open);

                using (IDbCommand command = conn.CreateCommand())
                {
                    command.CommandText = "select current_role()";
                    Assert.AreEqual(command.ExecuteScalar().ToString(), "PUBLIC");

                    command.CommandText = "select current_database()";
                    CollectionAssert.Contains(new [] { "SNOWFLAKE_SAMPLE_DATA", "" }, command.ExecuteScalar().ToString());

                    command.CommandText = "select current_schema()";
                    CollectionAssert.Contains(new [] { "INFORMATION_SCHEMA", "" }, command.ExecuteScalar().ToString());

                    command.CommandText = "select current_warehouse()";
                    // Command will return empty string if the hardcoded warehouse does not exist.
                    Assert.AreEqual("", command.ExecuteScalar().ToString());
                }
                conn.Close();
            }
        }
        public void testGetTimestampLTZ()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();

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

                DateTimeOffset now = DateTimeOffset.Now;

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

                var p1 = (SnowflakeDbParameter)cmd.CreateParameter();
                p1.ParameterName = "1";
                p1.Value         = now;
                p1.DbType        = DbType.DateTimeOffset;
                p1.SFDataType    = Core.SFDataType.TIMESTAMP_LTZ;
                cmd.Parameters.Add(p1);

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

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

                Assert.IsTrue(reader.Read());
                DateTimeOffset dtOffset = (DateTimeOffset)reader.GetValue(0);
                reader.Close();

                Assert.AreEqual(0, DateTimeOffset.Compare(now, dtOffset));

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

                conn.Close();
            }
        }
Example #8
0
        /// <summary>
        /// Executes a sequence of Snowflake statements that are not expected to return a result set.
        /// </summary>
        /// <param name="statements">The query statements to execute.</param>
        /// <returns>The number of rows affected. When more than one statement is supplied the method will return the number of rows affected for the last statement only.</returns>
        public int ExecuteNonQuery(params string[] statements)
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = this.connectionString;
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();

                int affectedRows = 0;
                foreach (string command in statements)
                {
                    cmd.CommandText = command;
                    affectedRows    = cmd.ExecuteNonQuery();
                }

                return(affectedRows);
            }
        }
        public void TestSwitchDb()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString;

                Assert.AreEqual(conn.State, ConnectionState.Closed);

                conn.Open();

                Assert.AreEqual(testConfig.database.ToUpper(), conn.Database);
                Assert.AreEqual(conn.State, ConnectionState.Open);

                conn.ChangeDatabase("SNOWFLAKE_SAMPLE_DATA");
                Assert.AreEqual("SNOWFLAKE_SAMPLE_DATA", conn.Database);

                conn.Close();
            }
        }
        public override void LoadUnitTestData(DataContainer dataContainer)
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = dataContainer.ConnectionString;
                conn.Open();

                using (var reader = new StreamReader("tests/data.sql"))
                {
                    var sb = new StringBuilder();
                    while (!reader.EndOfStream)
                    {
                        var line = reader.ReadLine();
                        if (String.IsNullOrEmpty(line))
                        {
                            continue;
                        }

                        if (line.Equals("quit;", StringComparison.InvariantCultureIgnoreCase) || line.Equals("/"))
                        {
                            continue;
                        }

                        sb.AppendLine(line);
                        if (line.TrimEnd().EndsWith(";"))
                        {
                            using (var cmd = conn.CreateCommand())
                            {
                                Console.WriteLine(sb.ToString());

                                cmd.CommandTimeout = 600;
                                cmd.CommandText    = sb.ToString().TrimEnd(new[] { ' ', '\n', '\r', '\t', ';' });

                                cmd.ExecuteNonQuery();
                            }

                            sb.Clear();
                        }
                    }
                }
            }
        }
Example #11
0
 public async Task <List <List <object> > > QueryAsLists(string query, params DbParameter[] parameters)
 {
     using (SnowflakeDbConnection connection = new SnowflakeDbConnection())
     {
         connection.ConnectionString = ConnectionString;
         connection.Open();
         SnowflakeDbCommand command = new SnowflakeDbCommand(connection);
         command.CommandText = query;
         foreach (var parameter in parameters)
         {
             command.Parameters.Add(parameter);
         }
         using (var reader = command.ExecuteReader())
         {
             List <List <object> > results = new List <List <object> >();
             int index = 0;
             while (await reader.ReadAsync())
             {
                 if (index == 0)
                 {
                     List <object> headers = new List <object>();
                     List <object> types   = new List <object>();
                     for (int i = 0; i < reader.FieldCount; i++)
                     {
                         headers.Add(reader.GetName(i));
                         types.Add(reader.GetFieldType(i));
                     }
                     results.Add(headers);
                     results.Add(types);
                 }
                 List <object> item = new List <object>();
                 for (int i = 0; i < reader.FieldCount; i++)
                 {
                     item.Add(reader.GetValue(i));
                 }
                 results.Add(item);
                 index++;
             }
             return(results);
         }
     }
 }
Example #12
0
        /// <summary>
        /// Run SQL commands in Snowflake and return a JSON object with column/value pairs from first row of the result.
        /// See https://github.com/snowflakedb/snowflake-connector-net for more details.
        /// </summary>
        /// <param name="log">ILogger object</param>
        /// <param name="snowflakeConnectionString">Snowflake connection string</param>
        /// <param name="sqlCommands">The SQL commands to execute</param>
        /// <returns>JSON object with column/value pairs from first row of the result</returns>
        private static JObject runSnowflakeSqlCommands(ILogger log, string snowflakeConnectionString, string[] sqlCommands)
        {
            var output = new JObject();

            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                // Connect to Snowflake
                conn.ConnectionString = snowflakeConnectionString;
                conn.Open();

                using (IDbCommand cmd = conn.CreateCommand())
                {
                    // Run every query except the last one using ExecuteNonQuery()
                    for (int i = 0; i < sqlCommands.Length - 1; i++)
                    {
                        cmd.CommandText = sqlCommands[i].Trim();
                        log.LogInformation($"Running SQL command #{i}: {cmd.CommandText}");
                        cmd.ExecuteNonQuery();
                    }

                    // Finally run the last query using ExecuteReader() so we can collect the output
                    cmd.CommandText = sqlCommands[sqlCommands.Length - 1].Trim();
                    log.LogInformation($"Running final SQL command: {cmd.CommandText}");
                    IDataReader reader = cmd.ExecuteReader();

                    // The result should be a table with one row and n columns, format the column/value pairs in JSON
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            var columnName = reader.GetName(i);
                            var value      = reader[i].ToString();
                            output.Add(columnName, value);
                        }
                    }
                }

                conn.Close();
            }

            return(output);
        }
Example #13
0
        public static void Insert()
        {
            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.*;";

            using (var conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = "insert into testtable1 values('hello','snowflake'); ";// while calling procedure
                var     reader = cmd.ExecuteReader();
                dynamic resultData;
                while (reader.Read())
                {
                    Console.WriteLine(reader.GetString(0));
                    resultData = reader.GetString(0);
                }
                conn.Close();
            }
        }
Example #14
0
 public void TestInValidOAuthTokenConnection()
 {
     try
     {
         using (var conn = new SnowflakeDbConnection())
         {
             conn.ConnectionString
                 = ConnectionStringWithoutAuth
                   + ";authenticator=oauth;token=notAValidOAuthToken";
             conn.Open();
             Assert.AreEqual(ConnectionState.Open, conn.State);
             Assert.Fail();
         }
     }
     catch (SnowflakeDbException e)
     {
         // Invalid OAuth access token
         Assert.AreEqual(390303, e.ErrorCode);
     }
 }
Example #15
0
        public void TestSimpleLargeResultSet()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString;

                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select seq4(), uniform(1, 10, 42) from table(generator(rowcount => 1000000)) v order by 1";
                IDataReader reader  = cmd.ExecuteReader();
                int         counter = 0;
                while (reader.Read())
                {
                    Assert.AreEqual(counter.ToString(), reader.GetString(0));
                    counter++;
                }
                conn.Close();
            }
        }
        public void TestBasicConnection()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();
                Assert.AreEqual(ConnectionState.Open, conn.State);

                Assert.AreEqual(0, conn.ConnectionTimeout);
                // Data source is empty string for now
                Assert.AreEqual("", ((SnowflakeDbConnection)conn).DataSource);

                string   serverVersion   = ((SnowflakeDbConnection)conn).ServerVersion;
                string[] versionElements = serverVersion.Split('.');
                Assert.AreEqual(3, versionElements.Length);

                conn.Close();
                Assert.AreEqual(ConnectionState.Closed, conn.State);
            }
        }
Example #17
0
        public List <Employee> GetData(string id)
        {
            List <Employee> EmployeeData = new List <Employee>();
            // string connectionString1 = "scheme=https;host=jo69808.us-central1.gcp.snowflakecomputing.com;ROLE=sysadmin;WAREHOUSE=John;user=johns;password=Water123;DB=JOHN;account=jo69808.us-central1.gcp.*;";
            DataTable             dt  = new DataTable();
            SnowflakeDbConnection con = new SnowflakeDbConnection();

            con.ConnectionString = connectionString;
            con.Open();
            SnowflakeDbDataAdapter adapt = new SnowflakeDbDataAdapter("CALL Employee_Select('" + id + "')", con);

            adapt.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                string data     = dt.Rows[0]["Employee_Select"].ToString();
                string Jsondata = data.Replace('\n'.ToString(), "");
                EmployeeData = JsonConvert.DeserializeObject <List <Employee> >(Jsondata);
            }
            return(EmployeeData);
        }
        private void testGetDateAndOrTime(string inputTimeStr, int?precision, SFDataType dataType)
        {
            // Can't use DateTime object as test case, must parse.
            DateTime inputTime;

            if (inputTimeStr == null)
            {
                inputTime = dataType == SFDataType.DATE ? DateTime.Today : DateTime.Now;
            }
            else
            {
                inputTime = DateTime.ParseExact(inputTimeStr, "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture);
            }

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

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = $"create or replace table testGetDateAndOrTime(cola {dataType}{ (precision == null ? string.Empty : $"({precision})" )});";
Example #19
0
        public List <Employee> ShowData()
        {
            List <Employee>       EmployeeData = new List <Employee>();
            DataTable             dt           = new DataTable();
            SnowflakeDbConnection con          = new SnowflakeDbConnection();

            con.ConnectionString = connectionString;
            con.Open();
            SnowflakeDbDataAdapter adapt = new SnowflakeDbDataAdapter("CALL Employee_Grid()", con);

            adapt.Fill(dt);


            if (dt.Rows.Count > 0)
            {
                string data = dt.Rows[0]["Employee_Grid"].ToString();

                EmployeeData = JsonConvert.DeserializeObject <List <Employee> >(data);
            }
            return(EmployeeData);
        }
Example #20
0
        public void TestCancelQuery()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString;

                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select count(seq4()) from table(generator(timelimit => 20)) v";
                Task executionThread = Task.Run(() =>
                {
                    try
                    {
                        cmd.ExecuteScalar();
                        Assert.Fail();
                    }
                    catch (SnowflakeDbException e)
                    {
                        Assert.AreEqual(e.ErrorCode, 604);
                    }
                });

                Thread.Sleep(8000);
                cmd.Cancel();

                try
                {
                    executionThread.Wait();
                }
                catch (AggregateException e)
                {
                    Assert.AreEqual(
                        "System.Threading.Tasks.TaskCanceledException",
                        e.InnerException.GetType().ToString());
                }

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

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

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

                byte[] testBytes = Encoding.UTF8.GetBytes("TEST_GET_BINARAY");

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

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

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

                Assert.IsTrue(reader.Read());
                Assert.IsTrue(testBytes.SequenceEqual((byte[])reader.GetValue(0)));
                reader.Close();

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

                conn.Close();
            }
        }
Example #22
0
 public void TestValidOAuthExpiredTokenConnection()
 {
     try
     {
         using (var conn = new SnowflakeDbConnection())
         {
             conn.ConnectionString
                 = ConnectionStringWithoutAuth
                   + String.Format(
                       ";authenticator=oauth;token={0}",
                       testConfig.expOauthToken);
             conn.Open();
             Assert.Fail();
         }
     }
     catch (SnowflakeDbException e)
     {
         Console.Write(e);
         // Token is expired
         Assert.AreEqual(390318, e.ErrorCode);
     }
 }
Example #23
0
 public void TestJwtConnectionWithWrongUser()
 {
     try
     {
         using (var conn = new SnowflakeDbConnection())
         {
             conn.ConnectionString
                 = ConnectionStringWithoutAuth
                   + String.Format(
                       ";authenticator=snowflake_jwt;user={0};private_key_file={1}",
                       "WrongUser",
                       testConfig.pemFilePath);
             conn.Open();
             Assert.Fail();
         }
     }
     catch (SnowflakeDbException e)
     {
         // Jwt token is invalid
         Assert.AreEqual(390144, e.ErrorCode);
     }
 }
Example #24
0
 public void TestJwtEncryptedPkFileNoPwdConnection()
 {
     try
     {
         using (var conn = new SnowflakeDbConnection())
         {
             conn.ConnectionString
                 = ConnectionStringWithoutAuth
                   + String.Format(
                       ";authenticator=snowflake_jwt;user={0};private_key_file={1}",
                       testConfig.jwtAuthUser,
                       testConfig.pwdProtectedPrivateKeyFilePath);
             conn.Open();
             Assert.Fail();
         }
     }
     catch (SnowflakeDbException e)
     {
         // Invalid password (none provided) for decrypting the private key
         Assert.AreEqual(270052, e.ErrorCode);
     }
 }
        public void testLoginTimeout()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                string invalidConnectionString = "host=invalidaccount.snowflakecomputing.com;connection_timeout=5;"
                                                 + "account=invalidaccount;user=snowman;password=test;";

                conn.ConnectionString = invalidConnectionString;

                Assert.AreEqual(conn.State, ConnectionState.Closed);
                try
                {
                    conn.Open();
                    Assert.Fail();
                }
                catch (AggregateException e)
                {
                    Assert.AreEqual(270007, ((SnowflakeDbException)e.InnerException).ErrorCode);
                }
                Assert.AreEqual(5, conn.ConnectionTimeout);
            }
        }
Example #26
0
        public void TestUseV2ChunkDownloader()
        {
            SFConfiguration.Instance().UseV2ChunkDownloader = true;

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

                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select seq4(), uniform(1, 10, 42) from table(generator(rowcount => 200000)) v order by 1";
                IDataReader reader  = cmd.ExecuteReader();
                int         counter = 0;
                while (reader.Read())
                {
                    Assert.AreEqual(counter.ToString(), reader.GetString(0));
                    counter++;
                }
                conn.Close();
            }
        }
        public void testGetBoolean()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = connectionString;
                conn.Open();

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

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

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

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

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

                Assert.IsTrue(reader.Read());
                Assert.IsTrue(reader.GetBoolean(0));
                reader.Close();

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

                conn.Close();
            }
        }
        public override (List <DataCollection>, List <DataEntity>) GetSchema(DataContainer container)
        {
            var collections = new List <DataCollection>();
            var entities    = new List <DataEntity>();

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

                var cmd = conn.CreateCommand();
                cmd.CommandText =
                    $"SELECT table_schema, table_name, ordinal_position, column_name, data_type FROM information_schema.columns order by table_schema, table_name, ordinal_position";

                DataCollection coll   = null;
                var            reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    var schema     = reader.GetString(0);
                    var tableName  = reader.GetString(1);
                    var columnName = reader.GetString(3);
                    var dataType   = reader.GetString(4);

                    if (coll == null || !coll.Name.Equals(tableName) || !coll.Schema.Equals(schema))
                    {
                        coll = new DataCollection(container, tableName)
                        {
                            Schema = schema
                        };
                        collections.Add(coll);
                    }

                    entities.Add(new DataEntity(columnName, ConvertDataType(dataType), dataType, container, coll));
                }
            }

            return(collections, entities);
        }
Example #29
0
        private void DeleteHDSButton_Click(object sender, EventArgs e)
        {
            using (IDbConnection snowConn = new SnowflakeDbConnection())
            {
                // Open the connection
                snowConn.ConnectionString = new Connection().SnowConnectInfo;
                snowConn.Open();

                // Declare the command and transactions which will be used throughout the entire batch job.
                IDbCommand     cmd = snowConn.CreateCommand();
                IDbTransaction transaction;

                // Start the transaction
                transaction = snowConn.BeginTransaction();

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction
                cmd.Connection  = snowConn;
                cmd.Transaction = transaction;

                try
                {
                    MessageBox.Show(DateTime.Now.ToString());
                    cmd.CommandText = "INSERT INTO Log.Date " +
                                      "VALUES (" + DateTime.Now.ToString() + ") ";
                    cmd.ExecuteReader();

                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    transaction.Rollback();
                }

                snowConn.Close();
            }
        }
Example #30
0
        public void testArrayBind()
        {
            using (IDbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString;
                conn.Open();

                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "create or replace table testArrayBind(cola integer, colb string)";
                    int count = cmd.ExecuteNonQuery();
                    Assert.AreEqual(0, count);

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

                    var p1 = cmd.CreateParameter();
                    p1.ParameterName = "1";
                    p1.DbType        = DbType.Int16;
                    p1.Value         = new int[] { 1, 2, 3 };
                    cmd.Parameters.Add(p1);

                    var p2 = cmd.CreateParameter();
                    p2.ParameterName = "2";
                    p2.DbType        = DbType.String;
                    p2.Value         = new string[] { "str1", "str2", "str3" };
                    cmd.Parameters.Add(p2);

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

                    cmd.CommandText = "drop table if exists testArrayBind";
                    cmd.ExecuteNonQuery();
                }

                conn.Close();
            }
        }