//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 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 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 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 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 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 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();
}
//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 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();
}
}
private void LinkToExistingProduct(CustomerProductSelectionForm productSelectionForm, DialogResult dr)
{
List <Product> productList = new List <Product>();
Customer customer = CustomerBindingSource.Current as Customer;
if (dr == DialogResult.OK)
{
using (UniConnection con = new UniConnection())
{
productList = productSelectionForm.GetSelectedProducts();
foreach (Product product in productList)
{
try
{
dataAccess.CreateProductSelection(customer.id, product.id); //perform an insert statement
}
catch (Exception ex)
{
MessageBox.Show("An exception occured opening a connection to the database: " + ex.Message);
}
}
LoadCustomerProducts(customer.id);
dgvCustomerProduct.CurrentRow.Selected = true;
MessageBox.Show("Selection made successfully...", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
//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();
}
}
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[] 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 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);
}
}
private MySQLDatabaseConnector(string connectionString)
{
try
{
ConnectionInformation = new ConnectionStringHelper(connectionString, DatabaseTypes.MySQL);
uniConnection = new UniConnection(ConnectionInformation.GetConnectionStringSqlClient());
DatabaseName = uniConnection.Database;
}
catch (Exception e)
{
throw new DatabaseLoaderException("Could not open database connection. See inner exception for details.", e);
}
}
public static UniConnection MakeConnection()
{
StringBuilder connectionStr = new StringBuilder("Provider=PostgreSQL");
connectionStr.Append(";host=" + "localhost");
connectionStr.Append(";port=" + 5432);
connectionStr.Append(";user="******"postgres");
connectionStr.Append(";password="******"password");
connectionStr.Append(";database=" + "RelationalCustomerProduct_db");
connectionStr.Append(";Pooling=false");
connectionStr.Append(";Unicode=true");
UniConnection conn = new UniConnection(connectionStr.ToString());
return(conn);
}
public MySQLDatabaseConnector(ConnectionStringHelper connectionStringHelper)
{
try
{
string connectionString = connectionStringHelper.GetConnectionStringSqlClient(DatabaseTypes.MySQL);
log.InfoFormat("Opening Database using connection string: {0}", connectionString);
uniConnection = new UniConnection(connectionString);
DatabaseName = uniConnection.Database;
ConnectionInformation = connectionStringHelper;
}
catch (Exception e)
{
throw new DatabaseLoaderException("Could not open database connection. See inner exception for details.", e);
}
}
//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();
}
}
private void Button5_Click(object sender, EventArgs e)
{
string sqlUserRole = "SELECT * FROM SMART_BD_USERROLE";
// string sqlOrderDetail = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";
// string sqlCustomerInsert = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";
using (var connection = new UniConnection("provider=Oracle;User Id=smart;Password=smart;Server=121.40.101.29;Direct=True;Port=1521;Sid=orcl;"))
{
var orderDetails = connection.Query <UserRole>(sqlUserRole).ToList();
// var orderDetail = connection.QueryFirstOrDefault<OrderDetail>(sqlOrderDetail, new { OrderDetailID = 1 });
// var affectedRows = connection.Execute(sqlCustomerInsert, new { CustomerName = "Mark" });
//
// Console.WriteLine(orderDetails.Count);
// Console.WriteLine(affectedRows);
//
// FiddleHelper.WriteTable(orderDetails);
// FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
}
}
/// <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);
}
//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();
}
}
//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);
}
//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[] 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());
}
private void Button6_Click(object sender, EventArgs e)
{
string sql = "INSERT INTO BD_TABLELOG(tablename,createtime) Values (@name, to_date(@time,'yyyy-MM-dd HH:mm:ss'))";
using (var connection = new UniConnection("provider=Oracle;User Id=smart;Password=smart;Server=121.40.101.29;Direct=True;Port=1521;Sid=orcl;"))
// using (var connection = new UniConnection("provider=SQL Server;Data Source=121.40.101.29;Initial Catalog=scm_main;Password=sa123;User ID=sa;"))
{
var affectedRows = connection.Execute(sql, new { name = "Mark", time = "2019-01-01 12:35:00" });
// var tableLog = new TableLog();
// tableLog.tableName = "aaa";
// tableLog.createTime=DateTime.Now;
//
//
// var identity = connection.Insert(sql);
// Only for see the Insert.
// var customer = connection.Query<Customer>("Select * FROM CUSTOMERS WHERE CustomerName = 'Mark'").ToList();
//
// FiddleHelper.WriteTable(customer);
}
}
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;
}
}
}
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());
}
//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[] 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 MySQLDatabaseConnector(ConnectionStringHelper connectionStringHelper)
{
try
{
string connectionString = connectionStringHelper.GetConnectionStringSqlClient(DatabaseTypes.MySQL);
log.InfoFormat("Opening Database using connection string: {0}", connectionString);
uniConnection = new UniConnection(connectionString);
DatabaseName = uniConnection.Database;
ConnectionInformation = connectionStringHelper;
}
catch (Exception e)
{
throw new DatabaseLoaderException("Could not open database connection. See inner exception for details.", e);
}
}
private MySQLDatabaseConnector(string connectionString)
{
try
{
ConnectionInformation = new ConnectionStringHelper(connectionString, DatabaseTypes.MySQL);
uniConnection = new UniConnection(ConnectionInformation.GetConnectionStringSqlClient());
DatabaseName = uniConnection.Database;
}
catch (Exception e)
{
throw new DatabaseLoaderException("Could not open database connection. See inner exception for details.", e);
}
}
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();
}
