void ExecuteQueryRecordCommand(string strQuery, string strDestTable, int iQueryID, string strSPName) { DataTable dtQueryResult = new DataTable(); DateTime dtTimeIn = DateTime.Now; DateTime dtTimeIn_UTC = DateTime.UtcNow; using (SqlConnection con1 = new SqlConnection(strLocalConnectionString)) { try { WSP.Console.WS_QUERY.AddRunningQueries wsInsert = new WSP.Console.WS_QUERY.AddRunningQueries(); wsInsert.Url = g_SharedData.WSP_AGENT_SETTING.strWS_URL + "/AddRunningQueries.asmx"; wsInsert.Timeout = 15000; int iServerNumber = g_SharedData.WSP_AGENT_SETTING.iServerNumber; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new SqlCommand("sp_executesql", con1); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.AddWithValue("@statement", strQuery); da.Fill(dtQueryResult); dtSavedResult = dtQueryResult.Clone(); dtSavedResult = dtQueryResult.Copy(); //컬럼매핑때문에 주석 처리해봄 DataColumnCollection columns = dtQueryResult.Columns; if (columns.Contains("ServerNum") && columns.Contains("RegDate") && columns.Contains("TimeIn")) { for (int i = 0; i < dtQueryResult.Rows.Count; i++) { dtQueryResult.Rows[i].BeginEdit(); dtQueryResult.Rows[i]["ServerNum"] = g_SharedData.WSP_AGENT_SETTING.iServerNumber; if (iQueryID > 3) // Queries for bulk insert. { dtQueryResult.Rows[i]["RegDate"] = DBNull.Value; } dtQueryResult.Rows[i]["TimeIn"] = dtTimeIn.ToString("yyyy-MM-dd HH:mm:ss"); dtQueryResult.Rows[i].EndEdit(); dtQueryResult.AcceptChanges(); } } if (dtQueryResult.Rows.Count > 0) { if (iQueryID == 2 || iQueryID == 3) { dtSavedResult.Clear(); dtSavedResult = dtQueryResult.Clone(); if (IsSQLAgentStatusOK(dtQueryResult, iQueryID)) { dtQueryResult.Clear(); return; } } //if (dtSavedResult.Rows.Count > 0) //{ // InsertQueryResult(dtSavedResult, strDestTable, strSPName); // dtQueryResult.Clear(); //} //if (iQueryID >= 4) // Custom Queries to insert BULK. //{ // InsertQueryResult(dtQueryResult, strDestTable, strSPName); // dtQueryResult.Clear(); //} if (iQueryID == 2) // Custom Queries to insert BULK. { wsInsert.SQLJobAgentFailCheck(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 3) // Custom Queries to insert BULK. { wsInsert.SQLJobStatusCheck(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 4) // Custom Queries to insert BULK. { wsInsert.SQLLinkedCheck(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 5) // Custom Queries to insert BULK. { wsInsert.SQLErrorlog(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 6) // Custom Queries to insert BULK. { wsInsert.SQLTableSize(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 7) // Custom Queries to insert BULK. { wsInsert.SQLBlock(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 8) // Custom Queries to insert BULK. { wsInsert.SQLObjectCheck(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 9) // Custom Queries to insert BULK. { wsInsert.SQLDatabase(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 11) // Custom Queries to insert BULK. { wsInsert.SQLIndexDuplication(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 12) // Custom Queries to insert BULK. { wsInsert.SQLServerInfo(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 13) // Custom Queries to insert BULK. { wsInsert.SQLServiceStatus(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 14) // Custom Queries to insert BULK. { wsInsert.SQLConfiguration(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 15) // Custom Queries to insert BULK. { wsInsert.SQLDataBaseFileSize(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 16) // Custom Queries to insert BULK. { wsInsert.SQLSession(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 17) // Custom Queries to insert BULK. { wsInsert.SQLIndexFlagment(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } if (iQueryID == 18) // Custom Queries to insert BULK. { wsInsert.SQLAgentErrorlog(DataTableToBytes(dtSavedResult), iServerNumber, g_TimeIn.ToString("yyyy-MM-dd HH:mm:ss"), g_TimeIn_UTC.ToString("yyyy-MM-dd HH:mm:ss")); dtQueryResult.Clear(); } } } catch (Exception e) { WSPEvent.WriteEvent("Running SQL Monitoring queries has failed. in this local database. - " + iQueryID.ToString() + ":" + e.Message, "W", 1131); } finally { con1.Close(); } } }