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);
}
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);
}
}
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());
}
}
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);
}
}
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);
}
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);
}
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);
}
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();
}
}
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();
}
}
public async Task OpenAsync()
{
await _conn.OpenAsync();
}
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);
}
}
