private void LoadTables()
{
Data.ApplicationDataSet.DatabasesRow database =
(Data.ApplicationDataSet.DatabasesRow)
((System.Data.DataRowView)databasesDataGrid.SelectedItem).Row;
// Need to go get the Instance Name. For now do the loads in the Instance Page
//SMO.Server server = new SMO.Server(instance.InstanceName);
//server.ConnectionContext.LoginSecure = false; // SQL Authentication
//server.ConnectionContext.Login = "******";
//server.ConnectionContext.Password = "******";
//server.ConnectionContext.ConnectTimeout = 10; // Seconds
//SMOH.Server serverH = new SMOH.Server(server);
//Data.ApplicationDataSetTableAdapters.DatabasesTableAdapter ta = new Data.ApplicationDataSetTableAdapters.DatabasesTableAdapter();
//ta.Connection.ConnectionString = ConfigData.SQLMonitorDBConnection;
//foreach(SMOH.Database dataBase in serverH.Databases.Values)
//{
// Data.ApplicationDataSet.DatabasesRow newDatabase = Common.ApplicationDataSet.Databases.NewDatabasesRow();
// newDatabase.ID = Guid.NewGuid();
// newDatabase.Name = dataBase.Name;
// newDatabase.DataBaseGuid = dataBase.DatabaseGuid;
// //newDatabase.DataBaseGuid = Guid.Parse(dataBase.DatabaseGuid);
// newDatabase.CreateDate = DateTime.Parse(dataBase.CreateDate);
// newDatabase.ID_DB = dataBase.ID;
// newDatabase.Instance_Id = instance.ID;
// Common.ApplicationDataSet.Databases.AddDatabasesRow(newDatabase);
// ta.Update(Common.ApplicationDataSet.Databases);
//}
}
//private void LoadDatabases()
//{
// throw new NotImplementedException();
//}
private static void LoadExtendedProperties(Data.ApplicationDataSet.DatabasesRow database)
{
var instanceName = from item in Common.ApplicationDataSet.Instances
where item.ID == database.Instance_Id
select item.InstanceName;
SMO.Server server = new SMO.Server((string)instanceName.First());
server.ConnectionContext.LoginSecure = false; // SQL Authentication
server.ConnectionContext.Login = "******";
server.ConnectionContext.Password = "******";
server.ConnectionContext.ConnectTimeout = 10; // Seconds
SMO.ExtendedPropertyCollection extendedProps = server.Databases[database.Name].ExtendedProperties;
foreach (SMO.ExtendedProperty prop in extendedProps)
{
Console.WriteLine(string.Format("EP Name:{0} Value:{1}", prop.Name, prop.Value));
}
try { database.EP_Area = (string)extendedProps["EP_Area"].Value; }
catch (Exception) { database.EP_Area = "[Not Set]"; }
try { database.EP_DBApprover = (string)extendedProps["EP_DBApprover"].Value; }
catch (Exception) { database.EP_DBApprover = "[Not Set]"; }
try { database.EP_DRTier = (string)extendedProps["EP_DRTier"].Value; }
catch (Exception) { database.EP_DRTier = "[Not Set]"; }
try { database.EP_PrimaryDBContact = (string)extendedProps["EP_PrimaryDBContact"].Value; }
catch (Exception) { database.EP_PrimaryDBContact = "[Not Set]"; }
try { database.EP_Team = (string)extendedProps["EP_Team"].Value; }
catch (Exception) { database.EP_Team = "[Not Set]"; }
}
private void btnLoadExtendedProperties_Click(object sender, RoutedEventArgs e)
{
Data.ApplicationDataSet.DatabasesRow database =
(Data.ApplicationDataSet.DatabasesRow)
((System.Data.DataRowView)databasesDataGrid.SelectedItem).Row;
LoadExtendedProperties(database);
}
private static void UpdateDatabaseWithSnapShot(Data.ApplicationDataSet.DatabasesRow dataRow, string snapShotError)
{
try
{
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = snapShotError;
Common.ApplicationDataSet.DatabasesTA.Update(Common.ApplicationDataSet.Databases);
}
catch (Exception ex)
{
string errorMessage = string.Format("DatabasesRow.ID:{0} - ex:{1} ex.Inner:{2}", dataRow.ID, ex, ex.InnerException);
VNC.AppLog.Error(errorMessage, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 19);
}
}
public static void UpdateDataSet(this MSMO.Database database, Data.ApplicationDataSet.DatabasesRow databaseRow)
{
try
{
databaseRow.CreateDate = database.CreateDate;
try
{
databaseRow.DataBaseGuid = database.DatabaseGuid.ToString();
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
databaseRow.DataBaseGuid = "<Not Available>";
}
try
{
databaseRow.DataSpaceUsage = database.DataSpaceUsage;
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
}
try
{
if (database.ExtendedProperties.Count > 0)
{
try { databaseRow.EP_Area = (string)database.ExtendedProperties["EP_Area"].Value; }
catch (Exception) { databaseRow.EP_Area = "[Not Set]"; }
try { databaseRow.EP_DBApprover = (string)database.ExtendedProperties["EP_DBApprover"].Value; }
catch (Exception) { databaseRow.EP_DBApprover = "[Not Set]"; }
try { databaseRow.EP_DRTier = (string)database.ExtendedProperties["EP_DRTier"].Value; }
catch (Exception) { databaseRow.EP_DRTier = "[Not Set]"; }
try { databaseRow.EP_PrimaryDBContact = (string)database.ExtendedProperties["EP_PrimaryDBContact"].Value; }
catch (Exception) { databaseRow.EP_PrimaryDBContact = "[Not Set]"; }
try { databaseRow.EP_Team = (string)database.ExtendedProperties["EP_Team"].Value; }
catch (Exception) { databaseRow.EP_Team = "[Not Set]"; }
}
}
catch (Exception ex)
{
// ExtendedProperties.Count throws exceptions on SQL2000??
}
try
{
databaseRow.IndexSpaceUsage = database.IndexSpaceUsage;
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
}
try
{
databaseRow.LastBackupDate = (database.LastBackupDate > SQLMinDateTime ? database.LastBackupDate : SQLMinDateTime);
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
}
try
{
databaseRow.LastDifferentialBackupDate = (database.LastDifferentialBackupDate > SQLMinDateTime ? database.LastDifferentialBackupDate : SQLMinDateTime);
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
}
try
{
databaseRow.LastLogBackupDate = (database.LastLogBackupDate > SQLMinDateTime ? database.LastLogBackupDate : SQLMinDateTime);
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
}
databaseRow.Owner = database.Owner;
databaseRow.RecoveryModel = database.RecoveryModel.ToString();
try
{
databaseRow.Size = database.Size;
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
}
try
{
databaseRow.SpaceAvailable = database.SpaceAvailable;
}
catch (Exception ex)
{
PLLog.Warning(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
}
}
catch (Exception ex)
{
// TODO(crhodes): Need to wrap anything above that throws an exception
// that we want to ignore, e.g. property not available because of
// SQL Edition.
PLLog.Error(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
throw ex;
}
}
public static void UpdateDataSet(this MSMO.Database database, Data.ApplicationDataSet.DatabasesRow dataRow)
{
try
{
// If we got here, update the Instance Name in case it was not accessible for some reason.
dataRow.Name_Instance = dataRow.Name_Instance.ToUpper();
dataRow.CreateDate = database.CreateDate;
try
{
dataRow.DataBaseGuid = database.DatabaseGuid.ToString();
}
catch (Exception)
{
#if TRACE
#endif
dataRow.DataBaseGuid = "<Not Available>";
}
try
{
dataRow.DefaultFileGroup = database.DefaultFileGroup;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 20);
#endif
}
try
{
dataRow.DataSpaceUsage = database.DataSpaceUsage;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 21);
#endif
}
try
{
if (database.ExtendedProperties.Count > 0)
{
try { dataRow.EP_Area = (string)database.ExtendedProperties["EP_Area"].Value; }
catch (Exception) { dataRow.EP_Area = "[Not Set]"; }
try { dataRow.EP_DBApprover = (string)database.ExtendedProperties["EP_DBApprover"].Value; }
catch (Exception) { dataRow.EP_DBApprover = "[Not Set]"; }
try { dataRow.EP_DRTier = (string)database.ExtendedProperties["EP_DRTier"].Value; }
catch (Exception) { dataRow.EP_DRTier = "[Not Set]"; }
try { dataRow.EP_PrimaryDBContact = (string)database.ExtendedProperties["EP_PrimaryDBContact"].Value; }
catch (Exception) { dataRow.EP_PrimaryDBContact = "[Not Set]"; }
try { dataRow.EP_Team = (string)database.ExtendedProperties["EP_Team"].Value; }
catch (Exception) { dataRow.EP_Team = "[Not Set]"; }
}
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 22);
#endif
// ExtendedProperties.Count throws exceptions on SQL2000??
}
try
{
dataRow.IndexSpaceUsage = database.IndexSpaceUsage;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 23);
#endif
}
try
{
dataRow.LastBackupDate = (database.LastBackupDate > SQLMinDateTime ? database.LastBackupDate : SQLMinDateTime);
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 24);
#endif
}
try
{
dataRow.LastDifferentialBackupDate = (database.LastDifferentialBackupDate > SQLMinDateTime ? database.LastDifferentialBackupDate : SQLMinDateTime);
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 25);
#endif
}
try
{
dataRow.LastLogBackupDate = (database.LastLogBackupDate > SQLMinDateTime ? database.LastLogBackupDate : SQLMinDateTime);
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 26);
#endif
}
try
{
dataRow.Owner = database.Owner;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 27);
#endif
}
dataRow.RecoveryModel = database.RecoveryModel.ToString();
try
{
dataRow.Size = database.Size;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 28);
#endif
}
try
{
dataRow.SpaceAvailable = database.SpaceAvailable;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 29);
#endif
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 30);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
}
}
private void SaveExtendedProperties(Data.ApplicationDataSet.DatabasesRow database)
{
var instanceName = from item in Common.ApplicationDataSet.Instances
where item.ID == database.Instance_Id
select item.InstanceName;
SMO.Server server = new SMO.Server((string)instanceName.First());
server.ConnectionContext.LoginSecure = false; // SQL Authentication
server.ConnectionContext.Login = "******";
server.ConnectionContext.Password = "******";
server.ConnectionContext.ConnectTimeout = 10; // Seconds
SMO.ExtendedPropertyCollection extendedProps = server.Databases[database.Name].ExtendedProperties;
SMO.Database smoDatabase = server.Databases[database.Name];
try
{
extendedProps["EP_Area"].Value = database.EP_Area;
extendedProps["EP_Area"].Alter();
}
catch (Exception)
{
SMO.ExtendedProperty ep = new SMO.ExtendedProperty(smoDatabase, "EP_Area", database.EP_Area);
ep.Create();
}
try
{
extendedProps["EP_DBApprover"].Value = database.EP_DBApprover;
extendedProps["EP_DBApprover"].Alter();
}
catch (Exception)
{
SMO.ExtendedProperty ep = new SMO.ExtendedProperty(smoDatabase, "EP_DBApprover", database.EP_DBApprover);
ep.Create();
}
try
{
extendedProps["EP_DRTier"].Value = database.EP_DRTier;
extendedProps["EP_DRTier"].Alter();
}
catch (Exception)
{
SMO.ExtendedProperty ep = new SMO.ExtendedProperty(smoDatabase, "EP_DRTier", database.EP_DRTier);
ep.Create();
}
try
{
extendedProps["EP_PrimaryDBContact"].Value = database.EP_PrimaryDBContact;
extendedProps["EP_PrimaryDBContact"].Alter();
}
catch (Exception)
{
SMO.ExtendedProperty ep = new SMO.ExtendedProperty(smoDatabase, "EP_PrimaryDBContact", database.EP_PrimaryDBContact);
ep.Create();
}
try
{
extendedProps["EP_Team"].Value = database.EP_Team;
extendedProps["EP_Team"].Alter();
}
catch (Exception)
{
SMO.ExtendedProperty ep = new SMO.ExtendedProperty(smoDatabase, "EP_Team", database.EP_Team);
ep.Create();
}
}
