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 static void Update(MSMO.StoredProcedure storedProcedure, SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow dataRow)
{
try
{
storedProcedure.UpdateDataSet(dataRow);
UpdateDatabaseWithSnapShot(dataRow, "");
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 10);
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
}
private static SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow GetInfoFromSMO(MSMO.StoredProcedure storedProcedure, Guid databaseID)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
#endif
SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow dataRow = null;
try
{
var dbs = from sp in Common.ApplicationDataSet.DBStoredProcedures
where sp.Database_ID == databaseID
select sp;
var dbs2 = from db2 in dbs
where db2.Name_StoredProcedure == storedProcedure.Name
select db2;
if (dbs2.Count() > 0)
{
dataRow = dbs2.First();
Update(storedProcedure, dataRow);
}
else
{
dataRow = Add(databaseID, storedProcedure);
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3);
}
#if TRACE
VNC.AppLog.Trace4("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4, startTicks);
#endif
return(dataRow);
}
public static void LoadFromSMO(MSMO.Database database, Guid databaseID)
{
#if TRACE
long startTicks = VNC.AppLog.Trace3("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 0);
#endif
MarkExistingItemsAsNotFound(databaseID); // This enables cleanup of items that once existed but were deleted.
foreach (MSMO.StoredProcedure storedProcedure in database.StoredProcedures)
{
if (storedProcedure.IsSystemObject)
{
continue; // Skip System StoredProcedures
}
SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow spRow = GetInfoFromSMO(storedProcedure, databaseID);
spRow.NotFound = false;
}
Common.ApplicationDataSet.DBStoredProceduresTA.Update(Common.ApplicationDataSet.DBStoredProcedures);
#if TRACE
VNC.AppLog.Trace3("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1, startTicks);
#endif
}
private static SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow Add(Guid databaseID, MSMO.StoredProcedure storedProcedure)
{
SQLInformation.Data.ApplicationDataSet.DBStoredProceduresRow dataRow = null;
try
{
dataRow = Common.ApplicationDataSet.DBStoredProcedures.NewDBStoredProceduresRow();
dataRow.ID = Guid.NewGuid(); // See if this is available from SP.
dataRow.Name_StoredProcedure = storedProcedure.Name;
dataRow.StoredProcedure_ID = storedProcedure.ID.ToString();;
dataRow.Database_ID = databaseID; // From above
dataRow.Owner = storedProcedure.Owner;
dataRow.CreateDate = storedProcedure.CreateDate;
dataRow.IsSystemObject = storedProcedure.IsSystemObject;
// Loading the body of the Stored Procedure is expensive. Optional.
if (Data.Config.ADSLoad_DBStoredProcedureContent)
{
try
{
dataRow.MethodName = storedProcedure.MethodName;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
#endif
dataRow.MethodName = "<Not Available>";
}
try
{
dataRow.TextHeader = storedProcedure.TextHeader;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6);
#endif
dataRow.TextHeader = "<No Access>";
}
try
{
dataRow.TextBody = storedProcedure.TextBody;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
#endif
dataRow.TextBody = "<No Access>";
}
}
try
{
dataRow.DateLastModified = storedProcedure.DateLastModified;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 8);
#endif
}
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.DBStoredProcedures.AddDBStoredProceduresRow(dataRow);
Common.ApplicationDataSet.DBStoredProceduresTA.Update(Common.ApplicationDataSet.DBStoredProcedures);
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 9);
// 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);
}
