// SQL Reader
public static void SQLReader(DBConnector.SQLConnStringModel SQLConnectionString, string queryString)
{
var connector = new DBConnector.SLQDBConnector();
SqlConnection connection = connector.SQLOpenConnection(SQLConnectionString);
using (connection)
{
SqlCommand command = new SqlCommand(queryString, connection);
try
{
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// TODO: Implementation of query export to csv file in batches
Console.WriteLine("\t{0}\t{1}\t{2}\t{3}\t{4}", reader[0], reader[1], reader[2], reader[3], reader[4]);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Unable to read data from SQL DB: ", e);
}
finally
{
connector.SQLCloseConnection(connection);
}
}
}
// SQL Server DB Connection String
public string SQLConnString(DBConnector.SQLConnStringModel SQLConnectionString)
{
string connectionStringSQL = String.Format("Data Source={0};Initial Catalog={1};User id={2};Password={3}; MultipleActiveResultSets=true;",
SQLConnectionString.dataSource, SQLConnectionString.initialCatalog, SQLConnectionString.userId, SQLConnectionString.pwd);
return(connectionStringSQL);
}
// SQL Open Connection
public SqlConnection SQLOpenConnection(DBConnector.SQLConnStringModel SQLConnectionString)
{
string connectionString = SQLConnString(SQLConnectionString);
SqlConnection connection = new SqlConnection(connectionString);
try
{
connection.Open();
return(connection);
}
catch (Exception e)
{
SQLCloseConnection(connection);
Console.WriteLine("Unable to open SQL DB connection" + e.Message);
throw e;
}
}
public DBConnector.SQLConnStringModel SetPlatformSQLConnString(bool isLogDatabase, bool isSaCredentials, ConfigFileDBInfo platformDBInfo = null, ConfigFileDBInfo loggingDBInfo = null, string saUser = null, string saPwd = null)
{
var sqlConnString = new DBConnector.SQLConnStringModel();
if (!isLogDatabase && !isSaCredentials) // Uses Runtime user and Platform Main Catalog
{
string platformDBRuntimeUser = platformDBInfo.GetProperty("RuntimeUser").Value;
string platformDBRuntimeUserPwd = platformDBInfo.GetProperty("RuntimePassword").GetDecryptedValue(CryptoUtils.GetPrivateKeyFromFile(Program.privateKeyFilepath));
sqlConnString.dataSource = platformDBInfo.GetProperty("Server").Value;
sqlConnString.initialCatalog = platformDBInfo.GetProperty("Catalog").Value;
sqlConnString.userId = platformDBRuntimeUser;
sqlConnString.pwd = platformDBRuntimeUserPwd;
}
else if (isLogDatabase) // Uses Runtime Log user and Log Catalog
{
sqlConnString.dataSource = loggingDBInfo.GetProperty("Server").Value;
sqlConnString.userId = loggingDBInfo.GetProperty("RuntimeUser").Value; // needs to use oslog configurations
sqlConnString.pwd = loggingDBInfo.GetProperty("RuntimePassword").GetDecryptedValue(CryptoUtils.GetPrivateKeyFromFile(Program.privateKeyFilepath));
sqlConnString.initialCatalog = loggingDBInfo.GetProperty("Catalog").Value;
}
else if (isSaCredentials) // Uses SA Credentials inputted on the Form
{
sqlConnString.dataSource = platformDBInfo.GetProperty("Server").Value;
sqlConnString.initialCatalog = platformDBInfo.GetProperty("Catalog").Value;
sqlConnString.userId = saUser;
sqlConnString.pwd = saPwd;
}
return(sqlConnString);
}
private void bt_TestSaConnection_Click(object sender, EventArgs e, string dbms, DBConnector.SQLConnStringModel SQLConnectionString = null, DBConnector.OracleConnStringModel OracleConnectionString = null)
{
string testConnectionResult = null;
if (dbms.ToLower().Equals("sqlserver"))
{
SQLConnectionString.userId = this.tb_iptSaUsername.Text;
SQLConnectionString.pwd = this.tb_iptSaPwd.Text;
try {
var connector = new DBConnector.SLQDBConnector();
SqlConnection connection = connector.SQLOpenConnection(SQLConnectionString);
connector.SQLCloseConnection(connection);
testConnectionResult = _successConnectionTest;
} catch {
testConnectionResult = _failedConnectionTest;
}
}
else if (dbms.ToLower().Equals("oracle"))
{
OracleConnectionString.userId = this.tb_iptSaUsername.Text;
OracleConnectionString.pwd = this.tb_iptSaPwd.Text;
try {
var connector = new DBConnector.OracleDBConnector();
OracleConnection connection = connector.OracleOpenConnection(OracleConnectionString);
connector.OracleCloseConnection(connection);
testConnectionResult = _successConnectionTest;
} catch {
testConnectionResult = _failedConnectionTest;
}
}
puf_popUpForm popup = new puf_popUpForm(puf_popUpForm._feedbackTestConnectionType, testConnectionResult);
DialogResult dg = popup.ShowDialog();
}
public OsDiagForm(OSDiagToolConf.ConfModel.strConfModel configurations, string dbms, DBConnector.SQLConnStringModel SQLConnectionString = null, DBConnector.OracleConnStringModel OracleConnectionString = null)
{
InitializeComponent();
this.cb_iisThreads.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_threadDumps][OSDiagToolConfReader._l3_iisW3wp]; // Iis thread dumps
this.cb_osServicesThreads.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_threadDumps][OSDiagToolConfReader._l3_osServices]; // OS services thread dumps
this.cb_iisMemDumps.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_memoryDumps][OSDiagToolConfReader._l3_iisW3wp];
this.cb_osMemDumps.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_memoryDumps][OSDiagToolConfReader._l3_osServices];
this.cb_EvtViewerLogs.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_serverLogs][OSDiagToolConfReader._l3_evtAndServer];
this.cb_iisAccessLogs.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_serverLogs][OSDiagToolConfReader._l3_iisLogs];
this.nud_iisLogsNrDays.Value = configurations.IISLogsNrDays; // Number of days of IIS logs
this.cb_platformLogs.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_platform][OSDiagToolConfReader._l3_platformLogs];
this.nud_topLogs.Value = configurations.osLogTopRecords;
this.cb_platformAndServerFiles.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_platform][OSDiagToolConfReader._l3_platformAndServerConfigFiles];
this.cb_dbPlatformMetamodel.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_databaseOperations][OSDiagToolConfReader._l3_platformMetamodel];
this.cb_dbTroubleshoot.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_databaseOperations][OSDiagToolConfReader._l3_databaseTroubleshoot];
this.lb_metamodelTables.Items.AddRange(configurations.tableNames.ToArray()); // add Platform Metamodel tables to list box
bt_TestSaConnection.Click += delegate(object sender, EventArgs e) { bt_TestSaConnection_Click(sender, e, dbms, SQLConnectionString, OracleConnectionString); };
bt_runOsDiagTool.Click += delegate(object sender, EventArgs e) { bt_runOsDiagTool_Click(sender, e, configurations); };
}
public static void PlatformLogExporter(string dbEngine, List <string> tableNames, OSDiagToolForm.OsDiagFormConfModel.strFormConfigurationsModel FormConfigurations, string outputDestination, int queryTimeout, DBConnector.SQLConnStringModel SQLConnectionString = null,
DBConnector.OracleConnStringModel OracleConnectionString = null, string adminSchema = null)
{
if (dbEngine.ToLower().Equals("sqlserver"))
{
var connector = new DBConnector.SLQDBConnector();
SqlConnection connection = connector.SQLOpenConnection(SQLConnectionString);
using (connection) {
foreach (string table in tableNames)
{
string sqlQuery = "SELECT TOP {0} * FROM {1} ORDER BY INSTANT DESC";
sqlQuery = string.Format(sqlQuery, FormConfigurations.osLogTopRecords, table);
FileLogger.TraceLog(string.Format("Exporting log table {0} ", table));
CSVExporter.SQLToCSVExport(dbEngine, table, Path.Combine(outputDestination), queryTimeout, sqlQuery, connection, null);
}
}
}
else if (dbEngine.ToLower().Equals("oracle"))
{
var connector = new DBConnector.OracleDBConnector();
OracleConnection connection = connector.OracleOpenConnection(OracleConnectionString);
using (connection) {
foreach (string table in tableNames)
{
string oracleQuery = "SELECT * FROM (SELECT * FROM {0}.{1} ORDER BY INSTANT DESC) WHERE ROWNUM < {2}";
oracleQuery = string.Format(oracleQuery, adminSchema, table, FormConfigurations.osLogTopRecords);
FileLogger.TraceLog(string.Format("Exporting log table {0} ", table));
CSVExporter.SQLToCSVExport(dbEngine, table, outputDestination, queryTimeout, oracleQuery, null, connection);
}
}
}
}
public static void DatabaseTroubleshooting(string dbEngine, OSDiagToolConf.ConfModel.strConfModel configurations, string outputDestination, DBConnector.SQLConnStringModel SQLConnectionString = null,
DBConnector.OracleConnStringModel OracleConnectionString = null)
{
// Needs user with sa permissions
if (dbEngine.ToLower().Equals("sqlserver"))
{
List <string> blockingAndBlockedSpids = new List <string>();
int top_statCachedPlan = Convert.ToInt32(configurations.databaseQueryConfigurations[OSDiagToolConfReader._l3_sqlServer][OSDiagToolConfReader._l4_top_statCachedPlans]);
int top_topCPU = Convert.ToInt32(configurations.databaseQueryConfigurations[OSDiagToolConfReader._l3_sqlServer][OSDiagToolConfReader._l4_top_topCPU]);
var sqlDBQueries = new SQLServerQueries();
Dictionary <string, string> sqlQueries = new Dictionary <string, string> { // use same name as in the DatabaseQueries
{ "sessionsSp_Who2", sqlDBQueries.sessionsSp_Who2 },
{ "sessionsSp_Who2_Blocked", sqlDBQueries.sessionsSp_Who2_Blocked },
{ "statCachedPlan", string.Format(sqlDBQueries.statCachedPlans, top_statCachedPlan) },
{ "costlyCPUQueries", string.Format(sqlDBQueries.costlyCPUQueries, top_topCPU) },
{ "dbccInputBuffer", sqlDBQueries.dbccInputBuffer }
};
var connector = new DBConnector.SLQDBConnector();
SqlConnection connection = connector.SQLOpenConnection(SQLConnectionString);
using (connection) {
foreach (KeyValuePair <string, string> entry in sqlQueries)
{
if (!(entry.Key.Equals("sessionsSp_Who2_Blocked") || entry.Key.Equals("dbccInputBuffer"))) // skip sp_who2_blocked and dbcc since it already exports the entire result set of sp_who2
{
CSVExporter.SQLToCSVExport(dbEngine, entry.Key, outputDestination, configurations.queryTimeout, entry.Value, connection, null);
}
else if (entry.Key.Equals("sessionsSp_Who2_Blocked"))
{
SqlCommand cmd = new SqlCommand(entry.Value, connection)
{
CommandTimeout = configurations.queryTimeout
};
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
blockingAndBlockedSpids.Add(dr.GetValue(0).ToString()); // add blockings spids to list
blockingAndBlockedSpids.Add(dr.GetValue(1).ToString()); // add blocked spids to list
}
}
}
else if (entry.Key.Equals("dbccInputBuffer"))
{
if (!(blockingAndBlockedSpids.Count.Equals(0))) // get sql text of blocked and blockig spids
{
string allBlockedSpidsInline = string.Join(",", blockingAndBlockedSpids.ToArray());
string blockedSqlTextQuery = string.Format(entry.Value, allBlockedSpidsInline);
CSVExporter.SQLToCSVExport(dbEngine, entry.Key, outputDestination, configurations.queryTimeout, blockedSqlTextQuery, connection, null);
}
}
}
;
}
}
else if (dbEngine.ToLower().Equals("oracle"))
{
List <string> orclSids = new List <string>();
int orcl_TopCPU = Convert.ToInt32(configurations.databaseQueryConfigurations[OSDiagToolConfReader._l3_oracle][OSDiagToolConfReader._l4_top_topCPU]);
var orclDBQueries = new OracleQueries();
Dictionary <string, string> orclQueries = new Dictionary <string, string> { // TODO: use reflection to get the property names
{ "orcl_lockedObjects", orclDBQueries.lockedObjects },
{ "orcl_lockedObjects_2", orclDBQueries.lockedObjects_2 },
{ "orcl_resourceLimit", orclDBQueries.resourceLimit },
{ "orcl_sessionByIOType_Reads", string.Format(orclDBQueries.sessionByIOType, "DISK_READS") },
{ "orcl_sessionByIOType_Writes", string.Format(orclDBQueries.sessionByIOType, "DIRECT_WRITES") },
{ "orcl_tk_queriesRunningNow", orclDBQueries.tk_queriesRunningNow },
{ "orcl_sqlTextBySID", orclDBQueries.sqlTextBySID },
{ "orcl_sidInfo", orclDBQueries.sidInfo },
{ "orcl_topCPUSqls", string.Format(orclDBQueries.topCPUSqls, orcl_TopCPU) }
};
var connector = new DBConnector.OracleDBConnector();
OracleConnection connection = connector.OracleOpenConnection(OracleConnectionString);
using (connection) {
foreach (KeyValuePair <string, string> entry in orclQueries)
{
if (!(entry.Key.Equals("orcl_lockedObjects") || (entry.Key.Equals("orcl_sessionByIOType_Writes") || (entry.Key.Equals("orcl_sessionByIOType_Reads") || (entry.Key.Equals("orcl_lockedObjects_2") ||
(entry.Key.Equals("orcl_sqlTextBySID") || (entry.Key.Equals("orcl_sidInfo")))))))) // skip queries that we want to know more about the sessions
{
CSVExporter.SQLToCSVExport(dbEngine, entry.Key, outputDestination, configurations.queryTimeout, entry.Value, null, connection);
}
else if (entry.Key.Equals("orcl_lockedObjects") || entry.Key.Equals("orcl_lockedObjects_2") || entry.Key.Equals("orcl_sessionByIOType_Reads") || entry.Key.Equals("orcl_sessionByIOType_Writes"))
{
OracleCommand cmd = new OracleCommand(entry.Value, connection)
{
CommandTimeout = configurations.queryTimeout
};
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
orclSids.Add(dr.GetValue(0).ToString()); // add Sids to list
}
}
CSVExporter.SQLToCSVExport(dbEngine, entry.Key, outputDestination, configurations.queryTimeout, entry.Value, null, connection);
}
else if (entry.Key.Equals("orcl_sidInfo") || entry.Key.Equals("orcl_sqlTextBySID"))
{
if (!(orclSids.Count.Equals(0)))
{
string allSidsInline = string.Join(",", orclSids.ToArray());
string sidsSqlTextQuery = string.Format(entry.Value, allSidsInline);
CSVExporter.SQLToCSVExport(dbEngine, entry.Key, outputDestination, configurations.queryTimeout, sidsSqlTextQuery, null, connection);
}
}
}
}
}
}
