public void TestCopyCmdUpdateCount()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace stage emptyStage";
cmd.ExecuteNonQuery();
cmd.CommandText = "create or replace table testCopy (cola string)";
cmd.ExecuteNonQuery();
cmd.CommandText = "copy into testCopy from @emptyStage";
int updateCount = cmd.ExecuteNonQuery();
Assert.AreEqual(0, updateCount);
// test rows_loaded exists
cmd.CommandText = "copy into @%testcopy from (select 'test_string')";
cmd.ExecuteNonQuery();
cmd.CommandText = "copy into testcopy";
updateCount = cmd.ExecuteNonQuery();
Assert.AreEqual(1, updateCount);
// clean up
cmd.CommandText = "drop stage emptyStage";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table testCopy";
cmd.ExecuteNonQuery();
conn.Close();
}
}
public void TestRetrieveSemiStructuredData()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace table testsemi(cola variant, colb array, colc object) " +
"as select '[\"1\", \"2\"]', '[\"1\", \"2\"]', '{\"key\": \"value\"}'";
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from testsemi";
using (IDataReader reader = cmd.ExecuteReader())
{
Assert.AreEqual(true, reader.Read());
Assert.AreEqual("[\n \"1\",\n \"2\"\n]", reader.GetString(0));
Assert.AreEqual("[\n \"1\",\n \"2\"\n]", reader.GetString(1));
Assert.AreEqual("{\n \"key\": \"value\"\n}", reader.GetString(2));
}
conn.Close();
}
}
public async Task <DataTable> GetTableColumns(IDictionary <string, object> config, string tableName)
{
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;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select * from " + tableName;
var reader = cmd.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(reader);
conn.Close();
return(await Task.FromResult(dataTable));
}
}
catch (Exception)
{
return(new DataTable());
}
}
public void testDataSourceError()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from table_not_exists";
try
{
IDataReader reader = cmd.ExecuteReader();
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(2003, e.ErrorCode);
Assert.AreNotEqual("", e.queryId);
}
conn.Close();
}
}
public void testReadOutNullVal()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select null";
using (IDataReader reader = cmd.ExecuteReader())
{
reader.Read();
object nullVal = reader.GetValue(0);
Assert.IsNull(nullVal);
Assert.IsTrue(reader.IsDBNull(0));
reader.Close();
}
}
conn.Close();
}
}
static void Main(string[] args)
{
// string connectionString = "Host=jo69808.us-central1.gcp.snowflakecomputing; ROLE=sysadmin;WAREHOUSE=John; USER=johns; PASSWORD=Water123;DB=JOHN;SCHEMA=EM";
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.*;";
//Scenario 1. Get the list of employee from Snowflake View which returns Json data ///Read The data using snow flake
using (var conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select * from testtable1;";
var reader = cmd.ExecuteReader();
dynamic employeeList;
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
employeeList = reader.GetString(0);
}
conn.Close();
Insert();
}
}
public void TestSimpleCommand()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select 1";
// command type can only be text, stored procedure are not supported.
Assert.AreEqual(CommandType.Text, cmd.CommandType);
try
{
cmd.CommandType = CommandType.StoredProcedure;
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270009, e.ErrorCode);
}
Assert.AreEqual(UpdateRowSource.None, cmd.UpdatedRowSource);
try
{
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270009, e.ErrorCode);
}
Assert.AreSame(conn, cmd.Connection);
try
{
cmd.Connection = null;
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270009, e.ErrorCode);
}
Assert.IsFalse(((SnowflakeDbCommand)cmd).DesignTimeVisible);
try
{
((SnowflakeDbCommand)cmd).DesignTimeVisible = true;
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270009, e.ErrorCode);
}
object val = cmd.ExecuteScalar();
Assert.AreEqual(1L, (long)val);
conn.Close();
}
}
public void testGetNumber()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace table testGetNumber(cola number)";
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
int numInt = 10000;
long numLong = 1000000L;
short numShort = 10;
string insertCommand = "insert into testgetnumber values (?),(?),(?)";
cmd.CommandText = insertCommand;
var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.Value = numInt;
p1.DbType = DbType.Int32;
cmd.Parameters.Add(p1);
var p2 = cmd.CreateParameter();
p2.ParameterName = "2";
p2.Value = numLong;
p2.DbType = DbType.Int32;
cmd.Parameters.Add(p2);
var p3 = cmd.CreateParameter();
p3.ParameterName = "3";
p3.Value = numShort;
p3.DbType = DbType.Int16;
cmd.Parameters.Add(p3);
count = cmd.ExecuteNonQuery();
Assert.AreEqual(3, count);
cmd.CommandText = "select * from testgetnumber";
IDataReader reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.AreEqual(numInt, reader.GetInt32(0));
Assert.IsTrue(reader.Read());
Assert.AreEqual(numLong, reader.GetInt64(0));
Assert.IsTrue(reader.Read());
Assert.AreEqual(numShort, reader.GetInt16(0));
Assert.IsFalse(reader.Read());
reader.Close();
cmd.CommandText = "drop table if exists testgetnumber";
count = cmd.ExecuteNonQuery();
Assert.AreEqual(0, count);
conn.Close();
}
}
public void TestResultSetMetadata()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create or replace table meta(c1 number(20, 4), c2 string(100), " +
"c3 double, c4 timestamp_ntz, c5 variant not null, c6 boolean) ";
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from meta";
using (IDataReader reader = cmd.ExecuteReader())
{
var dataTable = reader.GetSchemaTable();
dataTable.DefaultView.Sort = SchemaTableColumn.ColumnName;
dataTable = dataTable.DefaultView.ToTable();
DataRow row = dataTable.Rows[0];
Assert.AreEqual("C1", row[SchemaTableColumn.ColumnName]);
Assert.AreEqual(0, row[SchemaTableColumn.ColumnOrdinal]);
Assert.AreEqual(20, row[SchemaTableColumn.NumericPrecision]);
Assert.AreEqual(4, row[SchemaTableColumn.NumericScale]);
Assert.AreEqual(SFDataType.FIXED, (SFDataType)row[SchemaTableColumn.ProviderType]);
Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]);
row = dataTable.Rows[1];
Assert.AreEqual("C2", row[SchemaTableColumn.ColumnName]);
Assert.AreEqual(1, row[SchemaTableColumn.ColumnOrdinal]);
Assert.AreEqual(100, row[SchemaTableColumn.ColumnSize]);
Assert.AreEqual(SFDataType.TEXT, (SFDataType)row[SchemaTableColumn.ProviderType]);
Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]);
row = dataTable.Rows[2];
Assert.AreEqual("C3", row[SchemaTableColumn.ColumnName]);
Assert.AreEqual(2, row[SchemaTableColumn.ColumnOrdinal]);
Assert.AreEqual(SFDataType.REAL, (SFDataType)row[SchemaTableColumn.ProviderType]);
Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]);
row = dataTable.Rows[3];
Assert.AreEqual("C4", row[SchemaTableColumn.ColumnName]);
Assert.AreEqual(3, row[SchemaTableColumn.ColumnOrdinal]);
Assert.AreEqual(0, row[SchemaTableColumn.NumericPrecision]);
Assert.AreEqual(9, row[SchemaTableColumn.NumericScale]);
Assert.AreEqual(SFDataType.TIMESTAMP_NTZ, (SFDataType)row[SchemaTableColumn.ProviderType]);
Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]);
row = dataTable.Rows[4];
Assert.AreEqual("C5", row[SchemaTableColumn.ColumnName]);
Assert.AreEqual(4, row[SchemaTableColumn.ColumnOrdinal]);
Assert.AreEqual(SFDataType.VARIANT, (SFDataType)row[SchemaTableColumn.ProviderType]);
Assert.AreEqual(false, row[SchemaTableColumn.AllowDBNull]);
row = dataTable.Rows[5];
Assert.AreEqual("C6", row[SchemaTableColumn.ColumnName]);
Assert.AreEqual(5, row[SchemaTableColumn.ColumnOrdinal]);
Assert.AreEqual(SFDataType.BOOLEAN, (SFDataType)row[SchemaTableColumn.ProviderType]);
Assert.AreEqual(true, row[SchemaTableColumn.AllowDBNull]);
}
conn.Close();
}
}
public DynamicDataRow[] QuerySnowFlake(string query, string host, string account, string username, string password, string database, string schema, string warehouse, string role)
{
try
{
// Defined for use as the output handler.
DataTable outputTable = new DataTable();
List <DynamicDataRow> dynamicRows = new List <DynamicDataRow>();
using (IDbConnection conn = new SnowflakeDbConnection())
{
if (host.StartsWith("http", StringComparison.OrdinalIgnoreCase))
{
host = host.Split(':').Last();
host = host.Replace("//", string.Empty);
}
// Create the connection string and connect to Snowflake instance.
conn.ConnectionString = "host=" + host + ";account=" + account + ";role=" + role + ";user="******";password="******";db=" + database + ";schema=" + schema + ";warehouse=" + warehouse;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = query;
IDataReader reader = cmd.ExecuteReader();
// Get the column names in the result set.
DataTable schemaTable = reader.GetSchemaTable();
// Dynamically generate the outputTable using the columns from above.
foreach (DataRow row in schemaTable.Rows)
{
outputTable.Columns.Add(row["ColumnName"].ToString(), Type.GetType(row["DataType"].ToString()));
}
int tableColumns = schemaTable.Rows.Count;
// Read all resultset rows into outputTable.
while (reader.Read())
{
DataRow row = outputTable.NewRow();
for (int i = 0; i < tableColumns; i++)
{
row[i] = reader[i];
}
outputTable.Rows.Add(row);
dynamicRows.Add(new DynamicDataRow(row));
}
conn.Close();
}
// Create the output result set back to Decisions.
//Dictionary<string, object> resultData = new Dictionary<string, object>();
//resultData.Add("Result", LDR.ToArray());
return(dynamicRows.ToArray());
}
catch (Exception ex2)
{
throw;
}
}
public void Run(ConfigurationDataset configBoard)
{
try
{
var geckoboardClient = new GeckoboardClient(configBoard.GeckoBoadApiKey);
var canConnect = geckoboardClient.Ping();
var client = geckoboardClient.Datasets();
try
{
client.Delete(configBoard.DataSetName);
Console.WriteLine($"Deleted {configBoard.DataSetName} successfully : {DateTime.Now}");
}
catch (Exception e)
{
Console.WriteLine(e);
var original = Console.BackgroundColor;
Console.BackgroundColor = ConsoleColor.Red;
Console.WriteLine(e);
Console.BackgroundColor = original;
}
var cleanBoard = new GeckoDataset()
{
Fields = configBoard.Fields,
UniqueBy = configBoard.UniqueBy,
};
try
{
string jsonFields1 = cleanBoard.JSon();
var dataset = client.FindOrCreateByJson(configBoard.DataSetName, jsonFields1);
Console.WriteLine($"created {configBoard.DataSetName} successfully : {DateTime.Now}");
}
catch (Exception e)
{
Console.WriteLine(e);
var original = Console.BackgroundColor;
Console.BackgroundColor = ConsoleColor.Red;
Console.WriteLine(e);
Console.BackgroundColor = original;
}
var objupd = new GeckoDataset()
{
Data = new List <Dictionary <string, object> >()
};
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = configBoard.SnowflakeConnection;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = configBoard.DataStatement;
IDataReader reader = cmd.ExecuteReader();
var dbObject = new ExpandoObject();
var cols = reader.GetSchemaTable().Rows;
for (int i = 0; i < cols.Count; i++)
{
object v = cols[i][0];
((IDictionary <string, object>)dbObject).Add(v.ToString(), v);
}
List <dynamic> personList = new List <dynamic>();
var allSales = reader.DataReaderMapToList(dbObject);
conn.Close();
foreach (ExpandoObject saleOrder in allSales)
{
Dictionary <string, object> item = new Dictionary <string, object>()
{
};
foreach (var cleanBoardField in cleanBoard.Fields)
{
var entry = saleOrder.SingleOrDefault(p => p.Key.ToUpper().Equals(cleanBoardField.Value.Name,
StringComparison.InvariantCultureIgnoreCase));
var colValue = entry.Value;
var hasNoValue = ReferenceEquals(colValue, null) || colValue == DBNull.Value;
if (hasNoValue)
{
if (cleanBoardField.Value.Type == DatasetFieldType.datetime || cleanBoardField.Value.Type == DatasetFieldType.date)
{
if (cleanBoardField.Value.DefaultValue == "now")
{
item.Add(cleanBoardField.Value.Name.ToLower(), DateTime.Now);
}
else
{
item.Add(cleanBoardField.Value.Name.ToLower(), DateTime.Parse(cleanBoardField.Value.DefaultValue));
}
}
else
{
item.Add(cleanBoardField.Value.Name.ToLower(), "");
}
}
else
{
item.Add(cleanBoardField.Value.Name.ToLower(), colValue);
}
}
objupd.Data.Add(item);
}
}
// Act
var jsonFields = objupd.JSon();
var result = client.UpdateDataset(jsonFields, configBoard.DataSetName);
Console.WriteLine($"uploaded successfully: {configBoard.DataSetName} on: {DateTime.Now}");
}
catch (Exception e)
{
Console.WriteLine(e);
var original = Console.BackgroundColor;
Console.BackgroundColor = ConsoleColor.Red;
Console.WriteLine(e);
Console.BackgroundColor = original;
}
}
public string QuerySnowFlake(string query, string host, string account, string username, string password, string database, string schema, string warehouse, string role)
{
try
{
// Defined for use as the output handler.
DataTable outputTable = new DataTable();
List <DynamicDataRow> dynamicRows = new List <DynamicDataRow>();
using (IDbConnection conn = new SnowflakeDbConnection())
{
if (host.StartsWith("http", StringComparison.OrdinalIgnoreCase))
{
host = host.Split(':').Last();
host = host.Replace("//", string.Empty);
}
// Create the connection string and connect to Snowflake instance.
conn.ConnectionString = "host=" + host + ";account=" + account + ";role=" + role + ";user="******";password="******";db=" + database + ";schema=" + schema + ";warehouse=" + warehouse;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = query;
IDataReader reader = cmd.ExecuteReader();
rows = ConvertToDictionary(reader);
conn.Close();
}
// Create the output result set back to Decisions.
//Dictionary<string, object> resultData = new Dictionary<string, object>();
//resultData.Add("Result", LDR.ToArray());
return(JsonConvert.SerializeObject(rows));
}
catch (Exception ex2)
{
throw;
}
}