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); } }