public void LoadSingleTrigger(BLL.Trigger oTriggerID)
{
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("select")
.AppendLine(" *")
.AppendLine(" from")
.AppendLine(" [dbTrigger] a")
.AppendLine(" where")
.AppendFormat(" a.[TriggerID]='{0}'", oTriggerID.TriggerID);
DataTable oDT = base.ExecuteSQLToDatatable(sSQL.ToString());
if (oDT.Rows.Count > 0)
{
oTriggerID.DBID = (Guid)oDT.Rows[0]["DBID"];
oTriggerID.Name = oDT.Rows[0]["name"].ToString();
oTriggerID.Description = DBHelper.NullToString(oDT.Rows[0]["description"]);
oTriggerID.Configuration = DBHelper.NullToString(oDT.Rows[0]["configuration"]);
oTriggerID.FindFirstVersion = DBHelper.NullToString(oDT.Rows[0]["firstfindversion"]);
oTriggerID.FindLastVersion = DBHelper.NullToString(oDT.Rows[0]["lastfindversion"]);
oTriggerID.ScanInProgress = (bool)(oDT.Rows[0]["scaninprogress"]);
oTriggerID.CurrentlyExists = (bool)(oDT.Rows[0]["currentlyexists"]);
oTriggerID.ChangedInLastScan = (bool)(oDT.Rows[0]["changedinlastscan"]);
oTriggerID.ChangeDateTime = oDT.Rows[0]["change_date_time"].ToString();
oTriggerID.ChangeUID = oDT.Rows[0]["change_uid"].ToString();
oTriggerID.ConfigurationUpdated = false;
}
else
{
oTriggerID = null;
}
}
public List <SQLAutoDocLib.BLL.Trigger> ListAllTriggersInDatabase(Guid DBID, string VersionID, bool ChangedOnly)
{
List <SQLAutoDocLib.BLL.Trigger> oList = new List <SQLAutoDocLib.BLL.Trigger>();
DataTable oIDs = m_DBFactory.AllTriggers(DBID: DBID, VersionID: VersionID, ChangedOnly: ChangedOnly);
foreach (DataRow oID in oIDs.Rows)
{
BLL.Trigger oTrigger = new BLL.Trigger(DBID: DBID, TriggerID: (Guid)oID["TriggerID"]);
oTrigger.Load();
oList.Add(oTrigger);
}
return(oList);
}
public void InsertSingleTrigger(BLL.Trigger oTriggerID)
{
List <SqlParameter> oParms = new List <SqlParameter>();
oParms.Add(new SqlParameter("@DBID", oTriggerID.DBID));
oParms.Add(new SqlParameter("@TriggerID", oTriggerID.TriggerID));
oParms.Add(new SqlParameter("@Name", oTriggerID.Name));
oParms.Add(new SqlParameter("@Description", oTriggerID.Description));
oParms.Add(new SqlParameter("@Configuration", oTriggerID.Configuration));
oParms.Add(new SqlParameter("@FirstFindVersion", oTriggerID.FindFirstVersion));
oParms.Add(new SqlParameter("@LastFindVersion", oTriggerID.FindLastVersion));
oParms.Add(new SqlParameter("@ScanInProgress", oTriggerID.ScanInProgress));
oParms.Add(new SqlParameter("@CurrentlyExists", oTriggerID.CurrentlyExists));
oParms.Add(new SqlParameter("@ChangedInLastScan", oTriggerID.ChangedInLastScan));
oParms.Add(new SqlParameter("@ChangeDateTime", oTriggerID.ChangeDateTime));
oParms.Add(new SqlParameter("@ChangeUID", oTriggerID.ChangeUID));
base.ExecuteNonQuery("InsertTrigger", oParms);
}
public string Reconstitute(BLL.Trigger oTrigger)
{
StringBuilder SQL = new StringBuilder();
XmlDocument oDoc = new XmlDocument();
oDoc.LoadXml(oTrigger.Configuration);
SQL.AppendLine("if exists(select 1 from sys.objects where type='tr' and name='" + oTrigger.Name + "') drop proc " + oTrigger.Name);
SQL.AppendLine("GO");
//Get procedure definition
XmlNode oDef = oDoc.SelectSingleNode("/schema/trigger/definition");
string sTrigDef = oDef.InnerText;
SQL.AppendLine(sTrigDef);
SQL.AppendLine("GO");
return(SQL.ToString());
}
public void UpdateSingleTrigger(BLL.Trigger oTriggerID)
{
List <SqlParameter> oParms = new List <SqlParameter>();
oParms.Add(new SqlParameter("@TriggerID", oTriggerID.TriggerID));
oParms.Add(new SqlParameter("@Name", oTriggerID.Name));
oParms.Add(new SqlParameter("@Description", oTriggerID.Description));
oParms.Add(new SqlParameter("@FirstFindVersion", oTriggerID.FindFirstVersion));
oParms.Add(new SqlParameter("@LastFindVersion", oTriggerID.FindLastVersion));
oParms.Add(new SqlParameter("@ScanInProgress", oTriggerID.ScanInProgress));
oParms.Add(new SqlParameter("@CurrentlyExists", oTriggerID.CurrentlyExists));
oParms.Add(new SqlParameter("@ChangedInLastScan", oTriggerID.ChangedInLastScan));
oParms.Add(new SqlParameter("@ChangeDateTime", oTriggerID.ChangeDateTime));
oParms.Add(new SqlParameter("@ChangeUID", oTriggerID.ChangeUID));
if (oTriggerID.ConfigurationUpdated == true)
{
SqlParameter oConfiguration = new SqlParameter("@configuration", SqlDbType.NVarChar, -1);
oConfiguration.Value = oTriggerID.Configuration;
oParms.Add(oConfiguration);
}
base.ExecuteNonQuery("UpdateTrigger", oParms);
}
/// <summary>
/// Scan the database associated with a given connection string.
/// If no data exists in the current sqlautodoc database for this
/// server and database, then records are created, otherwise
/// the current records are used.
/// </summary>
/// <param name="sConnectionString"></param>
public void Scan(string sConnectionString)
{
DBL.DBTrigger_Factory oFactory = new DBL.DBTrigger_Factory();
//Delete any Trigger history records associated with incomplete versions.
//These records would be left over from a previously uncompleted scan.
oFactory.DeleteIncompleteHistory(sDBID: m_DBID);
//Indicate that all known Triggers (in the database) are being re-verified, in case a table has been dropped.
oFactory.MarkScanningInProgress(sDBID: m_DBID);
//Get list of Triggers from database
using (AGENT.DBTriggerAgent_Factory oTriggerAgent = new AGENT.DBTriggerAgent_Factory(sConnectionString: sConnectionString))
{
DataTable oAllTriggers = oTriggerAgent.GetList();
ScanCount(SQLAutoDocLib.UTIL.Constants.TRIGGERNODE, oAllTriggers.Rows.Count);
//For each Trigger...
foreach (DataRow oTrigger in oAllTriggers.Rows)
{
string sTriggerName = oTrigger[0].ToString();
//Alert that we're beginning the scan
ScanBegin(SQLAutoDocLib.UTIL.Constants.TRIGGERNODE, sTriggerName);
//Open a connection to the target server
oTriggerAgent.OpenConnection();
//Create temporary stored Triggers
// These will go away when the connection is closed
oTriggerAgent.make_TemporaryStoredTriggers();
//Get an xml summary of table schema
string sXML = oTriggerAgent.GetTriggerSchema(oTrigger[0].ToString());
//Close the connection to the target server
oTriggerAgent.CloseConnection();
//Record with same name and xml summary already exists in database?
bool bIsMatch = false;
Guid oFoundTrigger = oFactory.FindWithSchemaMatch(
DBID: m_DBID,
sName: sTriggerName,
sSchemaXML: sXML,
IsMatch: out bIsMatch);
if (oFoundTrigger == Guid.Empty)
{
// table not found
// create new Trigger record
BLL.Trigger oNewTrigger = new BLL.Trigger(DBID: m_DBID);
oNewTrigger.Name = sTriggerName;
oNewTrigger.Configuration = sXML;
oNewTrigger.FindFirstVersion = m_VersionID;
oNewTrigger.FindLastVersion = m_VersionID;
oNewTrigger.ScanInProgress = false;
oNewTrigger.ChangedInLastScan = true;
oNewTrigger.Save();
// create new Trigger version record
BLL.TriggerHistory oNewTableHistory = new BLL.TriggerHistory(TriggerID: oNewTrigger.TriggerID, VersionID: m_VersionID);
oNewTableHistory.Configuration = sXML;
oNewTableHistory.Save();
this.ChangesFound = true;
ScanNotFound(SQLAutoDocLib.UTIL.Constants.TRIGGERNODE, sTriggerName);
}
else if (bIsMatch == false)
{
// Trigger found but schema has changed
// update Trigger record
BLL.Trigger oNewTrigger = new BLL.Trigger(DBID: m_DBID, TriggerID: oFoundTrigger);
oNewTrigger.Load();
oNewTrigger.Configuration = sXML;
oNewTrigger.FindLastVersion = m_VersionID;
oNewTrigger.ScanInProgress = false;
oNewTrigger.ChangedInLastScan = true;
oNewTrigger.Save();
// create new table version record
BLL.TriggerHistory oNewTriggerHistory = new BLL.TriggerHistory(TriggerID: oNewTrigger.TriggerID, VersionID: m_VersionID);
oNewTriggerHistory.Configuration = sXML;
oNewTriggerHistory.Save();
this.ChangesFound = true;
ScanChanged(SQLAutoDocLib.UTIL.Constants.TRIGGERNODE, sTriggerName);
}
else
{
// Trigger found and schema is the same.
// update Trigger record
BLL.Trigger oNewTrigger = new BLL.Trigger(DBID: m_DBID, TriggerID: oFoundTrigger);
oNewTrigger.Load();
oNewTrigger.FindLastVersion = m_VersionID;
oNewTrigger.ScanInProgress = false;
oNewTrigger.ChangedInLastScan = false;
oNewTrigger.Save();
}
}
}
//After scan is complete...
//Note that any tables not found have been dropped
DataTable oDT = oFactory.FindUnfound(DBID: m_DBID);
foreach (DataRow oDroppedTrigger in oDT.Rows)
{
Guid oTriggerID = (Guid)oDroppedTrigger["Triggerid"];
// update Trigger record
BLL.Trigger oNewTrigger = new BLL.Trigger(DBID: m_DBID, TriggerID: oTriggerID);
oNewTrigger.Load();
oNewTrigger.FindLastVersion = m_VersionID;
oNewTrigger.ScanInProgress = false;
oNewTrigger.ChangedInLastScan = true;
oNewTrigger.CurrentlyExists = false;
oNewTrigger.Save();
// create new Trigger version record
BLL.TriggerHistory oNewTriggerHistory = new BLL.TriggerHistory(TriggerID: oNewTrigger.TriggerID, VersionID: m_VersionID);
oNewTriggerHistory.Configuration = "";
oNewTriggerHistory.CurrentlyExists = false;
oNewTriggerHistory.Save();
this.ChangesFound = true;
ScanDeleted(SQLAutoDocLib.UTIL.Constants.TRIGGERNODE, oNewTrigger.Name);
}
}
public string Reconstitute(BLL.Trigger oTrigger)
{
SCAN.AGENT.DBTriggerAgent_Factory oFactory = new SCAN.AGENT.DBTriggerAgent_Factory();
return(oFactory.Reconstitute(oTrigger));
}
