public static object CheckQuery(string SFC, string QueryID)
{
string MyResult = "";
var MyError1 = new object();
string TSQL = String.Format(" select * from table(information_schema.query_history()) WHERE QUERY_ID ='{0}';", QueryID);
SnowflakeDbConnection myConnection = new SnowflakeDbConnection();
myConnection.ConnectionString = SFC;
try
{
SnowflakeDbCommand myCommandmaster = new SnowflakeDbCommand(myConnection);
if (myConnection.IsOpen() == false)
{
myConnection.Open();
}
myCommandmaster = new SnowflakeDbCommand(myConnection);
myCommandmaster.CommandText = TSQL;
SnowflakeDbDataAdapter MasterSQLDataAdapter;
MasterSQLDataAdapter = new SnowflakeDbDataAdapter(myCommandmaster);
try
{
DbDataReader reader = myCommandmaster.ExecuteReader();
StringBuilder SB = new StringBuilder();
// MyResult = WriteReaderToJSON( SB, reader);
MyResult = WriteReaderToJSON(SB, reader);
MyError1 = JObject.Parse(MyResult);
reader.Close();
return(MyError1);//new AcceptedResult();
// return MyError1;
}
catch (Exception ex)
{
MyResult = @"{
""status"": ""Error"",
""result"": ""{0}""
}";
MyResult = MyResult.Replace("{0}", ex.Message.ToString());
MyError1 = JObject.Parse(MyResult);
return(MyError1);
}
}
catch (Exception ex)
{
MyResult = @"{ ""Status"":""Error"", ""Result"": ""{0}"" } ";
String.Format(MyResult, ex.Message.ToString());
MyError1 = JObject.Parse(MyResult);
return(MyError1);
}
}
public async Task <bool> PingAsync()
{
return(_conn.IsOpen());
}
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);
}
}