// Helper method: Find top 10 stocks on a particular date public static void FindTopOnDate(IRISADOConnection dbconnection, String onDate) { try { String sql = "SELECT distinct top 10 TransDate,Name,StockClose,StockOpen,High,Low,Volume FROM Demo.Stock WHERE TransDate=? ORDER BY stockclose desc"; IRISCommand cmd = new IRISCommand(sql, dbconnection); cmd.Parameters.AddWithValue("TransDate", Convert.ToDateTime(onDate)); IRISDataReader reader = cmd.ExecuteReader(); Console.WriteLine("Date\t\tName\tOpening Price\tDaily High\tDaily Low\tClosing Price\tVolume"); while (reader.Read()) { DateTime date = (DateTime)reader[reader.GetOrdinal("TransDate")]; decimal open = (decimal)reader[reader.GetOrdinal("StockOpen")]; decimal high = (decimal)reader[reader.GetOrdinal("High")]; decimal low = (decimal)reader[reader.GetOrdinal("Low")]; decimal close = (decimal)reader[reader.GetOrdinal("StockClose")]; int volume = (int)reader[reader.GetOrdinal("Volume")]; String name = (string)reader[reader.GetOrdinal("Name")]; Console.WriteLine(date.ToString("MM/dd/yyyy") + "\t" + name + "\t" + open + "\t" + high + "\t" + low + "\t" + close + "\t" + volume); } } catch (Exception e) { Console.WriteLine(e); } }
// Task 7: View your Portfolio to see how much you gain/loss // Note: Choose option 3 to add 2 or 3 stocks to your portfolio (using names from top 10 and 2016-08-12 as date); then // choose option 6 using date 2017-08-10 to view your % Gain or Loss after a year. public static void Task7(IRISADOConnection connect) { Console.WriteLine("Selling on which date? "); String sellDate = Console.ReadLine(); PortfolioProfile(connect, sellDate); }
// Helper method: Update item in Portfolio Table public static void UpdateStock(IRISADOConnection dbconnection, String stockname, String price, String transDate, int shares) { DateTime t = DateTime.Now; try { String sql = "UPDATE Demo.Portfolio SET purchaseDate = ?, purchasePrice= ?, shares = ?, DateTimeUpdated= ? WHERE name= ?"; IRISCommand cmd = new IRISCommand(sql, dbconnection); cmd.Parameters.AddWithValue("PurchaseDate", Convert.ToDateTime(transDate)); cmd.Parameters.AddWithValue("PurchasePrice", price); cmd.Parameters.AddWithValue("Shares", shares); cmd.Parameters.AddWithValue("DateTimeUpdated", t); cmd.Parameters.AddWithValue("Name", stockname); int count = cmd.ExecuteNonQuery(); if (count > 0) { Console.WriteLine(stockname + " updated."); } else { Console.WriteLine(stockname + " not found"); } } catch (Exception e) { Console.WriteLine("Error updating " + stockname + " : " + e); } }
// Task 2: View top 10 stocks for selected date // Note: Choose 2016/08/12 for date public static void Task2(IRISADOConnection connect) { Console.WriteLine("On which date? (YYYY/MM/DD) "); String queryDate = Console.ReadLine(); FindTopOnDate(connect, queryDate); }
// Display all airports using ADO.NET public static void getAirports(IRISADOConnection connection) { try { // This query uses a special shorthand notation (->, known as an implicit join) // to retrieve data from a related table without requiring you to think about how to join tables String sql = "SELECT name, code, location->city, location->state, location->zip FROM demo.airport"; IRISCommand cmd = new IRISCommand(sql, connection); IRISDataReader reader = cmd.ExecuteReader(); Console.WriteLine("Name\t\t\t\t\tCode\t\tLocation"); while (reader.Read()) { String name = (String)reader[reader.GetOrdinal("name")]; String code = (String)reader[reader.GetOrdinal("code")]; String city = (String)reader[reader.GetOrdinal("city")]; String state = (String)reader[reader.GetOrdinal("state")]; String zip = (String)reader[reader.GetOrdinal("zip")]; Console.WriteLine(name + "\t\t" + code + "\t\t" + city + ", " + state + " " + zip); } } catch (Exception e) { Console.WriteLine(e); } }
// Task 6: Delete item from Portfolio table public static void Task6(IRISADOConnection connect) { Console.WriteLine("Which stock would you like to remove? "); String removeName = Console.ReadLine(); DeleteStock(connect, removeName); }
public static void Task2(IRISADOConnection connection){ String sql = "SELECT distinct name FROM demo.stock"; IRISCommand cmd = new IRISCommand(sql, connection); IRISDataReader reader = cmd.ExecuteReader(); while(reader.Read()){ Console.WriteLine(reader[reader.GetOrdinal("Name")]); } }
static void Main(string[] args) { try { IRISADOConnection connection = InterSystems.Data.IRISClient.Gateway.Gateway.GetConnection("Server=localhost; Port=51774; Namespace=Ensemble; Password=SYS; User ID=_SYSTEM; SharedMemory=false"); InterSystems.EnsLib.PEX.BusinessService service = InterSystems.EnsLib.PEX.Director.CreateBusinessService(connection, "Demo.PEX.NonPollingBusinessService"); string response = (string)service.ProcessInput(String.Join(" ", args)); Console.WriteLine("\r\n" + response); } catch (Exception ex) { Console.Write(ex); } }
// Helper method: Create Portfolio Table public static void CreatePortfolioTable(IRISADOConnection dbconnection) { String createTable = "CREATE TABLE Demo.Portfolio(Name varchar(50) unique, PurchaseDate date, PurchasePrice numeric(10,4), Shares int, DateTimeUpdated DateTime)"; try { IRISCommand cmd = new IRISCommand(createTable, dbconnection); cmd.ExecuteNonQuery(); Console.WriteLine("Created Demo.Portfolio table successfully."); } catch (Exception e) { Console.WriteLine("Table not created and likely already exists."); } }
static void Main(String[] args) { // If you are using a remote instance, update IP and password here String user = "******"; String password = "******"; String IP = "localhost"; int port = 51773; try { // Connect to database using EventPersister, which is based on IRISDataSource // For more details on EventPersister, visit // https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=BNETXEP_xep EventPersister xepPersister = PersisterFactory.CreatePersister(); xepPersister.Connect(IP, port, "User", user, password); Console.WriteLine("Connected to InterSystems IRIS"); xepPersister.DeleteExtent("Demo.Airport"); // Remove old test data xepPersister.ImportSchemaFull("Demo.Airport"); // Import flat schema // Create XEP Event for object access Event xepEvent = xepPersister.GetEvent("Demo.Airport"); // Create IRIS Native object IRISADOConnection connection = (IRISADOConnection)xepPersister.GetAdoNetConnection(); IRIS irisNative = IRIS.CreateIRIS(connection); Console.WriteLine("Generating airport table..."); // Populate 5 airport objects and save to the database using XEP populateAirports(xepEvent); // Get all airports using ADO.NET getAirports(connection); // Store natively - Uncomment the following line for task 3 // StoreAirfare(irisNative); Console.ReadLine(); // Close everything xepEvent.Close(); xepPersister.Close(); } catch (Exception e) { Console.WriteLine("Error creating airport listing: " + e); } }
// Task 5: Update item in Portfolio table public static void Task5(IRISADOConnection connect) { Console.WriteLine("Which stock would you like to update? "); String stockName = Console.ReadLine(); Console.WriteLine("New Price: "); String updatePrice = Console.ReadLine(); Console.WriteLine("New Date: "); String updateDate = Console.ReadLine(); Console.WriteLine("New number of shares: "); String upShare = Console.ReadLine(); int updateShares; Int32.TryParse(upShare, out updateShares); UpdateStock(connect, stockName, updatePrice, updateDate, updateShares); }
public static void Task4(IRISADOConnection connection, IRIS native, Event xepEvent){ String sql = "SELECT distinct name FROM demo.stock"; IRISCommand cmd = new IRISCommand(sql, connection); IRISDataReader reader = cmd.ExecuteReader(); var array = new List<StockInfo>(); while(reader.Read()){ StockInfo stock = new StockInfo(); stock.name = (string) reader[reader.GetOrdinal("Name")]; Console.WriteLine("created stockinfo array."); // Generate mission and founder names (Native API) stock.founder = native.ClassMethodString("%PopulateUtils", "Name"); stock.mission = native.ClassMethodString("%PopulateUtils", "Mission"); Console.WriteLine("Adding object with name " + stock.name + " founder " + stock.founder + " and mission " + stock.mission); array.Add(stock); } xepEvent.Store(array.ToArray()); }
// Task 4: Add item to Portfolio table // Note: We recommend choosing stock name using list of stocks generated by Task 2 public static void Task4(IRISADOConnection connect) { Console.WriteLine("Name: "); String name = Console.ReadLine(); Console.WriteLine("Date: "); String tDate = Console.ReadLine(); Console.WriteLine("Price: "); String price = Console.ReadLine(); Console.WriteLine("Number of shares: "); String share = Console.ReadLine(); int shares; Int32.TryParse(share, out shares); AddPortfolioItem(connect, name, tDate, price, shares); }
static void Main(string[] args) { String ip = "localhost"; int port = 51773; String username = "******"; String password = "******"; String Namespace = "USER"; String className = "myApp.StockInfo"; try { // Connect to database using EventPersister EventPersister xepPersister = PersisterFactory.CreatePersister(); xepPersister.Connect(ip, port, Namespace, username, password); Console.WriteLine("Connected to InterSystems IRIS."); xepPersister.DeleteExtent(className); // remove old test data xepPersister.ImportSchema(className); // import flat schema // Create Event Event xepEvent = xepPersister.GetEvent(className); IRISADOConnection connection = (IRISADOConnection) xepPersister.GetAdoNetConnection(); IRIS native = IRIS.CreateIRIS(connection); // Task 2 // Uncomment the line below to run task 2 // Task2(connection); // Task 3 // Uncomment the line below to run task 3 // Task3(connection, xepEvent); // Task 4 // Comment out Task 2, Task 3 and uncomment the line below to run task 4 // Task4(connection, native, xepEvent); xepEvent.Close(); xepPersister.Close(); } catch (Exception e) { Console.WriteLine("Interactive prompt failed:\n" + e); } }
// Task 3: Generate sample stock info objects and stored into database using XEP public static void Task3(IRISADOConnection connection, Event xepEvent) { String sql = "SELECT distinct name FROM demo.stock"; IRISCommand cmd = new IRISCommand(sql, connection); IRISDataReader reader = cmd.ExecuteReader(); var array = new List <StockInfo>(); while (reader.Read()) { StockInfo stock = new StockInfo(); stock.name = (string)reader[reader.GetOrdinal("Name")]; Console.WriteLine("created stockinfo array."); stock.founder = "test founder"; stock.mission = "some mission statement"; Console.WriteLine("Adding object with name " + stock.name + " founder " + stock.founder + " and mission " + stock.mission); array.Add(stock); } xepEvent.Store(array.ToArray()); }
// Helper method: Add item to Portfolio Table public static void AddPortfolioItem(IRISADOConnection dbconnection, String name, String purchaseDate, String price, int shares) { DateTime t = DateTime.Now; try { String sql = "INSERT INTO Demo.Portfolio (Name, PurchaseDate, PurchasePrice, Shares, DateTimeUpdated) VALUES (?,?,?,?,?)"; IRISCommand cmd = new IRISCommand(sql, dbconnection); cmd.Parameters.AddWithValue("Name", name); cmd.Parameters.AddWithValue("PurchaseDate", Convert.ToDateTime(purchaseDate)); cmd.Parameters.AddWithValue("PurchasePrice", price); cmd.Parameters.AddWithValue("Shares", shares); cmd.Parameters.AddWithValue("DateTimeUpdated", t); cmd.ExecuteNonQuery(); Console.WriteLine("Added new line item for stock " + name + "."); } catch (Exception e) { Console.WriteLine("Error adding portfolio item: " + e); } }
// Helper method: Delete item from Portfolio Table public static void DeleteStock(IRISADOConnection dbconnection, String stockname) { try { String sql = "DELETE FROM Demo.Portfolio WHERE name = ?"; IRISCommand cmd = new IRISCommand(sql, dbconnection); cmd.Parameters.AddWithValue("Name", stockname); int count = cmd.ExecuteNonQuery(); if (count > 0) { Console.WriteLine("Deleted " + stockname + " successfully."); } else { Console.WriteLine(stockname + " not found."); } } catch (Exception e) { Console.WriteLine("Error deleting stock: " + e); } }
// Helper method: View Portfolio Table to see % gain or loss public static void PortfolioProfile(IRISADOConnection dbconnection, String sellDate) { decimal cumulStartValue = 0; decimal cumulEndValue = 0; DateTime t = DateTime.Now; try { String sql = "SELECT pf.Name, pf.PurchasePrice, pf.PurchaseDate, pf.Shares, pf.DateTimeUpdated, st.StockClose FROM Demo.Portfolio as pf JOIN Demo.Stock as st on st.Name = pf.Name WHERE st.TransDate = ?"; IRISCommand cmd = new IRISCommand(sql, dbconnection); cmd.Parameters.AddWithValue("Name", Convert.ToDateTime(sellDate)); IRISDataReader reader = cmd.ExecuteReader(); Console.WriteLine("Name" + " " + "Purchase Date" + " " + "Purchase Price" + " " + "Stock Close\tShares\tDatetime Updated\t% Change" + " " + "Gain or Loss"); while (reader.Read()) { String name = (string)reader[reader.GetOrdinal("Name")]; DateTime purchaseDate = (DateTime)reader[reader.GetOrdinal("purchaseDate")]; decimal purchasePrice = (decimal)reader[reader.GetOrdinal("PurchasePrice")]; decimal stockClose = (decimal)reader[reader.GetOrdinal("StockClose")]; int shares = (int)reader[reader.GetOrdinal("Shares")]; DateTime dateTimeUpdated = (DateTime)reader[reader.GetOrdinal("DateTimeUpdated")]; decimal percentChange = (stockClose - purchasePrice) / (purchasePrice) * 100; decimal startValue = purchasePrice * shares; decimal endValue = stockClose * shares; decimal gainOrLoss = Math.Round(endValue - startValue, 2); cumulStartValue += startValue; cumulEndValue += endValue; Console.WriteLine(name + "\t" + purchaseDate.ToString("MM/dd/yyyy") + "\t" + purchasePrice + "\t " + stockClose + "\t" + shares + "\t" + dateTimeUpdated + "\t" + String.Format("{0:0.##}", percentChange) + "\t " + gainOrLoss); } } catch (Exception e) { Console.WriteLine("Error printing portfolio information: " + e); } }
static void Main(string[] args) { String ip = "localhost"; int port = 51773; String Namespace = "USER"; String username = "******"; String password = "******"; try { // Using IRISADOConnection to connect IRISADOConnection connect = new IRISADOConnection(); // Create connection string connect.ConnectionString = "Server = " + ip + "; Port = " + port + "; Namespace = " + Namespace + "; Password = "******"; User ID = " + username; connect.Open(); Console.WriteLine("Hello World! You have successfully connected to InterSystems IRIS. Press any key to continue."); Console.ReadKey(); } catch (Exception e) { Console.WriteLine("Connection failed:\n" + e); } }
// Save trade into database using ADO.NET - which is slower than using XEP public static long StoreUsingADO(EventPersister persist, Trade[] sampleArray) { long totalTime = new long(); long startTime = DateTime.Now.Ticks; // Loop through objects to insert try { IRISDataAdapter da = new IRISDataAdapter(); String ClassName = "myApp.Trade"; IRISADOConnection con = (IRISADOConnection)persist.GetAdoNetConnection(); String SQL = "select purchaseDate, purchasePrice, stockName, shares, traderName from " + ClassName; da.SelectCommand = con.CreateCommand(); da.SelectCommand.CommandText = SQL; SQL = "INSERT INTO myApp.Trade (purchaseDate, purchasePrice, stockName, shares, traderName) VALUES (?,?,?,?,?)"; IRISCommand cmd = con.CreateCommand(); cmd.CommandText = SQL; da.InsertCommand = cmd; IRISParameter date_param = new IRISParameter("purchaseDate", IRISDbType.DateTime); cmd.Parameters.Add(date_param); date_param.SourceColumn = "purchaseDate"; IRISParameter price_param = new IRISParameter("purchasePrice", IRISDbType.Double); cmd.Parameters.Add(price_param); price_param.SourceColumn = "purchasePrice"; IRISParameter name_param = new IRISParameter("stockName", IRISDbType.NVarChar); cmd.Parameters.Add(name_param); name_param.SourceColumn = "stockName"; IRISParameter shares_param = new IRISParameter("shares", IRISDbType.Int); cmd.Parameters.Add(shares_param); shares_param.SourceColumn = "shares"; IRISParameter trader_param = new IRISParameter("traderName", IRISDbType.NVarChar); cmd.Parameters.Add(trader_param); trader_param.SourceColumn = "traderName"; da.TableMappings.Add("Table", ClassName); DataSet ds = new DataSet(); da.Fill(ds); for (int i = 0; i < sampleArray.Length; i++) { DataRow newRow = ds.Tables[0].NewRow(); newRow["purchaseDate"] = sampleArray[i].purchaseDate; newRow["purchasePrice"] = sampleArray[i].purchasePrice; newRow["stockName"] = sampleArray[i].stockName; newRow["shares"] = sampleArray[i].shares; newRow["traderName"] = sampleArray[i].traderName; ds.Tables[0].Rows.Add(newRow); } da.Update(ds); Console.WriteLine("Inserted " + sampleArray.Length + " item(s) via ADO.NET successfully."); totalTime = DateTime.Now.Ticks - startTime; } catch (Exception e) { Console.WriteLine("There was a problem storing items using ADO.NET.\n" + e); } return(totalTime / TimeSpan.TicksPerMillisecond); }
static void Main(string[] args) { Console.WriteLine("Hello World!"); // Initialize dictionary to store connection details from config.txt IDictionary <string, string> dictionary = new Dictionary <string, string>(); dictionary = generateConfig("..\\..\\..\\config.txt"); // Retrieve connection information from configuration file string ip = dictionary["ip"]; int port = Convert.ToInt32(dictionary["port"]); string Namespace = dictionary["namespace"]; string username = dictionary["username"]; string password = dictionary["password"]; try { // Using IRISADOConnection to connect IRISADOConnection connect = new IRISADOConnection(); // Create connection string connect.ConnectionString = "Server = " + ip + "; Port = " + port + "; Namespace = " + Namespace + "; Password = "******"; User ID = " + username; connect.Open(); Console.WriteLine("Connected to InterSystems IRIS."); // Starting interactive prompt bool always = true; while (always) { Console.WriteLine("1. View top 10"); Console.WriteLine("2. Create Portfolio table"); Console.WriteLine("3. Add to Portfolio"); Console.WriteLine("4. Update Portfolio"); Console.WriteLine("5. Delete from Portfolio"); Console.WriteLine("6. View Portfolio"); Console.WriteLine("7. Quit"); Console.WriteLine("What would you like to do? "); String option = Console.ReadLine(); switch (option) { // Task 2 case "1": Task2(connect); break; // Task 3 case "2": Task3(connect); break; // Task 4 case "3": Task4(connect); break; // Task 5 case "4": Task5(connect); break; // Task 6 case "5": Task6(connect); break; // Task 7 case "6": Task7(connect); break; case "7": Console.WriteLine("Exited."); always = false; break; default: Console.WriteLine("Invalid option. Try again!"); break; } } } catch (Exception e) { Console.WriteLine("Interactive prompt failed:\n" + e); } }
// Task 3: Create Portfolio Table // Note: We recommend finishing this task first before moving to the Task 4, Task 5 and Task 6 public static void Task3(IRISADOConnection connect) { Console.WriteLine("Creating table..."); CreatePortfolioTable(connect); }
static void Main(string[] args) { Console.WriteLine("Hello World!"); String ip = "localhost"; int port = 51773; String username = "******"; String password = "******"; String Namespace = "USER"; try { IRISADOConnection connect = new IRISADOConnection(); connect.ConnectionString = "Server = " + ip + "; Port = " + port + "; Namespace = " + Namespace + "; Password = "******"; User ID = " + username; connect.Open(); Console.WriteLine("Connected to InterSystems IRIS."); bool always = true; while (always) { Console.WriteLine("1. View top 10"); Console.WriteLine("2. Create Portfolio table"); Console.WriteLine("3. Add to Portfolio"); Console.WriteLine("4. Update Portfolio"); Console.WriteLine("5. Delete from Portfolio"); Console.WriteLine("6. View Portfolio"); Console.WriteLine("7. Quit"); Console.WriteLine("What would you like to do? "); String option = Console.ReadLine(); switch (option) { // Task 2 case "1": // Uncomment below line to run Task 2 // Task2(connect); break; // Task 3 case "2": // Uncomment below line to run Task 3 // Task3(connect); break; // Task 4 case "3": // Uncomment below line to run Task 4 // Task4(connect); break; // Task 5 case "4": // Uncomment below line to run Task 5 // Task5(connect); break; // Task 6 case "5": // Uncomment below line to run Task 6 // Task6(connect); break; // Task 7 case "6": // Uncomment below line to run Task 7 // Task7(connect); break; case "7": Console.WriteLine("Exited."); always = false; break; default: Console.WriteLine("Invalid option. Try again!"); break; } } } catch (Exception e) { Console.WriteLine("Interactive prompt failed:\n" + e); } }
static void Main(string[] args) { Console.WriteLine("Hello World!"); // Initialize dictionary to store connection details from config.txt IDictionary <string, string> dictionary = new Dictionary <string, string>(); dictionary = generateConfig("..\\..\\..\\config.txt"); // Retrieve connection information from configuration file string ip = dictionary["ip"]; int port = Convert.ToInt32(dictionary["port"]); string Namespace = dictionary["namespace"]; string username = dictionary["username"]; string password = dictionary["password"]; String className = "myApp.StockInfo"; try { // Connect to database using EventPersister EventPersister xepPersister = PersisterFactory.CreatePersister(); xepPersister.Connect(ip, port, Namespace, username, password); Console.WriteLine("Connected to InterSystems IRIS."); xepPersister.DeleteExtent(className); // Remove old test data xepPersister.ImportSchema(className); // Import flat schema // Create Event Event xepEvent = xepPersister.GetEvent(className); IRISADOConnection connection = (IRISADOConnection)xepPersister.GetAdoNetConnection(); IRIS native = IRIS.CreateIRIS(connection); // Starting interactive prompt bool always = true; while (always) { Console.WriteLine("1. Retrieve all stock names"); Console.WriteLine("2. Create objects"); Console.WriteLine("3. Populate properties"); Console.WriteLine("4. Quit"); Console.WriteLine("What would you like to do? "); String option = Console.ReadLine(); switch (option) { // Task 2 case "1": Task2(connection); break; // Task 3 case "2": Task3(connection, xepEvent); break; // Task 4 case "3": Task4(connection, native, xepEvent); break; case "4": Console.WriteLine("Exited."); always = false; break; default: Console.WriteLine("Invalid option. Try again!"); break; } } xepEvent.Close(); xepPersister.Close(); } catch (Exception e) { Console.WriteLine("Interactive prompt failed:\n" + e); } }