public void TestCrlCheckSwitchConnection()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString + ";INSECUREMODE=true";
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
}
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
}
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString + ";INSECUREMODE=false";
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
}
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
}
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString + ";INSECUREMODE=false";
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
}
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString + ";INSECUREMODE=true";
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
}
}
/// <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 void TestValidOAuthExpiredTokenConnection()
{
try
{
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString
= ConnectionStringWithoutAuth
+ ";authenticator=oauth;token=ETMsDgAAAXcmdv0gABRBRVMvQ0JDL1BLQ1M1UGFkZGluZwCAABAAECPcGqa/QJd3BMw5z2V/Fn8AAABQdCCJgbhZpzzIrh2j/ej8rXZBODsPIwM6oODfWZ3a2PNP91PdMadOoUh5NjWanGfUQdZNkVFLzh6BJdAT5XaaQdTiszkqtOao9QaWhtarKVoAFAQ+KiE/CavTBhURVKjXmSfe7k6N";
conn.Open();
Assert.Fail();
}
}
catch (SnowflakeDbException e)
{
// Token is expired
Assert.AreEqual(390318, e.ErrorCode);
}
}
override public int RunCommand()
{
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString = configuration.GetConnectionString("SnowFlake");
conn.Open();
var cmd = conn.CreateCommand();
var CommandText = "SELECT * from DB_APPS.PUBLIC.BOOKINGS as b ORDER BY b.CREATED_AT DESC LIMIT 10";
var reservations = conn.Query <Reservation>(CommandText);
foreach (var reservation in reservations)
{
console.WriteLine("= " + reservation.Id);
}
}
return(0);
}
public async Task <SnowflakeDbConnection> GetConnection(IDictionary <string, object> config)
{
try
{
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]);
var result = new SnowflakeDbConnection();
result.ConnectionString = connectionString;
result.Open();
return(await Task.FromResult(result));
}
catch (Exception e)
{
throw e;
}
}
public void TestConnectWithDifferentRole()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
var host = testConfig.host;
if (string.IsNullOrEmpty(host))
{
host = $"{testConfig.account}.snowflakecomputing.com";
}
string connStrFmt = "scheme={0};host={1};port={2};" +
"user={3};password={4};account={5};role=public;db=snowflake_sample_data;schema=information_schema;warehouse=WH_NOT_EXISTED";
conn.ConnectionString = string.Format(
connStrFmt,
testConfig.protocol,
testConfig.host,
testConfig.port,
testConfig.user,
testConfig.password,
testConfig.account
);
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 testGetTimestampLTZ()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace table testGetTimestampLTZ(cola timestamp_ltz)";
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
DateTimeOffset now = DateTimeOffset.Now;
string insertCommand = "insert into testgettimestampltz values (?)";
cmd.CommandText = insertCommand;
var p1 = (SnowflakeDbParameter)cmd.CreateParameter();
p1.ParameterName = "1";
p1.Value = now;
p1.DbType = DbType.DateTimeOffset;
p1.SFDataType = Core.SFDataType.TIMESTAMP_LTZ;
cmd.Parameters.Add(p1);
count = cmd.ExecuteNonQuery();
Assert.AreEqual(1, count);
cmd.CommandText = "select * from testgettimestampltz";
IDataReader reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
DateTimeOffset dtOffset = (DateTimeOffset)reader.GetValue(0);
reader.Close();
Assert.AreEqual(0, DateTimeOffset.Compare(now, dtOffset));
cmd.CommandText = "drop table if exists testgettimestamptz";
count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
conn.Close();
}
}
/// <summary>
/// Executes a sequence of Snowflake statements that are not expected to return a result set.
/// </summary>
/// <param name="statements">The query statements to execute.</param>
/// <returns>The number of rows affected. When more than one statement is supplied the method will return the number of rows affected for the last statement only.</returns>
public int ExecuteNonQuery(params string[] statements)
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = this.connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
int affectedRows = 0;
foreach (string command in statements)
{
cmd.CommandText = command;
affectedRows = cmd.ExecuteNonQuery();
}
return(affectedRows);
}
}
public void TestSwitchDb()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
Assert.AreEqual(conn.State, ConnectionState.Closed);
conn.Open();
Assert.AreEqual(testConfig.database.ToUpper(), conn.Database);
Assert.AreEqual(conn.State, ConnectionState.Open);
conn.ChangeDatabase("SNOWFLAKE_SAMPLE_DATA");
Assert.AreEqual("SNOWFLAKE_SAMPLE_DATA", conn.Database);
conn.Close();
}
}
public override void LoadUnitTestData(DataContainer dataContainer)
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = dataContainer.ConnectionString;
conn.Open();
using (var reader = new StreamReader("tests/data.sql"))
{
var sb = new StringBuilder();
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
if (String.IsNullOrEmpty(line))
{
continue;
}
if (line.Equals("quit;", StringComparison.InvariantCultureIgnoreCase) || line.Equals("/"))
{
continue;
}
sb.AppendLine(line);
if (line.TrimEnd().EndsWith(";"))
{
using (var cmd = conn.CreateCommand())
{
Console.WriteLine(sb.ToString());
cmd.CommandTimeout = 600;
cmd.CommandText = sb.ToString().TrimEnd(new[] { ' ', '\n', '\r', '\t', ';' });
cmd.ExecuteNonQuery();
}
sb.Clear();
}
}
}
}
}
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);
}
}
}
/// <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="sqlCommands">The SQL commands to execute</param>
/// <returns>JSON object with column/value pairs from first row of the result</returns>
private static JObject runSnowflakeSqlCommands(ILogger log, string snowflakeConnectionString, string[] sqlCommands)
{
var output = new JObject();
using (IDbConnection conn = new SnowflakeDbConnection())
{
// Connect to Snowflake
conn.ConnectionString = snowflakeConnectionString;
conn.Open();
using (IDbCommand cmd = conn.CreateCommand())
{
// 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}: {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 final SQL command: {cmd.CommandText}");
IDataReader reader = cmd.ExecuteReader();
// The result should be a table with one row and n columns, format the column/value pairs in JSON
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
var columnName = reader.GetName(i);
var value = reader[i].ToString();
output.Add(columnName, value);
}
}
}
conn.Close();
}
return(output);
}
public static void Insert()
{
string connectionString = "scheme=https;host=jo69808.us-central1.gcp.snowflakecomputing.com;ROLE=sysadmin;WAREHOUSE=John;user=johns;password=Water123;DB=JOHN;account=jo69808.us-central1.gcp.*;";
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "insert into testtable1 values('hello','snowflake'); ";// while calling procedure
var reader = cmd.ExecuteReader();
dynamic resultData;
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
resultData = reader.GetString(0);
}
conn.Close();
}
}
public void TestInValidOAuthTokenConnection()
{
try
{
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString
= ConnectionStringWithoutAuth
+ ";authenticator=oauth;token=notAValidOAuthToken";
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
Assert.Fail();
}
}
catch (SnowflakeDbException e)
{
// Invalid OAuth access token
Assert.AreEqual(390303, e.ErrorCode);
}
}
public void TestSimpleLargeResultSet()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select seq4(), uniform(1, 10, 42) from table(generator(rowcount => 1000000)) v order by 1";
IDataReader reader = cmd.ExecuteReader();
int counter = 0;
while (reader.Read())
{
Assert.AreEqual(counter.ToString(), reader.GetString(0));
counter++;
}
conn.Close();
}
}
public void TestBasicConnection()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
Assert.AreEqual(0, conn.ConnectionTimeout);
// Data source is empty string for now
Assert.AreEqual("", ((SnowflakeDbConnection)conn).DataSource);
string serverVersion = ((SnowflakeDbConnection)conn).ServerVersion;
string[] versionElements = serverVersion.Split('.');
Assert.AreEqual(3, versionElements.Length);
conn.Close();
Assert.AreEqual(ConnectionState.Closed, conn.State);
}
}
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);
}
private void testGetDateAndOrTime(string inputTimeStr, int?precision, SFDataType dataType)
{
// Can't use DateTime object as test case, must parse.
DateTime inputTime;
if (inputTimeStr == null)
{
inputTime = dataType == SFDataType.DATE ? DateTime.Today : DateTime.Now;
}
else
{
inputTime = DateTime.ParseExact(inputTimeStr, "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture);
}
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = $"create or replace table testGetDateAndOrTime(cola {dataType}{ (precision == null ? string.Empty : $"({precision})" )});";
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 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();
try
{
executionThread.Wait();
}
catch (AggregateException e)
{
Assert.AreEqual(
"System.Threading.Tasks.TaskCanceledException",
e.InnerException.GetType().ToString());
}
conn.Close();
}
}
public void testGetBinary()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace table testGetBinary(cola binary)";
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
string insertCommand = "insert into testgetbinary values (?)";
cmd.CommandText = insertCommand;
byte[] testBytes = Encoding.UTF8.GetBytes("TEST_GET_BINARAY");
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.DbType = DbType.Binary;
p1.Value = testBytes;
cmd.Parameters.Add(p1);
count = cmd.ExecuteNonQuery();
Assert.AreEqual(1, count);
cmd.CommandText = "select * from testgetbinary";
IDataReader reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.IsTrue(testBytes.SequenceEqual((byte[])reader.GetValue(0)));
reader.Close();
cmd.CommandText = "drop table if exists testgetbinary";
count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
conn.Close();
}
}
public void TestValidOAuthExpiredTokenConnection()
{
try
{
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString
= ConnectionStringWithoutAuth
+ String.Format(
";authenticator=oauth;token={0}",
testConfig.expOauthToken);
conn.Open();
Assert.Fail();
}
}
catch (SnowflakeDbException e)
{
Console.Write(e);
// Token is expired
Assert.AreEqual(390318, e.ErrorCode);
}
}
public void TestJwtConnectionWithWrongUser()
{
try
{
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString
= ConnectionStringWithoutAuth
+ String.Format(
";authenticator=snowflake_jwt;user={0};private_key_file={1}",
"WrongUser",
testConfig.pemFilePath);
conn.Open();
Assert.Fail();
}
}
catch (SnowflakeDbException e)
{
// Jwt token is invalid
Assert.AreEqual(390144, e.ErrorCode);
}
}
public void TestJwtEncryptedPkFileNoPwdConnection()
{
try
{
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString
= ConnectionStringWithoutAuth
+ String.Format(
";authenticator=snowflake_jwt;user={0};private_key_file={1}",
testConfig.jwtAuthUser,
testConfig.pwdProtectedPrivateKeyFilePath);
conn.Open();
Assert.Fail();
}
}
catch (SnowflakeDbException e)
{
// Invalid password (none provided) for decrypting the private key
Assert.AreEqual(270052, e.ErrorCode);
}
}
public void testLoginTimeout()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
string invalidConnectionString = "host=invalidaccount.snowflakecomputing.com;connection_timeout=5;"
+ "account=invalidaccount;user=snowman;password=test;";
conn.ConnectionString = invalidConnectionString;
Assert.AreEqual(conn.State, ConnectionState.Closed);
try
{
conn.Open();
Assert.Fail();
}
catch (AggregateException e)
{
Assert.AreEqual(270007, ((SnowflakeDbException)e.InnerException).ErrorCode);
}
Assert.AreEqual(5, conn.ConnectionTimeout);
}
}
public void TestUseV2ChunkDownloader()
{
SFConfiguration.Instance().UseV2ChunkDownloader = true;
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select seq4(), uniform(1, 10, 42) from table(generator(rowcount => 200000)) v order by 1";
IDataReader reader = cmd.ExecuteReader();
int counter = 0;
while (reader.Read())
{
Assert.AreEqual(counter.ToString(), reader.GetString(0));
counter++;
}
conn.Close();
}
}
public void testGetBoolean()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace table testGetBoolean(cola boolean)";
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
string insertCommand = "insert into testgetboolean values (?)";
cmd.CommandText = insertCommand;
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.DbType = DbType.Boolean;
p1.Value = true;
cmd.Parameters.Add(p1);
count = cmd.ExecuteNonQuery();
Assert.AreEqual(1, count);
cmd.CommandText = "select * from testgetboolean";
IDataReader reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.IsTrue(reader.GetBoolean(0));
reader.Close();
cmd.CommandText = "drop table if exists testgetboolean";
count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
conn.Close();
}
}
public override (List <DataCollection>, List <DataEntity>) GetSchema(DataContainer container)
{
var collections = new List <DataCollection>();
var entities = new List <DataEntity>();
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = container.ConnectionString;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText =
$"SELECT table_schema, table_name, ordinal_position, column_name, data_type FROM information_schema.columns order by table_schema, table_name, ordinal_position";
DataCollection coll = null;
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var schema = reader.GetString(0);
var tableName = reader.GetString(1);
var columnName = reader.GetString(3);
var dataType = reader.GetString(4);
if (coll == null || !coll.Name.Equals(tableName) || !coll.Schema.Equals(schema))
{
coll = new DataCollection(container, tableName)
{
Schema = schema
};
collections.Add(coll);
}
entities.Add(new DataEntity(columnName, ConvertDataType(dataType), dataType, container, coll));
}
}
return(collections, entities);
}
private void DeleteHDSButton_Click(object sender, EventArgs e)
{
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;
try
{
MessageBox.Show(DateTime.Now.ToString());
cmd.CommandText = "INSERT INTO Log.Date " +
"VALUES (" + DateTime.Now.ToString() + ") ";
cmd.ExecuteReader();
transaction.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
transaction.Rollback();
}
snowConn.Close();
}
}
public void testArrayBind()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "create or replace table testArrayBind(cola integer, colb string)";
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
string insertCommand = "insert into testArrayBind values (?, ?)";
cmd.CommandText = insertCommand;
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.DbType = DbType.Int16;
p1.Value = new int[] { 1, 2, 3 };
cmd.Parameters.Add(p1);
var p2 = cmd.CreateParameter();
p2.ParameterName = "2";
p2.DbType = DbType.String;
p2.Value = new string[] { "str1", "str2", "str3" };
cmd.Parameters.Add(p2);
count = cmd.ExecuteNonQuery();
Assert.AreEqual(3, count);
cmd.CommandText = "drop table if exists testArrayBind";
cmd.ExecuteNonQuery();
}
conn.Close();
}
}