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);
}
}
/// <summary>
/// Ensures that the database specified in the connection string exists.
/// </summary>
/// <param name="supported">Fluent helper type.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="logger">The <see cref="DbUp.Engine.Output.IUpgradeLog"/> used to record actions.</param>
/// <param name="timeout">Use this to set the command time out for creating a database in case you're encountering a time out in this operation.</param>
/// <param name="collation">The collation name to set during database creation</param>
/// <returns></returns>
public static void SnowflakeDatabase(
this SupportedDatabasesForEnsureDatabase supported,
string connectionString,
IUpgradeLog logger,
int timeout = -1,
string collation = null)
{
using (var connection = new SnowflakeDbConnection())
{
connection.ConnectionString = connectionString;
try
{
connection.Open();
}
catch (SnowflakeDbException)
{
//// Failed to connect to master, lets try direct
//if (DatabaseExistsIfConnectedToDirectly(logger, connectionString, connection.Database))
// return;
throw;
}
var sqlCommandText = $@"create database IF NOT EXISTS ""{connection.Database}"";";
// Create the database...
using (var command = new SnowflakeDbCommand()
{
CommandText = sqlCommandText,
Connection = connection,
CommandType = CommandType.Text
})
{
if (timeout >= 0)
{
command.CommandTimeout = timeout;
}
command.ExecuteNonQuery();
}
logger.WriteInformation(@"Created database {0}", connection.Database);
}
}
public async Task <string> DdlAsync(Connection parmList, string commandText, string client)
{
var sb = new StringBuilder();
if (commandText.ToLower() == "show shares")
{
var tenant = await GetReaderAsync(client);
sb.AppendLine(tenant.Locator);
}
else
{
try
{
_connection.ConnectionString = BuildConnectionString(parmList);
using (DbCommand cmd = new SnowflakeDbCommand())
{
await _connection.OpenAsync();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandTimeout = 0;
cmd.CommandText = commandText;
cmd.Connection = _connection;
using (var res = await cmd.ExecuteReaderAsync())
{
while (res.Read())
{
sb.AppendLine(res.GetString(0));
}
}
}
_connection.Close();
}
catch (Exception ex)
{
throw ex;
}
}
return(sb.ToString());
}
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 <string> CreateReaderAsync(Connection parmList, string commandText, string clientName)
{
SnowflakeTenant tenant = new SnowflakeTenant();
_connection.ConnectionString = BuildConnectionString(parmList);
await _connection.OpenAsync();
using (DbCommand cmd = new SnowflakeDbCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandTimeout = 0;
cmd.CommandText = commandText;
cmd.Connection = _connection;
try
{
_ = await cmd.ExecuteScalarAsync();
System.Threading.Thread.Sleep(2000);
}
catch (Exception ex)
{
throw ex;
}
}
_connection.Close();
try
{
var t = await GetReaderAsync(clientName);
}
catch (Exception ex)
{
throw ex;
}
return(Tenant.Locator);
}
public void Run(
IDbConnection connection,
IDbTransaction transaction,
string fileFullPath,
string delimiter = null,
int?batchSize = null,
int?commandTimeout = null,
List <KeyValuePair <string, string> > tokens = null
)
{
//get file name segments from potentially sequenceno.schemaname.tablename filename pattern
var fileName = Path.GetFileNameWithoutExtension(fileFullPath)
.ReplaceTokens(_traceService, tokens);
var fileNameSegments = fileName.SplitBulkFileName(defaultSchema: "PUBLIC");
var schemaName = fileNameSegments.Item2;
var tableName = fileNameSegments.Item3;
var stopwatch = new Stopwatch();
stopwatch.Start();
_traceService.Info($"SnowflakeImportService: Started copying data into destination table {schemaName}.{tableName}");
//read csv file and load into data table
var sqlStatement = PrepareMultiRowInsertStatement(schemaName, tableName, fileFullPath, delimiter);
var statementCorrelationId = Guid.NewGuid().ToString().Fixed();
_traceService.Debug($"Executing statement {statementCorrelationId}: {Environment.NewLine}{sqlStatement}");
using (var cmd = new SnowflakeDbCommand())
{
cmd.Connection = connection as SnowflakeDbConnection;
cmd.Transaction = transaction as SnowflakeDbTransaction;
cmd.CommandText = sqlStatement;
cmd.ExecuteNonQuery();
}
stopwatch.Stop();
_traceService?.Debug($"Statement {statementCorrelationId} executed in {stopwatch.ElapsedMilliseconds} ms");
_traceService.Info($"SnowflakeImportService: Finished copying data into destination table {schemaName}.{tableName} in {stopwatch.ElapsedMilliseconds} ms");
}
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);
}
}
public Command()
{
_cmd = new SnowflakeDbCommand();
}
public Command(string commandText, IConnection conn)
{
_cmd = new SnowflakeDbCommand();
_cmd.CommandText = commandText;
_cmd.Connection = (SnowflakeDbConnection)conn.GetConnection();
}
public Command(string commandText)
{
_cmd = new SnowflakeDbCommand();
_cmd.CommandText = commandText;
}
