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();
}
}
/// <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);
}
/// <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 <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 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[] 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 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();
}
}
//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();
}
}
private void ExecuteCommand(U2Connection con)
{
try
{
if (settings.AccessMode == "Native")
{
// Native mode
UniSession us1 = con.UniSession;
UniCommand uniCmd = us1.CreateUniCommand();
uniCmd.Command = settings.CommandText;
uniCmd.Execute();
// Get response string but not output
string strNative = uniCmd.Response;
}
else
{
// SQL mode
U2Command cmd = con.CreateCommand();
cmd.Connection = con;
cmd.CommandText = settings.CommandText;
cmd.ExecuteNonQuery();
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
}
}
// constructor
public U2DataReader(UniSession uSession, string fileName)
{
_uSession = uSession;
try
{
uFile = _uSession.CreateUniFile(fileName);
UniCommand uCmd = _uSession.CreateUniCommand();
uCmd.Command = string.Format("SELECT {0} BY @ID SAMPLED 100", uFile.FileName);
uCmd.Execute();
usl = _uSession.CreateUniSelectList(0);
_keySample = usl.ReadListAsStringArray();
RecordsAffected = 0;
}
catch (UniSessionException e) // unisession file not exists
{
if (e.ErrorCode == 14002)
{
Console.WriteLine("U2 file not found");
}
else
{
// dont know, so rethrow
throw;
}
}
}
//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();
}
}
//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);
}
//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 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);
}
private static CssCommandResult MakeMultiNote(AccountList list, Note note, CssCredentials cssCredentials)
{
var result = new CssCommandResult();
var lHostName = _cssHostname ?? cssCredentials.Hostname;
var lAccount = _cssAccount ?? cssCredentials.Account;
var lUser = _cssUserName ?? cssCredentials.User;
var lPassword = _cssUserPassword ?? cssCredentials.UserPassword;
const string lServiceType = CssServiceType;
UniSession us = null;
try
{
Console.WriteLine($"{lHostName}, {lUser}, {lAccount}, {lServiceType}");
us = UniObjects.OpenSession(lHostName, lUser, lPassword, lAccount, lServiceType);
UniCommand cmd = us.CreateUniCommand();
cmd.Command = $"GET-LIST {list.AccountListName}";
cmd.Execute();
cmd.Command = "MULTI.NOTES";
cmd.Execute();
cmd.Reply($"{note.Message}");
cmd.Reply("AGHERRERA");
cmd.Reply("G");
int lineNumber = 0;
foreach (var line in cmd.Response.Split("\r\n"))
{
result.Results.Add(new CommandResponse($"Response Line: {++lineNumber}", line));
}
return(result);
}
catch (Exception ex)
{
result.Results.Add(new CommandResponse("Exception", ex.ToString()));
}
finally
{
if (us != null && us.IsActive)
{
UniObjects.CloseSession(us);
}
}
result.Results.Add(new CommandResponse("Error", "There was an error procesing your request."));
return(result);
}
private static CssCommandResult Filter(string indexFile, string developerCode, string filterCriteria,
AccountList saveListName, CssCredentials cssCredentials)
{
var result = new CssCommandResult();
var lHostName = _cssHostname ?? cssCredentials.Hostname;
var lAccount = _cssAccount ?? cssCredentials.Account;
var lUser = _cssUserName ?? cssCredentials.User;
var lPassword = _cssUserPassword ?? cssCredentials.UserPassword;
const string lServiceType = CssServiceType;
UniSession us = null;
try
{
Console.WriteLine($"{lHostName}, {lUser}, {lAccount}, {lServiceType}");
//get the session object
us = UniObjects.OpenSession(lHostName, lUser, lPassword, lAccount, lServiceType);
UniCommand cmd = us.CreateUniCommand();
cmd.Command = $"SELECT {indexFile} WITH DEV.CODE = \"{developerCode}\"";
cmd.Execute();
result.Results.Add(new CommandResponse(cmd.Command, cmd.Response));
cmd.Command = $"QSELECT {indexFile}";
cmd.Execute();
result.Results.Add(new CommandResponse(cmd.Command, cmd.Response));
cmd.Command = filterCriteria;
cmd.Execute();
result.Results.Add(new CommandResponse(cmd.Command, cmd.Response));
cmd.Command = $"SAVE-LIST {saveListName}";
cmd.Execute();
result.Results.Add(new CommandResponse(cmd.Command, cmd.Response));
return result;
}
catch (Exception ex)
{
result.Results.Add(new CommandResponse("Exception", ex.ToString()));
}
finally
{
if (us != null && us.IsActive)
{
UniObjects.CloseSession(us);
}
}
result.Results.Add(new CommandResponse("Error", "There was an error procesing your request."));
return result;
}
public void RunNonQuerySQL(string sql)
{
try
{
UniCommand sqlCommand = new UniCommand(sql, uniConnection);
sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SQL: [{0}]", sql), e);
}
}
private static CssCommandResult CustomGetList(IEnumerable<string> accountList, AccountList saveListName, CssCredentials cssCredentials)
{
var result = new CssCommandResult();
var lHostName = _cssHostname ?? cssCredentials.Hostname;
var lAccount = _cssAccount ?? cssCredentials.Account;
var lUser = _cssUserName ?? cssCredentials.User;
var lPassword = _cssUserPassword ?? cssCredentials.UserPassword;
const string lServiceType = CssServiceType;
UniSession us = null;
try
{
Console.WriteLine($"{lHostName}, {lUser}, {lAccount}, {lServiceType}");
//get the session object
us = UniObjects.OpenSession(lHostName, lUser, lPassword, lAccount, lServiceType);
UniCommand cmd = us.CreateUniCommand();
cmd.Command = $"EDIT-LIST {saveListName.AccountListName}";
cmd.Execute();
result.Results.Add(new CommandResponse(cmd.Command, cmd.Response));
cmd.Reply("I"); //Get list ready for insert
foreach (var acct in accountList)
{
if (!acct.Contains("=")) continue;
var account = acct.Split("=")[1].Replace("]","");
cmd.Reply($"{account}");
}
cmd.Reply(""); // Send empty marker to close list
cmd.Reply("FI"); // Save list
result.Results.Add(new CommandResponse(cmd.CommandStatus.ToString(), cmd.Response));
return result;
}
catch (Exception ex)
{
result.Results.Add(new CommandResponse("Exception", ex.ToString()));
}
finally
{
if (us != null && us.IsActive)
{
UniObjects.CloseSession(us);
}
}
result.Results.Add(new CommandResponse("Error", "There was an error procesing your request."));
return result;
}
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);
}
}
public void RunStoredProcedureNonQuery(string storedProcedureName)
{
try
{
UniCommand sqlCommand = new UniCommand(storedProcedureName, uniConnection);
//sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SP: [{0}]", storedProcedureName), e);
}
}
public Boolean Execute(String cmd)
{
lock (_syncCall) {
try {
UniCommand c = _sess.CreateUniCommand();
c.Command = cmd;
c.Execute();
} catch (Exception ex) {
_lastError = ex.Message;
return(false);
}
}
return(true);
}
public DataTableCollection RunQueryMultipleResults(string sql)
{
try
{
UniCommand sqlCommand = new UniCommand(sql, uniConnection);
DataTable dataTable = new DataTable();
DataSet ds = new DataSet();
UniDataAdapter sqlDataAdapter = new UniDataAdapter(sqlCommand);
sqlDataAdapter.Fill(ds);
return(ds.Tables);
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SQL: [{0}]", sql), e);
}
}
static void Main(string[] args)
{
try
{
U2ConnectionStringBuilder conn_str = new U2ConnectionStringBuilder();
conn_str.UserID = "user";
conn_str.Password = "******";
conn_str.Server = "localhost";
conn_str.Database = "demo";
conn_str.ServerType = "UNIDATA";
conn_str.AccessMode = "Native"; // FOR UO
conn_str.RpcServiceType = "udcs"; // FOR UO
conn_str.Pooling = false;
string s = conn_str.ToString();
U2Connection con = new U2Connection();
con.ConnectionString = s;
con.Open();
Console.WriteLine("Connected.........................");
UniSession us1 = con.UniSession;
UniCommand cmd = us1.CreateUniCommand();
cmd.Command = "LIST STUDENT ALL SAMPLE 10";
cmd.Execute();
string response_str = cmd.Response;
Console.WriteLine("UniCommand Output" + response_str + Environment.NewLine);
con.Close();
}
catch (Exception e)
{
string s = e.Message;
if (e.InnerException != null)
{
s += e.InnerException.Message;
}
Console.WriteLine(s);
}
finally
{
Console.WriteLine("Enter to exit:");
string line = Console.ReadLine();
}
}
/// <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 DataTable RunQueryDataTable(string sql)
{
try
{
UniCommand sqlCommand = new UniCommand(sql, uniConnection);
DataTable dataTable = new DataTable();
DataSet ds = new DataSet();
UniDataAdapter sqlDataAdapter = new UniDataAdapter(sqlCommand);
//sqlDataAdapter.Fill(dataTable, null); // Just pass the DataTable into the SqlDataAdapters Fill Method
sqlDataAdapter.Fill(ds);
return(ds.Tables[0]);
//return dataTable;
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SQL: [{0}]", sql), 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();
}
}
//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());
}
//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();
}
}
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 void RunStoredProcedureNonQuery(string storedProcedureName)
{
try
{
UniCommand sqlCommand = new UniCommand(storedProcedureName, uniConnection);
//sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SP: [{0}]", storedProcedureName), 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);
}
}
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 DataTable RunQueryDataTable(string sql)
{
try
{
UniCommand sqlCommand = new UniCommand(sql, uniConnection);
DataTable dataTable = new DataTable();
DataSet ds = new DataSet();
UniDataAdapter sqlDataAdapter = new UniDataAdapter(sqlCommand);
//sqlDataAdapter.Fill(dataTable, null); // Just pass the DataTable into the SqlDataAdapters Fill Method
sqlDataAdapter.Fill(ds);
return ds.Tables[0];
//return dataTable;
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SQL: [{0}]", sql), e);
}
}
public void RunNonQuerySQL(string sql)
{
try
{
UniCommand sqlCommand = new UniCommand(sql, uniConnection);
sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SQL: [{0}]", sql), 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();
}
public DataTableCollection RunQueryMultipleResults(string sql)
{
try
{
UniCommand sqlCommand = new UniCommand(sql, uniConnection);
DataTable dataTable = new DataTable();
DataSet ds = new DataSet();
UniDataAdapter sqlDataAdapter = new UniDataAdapter(sqlCommand);
sqlDataAdapter.Fill(ds);
return ds.Tables;
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SQL: [{0}]", sql), e);
}
}
