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 void TestSelectStatement() { DataSet ds = new DataSet("ds"); using (SnowflakeDbConnection conn = new SnowflakeDbConnection()) { conn.ConnectionString = connectionString; conn.Open(); IDbDataAdapter adaptor = new SnowflakeDbDataAdapter("select 1 as col1, 2 AS col2", conn); adaptor.Fill(ds); conn.Close(); } Assert.AreEqual(ds.Tables[0].Rows[0]["col1"].ToString(), "1"); Assert.AreEqual(ds.Tables[0].Rows[0]["col2"].ToString(), "2"); }
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); }
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); }
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); } }