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); } }