//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); }
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); } }
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(); }
/// <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); }
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); }
/// <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 UniDataReader RunQuerySQL(string sql) { try { UniCommand sqlCommand = new UniCommand(sql, uniConnection); return(sqlCommand.ExecuteReader()); } catch (Exception e) { throw new Exception(string.Format("Error running SQL: [{0}]", sql), e); } }
public UniDataReader RunStoredProcedure(string storedProcedureName) { try { UniCommand sqlCommand = new UniCommand(storedProcedureName, uniConnection); //sqlCommand.CommandType = CommandType.StoredProcedure; return(sqlCommand.ExecuteReader()); } catch (Exception e) { throw new Exception(string.Format("Error running SP: [{0}]", storedProcedureName), e); } }
/// <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); }
/// <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); }
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()); }
//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); }
//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[] 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()); }
//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 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()); }
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 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); } } }
public UniDataReader RunStoredProcedure(string storedProcedureName) { try { UniCommand sqlCommand = new UniCommand(storedProcedureName, uniConnection); //sqlCommand.CommandType = CommandType.StoredProcedure; return sqlCommand.ExecuteReader(); } catch (Exception e) { throw new Exception(string.Format("Error running SP: [{0}]", storedProcedureName), e); } }
public UniDataReader RunQuerySQL(string sql) { try { UniCommand sqlCommand = new UniCommand(sql, uniConnection); return sqlCommand.ExecuteReader(); } catch (Exception e) { throw new Exception(string.Format("Error running SQL: [{0}]", sql), e); } }
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); }
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(); }