// 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);
}
}
// 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);
}
}
public List <string> searchbyname(string name)
{
idlist.Clear();
List <string> anamelist = new List <string>();
//CacheCommand cc = User.ADBK.ByName(adbksrc.conn);
//CacheParameter cp = new CacheParameter("Name", CacheDbType.NVarChar);
//cp.Value = name;
//cc.Parameters.Add(cp);
string SQLtext = "call sqluser.ADBK_byname(?)";
IRISCommand ic = new IRISCommand(SQLtext, adbksrc.conn);
IRISParameter ip = new IRISParameter("Name", IRISDbType.NVarChar);
ip.Value = name;
ic.Parameters.Add(ip);
//CacheDataReader reader = cc.ExecuteReader();
IRISDataReader reader = ic.ExecuteReader();
while (reader.Read())
{
string id = reader[reader.GetOrdinal("AID")].ToString();
string aname = reader[reader.GetOrdinal("ANAME")].ToString();
anamelist.Add(aname);
idlist.Add(id);
}
return(anamelist);
}
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")]);
}
}
public static void StoreStockData(IRIS irisNative, IRISConnection dbconnection)
{
// Clear global from previous runs
irisNative.Kill("^nyse");
Console.WriteLine("Storing stock data using Native API...");
// Get stock data using JDBC and write global
try {
String sql = "select top 1000 TransDate, Name, StockClose, StockOpen, High, Low, Volume from Demo.Stock";
IRISCommand cmd = new IRISCommand(sql, dbconnection);
IRISDataReader reader = cmd.ExecuteReader();
ArrayList list = new ArrayList();
string result;
while (reader.Read())
{
DateTime dt = (DateTime)reader[reader.GetOrdinal("TransDate")];
result = (string)reader[reader.GetOrdinal("Name")] +
dt.ToString("MM/dd/yyyy") +
reader[reader.GetOrdinal("High")] +
reader[reader.GetOrdinal("Low")] +
reader[reader.GetOrdinal("StockOpen")] +
reader[reader.GetOrdinal("StockClose")] +
(int)reader[reader.GetOrdinal("Volume")];
list.Add(result);
}
Console.WriteLine("Duong");
int id = list.Count;
long startConsume = DateTime.Now.Ticks;
for (int i = 0; i < id; i++)
{
irisNative.Set(list[i], "^nyse", i + 1);
}
long totalConsume = DateTime.Now.Ticks - startConsume;
Console.WriteLine("Stored natively successfully. Execution time: " + totalConsume / TimeSpan.TicksPerMillisecond + " ms");
}
catch (Exception e) {
Console.WriteLine("Error either retrieving data using JDBC or storing to globals: " + e);
}
}
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 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: 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);
}
}
public List <string> searchbyname(string name)
{
//CacheCommand cc;
IRISCommand ic;
idlist.Clear();
List <string> anamelist = new List <string>();
try
{
//cc = new CacheCommand("Select AID, ANAME from ADBK WHERE ANAME %startswith ?", adbksrc.conn);
//CacheParameter cp = new CacheParameter("Name", CacheDbType.NVarChar);
//cp.Value = name;
//cc.Parameters.Add(cp);
//CacheDataReader reader = cc.ExecuteReader();
ic = new IRISCommand("Select AID, ANAME from ADBK WHERE ANAME %startswith ?", adbksrc.conn);
IRISParameter ip = new IRISParameter("Name", IRISDbType.NVarChar);
ip.Value = name;
ic.Parameters.Add(ip);
IRISDataReader reader = ic.ExecuteReader();
while (reader.Read())
{
string id = reader[reader.GetOrdinal("AID")].ToString();
string aname = reader[reader.GetOrdinal("ANAME")].ToString();
anamelist.Add(aname);
idlist.Add(id);
}
return(anamelist);
}
catch (Exception err)
{
MessageBox.Show("searchbynameエラー " + err.Message);
return(anamelist);
}
//return anamelist;
}
//public void updatedatamodel(CacheDataReader reader) {
public void updatedatamodel(IRISDataReader reader)
{
try
{
while (reader.Read())
{
Age = (long)reader.GetInt32(reader.GetOrdinal("AAGE"));
City = reader[reader.GetOrdinal("ACITY")].ToString();
HomePhone = reader[reader.GetOrdinal("APHHOME")].ToString();
Name = reader[reader.GetOrdinal("ANAME")].ToString();
Street = reader[reader.GetOrdinal("ASTREET")].ToString();
WorkPhone = reader[reader.GetOrdinal("APHWORK")].ToString();
ZipCode = reader[reader.GetOrdinal("AZIP")].ToString();
dob = reader.GetDate(reader.GetOrdinal("ABTHDAY")).ToShortDateString();
id = reader[reader.GetOrdinal("AID")].ToString();
newflag = false;
}
}
catch (Exception err)
{
//MessageBox.Show("CacheDataReaderエラー " + err.Message);
MessageBox.Show("IRISDataReaderエラー " + err.Message);
}
}
public void save(string id)
{
//CacheCommand cc;
//CacheParameter cp;
//CacheTransaction ctx;
IRISCommand ic;
IRISParameter ip;
IRISTransaction itx;
try
{
//ctx = adbksrc.conn.BeginTransaction(IsolationLevel.ReadCommitted);
itx = adbksrc.conn.BeginTransaction(IsolationLevel.ReadCommitted);
if (newflag == true)
{
//cc = new CacheCommand("INSERT INTO ADBK (ANAME,ASTREET,APHHOME,APHWORK,AZIP,ABTHDAY) VALUES(?,?,?,?,?,?)", adbksrc.conn,ctx);
//cp = new CacheParameter("Name", CacheDbType.NVarChar);
//cp.Value = Name;
//cc.Parameters.Add(cp);
//cp = new CacheParameter("Street", CacheDbType.NVarChar);
//cp.Value = Street;
//cc.Parameters.Add(cp);
//cp = new CacheParameter("HomePhone", CacheDbType.NVarChar);
//cp.Value = HomePhone;
//cc.Parameters.Add(cp);
//cp = new CacheParameter("WorkPhone", CacheDbType.NVarChar);
//cp.Value = WorkPhone;
//cc.Parameters.Add(cp);
//cp = new CacheParameter("ZipCode", CacheDbType.NVarChar);
//cp.Value = ZipCode;
//cc.Parameters.Add(cp);
//cp = new CacheParameter("Dob", CacheDbType.Date);
//cp.Value = dob.ToString().Split(' ')[0].ToString();
//cc.Parameters.Add(cp);
ic = new IRISCommand("INSERT INTO ADBK (ANAME,ASTREET,APHHOME,APHWORK,AZIP,ABTHDAY) VALUES(?,?,?,?,?,?)", adbksrc.conn, itx);
ip = new IRISParameter("Name", IRISDbType.NVarChar);
ip.Value = Name;
ic.Parameters.Add(ip);
ip = new IRISParameter("Street", IRISDbType.NVarChar);
ip.Value = Street;
ic.Parameters.Add(ip);
ip = new IRISParameter("HomePhone", IRISDbType.NVarChar);
ip.Value = HomePhone;
ic.Parameters.Add(ip);
ip = new IRISParameter("WorkPhone", IRISDbType.NVarChar);
ip.Value = WorkPhone;
ic.Parameters.Add(ip);
ip = new IRISParameter("ZipCode", IRISDbType.NVarChar);
ip.Value = ZipCode;
ic.Parameters.Add(ip);
ip = new IRISParameter("Dob", IRISDbType.Date);
ip.Value = dob.ToString().Split(' ')[0].ToString();
ic.Parameters.Add(ip);
}
else
{
/*
* cc = new CacheCommand("UPDATE ADBK SET ANAME = ?,ASTREET = ?,APHHOME = ?,APHWORK = ?,AZIP = ?,ABTHDAY = ? WHERE AID = ?", adbksrc.conn,ctx);
*
* cp = new CacheParameter("Name", CacheDbType.NVarChar);
* cp.Value = Name;
* cc.Parameters.Add(cp);
* cp = new CacheParameter("Street", CacheDbType.NVarChar);
* cp.Value = Street;
* cc.Parameters.Add(cp);
* cp = new CacheParameter("HomePhone", CacheDbType.NVarChar);
* cp.Value = HomePhone;
* cc.Parameters.Add(cp);
* cp = new CacheParameter("WorkPhone", CacheDbType.NVarChar);
* cp.Value = WorkPhone;
* cc.Parameters.Add(cp);
* cp = new CacheParameter("ZipCode", CacheDbType.NVarChar);
* cp.Value = ZipCode;
* cc.Parameters.Add(cp);
* cp = new CacheParameter("Dob", CacheDbType.Date);
* cp.Value = dob.ToString().Split(' ')[0].ToString();
* cc.Parameters.Add(cp);
* cp = new CacheParameter("id", CacheDbType.NVarChar);
* cp.Value = id;
* cc.Parameters.Add(cp);
*/
ic = new IRISCommand("UPDATE ADBK SET ANAME = ?,ASTREET = ?,APHHOME = ?,APHWORK = ?,AZIP = ?,ABTHDAY = ? WHERE AID = ?", adbksrc.conn, itx);
ip = new IRISParameter("Name", IRISDbType.NVarChar);
ip.Value = Name;
ic.Parameters.Add(ip);
ip = new IRISParameter("Street", IRISDbType.NVarChar);
ip.Value = Street;
ic.Parameters.Add(ip);
ip = new IRISParameter("HomePhone", IRISDbType.NVarChar);
ip.Value = HomePhone;
ic.Parameters.Add(ip);
ip = new IRISParameter("WorkPhone", IRISDbType.NVarChar);
ip.Value = WorkPhone;
ic.Parameters.Add(ip);
ip = new IRISParameter("ZipCode", IRISDbType.NVarChar);
ip.Value = ZipCode;
ic.Parameters.Add(ip);
ip = new IRISParameter("Dob", IRISDbType.Date);
ip.Value = dob.ToString().Split(' ')[0].ToString();
ic.Parameters.Add(ip);
ip = new IRISParameter("id", IRISDbType.NVarChar);
ip.Value = id;
ic.Parameters.Add(ip);
}
//cc.ExecuteNonQuery();
ic.ExecuteNonQuery();
if (newflag == true)
{
//cc = new CacheCommand("SELECT MAX(AID) FROM ADBK", adbksrc.conn,ctx);
//cc = new CacheCommand("SELECT LAST_IDENTITY() FROM ADBK", adbksrc.conn, ctx);
//CacheDataReader reader = cc.ExecuteReader();
ic = new IRISCommand("SELECT LAST_IDENTITY() FROM ADBK", adbksrc.conn, itx);
IRISDataReader reader = ic.ExecuteReader();
while (reader.Read())
{
string aid = reader[0].ToString();
id = aid;
//cc = new CacheCommand("Select AID, ACITY, ANAME, ABTHDAY, ASTREET , APHHOME , APHWORK, AAGE, AZIP from ADBK WHERE AID = ?", adbksrc.conn,ctx);
//CacheParameter cp2 = new CacheParameter("id", CacheDbType.NVarChar);
//cp2.Value = aid;
//cc.Parameters.Add(cp2);
//CacheDataReader cr = cc.ExecuteReader();
//updatedatamodel(cr);
ic = new IRISCommand("Select AID, ACITY, ANAME, ABTHDAY, ASTREET , APHHOME , APHWORK, AAGE, AZIP from ADBK WHERE AID = ?", adbksrc.conn, itx);
IRISParameter ip2 = new IRISParameter("id", IRISDbType.NVarChar);
ip2.Value = aid;
ic.Parameters.Add(ip2);
IRISDataReader ir = ic.ExecuteReader();
updatedatamodel(ir);
}
}
//ctx.Commit();
itx.Commit();
}
catch (Exception err)
{
MessageBox.Show("保存エラー " + err.Message);
}
}