using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; using System.IO; using System.Collections.Concurrent; using System.Diagnostics; namespace BEMSDataGateway { static class Table { public const string RawT = "BemsMonitoringPointHistory15minRawData"; public const string FiFt = "BemsMonitoringPointHistory15min"; public const string Hourly = "BemsMonitoringPointHistoryHourly"; public const string Daily = "BemsMonitoringPointHistoryDaily"; } class My_DB_DML { public string ConnDBInfo() // DB접속 정보를 읽는 함수 { Readini Ri = new Readini(); Ri.ReadData(); string strconInfo = @"Data Source = " + Ri.UserIP + "," + Ri.UserPort + "; " + "Initial Catalog =" + Ri.UserDB + "; " + "Persist Security Info=True; " + "User ID =" + Ri.UserID + "; " + "Password =" + Ri.UserPW + ""; return strconInfo; } private StringBuilder sb_delete = new StringBuilder(); public void DB_Min_Delete(IDictionary MyDic, int NowMin) { LogFileCreate LFC = new LogFileCreate(); int tmpNowMin = 0; string InsertDate1 = ""; if (NowMin == 00) { tmpNowMin = 45; InsertDate1 = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000"); } else { tmpNowMin = NowMin - 15; InsertDate1 = DateTime.Now.ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000"); } string query = ""; string[] TmpStringValue = new string[8]; string strcon = ConnDBInfo(); using (SqlConnection conn = new SqlConnection(strcon)) { conn.Open(); foreach (KeyValuePair kv in MyDic) { /// Key값과 Value값을 담기 위한 TKey, TValue string TmpPoint = kv.Key; float[] TmpValue = kv.Value; if (TmpValue[7] != 7 || TmpValue[7] != 3) // 현재값과 RAW 아니면 삭제해야함 { sb_delete.Append("DELETE FROM "); sb_delete.Append(Table.FiFt); sb_delete.Append(" WHERE SiteId = "); sb_delete.Append(TmpValue[2]); sb_delete.Append(" AND FacilityTypeId = "); sb_delete.Append(TmpValue[3]); sb_delete.Append(" AND FacilityCode = "); sb_delete.Append(TmpValue[4]); sb_delete.Append(" AND PropertyId = "); sb_delete.Append(TmpValue[5]); sb_delete.Append(" AND CreatedDateTime = '" + InsertDate1 + "'"); query = sb_delete.ToString(); SqlCommand cmd = new SqlCommand(query, conn); cmd.ExecuteNonQuery(); cmd.Dispose(); } } conn.Close(); conn.Dispose(); } sb_delete.Clear(); } public void DB_Min_Insert(IDictionary MyDic, int NowMin) { LogFileCreate LFC = new LogFileCreate(); int tmpNowMin = 0; string InsertDate1 = ""; if (NowMin == 00) { tmpNowMin = 45; InsertDate1 = DateTime.Now.AddHours(-1).ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000"); } else { tmpNowMin = NowMin - 15; InsertDate1 = DateTime.Now.ToString("yyyy-MM-dd HH:" + tmpNowMin + ":00.000"); } string query = ""; string InsertDate2 = DateTime.Now.ToString("yyyy-MM-dd HH:" + NowMin + ":00.000"); string[] TmpStringValue = new string[8]; float InsertValue = 0; string strcon = ConnDBInfo(); try { using (SqlConnection conn = new SqlConnection(strcon)) { foreach (KeyValuePair kv in MyDic) { /// Key값과 Value값을 담기 위한 TKey, TValue string TmpPoint = kv.Key; float[] TmpValue = kv.Value; if (TmpValue[7] != 7) { conn.Open(); switch (NowMin) { case 15: InsertValue = TmpValue[8]; break; case 30: InsertValue = TmpValue[9]; break; case 45: InsertValue = TmpValue[10]; break; case 00: InsertValue = TmpValue[11]; break; } if (TmpValue[7] == 2 && InsertValue < 0) //적산값인데 0보다 작으면... { InsertValue = 0; } if (TmpValue[7] == 3) // 현재값 { query = "INSERT INTO " + Table.FiFt + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) " + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + InsertDate2 + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")"; } else { query = "INSERT INTO " + Table.FiFt + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) " + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + InsertDate1 + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")"; } if (InsertDate2 == DateTime.Now.ToString("yyyy-MM-dd 00:15:00.000") && TmpValue[7] != 3) { query += "INSERT INTO " + Table.Daily + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue) " + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + Convert.ToDateTime(InsertDate2).ToString("yyyy-MM-dd 00:00:00.000") + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + "," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + "," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")"; } using (SqlCommand cmd = new SqlCommand(query, conn)) { cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); } } } conn.Dispose(); } } catch (SqlException e) { if (Form1.Enable_EnglishMode)//영어 { LFC.Log("[DBinsert.cs] DB_Min_Insert Error : " + e + Environment.NewLine + Environment.NewLine); LFC.Log("[DBinsert.cs] DB_Min_Insert Error query: " + query); } else//한글 { LFC.Log("[DBinsert.cs] DB_Min_Insert 에러 : " + e + Environment.NewLine + Environment.NewLine); LFC.Log("[DBinsert.cs] DB_Min_Insert 에러 쿼리문: " + query); } } } public void DB_Min_Insert_Now(IDictionary MyDic, int NowMin) { LogFileCreate LFC = new LogFileCreate(); string query = ""; string InsertDate2 = DateTime.Now.ToString("yyyy-MM-dd HH:" + NowMin + ":00.000"); string[] TmpStringValue = new string[8]; string strcon = ConnDBInfo(); float InsertValue = 0; using (SqlConnection conn = new SqlConnection(strcon)) { foreach (KeyValuePair kv in MyDic) { /// Key값과 Value값을 담기 위한 TKey, TValue string TmpPoint = kv.Key; float[] TmpValue = kv.Value; if (TmpValue[7] != 7) { if (TmpValue[7] == 2 || TmpValue[7] == 5) //적산이거나 적산2인 경우. { InsertValue = 0; } else if (TmpValue[7] == 6) // 현재값2인 경우 { if (TmpValue[13] > 0) { InsertValue = 1; } else InsertValue = 0; } else { InsertValue = TmpValue[13]; } conn.Open(); query = "INSERT INTO " + Table.FiFt + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) " + "VALUES(" + TmpValue[2] + "," + TmpValue[3] + "," + TmpValue[4] + "," + TmpValue[5] + ",'" + InsertDate2 + "'," + Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero) + ")"; SqlCommand cmd = new SqlCommand(query, conn); cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); } } conn.Dispose(); } } public void DB_Hour_Update(IDictionary MyDic, int NowMin, string Hour) { string query = ""; string ConnInfo = ""; string strcon = ConnDBInfo(); string HourDate = ""; string NowHourDate = ""; if (NowMin == 0) //XX시 00분일때는 Hourly의 값이 XX-1에 들어가야함. { HourDate = Convert.ToDateTime(Hour).AddHours(-1).ToString("yyyy-MM-dd HH:00:00.000"); } else { HourDate = Hour; // 그렇지 않다면 그냥 HOur } My_DB_DML MDD = new My_DB_DML(); ReturnValue RV = new ReturnValue(); ConnInfo = MDD.ConnDBInfo(); // DB접속정보를 담고 있음. using (SqlConnection conn = new SqlConnection(strcon)) { foreach (KeyValuePair kv in MyDic) { /// Key값과 Value값을 담기 위한 TKey, TValue string TmpPoint = kv.Key; float[] TmpValue = kv.Value; if (TmpValue[7] != 7) // 7이란 값 변경에 대한것을 의미.. { conn.Open(); // 접속 if (TmpValue[7] == 2 && TmpValue[12] < 0) //적산값인데 0보다 작으면... { TmpValue[12] = 0; } if (NowMin == 15) // XX시 15분이라면. 무조건 Insert. { query = "INSERT INTO " + Table.Hourly + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue)" + " VALUES(" + TmpValue[2] + ", " + "" + TmpValue[3] + ", " + "" + TmpValue[4] + ", " + "" + TmpValue[5] + ", " + "'" + HourDate + "', " + "" + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ")"; } else // 그렇지 않다면 Update { if (TmpValue[7] == 3 && NowMin == 0) //현재값이라면.. { NowHourDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd HH:00:00.000"); query = "INSERT INTO " + Table.Hourly + "(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue)" + " VALUES(" + TmpValue[2] + ", " + "" + TmpValue[3] + ", " + "" + TmpValue[4] + ", " + "" + TmpValue[5] + ", " + "'" + NowHourDate + "', " + "" + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ")"; } else { query = "UPDATE " + Table.Hourly + " SET CurrentValue = " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + " WHERE " + "SiteId = " + TmpValue[2] + " " + "AND FacilityTypeId = " + TmpValue[3] + " " + "AND FacilityCode = " + TmpValue[4] + " " + "AND PropertyId = " + TmpValue[5] + " " + "AND CreatedDateTime = '" + HourDate + "'"; } } SqlCommand cmd = new SqlCommand(query, conn); cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); } } conn.Dispose(); } } #region StringBuilder private StringBuilder Up_Avg_sb = new StringBuilder(); private StringBuilder Tmp1_sb = new StringBuilder(); private StringBuilder Up_Sum_sb = new StringBuilder(); private StringBuilder Tmp2_sb = new StringBuilder(); private StringBuilder Up_Sum2_sb = new StringBuilder(); private StringBuilder Tmp3_sb = new StringBuilder(); private StringBuilder Up_On_sb = new StringBuilder(); private StringBuilder Tmp4_sb = new StringBuilder(); private StringBuilder Tmp5_sb = new StringBuilder(); private StringBuilder Tmp6_sb = new StringBuilder(); private StringBuilder Tmp7_sb = new StringBuilder(); private StringBuilder Tmp8_sb = new StringBuilder(); private StringBuilder Tmp9_sb = new StringBuilder(); private StringBuilder Tmp10_sb = new StringBuilder(); #endregion public void DB_Day_Update(IDictionary MyDic, int NowMin, string Hour) { string ConnInfo = ""; string strcon = ConnDBInfo(); string DailyDate = ""; string Max_DailyDate = ""; string Min_DailyDate = ""; float[] SelectValue = new float[2]; if (NowMin == 00 && (Convert.ToDateTime(Hour).ToString("HH") == "00")) { DailyDate = Convert.ToDateTime(Hour).AddDays(-1).ToString("yyyy-MM-dd 00:00:00.000"); LogFileCreate LFC = new LogFileCreate(); if (Form1.Enable_EnglishMode)//영어 { LFC.Log("[DBinsert.cs] DailyDate at 00:00= " + DailyDate + Environment.NewLine); } else//한글 { LFC.Log("[DBinsert.cs] 00시00분 일때의 DailyDate = " + DailyDate + Environment.NewLine); } Max_DailyDate = Convert.ToDateTime(Hour).AddDays(-1).ToString("yyyy-MM-dd 00:00:00.000"); Min_DailyDate = Convert.ToDateTime(Hour).AddDays(-1).ToString("yyyy-MM-dd 00:00:00.000"); } else { DailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000"); Max_DailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000"); Min_DailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000"); } My_DB_DML MDD = new My_DB_DML(); ReturnValue RV = new ReturnValue(); ConnInfo = MDD.ConnDBInfo(); // DB접속정보를 담고 있음. using (SqlConnection conn = new SqlConnection(strcon)) { foreach (KeyValuePair kv in MyDic) { object o = 0; object o2 = 0; object o3 = 0; object o4 = 0; string query = ""; string returnquery = ""; float InsertDailyValue = 0; float AvgDailyValue = 0; float SumDailyValue = 0; float Sum2DailyValue = 0; float OnDailyValue = 0; /// Key값과 Value값을 담기 위한 TKey, TValue string TmpPoint = kv.Key; float[] TmpValue = kv.Value; if (TmpValue[7] != 3) { switch (NowMin) { case 15: InsertDailyValue = (TmpValue[8]); break; case 30: InsertDailyValue = (TmpValue[9]); break; case 45: InsertDailyValue = (TmpValue[10]); break; case 00: InsertDailyValue = (TmpValue[11]); break; } } else // 3일때. { InsertDailyValue = TmpValue[12]; } if (TmpValue[7] == 2 && InsertDailyValue < 0) //적산값인데 0보다 작으면... { InsertDailyValue = 0; } conn.Open(); switch (Convert.ToInt32(TmpValue[7])) { case 0: return; case 1: Up_Avg_sb.Append("SELECT AVG(CurrentValue) FROM BemsMonitoringPointHistory15min "); Up_Avg_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'"); Up_Avg_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' "); Up_Avg_sb.Append("AND SiteId = " + TmpValue[2] + " "); Up_Avg_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " "); Up_Avg_sb.Append("AND FacilityCode = " + TmpValue[4] + " "); Up_Avg_sb.Append("AND PropertyId = " + TmpValue[5] + " "); returnquery = Up_Avg_sb.ToString(); SqlCommand cmd = new SqlCommand(returnquery, conn); o = cmd.ExecuteScalar(); cmd.Dispose(); Up_Avg_sb.Clear(); if (o.GetType() != typeof(DBNull)) { AvgDailyValue = (float)Convert.ToDouble(o); } else { return; } Tmp1_sb.Append("UPDATE "); Tmp1_sb.Append(Table.Daily); Tmp1_sb.Append(" SET DailyValue = "); Tmp1_sb.Append(Math.Round(AvgDailyValue, 4, MidpointRounding.AwayFromZero)); Tmp1_sb.Append(" WHERE "); query = Tmp1_sb.ToString(); Tmp1_sb.Clear(); break; case 2: Up_Sum_sb.Append("SELECT SUM(CurrentValue) FROM BemsMonitoringPointHistory15min "); Up_Sum_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'"); Up_Sum_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' "); Up_Sum_sb.Append("AND SiteId = " + TmpValue[2] + " "); Up_Sum_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " "); Up_Sum_sb.Append("AND FacilityCode = " + TmpValue[4] + " "); Up_Sum_sb.Append("AND PropertyId = " + TmpValue[5] + " "); returnquery = Up_Sum_sb.ToString(); SqlCommand cmd2 = new SqlCommand(returnquery, conn); o2 = cmd2.ExecuteScalar(); cmd2.Dispose(); Up_Sum_sb.Clear(); if (o2.GetType() != typeof(DBNull)) { SumDailyValue = (float)Convert.ToDouble(o2); } else { return; } Tmp2_sb.Append("UPDATE "); Tmp2_sb.Append(Table.Daily); Tmp2_sb.Append(" SET DailyValue = "); Tmp2_sb.Append(Math.Round(SumDailyValue, 4, MidpointRounding.AwayFromZero)); Tmp2_sb.Append(" WHERE "); query = Tmp2_sb.ToString(); Tmp2_sb.Clear(); break; case 3: if (NowMin == 00 && (Convert.ToDateTime(Hour).ToString("HH") == "00")) { string tmpDailyDate = Convert.ToDateTime(Hour).ToString("yyyy-MM-dd 00:00:00.000"); Tmp3_sb.Append("INSERT INTO "); Tmp3_sb.Append(Table.Daily); Tmp3_sb.Append("(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue)"); Tmp3_sb.Append(" VALUES(" + TmpValue[2] + ", " + TmpValue[3] + ", " + TmpValue[4] + ", " + TmpValue[5] + ", '" + tmpDailyDate + "', " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ", " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ", " + Math.Round(TmpValue[12], 4, MidpointRounding.AwayFromZero) + ")"); query = Tmp3_sb.ToString(); Tmp3_sb.Clear(); } break; case 4: Up_On_sb.Append("SELECT SUM(CurrentValue) FROM BemsMonitoringPointHistory15min "); Up_On_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'"); Up_On_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' "); Up_On_sb.Append("AND SiteId = " + TmpValue[2] + " "); Up_On_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " "); Up_On_sb.Append("AND FacilityCode = " + TmpValue[4] + " "); Up_On_sb.Append("AND PropertyId = " + TmpValue[5] + " "); returnquery = Up_On_sb.ToString(); SqlCommand cmd3 = new SqlCommand(returnquery, conn); o3 = cmd3.ExecuteScalar(); cmd3.Dispose(); Up_On_sb.Clear(); if (o3.GetType() != typeof(DBNull)) { OnDailyValue = (float)Convert.ToDouble(o3); } else { return; } Tmp4_sb.Append("UPDATE "); Tmp4_sb.Append(Table.Daily); Tmp4_sb.Append(" SET DailyValue = "); Tmp4_sb.Append(Math.Round(OnDailyValue, 4, MidpointRounding.AwayFromZero)); Tmp4_sb.Append(" WHERE "); query = Tmp4_sb.ToString(); Tmp4_sb.Clear(); break; case 5: Up_Sum2_sb.Append("SELECT SUM(CurrentValue) FROM BemsMonitoringPointHistory15min "); Up_Sum2_sb.Append("WHERE CreatedDateTime BETWEEN '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 00:00:00") + "'"); Up_Sum2_sb.Append("AND '" + Convert.ToDateTime(DailyDate).ToString("yyyy-MM-dd 23:45:00") + "' "); Up_Sum2_sb.Append("AND SiteId = " + TmpValue[2] + " "); Up_Sum2_sb.Append("AND FacilityTypeId = " + TmpValue[3] + " "); Up_Sum2_sb.Append("AND FacilityCode = " + TmpValue[4] + " "); Up_Sum2_sb.Append("AND PropertyId = " + TmpValue[5] + " "); returnquery = Up_Sum2_sb.ToString(); SqlCommand cmd4 = new SqlCommand(returnquery, conn); o4 = cmd4.ExecuteScalar(); cmd4.Dispose(); Up_Sum2_sb.Clear(); if (o4.GetType() != typeof(DBNull)) { Sum2DailyValue = (float)Convert.ToDouble(o4); } else { return; } Tmp5_sb.Append("UPDATE "); Tmp5_sb.Append(Table.Daily); Tmp5_sb.Append(" SET DailyValue = "); Tmp5_sb.Append(Math.Round(Sum2DailyValue, 4, MidpointRounding.AwayFromZero)); Tmp5_sb.Append(" WHERE "); query = Tmp5_sb.ToString(); Tmp5_sb.Clear(); break; case 6: Tmp6_sb.Append("UPDATE "); Tmp6_sb.Append(Table.Daily); Tmp6_sb.Append(" SET DailyValue = "); Tmp6_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero)); Tmp6_sb.Append(" WHERE "); query = Tmp6_sb.ToString(); Tmp6_sb.Clear(); break; case 7: break; case 8: break; } if (TmpValue[7] != 7) { if (TmpValue[7] != 3) { Tmp7_sb.Append("SiteId = "); Tmp7_sb.Append(TmpValue[2]); Tmp7_sb.Append(" AND FacilityTypeId = "); Tmp7_sb.Append(TmpValue[3]); Tmp7_sb.Append(" AND FacilityCode = "); Tmp7_sb.Append(TmpValue[4]); Tmp7_sb.Append(" AND PropertyId = "); Tmp7_sb.Append(TmpValue[5]); Tmp7_sb.Append(" AND CreatedDateTime = '" + DailyDate + "'"); query += Tmp7_sb.ToString(); Tmp7_sb.Clear(); } Tmp8_sb.Append(" UPDATE "); Tmp8_sb.Append(Table.Daily); Tmp8_sb.Append(" SET MaxValue = CASE WHEN MaxValue <= "); Tmp8_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero)); Tmp8_sb.Append(" THEN "); Tmp8_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero)); Tmp8_sb.Append(" WHEN MaxValue >= "); Tmp8_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero)); Tmp8_sb.Append(" THEN MaxValue END "); Tmp8_sb.Append("WHERE SiteId = "); Tmp8_sb.Append(TmpValue[2]); Tmp8_sb.Append(" AND FacilityTypeId = "); Tmp8_sb.Append(TmpValue[3]); Tmp8_sb.Append(" AND FacilityCode = "); Tmp8_sb.Append(TmpValue[4]); Tmp8_sb.Append(" AND PropertyId = "); Tmp8_sb.Append(TmpValue[5]); Tmp8_sb.Append(" AND CreatedDateTime = '"); Tmp8_sb.Append(Max_DailyDate); Tmp8_sb.Append("'"); query += Tmp8_sb.ToString(); Tmp8_sb.Clear(); Tmp9_sb.Append(" UPDATE "); Tmp9_sb.Append(Table.Daily); Tmp9_sb.Append(" SET MinValue = CASE WHEN MinValue >= "); Tmp9_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero)); Tmp9_sb.Append(" THEN "); Tmp9_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero)); Tmp9_sb.Append(" WHEN MinValue <= "); Tmp9_sb.Append(Math.Round(InsertDailyValue, 4, MidpointRounding.AwayFromZero)); Tmp9_sb.Append(" THEN MinValue END "); Tmp9_sb.Append("WHERE SiteId = "); Tmp9_sb.Append(TmpValue[2]); Tmp9_sb.Append(" AND FacilityTypeId = "); Tmp9_sb.Append(TmpValue[3]); Tmp9_sb.Append(" AND FacilityCode = "); Tmp9_sb.Append(TmpValue[4]); Tmp9_sb.Append(" AND PropertyId = "); Tmp9_sb.Append(TmpValue[5]); Tmp9_sb.Append(" AND CreatedDateTime = '"); Tmp9_sb.Append(Min_DailyDate); Tmp9_sb.Append("'"); query += Tmp9_sb.ToString(); SqlCommand cmd7 = new SqlCommand(query, conn); cmd7.ExecuteNonQuery(); cmd7.Dispose(); Tmp9_sb.Clear(); } conn.Close(); } conn.Dispose(); } } private StringBuilder TmpEx1_sb = new StringBuilder(); private StringBuilder TmpEx2_sb = new StringBuilder(); public void Expression_Insert(IDictionary MyDic, int NowMin) { string query = ""; string InsertDate = DateTime.Now.ToString("yyyy-MM-dd HH:" + NowMin + ":00.000"); string[] TmpStringValue = new string[8]; float InsertValue = 0; string strcon = ConnDBInfo(); using (SqlConnection conn = new SqlConnection(strcon)) { foreach (KeyValuePair kv in MyDic) { // Key값과 Value값을 담기 위한 TKey, TValue string TmpPoint = kv.Key; float[] TmpValue = kv.Value; conn.Open(); switch (NowMin) { case 15: InsertValue = TmpValue[8]; break; case 30: InsertValue = TmpValue[9]; break; case 45: InsertValue = TmpValue[10]; break; case 00: InsertValue = TmpValue[11]; break; } { TmpEx1_sb.Append("INSERT INTO "); TmpEx1_sb.Append(Table.FiFt); TmpEx1_sb.Append("(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,CurrentValue) "); TmpEx1_sb.Append("VALUES("); TmpEx1_sb.Append(TmpValue[2]); TmpEx1_sb.Append(","); TmpEx1_sb.Append(TmpValue[3]); TmpEx1_sb.Append(","); TmpEx1_sb.Append(TmpValue[4]); TmpEx1_sb.Append(","); TmpEx1_sb.Append(TmpValue[5]); TmpEx1_sb.Append(",'"); TmpEx1_sb.Append(InsertDate); TmpEx1_sb.Append("',"); TmpEx1_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero)); TmpEx1_sb.Append(")"); query = TmpEx1_sb.ToString(); TmpEx1_sb.Clear(); } if (InsertDate == DateTime.Now.ToString("yyyy-MM-dd 00:15:00.000")) { TmpEx2_sb.Append("INSERT INTO "); TmpEx2_sb.Append(Table.Daily); TmpEx2_sb.Append("(SiteId,FacilityTypeId,FacilityCode,PropertyId,CreatedDateTime,DailyValue,MaxValue,MinValue) "); TmpEx2_sb.Append("VALUES("); TmpEx2_sb.Append(TmpValue[2]); TmpEx2_sb.Append(","); TmpEx2_sb.Append(TmpValue[3]); TmpEx2_sb.Append(","); TmpEx2_sb.Append(TmpValue[4]); TmpEx2_sb.Append(","); TmpEx2_sb.Append(TmpValue[5]); TmpEx2_sb.Append(",'"); TmpEx2_sb.Append(Convert.ToDateTime(InsertDate).ToString("yyyy-MM-dd 00:00:00.000")); TmpEx2_sb.Append("',"); TmpEx2_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero)); TmpEx2_sb.Append(","); TmpEx2_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero)); TmpEx2_sb.Append(","); TmpEx2_sb.Append(Math.Round(InsertValue, 4, MidpointRounding.AwayFromZero)); TmpEx2_sb.Append(")"); query += TmpEx2_sb.ToString(); TmpEx2_sb.Clear(); } SqlCommand cmd = new SqlCommand(query, conn); cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); } conn.Dispose(); } } } }