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