public List <string> LoadCategoryComboBox()
{
List <string> categories = new List <string>();
categories.Add("");
string sql = "SELECT code FROM product_categories ORDER BY code";
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
UniCommand command = new UniCommand(sql, con);
if (con.State == System.Data.ConnectionState.Open)
{
using (UniDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
categories.Add(dr.GetString("code"));
}
}
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
return(categories);
}
}
//SELECT OPERATIONS - ProductCategoriesForm.cs
public List <Category> LoadProductCategories()
{
List <Category> categories = new List <Category>();
string sql = "SELECT code, description FROM product_categories ORDER BY code";
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
UniCommand command = new UniCommand(sql, con);
if (con.State == System.Data.ConnectionState.Open)
{
using (UniDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Category category = new Category();
category.code = dr.GetString(ProductCategoriesConstants.Code);
category.description = dr.GetString(ProductCategoriesConstants.Description);
category.IsNewRecord = false;
categories.Add(category);
}
}
}
con.Close();
}
return(categories);
}
/// <summary>
/// Gets unique SDIs given a particular MRID and M-Table interval
/// </summary>
/// <param name="conx"></param>
/// <param name="mridString"></param>
/// <param name="interval"></param>
/// <returns></returns>
private static string getUniqueSdisFromMTable(UniConnection conx, string mridString, string interval)
{
// Initialize stuff...
string sdiString = "";
string sql = "SELECT UNIQUE(SITE_DATATYPE_ID) FROM M_" + interval + " WHERE MODEL_RUN_ID IN (" + mridString + ")";
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Connect to and get HDB data
if (jrDebug)
{
Console.Write("Getting Unique SDIs... ");
}
UniCommand cmd = new UniCommand(sql, conx);
cmd.CommandType = System.Data.CommandType.Text;
UniDataReader dr = cmd.ExecuteReader();
var schemaTable = dr.GetSchemaTable();
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Build a string of SDIS with a comma delimiter
while (dr.Read())
{
sdiString = sdiString + dr[0].ToString() + ",";
}
dr.Dispose();
cmd.Dispose();
if (jrDebug)
{
Console.WriteLine("Success!");
}
return(sdiString);
}
/// <summary>
/// Gets SDI info from HDB and returns a DataTable
/// </summary>
/// <param name="conx"></param>
/// <param name="sdiString"></param>
/// <returns></returns>
private static DataTable queryHdbInfo(UniConnection conx, string sdiString)
{
// Initialize stuff...
var dTab = new DataTable();
List <string> sdiList = new List <string>(sdiString.Split(','));
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Connect to and get HDB data
if (jrDebug)
{
Console.Write("Downloading sdi info... ");
}
UniCommand cmd = new UniCommand("GET_HDB_CGI_INFO", conx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("o_cursorOutput", UniDbType.Cursor).Direction = ParameterDirection.Output;
cmd.Parameters.Add("i_sdiList", UniDbType.Char).Value = sdiString;
UniDataReader dr = cmd.ExecuteReader();
var schemaTable = dr.GetSchemaTable();
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Put DB data into a .NET DataTable
dTab.Load(dr);
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Return output
if (jrDebug)
{
Console.WriteLine("Success!");
}
dr.Dispose();
cmd.Dispose();
return(dTab);
}
public List <Product> LoadCustomerProducts(int id)
{
CustomerForm customerForm = new CustomerForm();
List <Product> products = new List <Product>();
string sql = "SELECT products.id, products.code, products.description, products.category FROM products INNER JOIN customer_products ON products.id = customer_products.product_id WHERE customer_products.customer_id = :id ORDER BY products.code";
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
UniCommand command = new UniCommand(sql, con);
command.Parameters.Add("id", UniDbType.Int).Value = id;
if (con.State == System.Data.ConnectionState.Open)
{
using (UniDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Product product = new Product();
product.id = dr.GetInt32(ProductConstants.ProductId);
product.code = dr.GetString(ProductConstants.Code);
product.description = dr.GetString(ProductConstants.Description);
product.category = dr.GetString(ProductConstants.Category);
products.Add(product);
}
}
}
}
return(products);
}
public List <string> GetTableNames()
{
string where = string.IsNullOrEmpty(SchemaFilterCSV) ? "WHERE NESTED <> 'YES'" : string.Format("WHERE OWNER IN ({0}) AND NESTED <> 'YES'", SchemaFilterCSV);
string sql1 = string.Format(@"
SELECT
TABLE_NAME,
OWNER AS TABLE_SCHEMA,
0 AS IsSystemObject
FROM All_Tables
{0}
ORDER BY TABLE_NAME
" , where);
List <string> tableNames = new List <string>();
UniDataReader sqlDataReader = null;
try
{
sqlDataReader = RunQuerySQL(sql1);
// Exclude system tables
int isSysObjectColumnOrdinal = sqlDataReader.GetOrdinal("IsSystemObject");
int ordTableName = sqlDataReader.GetOrdinal("TABLE_NAME");
int ordTableSchema = sqlDataReader.GetOrdinal("TABLE_SCHEMA");
while (sqlDataReader.Read())
{
bool isSystemObject = sqlDataReader.IsDBNull(isSysObjectColumnOrdinal) ? false : (bool)sqlDataReader.GetBoolean(isSysObjectColumnOrdinal);
if (!isSystemObject)
{
tableNames.Add(sqlDataReader.GetString(ordTableName) + "|" + sqlDataReader.GetString(ordTableSchema));
}
}
}
finally
{
if (sqlDataReader != null) // && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
}
return(tableNames);
}
/// <summary>
/// Passes an sql query to a specified HDB
/// </summary>
/// <param name="hdbString"></param>
/// <param name="sqlQuery"></param>
/// <returns></returns>
public static DataTable getDataUsingSQL(string hdbString, string sqlQuery)
{
var dTab = new DataTable();
var conx = ConnectHDB(hdbString);
UniCommand cmd = new UniCommand(sqlQuery, conx);
cmd.CommandType = System.Data.CommandType.Text;
UniDataReader dr = cmd.ExecuteReader();
var schemaTable = dr.GetSchemaTable();
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Put DB data into a .NET DataTable
dTab.Load(dr);
DisconnectHDB(conx);
return(dTab);
}
public List <string> GetTableNames()
{
string sql1 = @"
SELECT
TABLE_NAME,
TABLE_SCHEMA,
0 AS IsSystemObject
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
" ;
List <string> tableNames = new List <string>();
UniDataReader sqlDataReader = null;
try
{
sqlDataReader = RunQuerySQL(sql1);
// Exclude system tables
int isSysObjectColumnOrdinal = sqlDataReader.GetOrdinal("IsSystemObject");
int ordTableName = sqlDataReader.GetOrdinal("TABLE_NAME");
int ordTableSchema = sqlDataReader.GetOrdinal("TABLE_SCHEMA");
while (sqlDataReader.Read())
{
bool isSystemObject = sqlDataReader.IsDBNull(isSysObjectColumnOrdinal) ? false : (bool)sqlDataReader.GetBoolean(isSysObjectColumnOrdinal);
if (!isSystemObject)
{
tableNames.Add(sqlDataReader.GetString(ordTableName) + "|" + sqlDataReader.GetString(ordTableSchema));
}
}
}
finally
{
if (sqlDataReader != null) // && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
}
return(tableNames);
}
//SELECT OPERATIONS - ProductForm.cs
public List <Product> LoadProducts()
{
List <Product> products = new List <Product>();
string sql = "SELECT id, code, description, height, width, depth, colour, image, category FROM products ORDER BY id";
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
UniCommand command = new UniCommand(sql, con);
if (con.State == System.Data.ConnectionState.Open)
{
using (UniDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Product product = new Product();
ImageConversion images = new ImageConversion();
product.id = dr.GetInt32(ProductConstants.ProductId);
product.code = dr.GetString(ProductConstants.Code);
product.description = dr.GetString(ProductConstants.Description);
product.height = dr.GetInt32(ProductConstants.Height);
product.width = dr.GetInt32(ProductConstants.Width);
product.depth = dr.GetInt32(ProductConstants.Depth);
product.colour = Color.FromArgb(dr.GetInt32(ProductConstants.Colour));
product.image = images.Base64ToImage(dr.GetString(ProductConstants.Image));
product.category = dr.GetString(ProductConstants.Category);
products.Add(product);
}
}
}
con.Close();
}
return(products);
}
/// <summary>
/// Gets Oracle DB data using the GET_HDB_CGI_DATA stored procedure and returns a DataTable with a common date range and sdi columns
/// </summary>
/// <param name="conx"></param>
/// <param name="sdiList"></param>
/// <param name="runIDs"></param>
/// <returns></returns>
private static DataTable queryHdbDataUsingStoredProcedure(UniConnection conx, string sdiList, string tStep,
string startDate, string endDate, string sourceTable = "R", string modelRunIds = null)
{
// Initialize stuff...
var dTab = new DataTable();
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Connect to and get HDB data
if (jrDebug)
{
Console.Write("Downloading data... ");
}
UniCommand cmd = new UniCommand("GET_HDB_CGI_DATA", conx);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("o_cursorOutput", UniDbType.Cursor).Direction = ParameterDirection.Output;
cmd.Parameters.Add("i_sdiList", UniDbType.VarChar).Value = sdiList;
cmd.Parameters.Add("i_tStep", UniDbType.Char).Value = tStep;
cmd.Parameters.Add("i_startDate", UniDbType.VarChar).Value = startDate.ToUpper();
cmd.Parameters.Add("i_endDate", UniDbType.VarChar).Value = endDate.ToUpper();
cmd.Parameters.Add("i_sourceTable", UniDbType.Char).Value = sourceTable;
cmd.Parameters.Add("i_modelRunIds", UniDbType.Char).Value = modelRunIds;
UniDataReader dr = cmd.ExecuteReader();
var schemaTable = dr.GetSchemaTable();
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Put DB data into a .NET DataTable
dTab.Load(dr);
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Return output
if (jrDebug)
{
Console.WriteLine("Success!");
}
dr.Dispose();
cmd.Dispose();
return(dTab);
}
//SELECT OPERATIONS - CustomerForm.cs
public List <Customer> LoadCustomers()
{
List <Customer> customers = new List <Customer>();
string sql = "SELECT id, account, name, address_line_1, address_line_2, address_line_3, address_line_4, address_postcode FROM customers ORDER BY id";
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
UniCommand command = new UniCommand(sql, con);
if (con.State == System.Data.ConnectionState.Open)
{
using (UniDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Customer customer = new Customer();
customer.id = dr.GetInt32(CustomerConstants.CustomerId);
customer.account = dr.GetString(CustomerConstants.Account);
customer.name = dr.GetString(CustomerConstants.Name);
customer.address_line_1 = dr.GetString(CustomerConstants.AddressLine1);
customer.address_line_2 = dr.GetString(CustomerConstants.AddressLine2);
customer.address_line_3 = dr.GetString(CustomerConstants.AddressLine3);
customer.address_line_4 = dr.GetString(CustomerConstants.AddressLine4);
customer.address_postcode = dr.GetString(CustomerConstants.AddressPostcode);
customers.Add(customer);
}
}
}
con.Close();
}
return(customers);
}
public static string[] GetPostgreSQLSchemas(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
bool trustedConnection = helper.UseIntegratedSecurity;
int port = helper.Port;
string database = helper.DatabaseName;
UniConnection conn = new UniConnection(string.Format("Provider=PostgreSQL;host={0};port={1};user={2};password={3};initial schema=Public;database={4};", serverName, port, userName, password, database));
List <string> databaseNames = new List <string>();
try
{
conn.Open();
using (UniCommand sqlCommand = new UniCommand(@"
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name not like 'pg_catalog%' and
schema_name not like 'pg_toast%' and
schema_name not like 'pg_temp%' and
schema_name not like 'information_schema'", conn))
{
using (UniDataReader dr = sqlCommand.ExecuteReader())
{
while (dr.Read())
{
databaseNames.Add(dr.GetString(0));
}
}
}
}
finally
{
conn.Close();
}
return(databaseNames.ToArray());
}
public static string[] GetOracleDatabases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
bool trustedConnection = helper.UseIntegratedSecurity;
int port = helper.Port;
UniConnection conn;
//if (trustedConnection)
// conn = new UniConnection(string.Format("Provider=Oracle;host=server;user=root;password=root;database=myDB", serverName));
//else
//conn = new UniConnection(string.Format("Provider=Oracle;host={0};user={1};password={2};database=myDB", serverName, userName, password));
conn = new UniConnection(string.Format("Provider=Oracle;Direct=true;data source={0};user={1};password={2};port={3};", serverName, userName, password, port));
List <string> databaseNames = new List <string>();
try
{
conn.Open();
using (UniCommand sqlCommand = new UniCommand("SELECT DISTINCT OWNER FROM all_tables ORDER BY OWNER", conn))
{
using (UniDataReader dr = sqlCommand.ExecuteReader())
{
while (dr.Read())
{
databaseNames.Add(dr.GetString(0));
}
}
}
}
finally
{
conn.Close();
}
return(databaseNames.ToArray());
}
public static string[] GetPostgreSQLDatabases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
bool trustedConnection = helper.UseIntegratedSecurity;
int port = helper.Port;
UniConnection conn = new UniConnection(string.Format("Provider=PostgreSQL;host={0};port={1};user={2};password={3};initial schema=Public;", serverName, port, userName, password));
List <string> databaseNames = new List <string>();
try
{
conn.Open();
using (UniCommand sqlCommand = new UniCommand(@"
SELECT datname
FROM pg_catalog.pg_database
where not datistemplate
ORDER BY datname
", conn))
{
using (UniDataReader dr = sqlCommand.ExecuteReader())
{
while (dr.Read())
{
databaseNames.Add(dr.GetString(0));
}
}
}
}
finally
{
conn.Close();
}
return(databaseNames.ToArray());
}
//SELECT OPERATIONS - ProductSelectionForm.cs
public List <Product> LoadCustomerProducts()
{
List <Product> products = new List <Product>();
string sql = "SELECT id, code, description, height, width, depth FROM products ORDER BY id";
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
UniCommand command = new UniCommand(sql, con);
if (con.State == System.Data.ConnectionState.Open)
{
using (UniDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Product product = new Product();
product.id = dr.GetInt32(ProductConstants.ProductId);
product.code = dr.GetString(ProductConstants.Code);
product.description = dr.GetString(ProductConstants.Description);
product.height = dr.GetInt32(ProductConstants.Height);
product.width = dr.GetInt32(ProductConstants.Width);
product.depth = dr.GetInt32(ProductConstants.Depth);
products.Add(product);
}
}
}
con.Close();
return(products);
}
}
public bool IsExistingCustomerAccount(string account)
{
string sql = "SELECT account FROM customers WHERE account = :account";
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
UniCommand command = new UniCommand(sql, con);
command.Parameters.Add("account", UniDbType.VarChar).Value = account;
using (UniDataReader dr = command.ExecuteReader())
{
if (dr.Read())
{
con.Close();
return(true);
}
con.Close();
return(false);
}
}
}
public bool IsExistingCategoryCode(string code)
{
string sql = "SELECT code FROM product_categories WHERE code = :code";
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
UniCommand command = new UniCommand(sql, con);
command.Parameters.Add("code", UniDbType.VarChar).Value = code;
using (UniDataReader dr = command.ExecuteReader())
{
if (dr.Read())
{
con.Close();
return(true);
}
con.Close();
return(false);
}
}
}
private async Task <bool> DatabaseProcess()
{
var connectionString = config.sourceOptions?.FirstOrDefault(p => p.Key == "connectionString").Value;
var query = config.sourceOptions?.FirstOrDefault(p => p.Key == "query").Value;
if (string.IsNullOrWhiteSpace(connectionString) ||
string.IsNullOrWhiteSpace(query))
{
Logger.ErrorFormat("Database params from config is invalid.");
return(false);
}
UniConnection connection = new UniConnection(connectionString);
try
{
connection.Open();
UniCommand cmd = new UniCommand(query, connection);
UniDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
List <DataInfoField> dataInfoFields = new List <DataInfoField>();
var dataId = string.Empty;
for (int j = 0; j < dataReader.FieldCount; j++)
{
DataInfoField dataInfoField = new DataInfoField();
var columnName = dataReader.GetName(j);
if (columnName == columnId)
{
dataId = dataReader.GetValue(j).ToString();
}
else
{
dataInfoField.fieldname = columnName;
dataInfoField.value = dataReader.GetValue(j).ToString();
dataInfoField.type = "";
dataInfoFields.Add(dataInfoField);
}
}
DataInfo dataInfoItem = new DataInfo();
dataInfoItem.dataInfoFields = dataInfoFields;
if (!historyStorage.Exits(dataId))
{
if (await StartProcess(dataInfoItem, processId, communityId, config.initWF, config.superAdmin, dataId))
{
historyStorage.InsertItem(dataId, 0, 1);
}
else
{
historyStorage.InsertItem(dataId, 1, 0);
}
}
else
{
var faildAttems = historyStorage.IsFailed(dataId);
if (faildAttems > 0 && faildAttems < maxAttemps)
{
// retry and updateElement
if (await StartProcess(dataInfoItem, processId, communityId, config.initWF, config.superAdmin, dataId))
{
historyStorage.UpdateItem(dataId, faildAttems, 1);
}
else
{
historyStorage.UpdateItem(dataId, faildAttems + 1, 0);
}
}
}
}
}
else
{
Logger.InfoFormat("Empty query result.");
}
}
catch (Exception ex)
{
Logger.ErrorFormat("Unexpected error. Description: {0}", ex.Message);
}
finally
{
connection.Close();
}
return(true);
}
