コード例 #1
0
        public async Task <SnowflakeTenant> GetReaderAsync(string clientName)
        {
            SnowflakeTenant t = new SnowflakeTenant();

            //authenticator=https://<your_okta_account_name>.okta.com

            if (_connection.State == System.Data.ConnectionState.Open)
            {
                _connection.Close();
            }
            await _connection.OpenAsync();

            var x = await _connection.QueryAsync <Share>($"show managed accounts like '{clientName}_READER'", null, null, 10, System.Data.CommandType.Text);

            var share = x.Where(i => i.name.StartsWith(clientName)).First();

            t.Locator = share.locator;
            t.OvcId   = share.comment.Trim();
            t.Name    = clientName;
            _connection.Close();

            Tenant = t;

            return(t);
        }
コード例 #2
0
        public void TestAsyncConnectionFailFast()
        {
            using (var conn = new SnowflakeDbConnection())
            {
                string invalidConnectionString = "host=invalidaccount.snowflakecomputing.com;"
                                                 + "connection_timeout=0;account=invalidaccount;user=snowman;password=test;";

                conn.ConnectionString = invalidConnectionString;

                Assert.AreEqual(conn.State, ConnectionState.Closed);
                CancellationTokenSource connectionCancelToken = new CancellationTokenSource();
                Task connectTask = null;
                try
                {
                    connectTask = conn.OpenAsync(connectionCancelToken.Token);

                    connectTask.Wait();
                    Assert.Fail();
                }
                catch (AggregateException e)
                {
                    Assert.AreEqual(SFError.INTERNAL_ERROR.GetAttribute <SFErrorAttr>().errorCode,
                                    ((SnowflakeDbException)e.InnerException).ErrorCode);
                }

                Assert.AreEqual(ConnectionState.Closed, conn.State);
                Assert.IsTrue(connectTask.IsFaulted);
            }
        }
コード例 #3
0
        public async Task <DataTable> GetTables(IDictionary <string, object> config, string name = null)
        {
            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;

                    await conn.OpenAsync();

                    var cmd = conn.CreateCommand();
                    cmd.CommandText = "select * from " + name;
                    var reader = await cmd.ExecuteReaderAsync();

                    var dataTable = new DataTable();
                    dataTable.Load(reader);
                    await conn.CloseAsync();

                    return(dataTable);
                }
            }
            catch (Exception)
            {
                return(new DataTable());
            }
        }
コード例 #4
0
        public void TestAsyncConnectionFailFast()
        {
            using (var conn = new SnowflakeDbConnection())
            {
                // Just a way to get a 404 on the login request and make sure there are no retry
                string invalidConnectionString = "host=docs.microsoft.com;"
                                                 + "connection_timeout=0;account=testFailFast;user=testFailFast;password=testFailFast;";

                conn.ConnectionString = invalidConnectionString;

                Assert.AreEqual(conn.State, ConnectionState.Closed);
                CancellationTokenSource connectionCancelToken = new CancellationTokenSource();
                Task connectTask = null;
                try
                {
                    connectTask = conn.OpenAsync(connectionCancelToken.Token);
                    connectTask.Wait();
                    Assert.Fail();
                }
                catch (AggregateException e)
                {
                    Assert.AreEqual(SFError.INTERNAL_ERROR.GetAttribute <SFErrorAttr>().errorCode,
                                    ((SnowflakeDbException)e.InnerException).ErrorCode);
                }

                Assert.AreEqual(ConnectionState.Closed, conn.State);
                Assert.IsTrue(connectTask.IsFaulted);
            }
        }
コード例 #5
0
        public static async Task <SnowflakeDbConnection> OpenConnection(this SnowflakeCfg cfg)
        {
            var conn = new SnowflakeDbConnection {
                ConnectionString = cfg.ConnectionStirng(), Password = cfg.Creds.SecureString()
            };
            await conn.OpenAsync();

            return(conn);
        }
コード例 #6
0
        public async ValueTask <IDbConnection> OpenConnection(string connectionString, CancellationToken cancellationToken)
        {
            connectionString.NotNull();

            _logger.Information("Opening new SQL DB connection");

            var connection = new SnowflakeDbConnection()
            {
                ConnectionString = connectionString
            };
            await connection.OpenAsync(cancellationToken).ConfigureAwait(false);

            return(connection);
        }
コード例 #7
0
        private static async Task <SnowflakeDbConnection> CreateSnowflakeConnection(string cs)
        {
            SnowflakeDbConnection.HttpClientHandlerDelegate = client =>
            {
                // Verify no certificates have been revoked
                client.CheckCertificateRevocationList = false;
                return(client);
            };

            var snowflakeConnection = new SnowflakeDbConnection()
            {
                ConnectionString = cs
            };

            await snowflakeConnection.OpenAsync();

            return(snowflakeConnection);
        }
コード例 #8
0
        public async Task ExecuteCommandAsync(IConnectorConnection config, string commandText, IList <SqlParameter> param = null)
        {
            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.Authentication[SnowflakeConstants.KeyName.Account], (string)config.Authentication[SnowflakeConstants.KeyName.Host], (string)config.Authentication[SnowflakeConstants.KeyName.PortNumber], (string)config.Authentication[SnowflakeConstants.KeyName.Role], (string)config.Authentication[SnowflakeConstants.KeyName.Warehouse], (string)config.Authentication[SnowflakeConstants.KeyName.Username], (string)config.Authentication[SnowflakeConstants.KeyName.Password], (string)config.Authentication[SnowflakeConstants.KeyName.DatabaseName], (string)config.Authentication[SnowflakeConstants.KeyName.Schema]);

                conn.ConnectionString = connectionString;
                await conn.OpenAsync();

                var cmd = conn.CreateCommand();

                cmd.CommandText = commandText;

                if (param != null)
                {
                    cmd.Parameters.AddRange(param.ToArray());
                }

                await cmd.ExecuteNonQueryAsync();

                await conn.CloseAsync();
            }
        }
コード例 #9
0
        public void TestExecAsyncAPI()
        {
            using (DbConnection conn = new SnowflakeDbConnection())
            {
                conn.ConnectionString = ConnectionString;

                Task connectTask = conn.OpenAsync(CancellationToken.None);
                Assert.AreEqual(ConnectionState.Connecting, conn.State);

                connectTask.Wait();
                Assert.AreEqual(ConnectionState.Open, conn.State);

                using (DbCommand cmd = conn.CreateCommand())
                {
                    int queryResult = 0;
                    cmd.CommandText = "select count(seq4()) from table(generator(timelimit => 3)) v";
                    Task <DbDataReader> execution = cmd.ExecuteReaderAsync();
                    Task readCallback             = execution.ContinueWith((t) =>
                    {
                        using (DbDataReader reader = t.Result)
                        {
                            Assert.IsTrue(reader.Read());
                            queryResult = reader.GetInt32(0);
                            Assert.IsFalse(reader.Read());
                        }
                    });
                    // query is not finished yet, result is still 0;
                    Assert.AreEqual(0, queryResult);
                    // block till query finished
                    readCallback.Wait();
                    // queryResult should be updated by callback
                    Assert.AreNotEqual(0, queryResult);
                }

                conn.Close();
            }
        }
コード例 #10
0
 public async Task OpenAsync()
 {
     await _conn.OpenAsync();
 }
コード例 #11
0
        public static async Task <object> RunQuery(string SFC, string TSQL)
        {
            string MyResult = "";
            SnowflakeDbConnection myConnection  = new SnowflakeDbConnection();
            SnowflakeDbConnection myConnection2 = new SnowflakeDbConnection();

            DateTime baseDate = new DateTime(2020, 1, 1);
            TimeSpan diff     = DateTime.Now - baseDate;


            string TAG = " /* ADF" + diff.TotalMilliseconds.ToString() + "*/;";


            if (TSQL.IndexOf(";") > 0)
            {
                TSQL = TSQL.Replace(";", TAG);
            }
            else
            {
                TSQL = TSQL + TAG;
            }



            myConnection.ConnectionString  = SFC;
            myConnection2.ConnectionString = SFC;
            try
            {
                if (myConnection.IsOpen() == false)
                {
                    await myConnection.OpenAsync();
                }
                SnowflakeDbCommand myCommandmaster = new SnowflakeDbCommand(myConnection);

                myCommandmaster.CommandText = TSQL;
                SnowflakeDbDataAdapter MasterSQLDataAdapter;
                MasterSQLDataAdapter = new SnowflakeDbDataAdapter(myCommandmaster);

                int TryCount = 6;
                try
                {
                    try
                    {
                        _ = myCommandmaster.ExecuteNonQueryAsync();
                    }
                    catch (Exception ex)
                    {
                        string b = ex.Message.ToString();
                    }



                    await myConnection2.OpenAsync();

                    SnowflakeDbCommand myCommandmaster2 = new SnowflakeDbCommand(myConnection2);
                    myCommandmaster2.CommandText = "select QUERY_ID as QID,  EXECUTION_STATUS as STATUS, ERROR_MESSAGE from table(information_schema.query_history()) WHERE  QUERY_TEXT LIKE '%" + TAG + "' ORDER BY START_TIME DESC LIMIT 1;";

                    StringBuilder SB = new StringBuilder();

                    Thread.Sleep(5000);
                    DbDataReader reader = myCommandmaster2.ExecuteReader();

                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    int RecCount = dt.Rows.Count;



                    if (RecCount == 0)
                    {
                        for (int i = 0; i < TryCount; i++)
                        {
                            Thread.Sleep(10000);
                            reader = myCommandmaster2.ExecuteReader();

                            dt.Load(reader);
                            RecCount = dt.Rows.Count;


                            if (RecCount > 0)
                            {
                                goto checkQID;
                            }
                            else
                            {
                                if (i == TryCount - 1)
                                {
                                    throw new System.InvalidOperationException("Can't Find the QueryID in the Query Log tagged:" + TAG);
                                }
                            }
                        }
                    }

checkQID:

                    reader   = myCommandmaster2.ExecuteReader();
                    MyResult = WriteReaderToJSON(SB, reader);


                    JObject MyError1 = JObject.Parse(MyResult);


                    MyResult = (string)MyError1["Rows"][0]["QID"];


                    reader.Close();

                    //---- CLOSING CANCELS THE QUERY
                    //      myConnection2.Close();
                    //      myConnection.Close();


                    return(MyResult);
                }
                catch (Exception ex)
                {
                    MyResult = ex.Message.ToString();
                    return(MyResult);
                }
            }
            catch (Exception ex)
            {
                MyResult = ex.Message.ToString();
                return(MyResult);
            }
        }