public void LoadSingleFunction(BLL.Function oFunctionID)
{
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("select")
.AppendLine(" *")
.AppendLine(" from")
.AppendLine(" [dbFunction] a")
.AppendLine(" where")
.AppendFormat(" a.[FunctionID]='{0}'", oFunctionID.FunctionID);
DataTable oDT = base.ExecuteSQLToDatatable(sSQL.ToString());
if (oDT.Rows.Count > 0)
{
oFunctionID.DBID = (Guid)oDT.Rows[0]["DBID"];
oFunctionID.Name = oDT.Rows[0]["name"].ToString();
oFunctionID.Description = DBHelper.NullToString(oDT.Rows[0]["description"]);
oFunctionID.Configuration = DBHelper.NullToString(oDT.Rows[0]["configuration"]);
oFunctionID.FindFirstVersion = DBHelper.NullToString(oDT.Rows[0]["firstfindversion"]);
oFunctionID.FindLastVersion = DBHelper.NullToString(oDT.Rows[0]["lastfindversion"]);
oFunctionID.ScanInProgress = (bool)(oDT.Rows[0]["scaninprogress"]);
oFunctionID.CurrentlyExists = (bool)(oDT.Rows[0]["currentlyexists"]);
oFunctionID.ChangedInLastScan = (bool)(oDT.Rows[0]["changedinlastscan"]);
oFunctionID.ChangeDateTime = oDT.Rows[0]["change_date_time"].ToString();
oFunctionID.ChangeUID = oDT.Rows[0]["change_uid"].ToString();
oFunctionID.ConfigurationUpdated = false;
}
else
{
oFunctionID = null;
}
}
public List <SQLAutoDocLib.BLL.Function> ListAllFunctionsInDatabase(Guid DBID, string VersionID, bool ChangedOnly)
{
List <SQLAutoDocLib.BLL.Function> oList = new List <SQLAutoDocLib.BLL.Function>();
DataTable oIDs = m_DBFactory.AllFunctions(DBID: DBID, VersionID: VersionID, ChangedOnly: ChangedOnly);
foreach (DataRow oID in oIDs.Rows)
{
BLL.Function oFunction = new BLL.Function(DBID: DBID, FunctionID: (Guid)oID["FunctionID"]);
oFunction.Load();
oList.Add(oFunction);
}
return(oList);
}
public void InsertSingleFunction(BLL.Function oFunctionID)
{
List <SqlParameter> oParms = new List <SqlParameter>();
oParms.Add(new SqlParameter("@DBID", oFunctionID.DBID));
oParms.Add(new SqlParameter("@FunctionID", oFunctionID.FunctionID));
oParms.Add(new SqlParameter("@Name", oFunctionID.Name));
oParms.Add(new SqlParameter("@Description", oFunctionID.Description));
oParms.Add(new SqlParameter("@Configuration", oFunctionID.Configuration));
oParms.Add(new SqlParameter("@FirstFindVersion", oFunctionID.FindFirstVersion));
oParms.Add(new SqlParameter("@LastFindVersion", oFunctionID.FindLastVersion));
oParms.Add(new SqlParameter("@ScanInProgress", oFunctionID.ScanInProgress));
oParms.Add(new SqlParameter("@CurrentlyExists", oFunctionID.CurrentlyExists));
oParms.Add(new SqlParameter("@ChangedInLastScan", oFunctionID.ChangedInLastScan));
oParms.Add(new SqlParameter("@ChangeDateTime", oFunctionID.ChangeDateTime));
oParms.Add(new SqlParameter("@ChangeUID", oFunctionID.ChangeUID));
base.ExecuteNonQuery("InsertFunction", oParms);
}
public string Reconstitute(BLL.Function oFunction)
{
StringBuilder SQL = new StringBuilder();
XmlDocument oDoc = new XmlDocument();
oDoc.LoadXml(oFunction.Configuration);
SQL.AppendLine("if exists(select 1 from sys.objects where type in ('fn','tf') and name='" + oFunction.Name + "') drop function " + oFunction.Name);
SQL.AppendLine("GO");
//Get procedure definition
XmlNode oDef = oDoc.SelectSingleNode("/schema/function/definition");
string sViewDef = oDef.InnerText;
SQL.AppendLine(sViewDef);
SQL.AppendLine("GO");
return(SQL.ToString());
}
public void UpdateSingleFunction(BLL.Function oFunctionID)
{
List <SqlParameter> oParms = new List <SqlParameter>();
oParms.Add(new SqlParameter("@FunctionID", oFunctionID.FunctionID));
oParms.Add(new SqlParameter("@Name", oFunctionID.Name));
oParms.Add(new SqlParameter("@Description", oFunctionID.Description));
oParms.Add(new SqlParameter("@FirstFindVersion", oFunctionID.FindFirstVersion));
oParms.Add(new SqlParameter("@LastFindVersion", oFunctionID.FindLastVersion));
oParms.Add(new SqlParameter("@ScanInProgress", oFunctionID.ScanInProgress));
oParms.Add(new SqlParameter("@CurrentlyExists", oFunctionID.CurrentlyExists));
oParms.Add(new SqlParameter("@ChangedInLastScan", oFunctionID.ChangedInLastScan));
oParms.Add(new SqlParameter("@ChangeDateTime", oFunctionID.ChangeDateTime));
oParms.Add(new SqlParameter("@ChangeUID", oFunctionID.ChangeUID));
if (oFunctionID.ConfigurationUpdated == true)
{
SqlParameter oConfiguration = new SqlParameter("@configuration", SqlDbType.NVarChar, -1);
oConfiguration.Value = oFunctionID.Configuration;
oParms.Add(oConfiguration);
}
base.ExecuteNonQuery("UpdateFunction", 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.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);
}
}
public string Reconstitute(BLL.Function oFunction)
{
SCAN.AGENT.DBFunctionAgent_Factory oFactory = new SCAN.AGENT.DBFunctionAgent_Factory();
return(oFactory.Reconstitute(oFunction));
}
