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 testInvalidConnectioinString()
{
string[] invalidStrings =
{
// missing required connection property password
"ACCOUNT=testaccount;user=testuser",
// invalid account value
"ACCOUNT=A=C;USER=testuser;password=123",
"complete_invalid_string",
};
int[] expectedErrorCode = { 270006, 270008, 270008 };
using (IDbConnection conn = new SnowflakeDbConnection())
{
for (int i = 0; i < invalidStrings.Length; i++)
{
try
{
conn.ConnectionString = invalidStrings[i];
conn.Open();
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(expectedErrorCode[i], e.ErrorCode);
}
}
}
}
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 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 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 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 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 TestConnectionFailFast()
{
using (var conn = new SnowflakeDbConnection())
{
// Just a way to get a 404 on the login request and make sure there are no retry
string invalidConnectionString = "host=docs.microsoft.com;"
+ "connection_timeout=0;account=testFailFast;user=testFailFast;password=testFailFast;";
conn.ConnectionString = invalidConnectionString;
Assert.AreEqual(conn.State, ConnectionState.Closed);
try
{
conn.Open();
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(SFError.INTERNAL_ERROR.GetAttribute <SFErrorAttr>().errorCode,
e.ErrorCode);
}
Assert.AreEqual(ConnectionState.Closed, conn.State);
}
}
public void TestConnectViaSecureString()
{
String[] connEntries = connectionString.Split(';');
String connectionStringWithoutPassword = "";
using (var conn = new SnowflakeDbConnection())
{
var password = new System.Security.SecureString();
foreach (String entry in connEntries)
{
if (!entry.StartsWith("password="******"TESTDB_DOTNET", conn.Database);
Assert.AreEqual(conn.State, ConnectionState.Open);
conn.ChangeDatabase("SNOWFLAKE_SAMPLE_DATA");
Assert.AreEqual("SNOWFLAKE_SAMPLE_DATA", conn.Database);
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 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 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 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);
}
}
// 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 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 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 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>
/// 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 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();
}
}
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();
}
}
// 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 TestConnectWithoutHost()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
String connStrFmt = "account={0};user={1};password={2}";
conn.ConnectionString = String.Format(connStrFmt, testConfig.account,
testConfig.user, testConfig.password);
conn.Open();
Assert.AreEqual(conn.State, ConnectionState.Open);
conn.Close();
}
}
public void testUnknownConnectionProperty()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
// invalid propety will be ignored.
conn.ConnectionString = connectionString += ";invalidProperty=invalidvalue;";
conn.Open();
Assert.AreEqual(conn.State, ConnectionState.Open);
conn.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 TestValidOAuthConnection()
{
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString
= ConnectionStringWithoutAuth
+ String.Format(
";authenticator=oauth;token={0}",
testConfig.oauthToken);
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
}
}
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 TestJwtUnencryptedPkConnection()
{
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString
= ConnectionStringWithoutAuth
+ String.Format(
";authenticator=snowflake_jwt;user={0};private_key={1}",
testConfig.jwtAuthUser,
testConfig.privateKey);
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
}
}
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());
}
}
}