private CorrelationInfo_paraViewModel makeCorrInfo(List <string> underCodeList) { QueryStr queryStr = new QueryStr(this.corrQueryInfo_); StringBuilder sb = new StringBuilder(); for (int i = 0; i < underCodeList.Count; i++) { sb.Append(QueryStr.wrapComma(underCodeList[i])); if (i != underCodeList.Count - 1) { sb.Append(","); } } queryStr.VariableMap_.Add("STD_DATE", this.ReferenceDate_.ToString("yyyyMMdd")); queryStr.VariableMap_.Add("UNDER_LIST", sb.ToString()); conn_.addQuery("underCorrInfo", queryStr.getQuery()); conn_.excute(); DataTable tb = conn_.ResultDataSet_.Tables["underCorrInfo"]; List <string> first = new List <string>(); List <string> second = new List <string>(); List <string> values = new List <string>(); foreach (DataRow item in tb.Rows) { first.Add(item["STOCK_ID"].ToString()); second.Add(item["OTHER_ID"].ToString()); values.Add(item["CORR"].ToString()); } CorrelationInfo_paraViewModel corrInfoVM = new CorrelationInfo_paraViewModel(); corrInfoVM.Dimension_ = underCodeList.Count.ToString(); //Int32 dimensionTrans = underCodeList.Count; //Int32 needCorrNum = dimensionTrans * (dimensionTrans + 1) / 2; for (int i = 0; i < values.Count; i++) { corrInfoVM.Correlation_paraViewModel_.Add(this.makeCorr(first[i], second[i], values[i])); } return(corrInfoVM); }
public void addInstDB(InstPositionWrapperVM wrapperVM) { SqlCeConnection connection = new SqlCeConnection("Data Source=D:\\Project File\\OTCDerivativesCalculatorModule\\Project_CSharp\\RiskMonitor\\Data\\Data_Interface\\MarketDataBase.sdf"); try { DataTable tb = new DataTable(); SqlCeCommand command = connection.CreateCommand(); connection.Open(); SqlCeCommand delCommand = connection.CreateCommand(); //delCommand.CommandText = "DELETE FROM CORRELATION WHERE DATE ='" + ReferenceDate_.ToString("yyyyMMdd") + "'"; //delCommand.ExecuteNonQuery(); string KR_CODE = QueryStr.wrapComma(wrapperVM.KRCode_); string INST_NAME = QueryStr.wrapComma(wrapperVM.InstName_); string INST_CODE = QueryStr.wrapComma(wrapperVM.InstCode_); string BOOKED_DATE = QueryStr.wrapComma(wrapperVM.BookedDate_); string UNBOOKED_DATE = QueryStr.wrapComma(wrapperVM.UnBookedDate_); string MATURITY_DATE = QueryStr.wrapComma(wrapperVM.MaturityDate_); string INST_TYPE = QueryStr.wrapComma(wrapperVM.InstType_); string FILE_NAME = QueryStr.wrapComma(wrapperVM.FileName_); string POSITION_NAME = QueryStr.wrapComma(wrapperVM.PositionName_); string COUNTERPARTY = QueryStr.wrapComma(wrapperVM.CounterParty_); string NOTIONAL = QueryStr.wrapComma(wrapperVM.Notional_); string CURRENCY = QueryStr.wrapComma(wrapperVM.Currency_); string FUND_CODE = QueryStr.wrapComma(wrapperVM.FundCode_); string FUND_NAME = QueryStr.wrapComma(wrapperVM.FundName_); command.CommandText = "INSERT INTO POSITIONMASTER (KR_CODE,INST_NAME,INST_CODE,BOOKED_DATE,UNBOOKED_DATE," + " MATURITY_DATE,INST_TYPE,FILE_NAME,POSITION_NAME,COUNTERPARTY," + " NOTIONAL,CURRENCY,FUND_CODE,FUND_NAME) " + " VALUES (" + KR_CODE + "," + INST_NAME + "," + INST_CODE + "," + BOOKED_DATE + "," + UNBOOKED_DATE + "," + MATURITY_DATE + "," + INST_TYPE + "," + FILE_NAME + "," + POSITION_NAME + "," + COUNTERPARTY + "," + NOTIONAL + "," + CURRENCY + "," + FUND_CODE + "," + FUND_NAME + ")"; //command.ExecuteNonQuery(); //command.Parameters.Add("@pdate", SqlDbType.NVarChar); //command.Parameters.Add("@pcode_a", SqlDbType.NVarChar); //command.Parameters.Add("@pname_a", SqlDbType.NVarChar); //command.Parameters.Add("@pcode_b", SqlDbType.NVarChar); //command.Parameters.Add("@pname_b", SqlDbType.NVarChar); //command.Parameters.Add("@pcorr", SqlDbType.NVarChar); //command.Parameters.Add("@ptype", SqlDbType.NVarChar); //command.Parameters.Add("@pvendor", SqlDbType.NVarChar); //command.Parameters.Add("@pdescription", SqlDbType.NVarChar); //foreach (DataRow item in tb.Rows) //{ // string code_a = item["STOCK_ID"].ToString(); // string code_b = item["OTHER_ID"].ToString(); // if (code_a != code_b) // { // command.Parameters["@pdate"].Value = item["STD_DATE"].ToString(); // command.Parameters["@pcode_a"].Value = code_a; // command.Parameters["@pname_a"].Value = "null"; // command.Parameters["@pcode_b"].Value = code_b; // command.Parameters["@pname_b"].Value = "null"; // command.Parameters["@pcorr"].Value = item["CORR"].ToString(); // command.Parameters["@ptype"].Value = "History"; // command.Parameters["@pvendor"].Value = "MRO"; // command.Parameters["@pdescription"].Value = "This is test"; // command.ExecuteNonQuery(); // } //} command.ExecuteNonQuery(); connection.Close(); } catch (SqlCeException e) { connection.Close(); OutputLogViewModel.addResult("DataBase Connection Error : MRO DB Load"); throw; } }
public void addInstDB(string tableName, List <string> fieldName, List <string> fieldValue) { // error fieldName Count != fieldValue; // table의 columnNum 와 filedNameCount 는 같아야함 int fieldNameNum = fieldName.Count; SqlCeConnection connection = new SqlCeConnection(@"Data Source=D:\Project File\OTCDerivativesCalculatorModule\ExcelOTCManagerAddIn\PositionMasterInformationDB.sdf"); List <string> wrappingFieldValue = new List <string>(); foreach (var item in fieldValue) { wrappingFieldValue.Add(QueryStr.wrapComma(item)); } try { StringBuilder sb = new StringBuilder(); DataTable tb = new DataTable(); SqlCeCommand command = connection.CreateCommand(); connection.Open(); SqlCeCommand delCommand = connection.CreateCommand(); sb.Append("INSERT INTO " + tableName + " ( "); for (int i = 0; i < fieldNameNum; i++) { sb.Append(fieldName[i]); if (i != fieldNameNum - 1) { sb.Append(", "); } } sb.Append(") "); sb.Append("VALUES ("); for (int i = 0; i < fieldNameNum; i++) { sb.Append(wrappingFieldValue[i]); if (i != fieldNameNum - 1) { sb.Append(", "); } } sb.Append(") "); command.CommandText = sb.ToString(); command.ExecuteNonQuery(); connection.Close(); } catch (SqlCeException e) { connection.Close(); OutputLogViewModel.addResult("DataBase Connection Error : MRO DB Load"); throw; } }