/// <summary>
/// Store current values in DatabaseInfo history table.
/// </summary>
/// <param name="instance"></param>
/// <param name="database"></param>
private void TakeSnapShot(string instanceName, SQLInformation.Data.ApplicationDataSet.DatabasesRow databaseRow)
{
//SMO.Server server = SMOH.SMOD.GetServer(instanceName);
SMO.Server server = SQLInformation.SMO.Helper.GetServer(instanceName);
SQLInformation.Data.ApplicationDataSetTableAdapters.DatabaseInfoTableAdapter tableAdapter = new SQLInformation.Data.ApplicationDataSetTableAdapters.DatabaseInfoTableAdapter();
tableAdapter.Connection.ConnectionString = SQLInformation.Data.Config.SQLMonitorDBConnection;
int dbID = databaseRow.ID_DB;
SMO.Database db = server.Databases.ItemById(dbID);
SQLInformation.Data.ApplicationDataSet.DatabaseInfoRow newSnapShot = Common.ApplicationDataSet.DatabaseInfo.NewDatabaseInfoRow();
newSnapShot.SnapShotDate = DateTime.Now;
newSnapShot.Database_ID = databaseRow.ID;
newSnapShot.Instance_ID = databaseRow.Instance_ID;
newSnapShot.IndexSpaceUsage = db.IndexSpaceUsage;
newSnapShot.DataSpaceUsage = db.DataSpaceUsage;
newSnapShot.Size = db.Size;
newSnapShot.SpaceAvailable = db.SpaceAvailable;
Common.ApplicationDataSet.DatabaseInfo.AddDatabaseInfoRow(newSnapShot);
tableAdapter.Update(Common.ApplicationDataSet.DatabaseInfo);
}
// TODO. Make this look like LoadStoredProcedures
private static void LoadExtendedProperties(SQLInformation.Data.ApplicationDataSet.DatabasesRow database)
{
var instanceName = from item in Common.ApplicationDataSet.Instances
where item.ID == database.Instance_ID
select item.Name_Instance;
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_Database].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 OnDisplayDBLogins(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
{
// Get the list of logins for the Database
var row = gc_Databases.View.FocusedRowData.Row;
SQLInformation.Data.ApplicationDataSet.DatabasesRow database =
(SQLInformation.Data.ApplicationDataSet.DatabasesRow)((DataRowView)row).Row;
Guid instanceID = database.Instance_ID;
Guid dbID = database.ID;
var logins = Common.ApplicationDataSet.Logins.Where(l => l.Instance_ID == instanceID).OrderBy(n => n.Name_Login);
var dbUsers = Common.ApplicationDataSet.DBUsers.Where(u => u.Database_ID == dbID).OrderBy(n => n.Name_User);
var win = new EyeOnLife.User_Interface.Windows.InstanceDatabaseInfo();
win.tb_Instance.Text = database.Name_Instance;
win.tb_Database.Text = database.Name_Database;
win.lv_Logins.ItemsSource = logins;
win.lv_Users.ItemsSource = dbUsers;
win.Show();
//foreach (var login in logins)
//{
// System.Diagnostics.Debug.WriteLine("{0} - {1}", login.Name_Instance, login.Name_Login);
//}
//foreach (var user in dbUsers)
//{
// System.Diagnostics.Debug.WriteLine("{0} - {1}", user.Login, user.Name_User);
//}
}
private void btnSaveExtendedProperties_Click(object sender, RoutedEventArgs e)
{
SQLInformation.Data.ApplicationDataSet.DatabasesRow database =
(SQLInformation.Data.ApplicationDataSet.DatabasesRow)
((System.Data.DataRowView)dataGrid.SelectedItem).Row;
SaveExtendedProperties(database);
}
private void LoadStoredProcedures(string instanceName, SQLInformation.Data.ApplicationDataSet.DatabasesRow databaseRow)
{
#if TRACE
//long startTicks = Common.WriteToDebugWindow(string.Format("Enter {0}:{1}()", TYPE_NAME, System.Reflection.MethodInfo.GetCurrentMethod().Name));
#endif
SMO.Server server = SMOH.SMOD.GetServer(instanceName);
SQLInformation.Data.ApplicationDataSetTableAdapters.DBStoredProceduresTableAdapter tableAdapter = new SQLInformation.Data.ApplicationDataSetTableAdapters.DBStoredProceduresTableAdapter();
tableAdapter.Connection.ConnectionString = SQLInformation.Data.Config.SQLMonitorDBConnection;
int dbID = databaseRow.ID_DB;
SMO.Database db = server.Databases.ItemById(dbID);
foreach (SMO.StoredProcedure storedProcedure in db.StoredProcedures)
{
if (storedProcedure.IsSystemObject)
{
continue; // Skip System StoredProcedures
}
SMOH.StoredProcedure spH = new SMOH.StoredProcedure(storedProcedure);
SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow newStoredProcedure = Common.ApplicationDataSet.DBStoredProcedures.NewDBStoredProceduresRow();
newStoredProcedure.ID = Guid.NewGuid(); // See if this is available from SP.
newStoredProcedure.Name_StoredProcedure = spH.Name;
newStoredProcedure.StoredProcedure_ID = spH.ID;
newStoredProcedure.Database_ID = databaseRow.ID; // From above
newStoredProcedure.Owner = spH.Owner;
newStoredProcedure.CreateDate = DateTime.Parse(spH.CreateDate);
newStoredProcedure.IsSystemObject = bool.Parse(spH.IsSystemObject);
newStoredProcedure.MethodName = spH.MethodName;
newStoredProcedure.TextHeader = spH.TextHeader;
newStoredProcedure.TextBody = spH.TextBody;
try
{
newStoredProcedure.DateLastModified = DateTime.Parse(spH.DateLastModified);
}
catch (Exception ex)
{
}
Common.ApplicationDataSet.DBStoredProcedures.AddDBStoredProceduresRow(newStoredProcedure);
tableAdapter.Update(Common.ApplicationDataSet.DBStoredProcedures);
}
#if TRACE
//Common.WriteToDebugWindow(string.Format("Exit {0}:{1}()", TYPE_NAME, System.Reflection.MethodInfo.GetCurrentMethod().Name), startTicks);
#endif
}
private void btnLoadStoredProcedures_Click(object sender, RoutedEventArgs e)
{
DataGridRow row = this.dataGrid.ItemContainerGenerator.ContainerFromIndex(this.dataGrid.SelectedIndex) as DataGridRow;
ComboBox ele = this.dataGrid.Columns[3].GetCellContent(row) as ComboBox;
string instanceName = ele.Text;
SQLInformation.Data.ApplicationDataSet.DatabasesRow database =
(SQLInformation.Data.ApplicationDataSet.DatabasesRow)
((System.Data.DataRowView)dataGrid.SelectedItem).Row;
LoadStoredProcedures(instanceName, database);
}
private void LoadViews(string instanceName, SQLInformation.Data.ApplicationDataSet.DatabasesRow databaseRow)
{
#if TRACE
//long startTicks = Common.WriteToDebugWindow(string.Format("Enter {0}:{1}()", TYPE_NAME, System.Reflection.MethodInfo.GetCurrentMethod().Name));
#endif
SMO.Server server = SMOH.SMOD.GetServer(instanceName);
SQLInformation.Data.ApplicationDataSetTableAdapters.DBViewsTableAdapter tableAdapter = new SQLInformation.Data.ApplicationDataSetTableAdapters.DBViewsTableAdapter();
tableAdapter.Connection.ConnectionString = SQLInformation.Data.Config.SQLMonitorDBConnection;
int dbID = databaseRow.ID_DB;
SMO.Database db = server.Databases.ItemById(dbID);
foreach (SMO.View view in db.Views)
{
if (view.IsSystemObject)
{
continue; // Skip System Views
}
SMOH.View viewH = new SMOH.View(view);
SQLInformation.Data.ApplicationDataSet.DBViewsRow newView = Common.ApplicationDataSet.DBViews.NewDBViewsRow();
newView.ID = Guid.NewGuid();
newView.Name_View = viewH.Name;
newView.View_ID = Int32.Parse(viewH.ID);
newView.Database_ID = databaseRow.ID; // From above
newView.Owner = viewH.Owner;
newView.CreateDate = DateTime.Parse(viewH.CreateDate);
//newView.DataSpaceUsed = int.Parse(viewH.DataSpaceUsed); // This is not supported yet.
try
{
newView.DateLastModified = DateTime.Parse(viewH.DateLastModified);
}
catch (Exception ex)
{
}
Common.ApplicationDataSet.DBViews.AddDBViewsRow(newView);
tableAdapter.Update(Common.ApplicationDataSet.DBViews);
}
#if TRACE
//Common.WriteToDebugWindow(string.Format("Exit {0}:{1}()", TYPE_NAME, System.Reflection.MethodInfo.GetCurrentMethod().Name), startTicks);
#endif
}
private void btnTakeSnapshot_Click(object sender, RoutedEventArgs e)
{
// This seems ridiculous but what the heck, it works.
DataGridRow row = this.dataGrid.ItemContainerGenerator.ContainerFromIndex(this.dataGrid.SelectedIndex) as DataGridRow;
ComboBox ele = this.dataGrid.Columns[3].GetCellContent(row) as ComboBox;
string instanceName = ele.Text;
SQLInformation.Data.ApplicationDataSet.DatabasesRow databaseRow =
(SQLInformation.Data.ApplicationDataSet.DatabasesRow)
((System.Data.DataRowView)dataGrid.SelectedItem).Row;
TakeSnapShot(instanceName, databaseRow);
}
private void OnUpdateExtendedProperties(object sender, RoutedEventArgs e)
{
var row = dataGrid.View.FocusedRowData.Row;
SQLInformation.Data.ApplicationDataSet.DatabasesRow database =
(SQLInformation.Data.ApplicationDataSet.DatabasesRow)((DataRowView)row).Row;
SaveExtendedProperties(database);
// NB. The UI textBoxes are Bound to the dataGrid. No need to updated.
// However, push to SQLMonitor DB so still around if user relauches app before crawl updates.
Common.ApplicationDataSet.Databases_Update();
//Common.ApplicationDataSet.DatabasesTA.Update(Common.ApplicationDataSet.Databases);
}
private void LoadTables(string instanceName, SQLInformation.Data.ApplicationDataSet.DatabasesRow databaseRow)
{
#if TRACE
//long startTicks = Common.WriteToDebugWindow(string.Format("Enter {0}:{1}()", TYPE_NAME, System.Reflection.MethodInfo.GetCurrentMethod().Name));
#endif
SMO.Server server = SMOH.SMOD.GetServer(instanceName);
SQLInformation.Data.ApplicationDataSetTableAdapters.DBTablesTableAdapter tableAdapter = new SQLInformation.Data.ApplicationDataSetTableAdapters.DBTablesTableAdapter();
tableAdapter.Connection.ConnectionString = SQLInformation.Data.Config.SQLMonitorDBConnection;
int dbID = databaseRow.ID_DB;
SMO.Database db = server.Databases.ItemById(dbID);
foreach (SMO.Table table in db.Tables)
{
SMOH.Table tableH = new SMOH.Table(table);
SQLInformation.Data.ApplicationDataSet.DBTablesRow newTable = Common.ApplicationDataSet.DBTables.NewDBTablesRow();
newTable.ID = Guid.NewGuid(); // See if this is available from table.
newTable.Name_Table = tableH.Name;
newTable.Table_ID = tableH.ID;
newTable.Database_ID = databaseRow.ID; // From above
newTable.Owner = tableH.Owner;
newTable.CreateDate = DateTime.Parse(tableH.CreateDate);
newTable.DataSpaceUsed = int.Parse(tableH.DataSpaceUsed);
try
{
newTable.DateLastModified = DateTime.Parse(tableH.DateLastModified);
}
catch (Exception ex)
{
}
newTable.RowCount = int.Parse(tableH.RowCount);
Common.ApplicationDataSet.DBTables.AddDBTablesRow(newTable);
tableAdapter.Update(Common.ApplicationDataSet.DBTables);
}
#if TRACE
//Common.WriteToDebugWindow(string.Format("Exit {0}:{1}()", TYPE_NAME, System.Reflection.MethodInfo.GetCurrentMethod().Name), startTicks);
#endif
}
private static void Update(MSMO.Database database, SQLInformation.Data.ApplicationDataSet.DatabasesRow dataRow)
{
try
{
if (dataRow.IsMonitored)
{
database.UpdateDataSet(dataRow);
UpdateDatabaseWithSnapShot(dataRow, "");
// Add the Snapshot
SMO.Helper.TakeDatabaseSnapShot(dataRow);
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 18);
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
}
public static void TakeDatabaseSnapShot(SQLInformation.Data.ApplicationDataSet.DatabasesRow databaseRow)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4("Start", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 0);
#endif
Data.ApplicationDataSet.DatabaseInfoRow databaseInfoRow = null;
try
{
databaseInfoRow = Common.ApplicationDataSet.DatabaseInfo.NewDatabaseInfoRow();
databaseInfoRow.SnapShotDate = databaseRow.SnapShotDate;
databaseInfoRow.SnapShotError = databaseRow.SnapShotError;
databaseInfoRow.Database_ID = databaseRow.ID;
databaseInfoRow.Name_Database = databaseRow.Name_Database;
databaseInfoRow.Instance_ID = databaseRow.Instance_ID;
databaseInfoRow.Name_Instance = databaseRow.Name_Instance;
databaseInfoRow.IndexSpaceUsage = databaseRow.IndexSpaceUsage;
databaseInfoRow.DataSpaceUsage = databaseRow.DataSpaceUsage;
databaseInfoRow.Size = databaseRow.Size;
databaseInfoRow.SpaceAvailable = databaseRow.SpaceAvailable;
Common.ApplicationDataSet.DatabaseInfo.AddDatabaseInfoRow(databaseInfoRow);
Common.ApplicationDataSet.DatabaseInfoTA.Update(Common.ApplicationDataSet.DatabaseInfo);
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
databaseInfoRow.SnapShotDate = DateTime.Now;
databaseInfoRow.SnapShotError = ex.ToString().Substring(0, 256);
Common.ApplicationDataSet.DatabaseInfoTA.Update(Common.ApplicationDataSet.DatabaseInfo);
}
#if TRACE
VNC.AppLog.Trace4("End", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3, startTicks);
#endif
}
private static SQLInformation.Data.ApplicationDataSet.DatabasesRow GetInfoFromSMO(Guid instanceID, string instanceName, MSMO.Database database)
{
#if TRACE
long startTicks = VNC.AppLog.Trace3(string.Format("Enter ({0})", database.Name), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4);
#endif
Debug.WriteLine(" DB:{0}", database.Name);
SQLInformation.Data.ApplicationDataSet.DatabasesRow dataRow = null;
try
{
var dbs = from db in Common.ApplicationDataSet.Databases
where db.Instance_ID == instanceID
select db;
var dbs2 = from db2 in dbs
where db2.Name_Database == database.Name
select db2;
if (dbs2.Count() > 0)
{
dataRow = dbs2.First();
Update(database, dataRow);
}
else
{
dataRow = Add(instanceID, instanceName, database);
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
}
#if TRACE
VNC.AppLog.Trace3(string.Format("Exit ({0})", database.Name), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6, startTicks);
#endif
return(dataRow);
}
private void SaveExtendedProperties(SQLInformation.Data.ApplicationDataSet.DatabasesRow database)
{
var instanceName = from item in Common.ApplicationDataSet.Instances
where item.ID == database.Instance_ID
select item.Name_Instance;
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_Database].ExtendedProperties;
SMO.Database smoDatabase = server.Databases[database.Name_Database];
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();
}
}
public static void LoadFromSMO(MSMO.Server server, Guid instanceID, ExpandMask.DatabaseExpandSetting databaseExpandSettings)
{
#if TRACE
long startTicks = VNC.AppLog.Trace2("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 0);
#endif
long stopwatchFrequency = Stopwatch.Frequency;
MarkExistingItemsAsNotFound(instanceID); // This enables cleanup of items that once existed but were deleted.
foreach (MSMO.Database database in server.Databases)
{
long loopStartTicks = Stopwatch.GetTimestamp();
if (!database.IsAccessible)
{
VNC.AppLog.Warning(string.Format("Database: {0} is not accessible, skipping", database.Name), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1);
continue;
}
SQLInformation.Data.ApplicationDataSet.DatabasesRow databaseRow = GetInfoFromSMO(instanceID, server.Name, database);
databaseRow.NotFound = false;
try
{
if (databaseRow.IsMonitored && databaseExpandSettings.IsMonitored)
{
if (databaseRow.ExpandStoredProcedures && databaseExpandSettings.ExpandStoredProcedures)
{
StoredProcedure.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandTables && databaseExpandSettings.ExpandTables)
{
ExpandMask.TableExpandSetting tableExpandSetting = new ExpandMask.TableExpandSetting(databaseRow.DefaultTableExpandMask);
Table.LoadFromSMO(database, databaseRow.ID, tableExpandSetting);
}
if (databaseRow.ExpandViews && databaseExpandSettings.ExpandViews)
{
ExpandMask.ViewExpandSetting viewExpandSetting = new ExpandMask.ViewExpandSetting(databaseRow.DefaultViewExpandMask);
View.LoadFromSMO(database, databaseRow.ID, viewExpandSetting);
}
if (databaseRow.ExpandFileGroups && databaseExpandSettings.ExpandFileGroups)
{
FileGroup.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandLogFiles && databaseExpandSettings.ExpandLogFiles)
{
LogFile.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandRoles && databaseExpandSettings.ExpandRoles)
{
DatabaseRole.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandTriggers && databaseExpandSettings.ExpandTriggers)
{
DatabaseDdlTrigger.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandUserDefinedFunctions && databaseExpandSettings.ExpandUserDefinedFunctions)
{
UserDefinedFunction.LoadFromSMO(database, databaseRow.ID);
}
if (databaseRow.ExpandUsers && databaseExpandSettings.ExpandUsers)
{
User.LoadFromSMO(database, databaseRow.ID, databaseRow.Name_Database);
}
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
}
databaseRow.SnapShotDuration = (Stopwatch.GetTimestamp() - loopStartTicks) / stopwatchFrequency;
Common.ApplicationDataSet.DatabasesTA.Update(Common.ApplicationDataSet.Databases);
}
#if TRACE
VNC.AppLog.Trace2("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3, startTicks);
#endif
}
private static SQLInformation.Data.ApplicationDataSet.DatabasesRow Add(Guid instanceID, string instanceName, MSMO.Database database)
{
SQLInformation.Data.ApplicationDataSet.DatabasesRow dataRow = null;
try
{
dataRow = Common.ApplicationDataSet.Databases.NewDatabasesRow();
dataRow.ID = Guid.NewGuid();
dataRow.Name_Database = database.Name;
dataRow.CreateDate = database.CreateDate;
try
{
dataRow.DataBaseGuid = database.DatabaseGuid.ToString();
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
#endif
dataRow.DataBaseGuid = "<Not Available>";
}
dataRow.ID_DB = database.ID;
dataRow.Instance_ID = instanceID;
dataRow.Name_Instance = instanceName;
try
{
dataRow.DefaultFileGroup = database.DefaultFileGroup;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 8);
#endif
}
try
{
dataRow.DataSpaceUsage = database.DataSpaceUsage;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 9);
#endif
}
try
{
dataRow.IndexSpaceUsage = database.IndexSpaceUsage;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 10);
#endif
}
try
{
dataRow.Size = database.Size;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 11);
#endif
}
try
{
dataRow.SpaceAvailable = database.SpaceAvailable;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 12);
#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 + 13);
#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 + 14);
#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 + 15);
#endif
}
try
{
dataRow.Owner = database.Owner;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 16);
#endif
}
dataRow.RecoveryModel = database.RecoveryModel.ToString();
// TODO(crhodes): Think through how to set this.
ExpandMask.DatabaseExpandSetting expandSetting = new ExpandMask.DatabaseExpandSetting(instanceID);
dataRow.IsMonitored = expandSetting.IsMonitored;
dataRow.ExpandDataFiles = expandSetting.ExpandDataFiles;
dataRow.ExpandFileGroups = expandSetting.ExpandFileGroups;
dataRow.ExpandLogFiles = expandSetting.ExpandLogFiles;
dataRow.ExpandRoles = expandSetting.ExpandRoles;
dataRow.ExpandStoredProcedures = expandSetting.ExpandStoredProcedures;
dataRow.ExpandTables = expandSetting.ExpandTables;
dataRow.ExpandTriggers = expandSetting.ExpandTriggers;
dataRow.ExpandUserDefinedFunctions = expandSetting.ExpandUserDefinedFunctions;
dataRow.ExpandUsers = expandSetting.ExpandUsers;
dataRow.ExpandViews = expandSetting.ExpandViews;
// TODO(crhodes): Need to get this passed in.
dataRow.DefaultTableExpandMask = 0;
dataRow.DefaultViewExpandMask = 0;
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.Databases.AddDatabasesRow(dataRow);
Common.ApplicationDataSet.DatabasesTA.Update(Common.ApplicationDataSet.Databases);
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 17);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
return(dataRow);
}
private void SaveExtendedProperties(SQLInformation.Data.ApplicationDataSet.DatabasesRow database)
{
var instances = from item in Common.ApplicationDataSet.Instances
where item.ID == database.Instance_ID
select new { Name = item.Name_Instance, Port = item.Port };
string instanceName = (string)instances.First().Name;
int port = (int)instances.First().Port;
//MSMO.Server server = new MSMO.Server((string)instances.First());
Microsoft.SqlServer.Management.Common.ServerConnection connection = new Microsoft.SqlServer.Management.Common.ServerConnection();
connection.ServerInstance = string.Format("{0},{1}", instanceName, port);
connection.NetworkProtocol = Microsoft.SqlServer.Management.Common.NetworkProtocol.TcpIp;
MSMO.Server server = new MSMO.Server(connection);
server.ConnectionContext.LoginSecure = true; // SQL Authentication
//server.ConnectionContext.Login = "******";
//server.ConnectionContext.Password = "******";
server.ConnectionContext.ConnectTimeout = 10; // Seconds
MSMO.ExtendedPropertyCollection extendedProps = server.Databases[database.Name_Database].ExtendedProperties;
MSMO.Database smoDatabase = server.Databases[database.Name_Database];
try
{
//extendedProps["EP_Area"].Value = database.EP_Area;
extendedProps["EP_Area"].Value = te_EP_Area.Text;
extendedProps["EP_Area"].Alter();
}
catch (Exception)
{
MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_Area", te_EP_Area.Text);
//MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_Area", database.EP_Area);
ep.Create();
}
try
{
extendedProps["EP_DBApprover"].Value = te_EP_DBApprover.Text;
extendedProps["EP_DBApprover"].Alter();
}
catch (Exception)
{
MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_DBApprover", te_EP_DBApprover.Text);
ep.Create();
}
try
{
extendedProps["EP_DRTier"].Value = te_EP_DRTier.Text;
extendedProps["EP_DRTier"].Alter();
}
catch (Exception)
{
MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_DRTier", te_EP_DRTier.Text);
ep.Create();
}
try
{
extendedProps["EP_PrimaryDBContact"].Value = te_EP_PrimaryDBContact.Text;
extendedProps["EP_PrimaryDBContact"].Alter();
}
catch (Exception)
{
MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_PrimaryDBContact", te_EP_PrimaryDBContact.Text);
ep.Create();
}
try
{
extendedProps["EP_Team"].Value = te_EP_Team.Text;
extendedProps["EP_Team"].Alter();
}
catch (Exception)
{
MSMO.ExtendedProperty ep = new MSMO.ExtendedProperty(smoDatabase, "EP_Team", te_EP_Team.Text);
ep.Create();
}
}
