public void LoadSingleFunctionHistory(BLL.FunctionHistory oFunctionHID)
{
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("select")
.AppendLine(" *")
.AppendLine(" from")
.AppendLine(" [dbFunctionhistory] a")
.AppendLine(" where")
.AppendFormat(" FunctionHistoryID='{0}'", oFunctionHID.FunctionHistoryID);
DataTable oDT = base.ExecuteSQLToDatatable(sSQL.ToString());
if (oDT.Rows.Count > 0)
{
oFunctionHID.FunctionID = (Guid)(oDT.Rows[0]["FunctionID"]);
oFunctionHID.VersionID = DBHelper.NullToString(oDT.Rows[0]["VersionID"]);
oFunctionHID.Description = DBHelper.NullToString(oDT.Rows[0]["description"]);
oFunctionHID.Configuration = oDT.Rows[0]["configuration"].ToString();
oFunctionHID.ChangeDateTime = oDT.Rows[0]["change_date_time"].ToString();
oFunctionHID.ChangeUID = oDT.Rows[0]["change_uid"].ToString();
}
else
{
oFunctionHID = null;
}
}
public void InsertSingleFunctionHistory(BLL.FunctionHistory oFunctionHID)
{
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("insert into [dbFunctionhistory] (")
.AppendLine(" FunctionHistoryID,")
.AppendLine(" FunctionID,")
.AppendLine(" VersionID,")
.AppendLine(" description,")
.AppendLine(" configuration,")
.AppendLine(" currentlyexists,")
.AppendLine(" Change_Date_Time,")
.AppendLine(" Change_UID) values (")
.AppendFormat("'{0}',", oFunctionHID.FunctionHistoryID)
.AppendFormat("'{0}',", oFunctionHID.FunctionID)
.AppendFormat("'{0}',", oFunctionHID.VersionID)
.AppendFormat("'{0}',", oFunctionHID.Description)
.AppendFormat("'{0}',", oFunctionHID.Configuration.Replace("'", "''"))
.AppendFormat("'{0}',", DBHelper.BoolToBit(oFunctionHID.CurrentlyExists))
.AppendFormat("'{0}',", oFunctionHID.ChangeDateTime)
.AppendFormat("'{0}')", oFunctionHID.ChangeUID);
base.ExecuteNonQuery(sSQL.ToString());
}
public void UpdateSingleFunctionHistory(BLL.FunctionHistory oFunctionHID)
{
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("update [dbFunctionhistory] set")
.AppendFormat(" FunctionID='{0}',", oFunctionHID.FunctionID)
.AppendFormat(" VersionID='{0}',", oFunctionHID.VersionID)
.AppendFormat(" description='{0}',", oFunctionHID.Description)
.AppendFormat(" configuration='{0}',", oFunctionHID.Configuration.Replace("'", "''"))
.AppendFormat(" currentlyexists='{0}',", DBHelper.BoolToBit(oFunctionHID.CurrentlyExists))
.AppendFormat(" Change_Date_Time='{0}',", oFunctionHID.ChangeDateTime)
.AppendFormat(" Change_UID='{0}'", oFunctionHID.ChangeUID)
.AppendLine(" where")
.AppendFormat(" FunctionHistoryID='{0}'", oFunctionHID.FunctionHistoryID);
base.ExecuteNonQuery(sSQL.ToString());
}
/// <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.DBFunction_Factory oFactory = new DBL.DBFunction_Factory();
//Delete any Function 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 Functions (in the database) are being re-verified, in case a table has been dropped.
oFactory.MarkScanningInProgress(sDBID: m_DBID);
//Get list of Functions from database
using (AGENT.DBFunctionAgent_Factory oFunctionAgent = new AGENT.DBFunctionAgent_Factory(sConnectionString: sConnectionString))
{
DataTable oAllFunctions = oFunctionAgent.GetList();
ScanCount(SQLAutoDocLib.UTIL.Constants.FUNCTIONNODE, oAllFunctions.Rows.Count);
//For each Function...
foreach (DataRow oFunction in oAllFunctions.Rows)
{
string sFunctionName = oFunction[0].ToString();
//Alert that we're beginning the scan
ScanBegin(SQLAutoDocLib.UTIL.Constants.FUNCTIONNODE, sFunctionName);
//Open a connection to the target server
oFunctionAgent.OpenConnection();
//Create temporary stored Functions
// These will go away when the connection is closed
oFunctionAgent.make_TemporaryStoredProcedures();
//Get an xml summary of table schema
string sXML = oFunctionAgent.GetFunctionSchema(oFunction[0].ToString());
//Close the connection to the target server
oFunctionAgent.CloseConnection();
//Record with same name and xml summary already exists in database?
bool bIsMatch = false;
Guid oFoundFunction = oFactory.FindWithSchemaMatch(
DBID: m_DBID,
sName: sFunctionName,
sSchemaXML: sXML,
IsMatch: out bIsMatch);
if (oFoundFunction == Guid.Empty)
{
// table not found
// create new Function record
BLL.Function oNewFunction = new BLL.Function(DBID: m_DBID);
oNewFunction.Name = sFunctionName;
oNewFunction.Configuration = sXML;
oNewFunction.FindFirstVersion = m_VersionID;
oNewFunction.FindLastVersion = m_VersionID;
oNewFunction.ScanInProgress = false;
oNewFunction.ChangedInLastScan = true;
oNewFunction.Save();
// create new Function version record
BLL.FunctionHistory oNewFunctionHistory = new BLL.FunctionHistory(FunctionID: oNewFunction.FunctionID, VersionID: m_VersionID);
oNewFunctionHistory.Configuration = sXML;
oNewFunctionHistory.Save();
this.ChangesFound = true;
ScanNotFound(SQLAutoDocLib.UTIL.Constants.FUNCTIONNODE, sFunctionName);
}
else if (bIsMatch == false)
{
// Function found but schema has changed
// update Function record
BLL.Function oNewFunction = new BLL.Function(DBID: m_DBID, FunctionID: oFoundFunction);
oNewFunction.Load();
oNewFunction.Configuration = sXML;
oNewFunction.FindLastVersion = m_VersionID;
oNewFunction.ScanInProgress = false;
oNewFunction.ChangedInLastScan = true;
oNewFunction.Save();
// create new table version record
BLL.FunctionHistory oNewFunctionHistory = new BLL.FunctionHistory(FunctionID: oNewFunction.FunctionID, VersionID: m_VersionID);
oNewFunctionHistory.Configuration = sXML;
oNewFunctionHistory.Save();
this.ChangesFound = true;
ScanChanged(SQLAutoDocLib.UTIL.Constants.FUNCTIONNODE, sFunctionName);
}
else
{
// Function found and schema is the same.
// update Function record
BLL.Function oNewFunction = new BLL.Function(DBID: m_DBID, FunctionID: oFoundFunction);
oNewFunction.Load();
oNewFunction.FindLastVersion = m_VersionID;
oNewFunction.ScanInProgress = false;
oNewFunction.ChangedInLastScan = false;
oNewFunction.Save();
}
}
}
//After scan is complete...
//Note that any tables not found have been dropped
DataTable oDT = oFactory.FindUnfound(DBID: m_DBID);
foreach (DataRow oDroppedFunction in oDT.Rows)
{
Guid oFunctionID = (Guid)oDroppedFunction["functionid"];
// update table record
BLL.Function oNewFunction = new BLL.Function(DBID: m_DBID, FunctionID: oFunctionID);
oNewFunction.Load();
oNewFunction.FindLastVersion = m_VersionID;
oNewFunction.ScanInProgress = false;
oNewFunction.ChangedInLastScan = true;
oNewFunction.CurrentlyExists = false;
oNewFunction.Save();
// create new table version record
BLL.FunctionHistory oNewFunctionHistory = new BLL.FunctionHistory(FunctionID: oNewFunction.FunctionID, VersionID: m_VersionID);
oNewFunctionHistory.Configuration = "";
oNewFunctionHistory.CurrentlyExists = false;
oNewFunctionHistory.Save();
this.ChangesFound = true;
ScanDeleted(SQLAutoDocLib.UTIL.Constants.FUNCTIONNODE, oNewFunction.Name);
}
}
