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 <List <List <object> > > QueryAsLists(string query, params DbParameter[] parameters)
{
using (SnowflakeDbConnection connection = new SnowflakeDbConnection())
{
connection.ConnectionString = ConnectionString;
connection.Open();
SnowflakeDbCommand command = new SnowflakeDbCommand(connection);
command.CommandText = query;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
using (var reader = command.ExecuteReader())
{
List <List <object> > results = new List <List <object> >();
int index = 0;
while (await reader.ReadAsync())
{
if (index == 0)
{
List <object> headers = new List <object>();
List <object> types = new List <object>();
for (int i = 0; i < reader.FieldCount; i++)
{
headers.Add(reader.GetName(i));
types.Add(reader.GetFieldType(i));
}
results.Add(headers);
results.Add(types);
}
List <object> item = new List <object>();
for (int i = 0; i < reader.FieldCount; i++)
{
item.Add(reader.GetValue(i));
}
results.Add(item);
index++;
}
return(results);
}
}
}
public async Task <List <T> > Query <T>(string query, params DbParameter[] parameters) where T : class, new()
{
using (SnowflakeDbConnection connection = new SnowflakeDbConnection())
{
connection.ConnectionString = ConnectionString;
connection.Open();
SnowflakeDbCommand command = new SnowflakeDbCommand(connection);
command.CommandText = query;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
using (var reader = command.ExecuteReader())
{
var type = typeof(T);
var properties = type.GetProperties()
.ToDictionary(p => p.Name.ToLower(), p => p);
List <T> results = new List <T>();
while (await reader.ReadAsync())
{
T item = new T();
for (int i = 0; i < reader.FieldCount; i++)
{
string columnName = reader.GetName(i).ToLower();
if (!properties.ContainsKey(columnName))
{
continue;
}
var property = properties[columnName];
var valueType = reader.GetFieldType(i);
if (valueType != property.PropertyType)
{
throw new Exception($"Query Deserialization Error: Column '{reader.GetName(i)}' type '{valueType.FullName}' does now match PropertyType '{property.PropertyType.FullName}' of '{type.FullName}->{property.Name}'");
}
if (valueType == typeof(string))
{
property.SetValue(item, await reader.GetFieldValueAsync <string>(i), emptyArguments);
}
else if (valueType == typeof(int))
{
property.SetValue(item, await reader.GetFieldValueAsync <int>(i), emptyArguments);
}
else if (valueType == typeof(uint))
{
property.SetValue(item, await reader.GetFieldValueAsync <uint>(i), emptyArguments);
}
else if (valueType == typeof(long))
{
property.SetValue(item, await reader.GetFieldValueAsync <long>(i), emptyArguments);
}
else if (valueType == typeof(ulong))
{
property.SetValue(item, await reader.GetFieldValueAsync <ulong>(i), emptyArguments);
}
else if (valueType == typeof(bool))
{
property.SetValue(item, await reader.GetFieldValueAsync <bool>(i), emptyArguments);
}
else if (valueType == typeof(char))
{
property.SetValue(item, await reader.GetFieldValueAsync <char>(i), emptyArguments);
}
else if (valueType == typeof(DateTime))
{
property.SetValue(item, await reader.GetFieldValueAsync <DateTime>(i), emptyArguments);
}
else if (valueType == typeof(byte[]))
{
property.SetValue(item, await reader.GetFieldValueAsync <byte[]>(i), emptyArguments);
}
else if (valueType == typeof(char[]))
{
property.SetValue(item, await reader.GetFieldValueAsync <char[]>(i), emptyArguments);
}
}
results.Add(item);
}
return(results);
}
}
}
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);
}
}
