private async Task <DeviceServerObjects> PostDeviceServerObjectsAsync(DeviceServerObjects serverObjects)
{
DeviceServerObjects dso = null;
try
{
HttpResponseMessage response = await client.PostAsJsonAsync(
Constants.SQLSRV_DIR, serverObjects);
response.EnsureSuccessStatusCode();
if (response.IsSuccessStatusCode)
{
dso = await response.Content.ReadAsAsync <DeviceServerObjects>();
}
}
catch (HttpRequestException httprex)
{
return(new DeviceServerObjects {
HttpRequestException = httprex
});
}
return(dso);
}
/// <summary>
/// Posts and returns the updated DeviceServerObjects as echo.
/// </summary>
/// <param name="dso"></param>
/// <returns></returns>
private DeviceServerObjects PostServerObjects(DeviceServerObjects dso)
{
SQLServerRequests requests = new SQLServerRequests();
dso = requests.PostDeviceServerObjects(dso, DeviceServerObjectAction.insert);
return(dso);
}
/// <summary>
/// Initializes MattimonSqlServerPostDataWorker that will be posting DeviceServerObjects
/// </summary>
private void InitializeMattimonSqlServerPostWorker()
{
if (mSqlSrvPostWrkInit)
{
return;
}
MattimonSqlServerPostDataWorker = new BackgroundWorker();
MattimonSqlServerPostDataWorker.DoWork += (s, e) =>
{
DeviceServerObjects dso = (DeviceServerObjects)e.Argument;
e.Result = PostServerObjects(dso);
};
MattimonSqlServerPostDataWorker.RunWorkerCompleted += (s, e) =>
{
lblStatus.Text = "Ready";
if (e.Error != null)
{
MessageBox.Show(e.Error.ToString());
return;
}
if (e.Result != null && e.Result is DeviceServerObjects serverObjects)
{
if (serverObjects.HttpRequestException != null)
{
GUI.BitscoreForms.BitscoreMessageBox.Show(serverObjects.HttpRequestException.Message + "\n\n" + serverObjects.HttpRequestException.StackTrace +
(serverObjects.HttpRequestException.InnerException != null ?
serverObjects.HttpRequestException.InnerException.Message + "\n\n" + serverObjects.HttpRequestException.InnerException.StackTrace : ""
), "Http Request Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (serverObjects.Exception != null)
{
GUI.BitscoreForms.BitscoreMessageBox.Show(
serverObjects.Exception.Message + "\n\n" + serverObjects.Exception.StackTrace +
(serverObjects.Exception.InnerException != null ?
serverObjects.Exception.InnerException.Message + "\n\n" + serverObjects.Exception.InnerException.StackTrace : ""
), "Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
UpdateGUISQLServer(serverObjects, null, false);
}
}
};
mSqlSrvPostWrkInit = true;
}
/// <summary>
///
/// </summary>
/// <param name="serverObjects"></param>
private void UpdateGUISQLServer(DeviceServerObjects serverObjects, DataGridView gridView, bool local)
{
lblStatus.Text = "Ready";
bool hasInstances = serverObjects.Instances != null && serverObjects.Instances.Length > 0;
ucsqlServerInstanceGrid1.Visible = hasInstances;
btn_sqlsrv_refresh.Visible = hasInstances;
btn_sqlsrv_deleteInstances.Visible = hasInstances;
btn_sqlsrv_refresh.Enabled = true;
if (hasInstances)
{
int dbcount = 0;
int dccount = 0;
foreach (ServerInstance si in serverObjects.Instances)
{
dbcount += si.Databases.Length;
foreach (Database db in si.Databases)
{
dccount += db.Connections.Length;
}
}
if (dbcount > 0)
{
lbl_sqlsrv_hint.Text += "\tDatabases: " + dbcount;
}
lbl_sqlsrv_hint.Text = string.Format("Instances: {0} - Databases: {1} - Connections: {2}", serverObjects.Instances.Length, dbcount, dccount);
}
else
{
lbl_sqlsrv_hint.Text = "You're not monitoring any SQL Server instances. Click\"Add New\" to get started!";
}
try
{
DataSet serverObjectsDataSet = DataHelper.CreateDataSet(serverObjects, local);
ucsqlServerInstanceGrid1.SetDataSet(serverObjectsDataSet, true);
}
catch (Exception ex)
{
MenuButtonClick(btnMenu_SqlSrv, EventArgs.Empty);
GUI.BitscoreForms.BitscoreMessageBox.Show
(this, "An error occurred while attempting to parse SQL Server Data.\n\nError details:\n\n" + ex.Message + "\n\n" + ex.StackTrace, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private DeviceServerObjects RequestDeviceServerObjects()
{
SQLServerRequests serverRequests = new SQLServerRequests();
DeviceServerObjects serverObjects = serverRequests.PostDeviceServerObjects(new DeviceServerObjects {
DeviceID = GetDeviceID()
}, DeviceServerObjectAction.select);
new MattimonAgentLibrary.Tools.DebugTools.TextLogging().LogToFile(serverObjects, mWriteLogToInstallDirectory, out Exception exception);
if (exception != null)
{
new System.Threading.Thread(() => {
Invoke((MethodInvoker) delegate() {
GUI.BitscoreForms.BitscoreMessageBox.Show("An error occurred while attempted to read or write to local log file.\nYou may simply ignore this error." +
"\n\nError details:\n" + exception.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
});
});
}
return(serverObjects);
}
/// <summary>
///
/// </summary>
/// <param name="serverObjects"></param>
public void LogToFile(DeviceServerObjects serverObjects, bool useInstallLocation, out Exception exception)
{
exception = null;
try
{
string location = useInstallLocation ? RegistryTools.GetInstallLocationByDisplayName(
Static.MattimonRegistrySubkeyNames.DisplayName) + "\\" : "";
FileStream rfs = new FileStream(location + serverObjects.GetType().Name + "." +
(serverObjects.PostAction ?? "LocalRequest") + ".log",
FileMode.OpenOrCreate, FileAccess.Read);
StreamReader sr = new StreamReader(rfs);
bool hasData = sr.ReadLine() != null;
sr.Close();
rfs.Close();
FileStream wfs = new FileStream(location + serverObjects.GetType().Name + "." +
(serverObjects.PostAction ?? "LocalRequest") + ".log",
FileMode.Append, FileAccess.Write);
StreamWriter sw = new StreamWriter(wfs);
if (hasData)
{
sw.WriteLine();
sw.WriteLine();
sr.Close();
rfs.Close();
}
sw.WriteLine("[{0}]", DateTime.Now);
foreach (ServerInstance si in serverObjects.Instances)
{
sw.WriteLine("[INSTANCE]");
sw.WriteLine(" WEBID: " + (si.ID == 0 ? "n/a" : si.ID.ToString()));
sw.WriteLine(" Server name: " + si.ServerName);
sw.WriteLine(" Instance name: " + (si.InstanceName == "" || si.InstanceName == null ? "DEFAULT" : si.InstanceName));
sw.WriteLine(" Last Reported: " + (si.LastReportedTimestamp <= 0 ? "Never" : DateUtils.UnixTimeStampToDateTime(si.LastReportedTimestamp).ToString()));
foreach (Database db in si.Databases)
{
sw.WriteLine(" [DATABASE]");
sw.WriteLine(" WEBID: " + (db.ID == 0 ? "n/a" : db.ID.ToString()));
sw.WriteLine(" Name: " + db.DatabaseName);
sw.WriteLine(" [/DATABASE]");
foreach (DatabaseConnectionInfo ci in db.Connections)
{
sw.WriteLine(" [CONNECTION]");
sw.WriteLine(" WEBID: " + (ci.ID == 0 ? "n/a" : ci.ID.ToString()));
sw.WriteLine(" Host: " + ci.Hostname);
sw.WriteLine(" Program: " + ci.ProgramName);
sw.WriteLine(" [/CONNECTION]");
}
}
sw.WriteLine("[/INSTANCE]");
sw.WriteLine();
sw.WriteLine();
sw.WriteLine();
}
sw.Close();
wfs.Close();
}
catch (Exception ex) { exception = ex; }
}
/// <summary>
///
/// </summary>
/// <param name="serverObjects"></param>
/// <returns></returns>
public static DataSet CreateDataSet(DeviceServerObjects serverObjects, bool local)
{
DataTable instances = new DataTable("instances");
instances.Columns.Add("pk", typeof(Int32));
instances.Columns.Add("servername", typeof(String));
instances.Columns.Add("instancename", typeof(String));
instances.Columns.Add("clustered", typeof(String));
instances.Columns.Add("version", typeof(String));
instances.Columns.Add("user", typeof(String));
instances.Columns.Add("monitored", typeof(String));
instances.Columns.Add("numberdatabases", typeof(Int32));
instances.Columns.Add("lastreported", typeof(DateTime));
if (!local)
{
instances.PrimaryKey = new DataColumn[] { instances.Columns[0] }
}
;
///
/// Databases
///
DataTable databases = new DataTable("databases");
databases.Columns.Add("pk", typeof(Int32));
databases.Columns.Add("fk_instances", typeof(Int32));
databases.Columns.Add("collationname", typeof(String));
databases.Columns.Add("state", typeof(Int32));
databases.Columns.Add("createdate", typeof(Int32));
databases.Columns.Add("monitored", typeof(String));
if (!local)
{
databases.PrimaryKey = new DataColumn[] { databases.Columns[0] }
}
;
///
/// Connections
///
DataTable connections = new DataTable("connections");
connections.Columns.Add("pk", typeof(Int32));
connections.Columns.Add("fk_databases", typeof(Int32));
connections.Columns.Add("spid", typeof(Int32));
connections.Columns.Add("spid2", typeof(Int32));
connections.Columns.Add("status", typeof(String));
connections.Columns.Add("login", typeof(String));
connections.Columns.Add("host", typeof(String));
connections.Columns.Add("blkby", typeof(String));
connections.Columns.Add("dbname", typeof(String));
connections.Columns.Add("command", typeof(String));
connections.Columns.Add("CPU Time", typeof(Int64));
connections.Columns.Add("diskio", typeof(Int64));
connections.Columns.Add("lastbatch", typeof(String));
connections.Columns.Add("programname", typeof(String));
connections.Columns.Add("requestid", typeof(Int32));
///
/// DataSet
///
DataSet serverObjectsDataSet = new DataSet();
serverObjectsDataSet.Tables.Add(instances);
serverObjectsDataSet.Tables.Add(databases);
serverObjectsDataSet.Tables.Add(connections);
if (!local)
{
serverObjectsDataSet.Relations.Add(new DataRelation("instances-databases", instances.Columns[0], databases.Columns[1]));
serverObjectsDataSet.Relations.Add(new DataRelation("databases-connections", databases.Columns[0], connections.Columns[1]));
}
// If there are instances
if (serverObjects.Instances != null && serverObjects.Instances.Length > 0)
{
foreach (ServerInstance si in serverObjects.Instances)
{
// Add instances in instances DataTable
int dblen = si.Databases != null ? si.Databases.Length : 0;
instances.Rows.Add(si.ID, si.ServerName, si.InstanceName, si.Clustered ? "Yes" : "No", si.Version, si.User, si.Monitored ? MonitorSwitch.Enabled.ToString() : MonitorSwitch.Disabled.ToString(), dblen, DateUtils.UnixTimeStampToDateTime(si.LastReportedTimestamp));
// Then if there are databases
if (si.Databases != null && si.Databases.Length > 0)
{
foreach (Database db in si.Databases)
{
// Add databases in databases DataTable
databases.Rows.Add(db.ID, db.ServerInstanceID, db.CollationName, db.State, db.CreateDateTimestamp, db.Monitored ? MonitorSwitch.Enabled.ToString() : MonitorSwitch.Disabled.ToString());
// Then if there are connections
if (db.Connections != null && db.Connections.Length > 0)
{
foreach (DatabaseConnectionInfo ci in db.Connections)
{
// Add connections in connections DataTable
connections.Rows.Add(
ci.ID, ci.DatabaseID, ci.SPID,
ci.SPID2, ci.Status, ci.Login,
ci.Hostname, ci.BlkBy, ci.DatabaseName,
ci.Command, ci.CpuTime, ci.DiskIO,
ci.LastBatch, ci.ProgramName, ci.RequestID);
} // end foreach DatabaseConnectionInfo
} // end if Connections
} // end foreach Database
} // end if Databases
} // end foreach ServerInstance
}
return(serverObjectsDataSet);
}
}
}
public DeviceServerObjects PostDeviceServerObjects(DeviceServerObjects serverObjects, DeviceServerObjectAction action)
{
serverObjects.PostAction = action.ToString();
return(PostDeviceServerObjectsAsync(serverObjects).Result);
}
/// <summary>
///
/// </summary>
/// <param name="instances"></param>
private void DeleteSqlServerInstance(ServerInstance[] instances)
{
string svcPath = string.Empty, svcName = string.Empty;
FormBackgroundWorker deleteInstances = new FormBackgroundWorker(this,
new DoWorkEventHandler((s, e) => {
SQLServerRequests requests = new SQLServerRequests();
DeviceServerObjects dso = new DeviceServerObjects();
dso.DeviceID = GetDeviceID();
dso.Instances = instances;
try
{
svcPath = Path.Combine(RegistryTools.GetInstallLocationByDisplayName(
MattimonAgentLibrary.Static.MattimonRegistrySubkeyNames.DisplayName),
"MattimonSQLServerService.exe");
if (!File.Exists(svcPath))
{
throw new IOException("Could not locate the service file: " + svcPath + ".\n" +
"Re-installing the application might fix this issue.");
}
}
catch (IOException ioe) { throw ioe; }
catch (Exception ex) { throw ex; }
svcName = new ProjectAssemblyAtrributes(svcPath).AssemblyTitle;
SQLiteClientDatabase db = GetLocalDatabase();
foreach (ServerInstance si in dso.Instances)
{
string insname = si.InstanceName.ToLower().Equals("mssqlserver") ? "" : si.InstanceName;
db.DeleteConnectionStringEntry(si.ServerName, insname);
}
e.Result = requests.PostDeviceServerObjects(dso, DeviceServerObjectAction.delete);
}), "Deleting Instance(s)");
deleteInstances.RunWorkerCompletedEventHandler = new RunWorkerCompletedEventHandler((s, e) => {
if (e.Error != null)
{
GUI.BitscoreForms.BitscoreMessageBox.Show(this, "An error occurred while attempting to delete SQL Server instances.\n\n" +
"Error details:\n" + Tools.ExceptionHelper.GetFormatedExceptionMessage(e.Error),
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (e.Result != null && e.Result is DeviceServerObjects dso)
{
if (dso.Exception != null || dso.HttpRequestException != null)
{
if (dso.Exception != null)
{
GUI.BitscoreForms.BitscoreMessageBox.Show(this, "An server-side error occurred.\n\n" +
"Error details:\n" + Tools.ExceptionHelper.GetFormatedExceptionMessage(dso.Exception),
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (dso.HttpRequestException != null)
{
GUI.BitscoreForms.BitscoreMessageBox.Show(this, "An server-side error occurred.\n\n" +
"Error details:\n" + Tools.ExceptionHelper.GetFormatedExceptionMessage(dso.HttpRequestException),
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return;
}
BtnSqlSrv_Refresh_Click(btn_sqlsrv_refresh, EventArgs.Empty);
GUI.BitscoreForms.BitscoreMessageBox.Show(this, "Instance(s) have been deleted.",
Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
});
deleteInstances.DoWork();
}
/// <summary>
/// Initializes MattimonSqlServerDataRequestWorker that will be requesting DeviceServerObjects
/// </summary>
private void InitializeMattimonSqlServerDataRequestWorker()
{
if (mSqlSrvRequestWrkInit)
{
return;
}
MattimonSqlServerDataRequestWorker = new BackgroundWorker();
MattimonSqlServerDataRequestWorker.DoWork += (s, e) =>
{
object[] result = new object[2];
if (e.Argument is bool loadFromServer)
{
if (loadFromServer)
{
// Load the selected instances that where stored on the server
result[0] = RequestDeviceServerObjects();
}
else
{
// Only provide the instances stored on server to retreive
// local sql monitoring data : that data may not be yet posted on the server
// this is allowing us to see current data that haven't yet been posted by the MattimonService
ServerInstance[] instances = LocalReadServerInstances();
if (instances != null)
{
result[0] = new SQLServerObjectProvider().GetDeviceServerObjects(
instances, GetDeviceID(), out Exception ex);
}
}
result[1] = loadFromServer;
e.Result = result;
}
};
MattimonSqlServerDataRequestWorker.RunWorkerCompleted += (s, e) =>
{
if (e.Error != null)
{
GUI.BitscoreForms.BitscoreMessageBox.Show(this,
"An error occurred while attempting to post on the server.\n\nError details:\n\n" +
e.Error.Message + "\n" + e.Error.StackTrace +
(e.Error.InnerException != null ? "\n\nInner Exception:\n\n" + e.Error.InnerException.Message + "\n" + e.Error.InnerException.StackTrace : "")
, "Http Request Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
///
/// Result acquired
///
if (e.Result != null && e.Result is object[] result)
{
if (!(result[0] is DeviceServerObjects serverObjects))
{
serverObjects = new DeviceServerObjects();
}
bool local = Convert.ToBoolean(result[1]);
///
/// Handle Server exceptions
///
if (serverObjects.Exception != null || serverObjects.HttpRequestException != null)
{
if (serverObjects.Exception is SqlException sqlex)
{
GUI.BitscoreForms.BitscoreMessageBox.Show(this,
"An error occurred while attempting to post on the server.\n\nError details:\n\n" +
sqlex.Message + "\n" + sqlex.StackTrace +
(sqlex.InnerException != null ? "\n\nInner Exception:\n\n" + sqlex.InnerException.Message + "\n" + sqlex.InnerException.StackTrace : "")
, "Http Request Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (serverObjects.HttpRequestException != null)
{
GUI.BitscoreForms.BitscoreMessageBox.Show(this,
"An error occurred while attempting to post on the server.\n\nError details:\n\n" +
serverObjects.HttpRequestException.Message + "\n" + serverObjects.HttpRequestException.StackTrace +
(serverObjects.HttpRequestException.InnerException != null ? "\n\nInner Exception:\n\n" + serverObjects.HttpRequestException.InnerException.Message + "\n" + serverObjects.HttpRequestException.InnerException.StackTrace : "")
, "Http Request Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return;
}
///
/// Success
///
else
{
UpdateGUISQLServer(serverObjects, null, false);
if (serverObjects.Instances != null)
{
if (serverObjects.Instances.Length == 0 && MyServiceController.GetServiceStatus("MattimonSQLServerService") == MyServiceController.ServiceState.Running)
{
new Thread(() =>
{
MyServiceController.StopService("MattimonSQLServerService");
}).Start();
}
}
}
}
};
mSqlSrvRequestWrkInit = true;
}