// Test that when a transaction is disposed, rollback would be sent out
public void TestTransactionDispose()
{
var conn = new SnowflakeDbConnection();
try
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand command = conn.CreateCommand();
command.CommandText = "create or replace table testTransactionDispose(c int)";
command.ExecuteNonQuery();
using (IDbTransaction t1 = conn.BeginTransaction())
{
IDbCommand t1c1 = conn.CreateCommand();
t1c1.Transaction = t1;
t1c1.CommandText = "insert into testTransactionDispose values (1)";
t1c1.ExecuteNonQuery();
}
// Transaction t1 would be disposed and rollback at this point, tuple inserted is not visible
IDbCommand c2 = conn.CreateCommand();
c2.CommandText = "SELECT * FROM testTransactionDispose";
IDataReader reader2 = c2.ExecuteReader();
Assert.IsFalse(reader2.Read());
}
finally
{
IDbCommand command = conn.CreateCommand();
command.CommandText = "DROP TABLE IF EXISTS testTransactionDispose";
command.ExecuteNonQuery();
conn.Close();
}
}
public void TestConnectionDispose()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
// Setup
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand command = conn.CreateCommand();
command.CommandText = "create or replace table testConnDispose(c int)";
command.ExecuteNonQuery();
IDbTransaction t1 = conn.BeginTransaction();
IDbCommand t1c1 = conn.CreateCommand();
t1c1.Transaction = t1;
t1c1.CommandText = "insert into testConnDispose values (1)";
t1c1.ExecuteNonQuery();
}
using (IDbConnection conn = new SnowflakeDbConnection())
{
// Previous connection would be disposed and
// uncommitted txn would rollback at this point
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand command = conn.CreateCommand();
command.CommandText = "SELECT * FROM testConnDispose";
IDataReader reader = command.ExecuteReader();
Assert.IsFalse(reader.Read());
// Cleanup
command.CommandText = "DROP TABLE IF EXISTS testConnDispose";
command.ExecuteNonQuery();
}
}
public void TestConnectWithDifferentRole()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
String connStrFmt = "account={0};user={1};password={2};role=public;db=snowflake_sample_data;schema=information_schema;warehouse=shige_wh";
conn.ConnectionString = String.Format(connStrFmt, testConfig.account,
testConfig.user, testConfig.password);
conn.Open();
Assert.AreEqual(conn.State, ConnectionState.Open);
using (IDbCommand command = conn.CreateCommand())
{
command.CommandText = "select current_role()";
Assert.AreEqual(command.ExecuteScalar().ToString(), "PUBLIC");
command.CommandText = "select current_database()";
Assert.AreEqual(command.ExecuteScalar().ToString(), "SNOWFLAKE_SAMPLE_DATA");
command.CommandText = "select current_schema()";
Assert.AreEqual(command.ExecuteScalar().ToString(), "INFORMATION_SCHEMA");
command.CommandText = "select current_warehouse()";
Assert.AreEqual(command.ExecuteScalar().ToString(), "SHIGE_WH");
}
conn.Close();
}
}
public void CDC() // Put in arguments from form selection as parameters
{
using (IDbConnection snowConn = new SnowflakeDbConnection())
{
// Open the connection
snowConn.ConnectionString = new Connection().SnowConnectInfo;
snowConn.Open();
// Declare the command and transactions which will be used throughout the entire batch job.
IDbCommand cmd = snowConn.CreateCommand();
IDbTransaction transaction;
// Start the transaction
transaction = snowConn.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
cmd.Connection = snowConn;
cmd.Transaction = transaction;
SnowLog.LoggingInfo loginfo = new SnowLog.LoggingInfo("", "", "", "", "", "", 0);
try
{
var op = new CdcSQL();
op.SetJobstart(cmd);
logger.SetLogStart(cmd);
loginfo.Step = "Step 1";
loginfo.StepRowsAffected = op.HdsDelete(cmd, "Customer");
logger.SuccessLog(snowConn, loginfo);
logger.SetLogStart(cmd);
loginfo.Step = "Step 2";
loginfo.StepRowsAffected = op.HdsUpdate(cmd, "Customer");
logger.SuccessLog(snowConn, loginfo);
logger.SetLogStart(cmd);
loginfo.Step = "Step 3";
loginfo.StepRowsAffected = op.HdsAdd(cmd, "Customer");
logger.SuccessLog(snowConn, loginfo);
MessageBox.Show("Add");
op.TruncateLanding(cmd, "Customer");
transaction.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
loginfo.StepRowsAffected = 0;
logger.FailLog(snowConn, loginfo);
transaction.Rollback();
}
snowConn.Close();
}
}
public void testRecordsAffected()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace table testRecordsAffected(cola number)";
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
string insertCommand = "insert into testRecordsAffected values (1),(1),(1)";
cmd.CommandText = insertCommand;
IDataReader reader = cmd.ExecuteReader();
Assert.AreEqual(3, reader.RecordsAffected);
// Reader's RecordsAffected should be available even if the reader is closed
reader.Close();
Assert.AreEqual(3, reader.RecordsAffected);
cmd.CommandText = "drop table if exists testRecordsAffected";
count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
// Reader's RecordsAffected should be available even if the connection is closed
conn.Close();
Assert.AreEqual(3, reader.RecordsAffected);
}
}
public void TestQueryTimeout()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
// timelimit = 17min
cmd.CommandText = "select count(seq4()) from table(generator(timelimit => 1020)) v";
// timeout = 16min - Using a timeout > default Rest timeout of 15min
cmd.CommandTimeout = 16 * 60;
try
{
Stopwatch stopwatch = Stopwatch.StartNew();
cmd.ExecuteScalar();
stopwatch.Stop();
//Should timeout before the query time limit of 17min
Assert.Less(stopwatch.ElapsedMilliseconds, 17 * 60 * 1000);
// Should timeout after the defined query timeout of 16min
Assert.GreaterOrEqual(stopwatch.ElapsedMilliseconds, 16 * 60 * 1000);
Assert.Fail();
}
catch (SnowflakeDbException e)
{
// 604 is error code from server meaning query has been cancelled
Assert.AreEqual(e.ErrorCode, 604);
}
conn.Close();
}
}
public void TestCancelQuery()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select count(seq4()) from table(generator(timelimit => 20)) v";
Task executionThread = Task.Run(() =>
{
try
{
cmd.ExecuteScalar();
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(e.ErrorCode, 604);
}
});
Thread.Sleep(8000);
cmd.Cancel();
executionThread.Wait();
conn.Close();
}
}
public void TestQueryTimeout()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select count(seq4()) from table(generator(timelimit => 20)) v";
cmd.CommandTimeout = 10;
try
{
cmd.ExecuteScalar();
Assert.Fail();
}
catch (SnowflakeDbException e)
{
// 604 is error code from server meaning query has been cancelled
Assert.AreEqual(e.ErrorCode, 604);
}
conn.Close();
}
}
public void testBasicDataReader()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select 1 as colone, 2 as coltwo";
using (IDataReader reader = cmd.ExecuteReader())
{
Assert.AreEqual(2, reader.FieldCount);
Assert.AreEqual(0, reader.Depth);
Assert.IsTrue(((SnowflakeDbDataReader)reader).HasRows);
Assert.IsFalse(reader.IsClosed);
Assert.AreEqual("COLONE", reader.GetName(0));
Assert.AreEqual("COLTWO", reader.GetName(1));
Assert.AreEqual(typeof(long), reader.GetFieldType(0));
Assert.AreEqual(typeof(long), reader.GetFieldType(1));
Assert.IsFalse(reader.NextResult());
Assert.AreEqual(-1, reader.RecordsAffected);
Assert.AreEqual(0, reader.GetOrdinal("COLONE"));
// reapet calling to test if cache in memory worked or not
Assert.AreEqual(0, reader.GetOrdinal("COLONE"));
Assert.AreEqual(0, reader.GetOrdinal("COLONE"));
Assert.AreEqual(1, reader.GetOrdinal("COLTWO"));
Assert.AreEqual(-1, reader.GetOrdinal("COL_NOT_EXISTS"));
reader.Close();
Assert.IsTrue(reader.IsClosed);
try
{
reader.Read();
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270010, e.ErrorCode);
}
try
{
reader.GetInt16(0);
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270010, e.ErrorCode);
}
}
}
conn.Close();
}
}
public void testReadOutNullVal()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "create or replace table testnull(a integer, b string)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into testnull values(null, null)";
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from testnull";
using (IDataReader reader = cmd.ExecuteReader())
{
reader.Read();
object nullVal = reader.GetValue(0);
Assert.AreEqual(DBNull.Value, nullVal);
Assert.IsTrue(reader.IsDBNull(0));
Assert.IsTrue(reader.IsDBNull(1));
reader.Close();
}
cmd.CommandText = "drop table if exists testnull";
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public void TestConnectWithDifferentRole()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
var host = testConfig.host;
if (string.IsNullOrEmpty(host))
{
host = $"{testConfig.account}.snowflakecomputing.com";
}
string connStrFmt = "host={0};port=443;account={1};user={2};password={3};role=public;db=snowflake_sample_data;schema=information_schema;warehouse=WH_NOT_EXISTED";
conn.ConnectionString = string.Format(connStrFmt, host, testConfig.account, testConfig.user, testConfig.password);
conn.Open();
Assert.AreEqual(conn.State, ConnectionState.Open);
using (IDbCommand command = conn.CreateCommand())
{
command.CommandText = "select current_role()";
Assert.AreEqual(command.ExecuteScalar().ToString(), "PUBLIC");
command.CommandText = "select current_database()";
CollectionAssert.Contains(new [] { "SNOWFLAKE_SAMPLE_DATA", "" }, command.ExecuteScalar().ToString());
command.CommandText = "select current_schema()";
CollectionAssert.Contains(new [] { "INFORMATION_SCHEMA", "" }, command.ExecuteScalar().ToString());
command.CommandText = "select current_warehouse()";
// Command will return empty string if the hardcoded warehouse does not exist.
Assert.AreEqual("", command.ExecuteScalar().ToString());
}
conn.Close();
}
}
public void TestCancelExecuteAsync()
{
CancellationTokenSource externalCancel = new CancellationTokenSource(TimeSpan.FromSeconds(8));
using (DbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select count(seq4()) from table(generator(timelimit => 20)) v";
// external cancellation should be triggered before timeout
cmd.CommandTimeout = 10;
try
{
Task <object> t = cmd.ExecuteScalarAsync(externalCancel.Token);
t.Wait();
Assert.Fail();
}
catch (AggregateException e)
{
// assert that cancel is not triggered by timeout, but external cancellation
Assert.IsTrue(externalCancel.IsCancellationRequested);
}
Thread.Sleep(2000);
conn.Close();
}
}
public async Task <DataTable> GetTables(IDictionary <string, object> config, string name = null)
{
try
{
using (var conn = new SnowflakeDbConnection())
{
string connectionString = string.Format("scheme=https;ACCOUNT={0};HOST={1};port={2};ROLE={3};WAREHOUSE={4};USER={5};PASSWORD={6};DB={7};SCHEMA={8}", (string)config[SnowflakeConstants.KeyName.Account], (string)config[SnowflakeConstants.KeyName.Host], (string)config[SnowflakeConstants.KeyName.PortNumber], (string)config[SnowflakeConstants.KeyName.Role], (string)config[SnowflakeConstants.KeyName.Warehouse], (string)config[SnowflakeConstants.KeyName.Username], (string)config[SnowflakeConstants.KeyName.Password], (string)config[SnowflakeConstants.KeyName.DatabaseName], (string)config[SnowflakeConstants.KeyName.Schema]);
conn.ConnectionString = connectionString;
await conn.OpenAsync();
var cmd = conn.CreateCommand();
cmd.CommandText = "select * from " + name;
var reader = await cmd.ExecuteReaderAsync();
var dataTable = new DataTable();
dataTable.Load(reader);
await conn.CloseAsync();
return(dataTable);
}
}
catch (Exception)
{
return(new DataTable());
}
}
public ActionResult AddOREdit(Employee mod)
{
string IDs = "";
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
var cmd = conn.CreateCommand();
if (mod.Id == null)
{
IDs = System.Guid.NewGuid().ToString();
}
else
{
IDs = mod.Id;
}
cmd.CommandText = "call Employeesave('" + mod.Firstname + "','" + mod.Lastname + "','" + mod.Address + "','" + IDs + "')";
var reader = cmd.ExecuteReader();
conn.Close();
ShowData();
return(RedirectToAction("Index"));
}
}
public override List <DataCollectionMetrics> GetDataCollectionMetrics(DataContainer container)
{
var metrics = new List <DataCollectionMetrics>();
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = container.ConnectionString;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText =
$"SELECT table_catalog, table_schema, table_name, row_count, bytes FROM information_schema.tables";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var schema = reader.GetString(1);
var name = reader.GetString(2);
var rowCount = reader.IsDBNull(3) ? 0 : reader.GetInt64(3);
var bytes = reader.IsDBNull(4) ? 0 : reader.GetInt64(4);
metrics.Add(new DataCollectionMetrics()
{
Name = name,
RowCount = rowCount,
Schema = schema,
TotalSpaceKB = bytes / 1024,
UsedSpaceKB = bytes / 1024,
});
}
}
return(metrics);
}
public void testBindNullValue()
{
using (SnowflakeDbConnection dbConnection = new SnowflakeDbConnection())
{
dbConnection.ConnectionString = ConnectionString;
dbConnection.Open();
try
{
using (IDbCommand command = dbConnection.CreateCommand())
{
command.CommandText = "create or replace table TEST_TBL (ID number);";
command.ExecuteNonQuery();
}
using (IDbCommand command = dbConnection.CreateCommand())
{
command.CommandText = "insert into TEST_TBL values(:p0)";
var param = command.CreateParameter();
param.ParameterName = "p0";
param.DbType = System.Data.DbType.Int32;
param.Value = DBNull.Value;
command.Parameters.Add(param);
int rowsInserted = command.ExecuteNonQuery();
Assert.AreEqual(1, rowsInserted);
}
using (IDbCommand command = dbConnection.CreateCommand())
{
command.CommandText = "select ID from TEST_TBL;";
using (IDataReader reader = command.ExecuteReader())
{
reader.Read();
Assert.IsTrue(reader.IsDBNull(0));
reader.Close();
}
}
}
finally
{
using (IDbCommand command = dbConnection.CreateCommand())
{
command.CommandText = "drop table TEST_TBL";
command.ExecuteNonQuery();
}
}
}
}
public async Task <IPutFile> Renew()
{
using var cmd = _connection.CreateCommand() as SnowflakeDbCommand;
cmd.CommandText = @"put file://placeholder @haven_work.public.my_stage/bingo/stagey overwrite=true source_compression=gzip";
cmd.CommandType = System.Data.CommandType.Text;
var response = await cmd.CustomExecute <SnowflakePutResponse>(CancellationToken.None);
return(PutFiles.Create(response, DateTime.Now.AddSeconds(60 * 15)));
}
/// <summary>
/// Run SQL commands in Snowflake and return a JSON object with column/value pairs from first row of the result.
/// See https://github.com/snowflakedb/snowflake-connector-net for more details.
/// </summary>
/// <param name="log">ILogger object</param>
/// <param name="snowflakeConnectionString">Snowflake connection string</param>
/// <param name="setVariableCommand">The SQL set variable command to execute</param>
/// <param name="sqlCommands">The SQL commands to execute</param>
/// <returns>JSON object with column/value pairs from first row of the result</returns>
private static List <string> runSnowflakeSqlCommands(ILogger log, string snowflakeConnectionString, string setVariableCommand, string[] sqlCommands)
{
var output = new List <string>();
log.LogInformation($"Found {sqlCommands.Length} queries to execute");
using (IDbConnection conn = new SnowflakeDbConnection())
{
// Connect to Snowflake
conn.ConnectionString = snowflakeConnectionString;
conn.Open();
using (IDbCommand cmd = conn.CreateCommand())
{
// First run the set variable command, if we have one
if (!String.IsNullOrEmpty(setVariableCommand))
{
cmd.CommandText = setVariableCommand;
log.LogInformation($"Running SQL set variable command: {cmd.CommandText}");
cmd.ExecuteNonQuery();
}
// Run every query except the last one using ExecuteNonQuery()
for (int i = 0; i < sqlCommands.Length - 1; i++)
{
cmd.CommandText = sqlCommands[i].Trim();
log.LogInformation($"Running SQL command #{i+1}: {cmd.CommandText}");
cmd.ExecuteNonQuery();
}
// Finally run the last query using ExecuteReader() so we can collect the output
cmd.CommandText = sqlCommands[sqlCommands.Length - 1].Trim();
log.LogInformation($"Running SQL command #{sqlCommands.Length} (final): {cmd.CommandText}");
IDataReader reader = cmd.ExecuteReader();
// The final result should be a table with one row and n columns, format the column/value pairs in JSON.
// Warning: If more than one row is returned in the final result this will return the following error:
// "Property with the same name already exists on object."
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
var columnName = reader.GetName(i);
var value = reader[i].ToString();
var columnType = reader.GetFieldType(i).Name;
output.Add(value);
}
}
}
conn.Close();
}
return(output);
}
public void testGetFloat()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace table testGetDouble(cola double)";
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
float numFloat = (float)1.23;
double numDouble = (double)1.2345678;
string insertCommand = "insert into testgetdouble values (?),(?)";
cmd.CommandText = insertCommand;
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.Value = numFloat;
p1.DbType = DbType.Double;
cmd.Parameters.Add(p1);
var p2 = cmd.CreateParameter();
p2.ParameterName = "2";
p2.Value = numDouble;
p2.DbType = DbType.Double;
cmd.Parameters.Add(p2);
count = cmd.ExecuteNonQuery();
Assert.AreEqual(2, count);
cmd.CommandText = "select * from testgetdouble";
IDataReader reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.AreEqual(numFloat, reader.GetFloat(0));
Assert.AreEqual((decimal)numFloat, reader.GetDecimal(0));
Assert.IsTrue(reader.Read());
Assert.AreEqual(numDouble, reader.GetDouble(0));
Assert.IsFalse(reader.Read());
reader.Close();
cmd.CommandText = "drop table if exists testgetdouble";
count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
conn.Close();
}
}
public void testParameterCollection()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
using (IDbCommand cmd = conn.CreateCommand())
{
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.DbType = DbType.Int16;
p1.Value = 1;
var p2 = cmd.CreateParameter();
p2.ParameterName = "2";
p1.DbType = DbType.Int16;
p2.Value = 2;
var p3 = cmd.CreateParameter();
p2.ParameterName = "2";
p1.DbType = DbType.Int16;
p2.Value = 2;
Array parameters = Array.CreateInstance(typeof(IDbDataParameter), 3);
parameters.SetValue(p1, 0);
parameters.SetValue(p2, 1);
parameters.SetValue(p3, 2);
((SnowflakeDbParameterCollection)cmd.Parameters).AddRange(parameters);
Assert.Throws <NotImplementedException>(
() => { cmd.Parameters.CopyTo(parameters, 5); });
Assert.AreEqual(3, cmd.Parameters.Count);
Assert.IsTrue(cmd.Parameters.Contains(p2));
Assert.IsTrue(cmd.Parameters.Contains("2"));
Assert.AreEqual(1, cmd.Parameters.IndexOf(p2));
Assert.AreEqual(1, cmd.Parameters.IndexOf("2"));
cmd.Parameters.Remove(p2);
Assert.AreEqual(2, cmd.Parameters.Count);
Assert.AreSame(p1, cmd.Parameters[0]);
cmd.Parameters.RemoveAt(0);
Assert.AreSame(p3, cmd.Parameters[0]);
cmd.Parameters.Clear();
Assert.AreEqual(0, cmd.Parameters.Count);
}
conn.Close();
}
}
// Constructor - Make the necessary table for log storage if it does not exist.
public SnowLog(string connectInfo)
{
using (IDbConnection c = new SnowflakeDbConnection())
{
c.ConnectionString = connectInfo;
c.Open();
// Declare the command and transactions which will be used throughout the entire batch job.
IDbCommand cmd = c.CreateCommand();
IDbTransaction logTransaction;
// Start the transaction
logTransaction = c.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
cmd.Connection = c;
cmd.Transaction = logTransaction;
try
{
// Ensure the Datetime columns will have the proper data type.
cmd.CommandText = "ALTER SESSION SET timestamp_type_mapping = timestamp_ltz;";
cmd.ExecuteReader();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS Log.TransactionJobTracking ( " +
"BatchID Date, " +
"JobDatabase string, " +
"JobName string, " +
"JobRunStart TIMESTAMP, " +
"Step string, " +
"StepLabel string, " +
"StepDescription string, " +
"StepTargetSchema string, " +
"StepTargetTable string, " +
"StepRowsAffected int, " +
"StepStatus string, " +
"StepStartDatetime TIMESTAMP, " +
"StepEndDatetime TIMESTAMP, " +
"StepDuration int); ";
cmd.ExecuteReader();
logTransaction.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
logTransaction.Rollback();
}
c.Close();
}
}
public void testGetDateTime()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace table testGetDateTime(cola date, colb time)";
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
DateTime today = DateTime.Today;
DateTime now = DateTime.Now;
string insertCommand = "insert into testgetdatetime values (?, ?)";
cmd.CommandText = insertCommand;
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.Value = today;
p1.DbType = DbType.Date;
cmd.Parameters.Add(p1);
var p2 = cmd.CreateParameter();
p2.ParameterName = "2";
p2.Value = now;
p2.DbType = DbType.Time;
cmd.Parameters.Add(p2);
count = cmd.ExecuteNonQuery();
Assert.AreEqual(1, count);
cmd.CommandText = "select * from testgetdatetime";
IDataReader reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.AreEqual(0, DateTime.Compare(today, reader.GetDateTime(0)));
Assert.AreEqual(today.ToString("yyyy-MM-dd"), reader.GetString(0));
// For time, we getDateTime on the column and ignore date part
DateTime actualTime = reader.GetDateTime(1);
Assert.AreEqual(now.Ticks - now.Date.Ticks, actualTime.Ticks - actualTime.Date.Ticks);
reader.Close();
cmd.CommandText = "drop table if exists testgetdatetime";
count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
conn.Close();
}
}
public void TestTransaction()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
try
{
conn.BeginTransaction(IsolationLevel.ReadUncommitted);
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270009, e.ErrorCode);
}
IDbTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
IDbCommand command = conn.CreateCommand();
command.Transaction = tran;
command.CommandText = "create or replace table testtransaction(cola string)";
command.ExecuteNonQuery();
command.Transaction.Commit();
command.CommandText = "show tables like 'testtransaction'";
IDataReader reader = command.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.IsFalse(reader.Read());
// start another transaction to test rollback
tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = tran;
command.CommandText = "insert into testtransaction values('test')";
command.ExecuteNonQuery();
command.CommandText = "select * from testtransaction";
reader = command.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.AreEqual("test", reader.GetString(0));
command.Transaction.Rollback();
// no value will be in table since it has been rollbacked
command.CommandText = "select * from testtransaction";
reader = command.ExecuteReader();
Assert.IsFalse(reader.Read());
conn.Close();
}
}
public void TestOktaConnection()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = "scheme=http;host=testaccount.reg.snowflakecomputing.com;port=8082;[email protected];password=Test123!;" +
"account=testaccount;role=sysadmin;db=testdb;schema=public;warehouse=regress;authenticator=https://snowflakecomputing.okta.com";
conn.Open();
using (IDbCommand command = conn.CreateCommand())
{
command.CommandText = "SELECT 1";
Assert.AreEqual("1", command.ExecuteScalar().ToString());
}
}
}
public void TestCreateCommandBeforeOpeningConnection()
{
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
using (var command = conn.CreateCommand())
{
conn.Open();
command.CommandText = "select 1";
Assert.DoesNotThrow(() => command.ExecuteNonQuery());
}
}
}
public void testGetGuid()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace table testGetGuid(cola string)";
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
string insertCommand = "insert into testgetguid values (?)";
cmd.CommandText = insertCommand;
Guid val = Guid.NewGuid();
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.DbType = DbType.Guid;
p1.Value = val;
cmd.Parameters.Add(p1);
count = cmd.ExecuteNonQuery();
Assert.AreEqual(1, count);
cmd.CommandText = "select * from testgetguid";
IDataReader reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.AreEqual(val, reader.GetGuid(0));
// test using [] operator
Assert.AreEqual(val.ToString(), reader[0]);
Assert.AreEqual(val.ToString(), reader["COLA"]);
object[] values = new object[1];
Assert.AreEqual(1, reader.GetValues(values));
Assert.AreEqual(val.ToString(), values[0]);
reader.Close();
cmd.CommandText = "drop table if exists testgetguid";
count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
conn.Close();
}
}
public static async Task <IActionResult> Run([HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req, ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a COPY request.");
string host = req.Query["host"];
string account = req.Query["account"];
string user = req.Query["user"];
string secretname = req.Query["secretname"];
string database = req.Query["database"];
string warehouse = req.Query["warehouse"];
string schema = req.Query["schema"];
string table = req.Query["table"];
string stage = req.Query["stage"];
string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
dynamic data = JsonConvert.DeserializeObject(requestBody);
host = host ?? data?.host;
account = account ?? data?.account;
user = user ?? data?.user;
secretname = secretname ?? data?.secretname;
database = database ?? data?.database;
warehouse = warehouse ?? data?.warehouse;
schema = schema ?? data?.schema;
table = table ?? data?.table;
stage = stage ?? data?.stage;
log.LogInformation($"Requesting setting {secretname}.");
string pwd = Environment.GetEnvironmentVariable(secretname);
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = $"host={host};account={account};user={user};password={pwd};db={database};schema={schema};warehouse={warehouse}";
conn.Open();
int affectedrows = 0;
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = $"copy into {table} from '@{stage}'";
IDataReader reader = cmd.ExecuteReader();
affectedrows = cmd.ExecuteNonQuery();
conn.Close();
log.LogInformation($"Processed COPY for {warehouse} {database} {table}");
return(ReturnMethod(affectedrows));
}
}
public void TestSSOConnectionWithUser()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString
= ConnectionStringWithoutAuth
+ ";authenticator=externalbrowser;[email protected]";
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
using (IDbCommand command = conn.CreateCommand())
{
command.CommandText = "SELECT CURRENT_USER()";
Assert.AreEqual("QA", command.ExecuteScalar().ToString());
}
}
}
public void TestExecuteScalarNull()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
using (IDbCommand command = conn.CreateCommand())
{
command.CommandText = "select 1 where 2 > 3";
object val = command.ExecuteScalar();
Assert.AreEqual(DBNull.Value, val);
}
conn.Close();
}
}
public void TestLongRunningQuery()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select count(seq4()) from table(generator(timelimit => 60)) v order by 1";
IDataReader reader = cmd.ExecuteReader();
// only one result is returned
Assert.IsTrue(reader.Read());
conn.Close();
}
}