/// <summary>
/// DB 저장 - 공통
/// </summary>
/// <param name="tableName"></param>
/// <param name="dt"></param>
public void DB_insert(string tableName, DataTable dt)
{
string MyconnectString = "server=127.0.0.1; port=3306 ; database= investment ; uid=root ; pwd= 5587 ; SslMode=none";
logDTO = new PuffAdderApplication.DTO.LogDTO();
logDTO.setDbNm(tableName);
logDTO.setFuncNm(MethodBase.GetCurrentMethod().Name);
MySqlConnection conn = new MySqlConnection(MyconnectString);
conn.Open();
try
{
switch (tableName)
{
case "price":
for (int i = 0; i < dt.Rows.Count; i++)
{
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO price" +
" (CATE_CD" +
", TRADE_DT" +
", ST_PRICE" +
", HIGH_PRICE" +
", LOW_PRICE" +
", END_PRICE" +
", TRADE_SUM) " +
"VALUES(@CATE_CD" +
", @TRADE_DT" +
", @ST_PRICE" +
", @HIGH_PRICE" +
", @LOW_PRICE" +
", @END_PRICE" +
", @TRADE_SUM) " +
"ON DUPLICATE KEY " +
"UPDATE ST_PRICE = @ST_PRICE" +
", HIGH_PRICE = @HIGH_PRICE" +
", LOW_PRICE = @LOW_PRICE" +
", END_PRICE = @END_PRICE" +
", TRADE_SUM = @TRADE_SUM";
logDTO.setCateCd(dt.Rows[i]["cateCd"].ToString());
logDTO.setTradeDt(dt.Rows[i]["tradeDt"].ToString());
comm.Parameters.AddWithValue("@CATE_CD", dt.Rows[i]["cateCd"]);
comm.Parameters.AddWithValue("@TRADE_DT", dt.Rows[i]["tradeDt"]);
comm.Parameters.AddWithValue("@ST_PRICE", dt.Rows[i]["stPrice"]);
comm.Parameters.AddWithValue("@HIGH_PRICE", dt.Rows[i]["highPrice"]);
comm.Parameters.AddWithValue("@LOW_PRICE", dt.Rows[i]["lowPrice"]);
comm.Parameters.AddWithValue("@END_PRICE", dt.Rows[i]["endPrice"]);
comm.Parameters.AddWithValue("@TRADE_SUM", dt.Rows[i]["tradeSum"]);
comm.ExecuteNonQuery();
}
break;
case "categ":
for (int i = 0; i < dt.Rows.Count; i++)
{
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO categ" +
" (CATE_CD" +
", CATE_NM" +
", MARKET" +
", INDU_BASIC" +
", INDU_DTL" +
", VALD_YN" +
", ST_DT" +
", END_DT) " +
"VALUES(@CATE_CD" +
", @CATE_NM" +
", @MARKET" +
", @INDU_BASIC" +
", @INDU_DTL" +
", @VALD_YN" +
", @ST_DT" +
", @END_DT) " +
"ON DUPLICATE KEY " +
"UPDATE CATE_NM = @CATE_NM" +
", MARKET = @MARKET" +
", INDU_BASIC = @INDU_BASIC" +
", INDU_DTL = @INDU_DTL" +
", VALD_YN = @VALD_YN" +
", ST_DT = @ST_DT" +
", END_DT = @END_DT";
comm.Parameters.AddWithValue("@CATE_CD", dt.Rows[i]["cateCd"]);
comm.Parameters.AddWithValue("@CATE_NM", dt.Rows[i]["cateNm"]);
comm.Parameters.AddWithValue("@MARKET", dt.Rows[i]["market"]);
comm.Parameters.AddWithValue("@INDU_BASIC", dt.Rows[i]["induBasic"]);
comm.Parameters.AddWithValue("@INDU_DTL", dt.Rows[i]["induDtl"]);
comm.Parameters.AddWithValue("@VALD_YN", "Y");
comm.Parameters.AddWithValue("@ST_DT", "19000101");
comm.Parameters.AddWithValue("@END_DT", "99991231");
comm.ExecuteNonQuery();
}
break;
case "trade":
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["tradeDt"] == null || dt.Rows[i]["tradeDt"].ToString() == "" || dt.Rows[i]["tradeDt"].ToString() == "0")
{
return;
}
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO trade" +
" (CATE_CD" +
", TRADE_DT" +
", ORG_SUM" +
", FOR_SUM" +
", FOR_HAVE_CNT" +
", FOR_HAVE_PNT) " +
"VALUES(@CATE_CD" +
", @TRADE_DT" +
", @ORG_SUM" +
", @FOR_SUM" +
", @FOR_HAVE_CNT" +
", @FOR_HAVE_PNT) " +
"ON DUPLICATE KEY " +
"UPDATE ORG_SUM = @ORG_SUM" +
", FOR_SUM = @FOR_SUM" +
", FOR_HAVE_CNT = @FOR_HAVE_CNT" +
", FOR_HAVE_PNT = @FOR_HAVE_PNT";
comm.Parameters.AddWithValue("@CATE_CD", dt.Rows[i]["cateCd"]);
comm.Parameters.AddWithValue("@TRADE_DT", dt.Rows[i]["tradeDt"]);
comm.Parameters.AddWithValue("@ORG_SUM", dt.Rows[i]["orgSum"]);
comm.Parameters.AddWithValue("@FOR_SUM", dt.Rows[i]["forSum"]);
comm.Parameters.AddWithValue("@FOR_HAVE_CNT", dt.Rows[i]["forHaveCnt"]);
comm.Parameters.AddWithValue("@FOR_HAVE_PNT", dt.Rows[i]["forHavePnt"]);
comm.ExecuteNonQuery();
}
break;
case "success":
for (int i = 0; i < dt.Rows.Count; i++)
{
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO success" +
" (CATE_CD " +
", LAST_DTM )" +
"VALUES(@CATE_CD " +
", @LAST_DTM )" +
"ON DUPLICATE KEY " +
"UPDATE LAST_DTM = @LAST_DTM";
comm.Parameters.AddWithValue("@CATE_CD", dt.Rows[i]["cateCd"]);
comm.Parameters.AddWithValue("@LAST_DTM", DateTime.Today.ToString("yyyyMMdd"));
comm.ExecuteNonQuery();
if (i == dt.Rows.Count - 1)
{
//memoLog.Text += "\n엑셀데이터 저장 완료";
}
}
break;
}
}
catch (Exception ex)
{
logDTO.setMemo(ex.Message.ToString());
DB_insert_log();
//PuffAdderApplication.Client.ActiveForm.
//memoLog.Text += "\n" + MethodBase.GetCurrentMethod().Name + "==>" + ex.Message.ToString();
}
}
