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