private void btConnect_Click(object sender, System.EventArgs e)
{
connection.Close();
string oldConnectionString = connection.ConnectionString;
try {
connection.ConnectionString = BuildConnectString();
config.sourceOptions.Add(new KeyValuePair <string, string>("sourceType", "Base de datos"));
config.sourceOptions.Add(new KeyValuePair <string, string>("connectionString", connection.ConnectionString));
Cursor.Current = Cursors.WaitCursor;
connection.Open();
Cursor.Current = Cursors.Default;
connection.Close();
DialogResult = DialogResult.OK;
}
catch (Exception exception) {
if (oldConnectionString != String.Empty)
{
connection.ConnectionString = oldConnectionString;
}
Cursor.Current = Cursors.Default;
retries--;
if (retries == 0)
{
DialogResult = DialogResult.Cancel;
}
MessageBox.Show(exception.Message, Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void TestConnection()
{
//if (string.IsNullOrEmpty(ConnectionInformation.DatabaseName))
// throw new DatabaseException("The database name cannot be empty.");
uniConnection.Open();
uniConnection.Close();
}
//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 static string[] GetMySQLDatabases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
bool trustedConnection = helper.UseIntegratedSecurity;
UniConnection conn;
//if (trustedConnection)
// conn = new UniConnection(string.Format("Provider=MySQL;host=server;user=root;password=root;database=myDB", serverName));
//else
//conn = new UniConnection(string.Format("Provider=MySQL;host={0};user={1};password={2};database=myDB", serverName, userName, password));
conn = new UniConnection(string.Format("Provider=MySQL;direct=true;host={0};user={1};password={2};", serverName, userName, password));
List <string> databaseNames = new List <string>();
try
{
conn.Open();
DataTable table1 = conn.GetSchema("Databases");
foreach (DataRow row in table1.Rows)
{
string dbName = row[0].ToString();
databaseNames.Add(dbName);
}
}
finally
{
conn.Close();
}
return(databaseNames.ToArray());
}
public static string[] GetFirebirdDatabases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
int port = helper.Port;
bool trustedConnection = helper.UseIntegratedSecurity;
UniConnection conn = new UniConnection(string.Format("Provider=Firebird;direct=true;Server={0};User={1};Password={2};Port={3};", serverName, userName, password, port));
List <string> databaseNames = new List <string>();
try
{
conn.Open();
DataTable table1 = conn.GetSchema("Databases");
foreach (DataRow row in table1.Rows)
{
string dbName = row[0].ToString();
databaseNames.Add(dbName);
}
}
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();
}
public void UpdateCustomersCSV(Customer customer)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("UPDATE customers SET name = :name, address_line_1 = :address_line_1, address_line_2 = :address_line_2, address_line_3 = :address_line_3, address_line_4 = :address_line_4, address_postcode = :address_postcode WHERE account = :account", con))
{
command.Parameters.Add("account", UniDbType.VarChar).Value = customer.account;
command.Parameters.Add("name", UniDbType.VarChar).Value = customer.name;
command.Parameters.Add("address_line_1", UniDbType.VarChar).Value = customer.address_line_1;
command.Parameters.Add("address_line_2", UniDbType.VarChar).Value = customer.address_line_2;
command.Parameters.Add("address_line_3", UniDbType.VarChar).Value = customer.address_line_3;
command.Parameters.Add("address_line_4", UniDbType.VarChar).Value = customer.address_line_4;
command.Parameters.Add("address_postcode", UniDbType.VarChar).Value = customer.address_postcode;
command.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
public static string[] GetFirebirdDatabases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
int port = helper.Port;
bool trustedConnection = helper.UseIntegratedSecurity;
UniConnection conn = new UniConnection(string.Format("Provider=Firebird;direct=true;Server={0};User={1};Password={2};Port={3};", serverName, userName, password, port));
List<string> databaseNames = new List<string>();
try
{
conn.Open();
DataTable table1 = conn.GetSchema("Databases");
foreach (DataRow row in table1.Rows)
{
string dbName = row[0].ToString();
databaseNames.Add(dbName);
}
}
finally
{
conn.Close();
}
return databaseNames.ToArray();
}
public void CreateProductSelection(int customerid, int productid)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("INSERT into customer_products(customer_id, product_id) VALUES (:customer_id, :product_id) returning id", con))
{
//INSERT OPERATION BELOW..
command.Parameters.Add("customer_id", UniDbType.Int).Value = customerid;
command.Parameters.Add("product_id", UniDbType.Int).Value = productid;
command.ExecuteScalar();
}
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
//INSERT OPERATIONS - ProductForm.cs
public void InsertProduct(Product product)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
using (UniCommand command = new UniCommand("INSERT into products(code, description, height, width, depth, colour, image, category) VALUES (:code, :description, :height, :width, :depth, :colour, :image, :category) returning id", con))
{
ImageConversion images = new ImageConversion();
//INSERT OPERATION BELOW..
command.Parameters.Add("code", UniDbType.VarChar).Value = product.code;
command.Parameters.Add("description", UniDbType.VarChar).Value = product.description;
command.Parameters.Add("height", UniDbType.Int).Value = product.height;
command.Parameters.Add("width", UniDbType.Int).Value = product.width;
command.Parameters.Add("depth", UniDbType.Int).Value = product.depth;
command.Parameters.Add("colour", UniDbType.Int).Value = product.colour.ToArgb();
command.Parameters.Add("image", UniDbType.VarChar).Value = images.ImageToBase64(product.image); //passing base64 string in
command.Parameters.Add("category", UniDbType.VarChar).Value = product.category;
int newProductID = (int)command.ExecuteScalar();
}
con.Close();
}
}
//UPDATE OPERATIONS - ProductCategoriesForm.cs
public void UpdateProductCategory(Category category, string old_code)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("UPDATE product_categories SET code = :code, description = :description WHERE code = :pcode", con))
{
command.Parameters.Add("code", UniDbType.VarChar).Value = category.code;
command.Parameters.Add("description", UniDbType.VarChar).Value = category.description;
command.Parameters.Add("pcode", UniDbType.VarChar).Value = old_code;
command.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
//INSERT OPERATIONS - CustomerForm.cs
public void InsertCustomer(Customer customer)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("INSERT into customers(account, name, address_line_1, address_line_2, address_line_3, address_line_4, address_postcode) VALUES (:account, :name, :address_line_1, :address_line_2, :address_line_3, :address_line_4, :address_postcode) returning id", con))
{
//INSERT OPERATION BELOW..
command.Parameters.Add("account", UniDbType.VarChar).Value = customer.account;
command.Parameters.Add("name", UniDbType.VarChar).Value = customer.name;
command.Parameters.Add("address_line_1", UniDbType.VarChar).Value = customer.address_line_1;
command.Parameters.Add("address_line_2", UniDbType.VarChar).Value = customer.address_line_2;
command.Parameters.Add("address_line_3", UniDbType.VarChar).Value = customer.address_line_3;
command.Parameters.Add("address_line_4", UniDbType.VarChar).Value = customer.address_line_4;
command.Parameters.Add("address_postcode", UniDbType.VarChar).Value = customer.address_postcode;
int newCustomerID = (int)command.ExecuteScalar();
}
}
con.Close();
}
}
public static string[] GetMySQLDatabases(ConnectionStringHelper helper)
{
string serverName = helper.ServerName;
string userName = helper.UserName;
string password = helper.Password;
bool trustedConnection = helper.UseIntegratedSecurity;
UniConnection conn;
//if (trustedConnection)
// conn = new UniConnection(string.Format("Provider=MySQL;host=server;user=root;password=root;database=myDB", serverName));
//else
//conn = new UniConnection(string.Format("Provider=MySQL;host={0};user={1};password={2};database=myDB", serverName, userName, password));
conn = new UniConnection(string.Format("Provider=MySQL;direct=true;host={0};user={1};password={2};", serverName, userName, password));
List<string> databaseNames = new List<string>();
try
{
conn.Open();
DataTable table1 = conn.GetSchema("Databases");
foreach (DataRow row in table1.Rows)
{
string dbName = row[0].ToString();
databaseNames.Add(dbName);
}
}
finally
{
conn.Close();
}
return databaseNames.ToArray();
}
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 void UpdateProductsCSV(Product product)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("UPDATE products SET description = :description, height = :height, width = :width, depth = :depth, colour = :colour, image = :image, category = :category WHERE code = :code", con))
{
ImageConversion images = new ImageConversion();
command.Parameters.Add("code", UniDbType.VarChar).Value = product.code;
command.Parameters.Add("description", UniDbType.VarChar).Value = product.description;
command.Parameters.Add("height", UniDbType.Int).Value = product.height;
command.Parameters.Add("width", UniDbType.Int).Value = product.width;
command.Parameters.Add("depth", UniDbType.Int).Value = product.depth;
command.Parameters.Add("colour", UniDbType.Int).Value = product.colour.ToArgb();
command.Parameters.Add("image", UniDbType.VarChar).Value = images.ImageToBase64(product.image); //passing base64 string in
command.Parameters.Add("category", UniDbType.VarChar).Value = product.category;
command.ExecuteNonQuery();
}
}
con.Close();
}
}
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 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);
}
}
}
//INSERT OPERATIONS - ProductCategoriesForm.cs
public void InsertProductCategory(Category category)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
using (UniCommand command = new UniCommand("INSERT into product_categories(code, description) VALUES (:code, :description)", con))
{
//INSERT OPERATION BELOW..
command.Parameters.Add("code", UniDbType.VarChar).Value = category.code;
command.Parameters.Add("description", UniDbType.VarChar).Value = category.description;
command.ExecuteNonQuery();
}
con.Close();
}
}
public void DeleteCustomer(Customer customer)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
UniCommand command = new UniCommand("DELETE FROM customers WHERE id = :customerid", con);
command.Parameters.Add("customerid", UniDbType.Int).Value = customer.id;
command.ExecuteNonQuery();
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
//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());
}
//DELETE OPERATIONS - ProductForm.cs
public void DeleteProduct(Product product)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("DELETE FROM products WHERE id = :id", con))
{
command.Parameters.Add("id", UniDbType.VarChar).Value = product.id;
command.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
//DELETE OPERATIONS - CustomerForm.cs
public void RemoveProductSelection(int customerID, int productID)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
UniCommand command = new UniCommand("DELETE FROM customer_products WHERE customer_products.customer_id = :customerID AND customer_products.product_id = :productID", con);
command.Parameters.Add("customerID", UniDbType.Int).Value = customerID;
command.Parameters.Add("productID", UniDbType.Int).Value = productID;
command.ExecuteNonQuery();
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
//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);
}
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());
}
private void ConfigTabControl_Selected(object sender, TabControlEventArgs e)
{
SourceComboBox.Text = config.sourceOptions?.FirstOrDefault(p => p.Key == "sourceType").Value;
if (ConfigTabControl.SelectedIndex == 1) //Preview Tab Selected
{
PreviewDataGridView.DataSource = null;
if (config.sourceOptions?.FirstOrDefault(p => p.Key == "sourceType").Value == "Base de datos")
{
var query = queryTextBox.Text;
if (!String.IsNullOrEmpty(query))
{
try
{
connection = new UniConnection(config.sourceOptions?.FirstOrDefault(p => p.Key == "connectionString").Value);
connection.Open();
UniCommand cmd = new UniCommand(query, connection);
UniDataAdapter uniDataAdapter = new UniDataAdapter(cmd);
DataSet dataSet = new DataSet("PreviewTable");
uniDataAdapter.Fill(dataSet, "PreviewTable");
PreviewDataGridView.DataSource = dataSet.Tables["PreviewTable"];
}
catch (Exception ex)
{
MessageBox.Show("Ocurrió un error al ejecutar la consulta. Descripción del error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
connection.Close();
}
}
}
if (config.sourceOptions?.FirstOrDefault(p => p.Key == "sourceType").Value == "Ficheros Excel")
{
MessageBox.Show("Opción en desarrollo.", "Mensaje", MessageBoxButtons.OK, MessageBoxIcon.Information);
ConfigTabControl.SelectedTab = QueryTabPage;
}
}
}
//DELETE OPERATIONS - ProductCategoriesForm.cs
public void DeleteProductCategory(Category category)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("DELETE FROM product_categories WHERE code = :code", con))
{
//INSERT OPERATION BELOW..
command.Parameters.Add("code", UniDbType.VarChar).Value = category.code;
command.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
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 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();
}
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();
}
