public void LoadSingleView(BLL.View oViewID)
{
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("select")
.AppendLine(" *")
.AppendLine(" from")
.AppendLine(" [dbView] a")
.AppendLine(" where")
.AppendFormat(" a.[ViewID]='{0}'", oViewID.ViewID);
DataTable oDT = base.ExecuteSQLToDatatable(sSQL.ToString());
if (oDT.Rows.Count > 0)
{
oViewID.DBID = (Guid)oDT.Rows[0]["DBID"];
oViewID.Name = oDT.Rows[0]["name"].ToString();
oViewID.Description = DBHelper.NullToString(oDT.Rows[0]["description"]);
oViewID.Configuration = DBHelper.NullToString(oDT.Rows[0]["configuration"]);
oViewID.FindFirstVersion = DBHelper.NullToString(oDT.Rows[0]["firstfindversion"]);
oViewID.FindLastVersion = DBHelper.NullToString(oDT.Rows[0]["lastfindversion"]);
oViewID.ScanInProgress = (bool)(oDT.Rows[0]["scaninprogress"]);
oViewID.CurrentlyExists = (bool)(oDT.Rows[0]["currentlyexists"]);
oViewID.ChangedInLastScan = (bool)(oDT.Rows[0]["changedinlastscan"]);
oViewID.ChangeDateTime = oDT.Rows[0]["change_date_time"].ToString();
oViewID.ChangeUID = oDT.Rows[0]["change_uid"].ToString();
oViewID.ConfigurationUpdated = false;
}
else
{
oViewID = null;
}
}
public List <SQLAutoDocLib.BLL.View> ListAllViewsInDatabase(Guid DBID, string VersionID, bool ChangedOnly)
{
List <SQLAutoDocLib.BLL.View> oList = new List <SQLAutoDocLib.BLL.View>();
DataTable oIDs = m_DBFactory.AllViews(DBID: DBID, VersionID: VersionID, ChangedOnly: ChangedOnly);
foreach (DataRow oID in oIDs.Rows)
{
BLL.View oView = new BLL.View(DBID: DBID, ViewID: (Guid)oID["ViewID"]);
oView.Load();
oList.Add(oView);
}
return(oList);
}
public void InsertSingleView(BLL.View oViewID)
{
List <SqlParameter> oParms = new List <SqlParameter>();
oParms.Add(new SqlParameter("@DBID", oViewID.DBID));
oParms.Add(new SqlParameter("@ViewID", oViewID.ViewID));
oParms.Add(new SqlParameter("@Name", oViewID.Name));
oParms.Add(new SqlParameter("@Description", oViewID.Description));
oParms.Add(new SqlParameter("@Configuration", oViewID.Configuration));
oParms.Add(new SqlParameter("@FirstFindVersion", oViewID.FindFirstVersion));
oParms.Add(new SqlParameter("@LastFindVersion", oViewID.FindLastVersion));
oParms.Add(new SqlParameter("@ScanInProgress", oViewID.ScanInProgress));
oParms.Add(new SqlParameter("@CurrentlyExists", oViewID.CurrentlyExists));
oParms.Add(new SqlParameter("@ChangedInLastScan", oViewID.ChangedInLastScan));
oParms.Add(new SqlParameter("@ChangeDateTime", oViewID.ChangeDateTime));
oParms.Add(new SqlParameter("@ChangeUID", oViewID.ChangeUID));
base.ExecuteNonQuery("InsertView", oParms);
}
/// <summary>
/// Gets the database schema and creats an object model.
/// </summary>
/// <param name="name"></param>
/// <param name="databaseType"></param>
/// <param name="connectionString"></param>
/// <param name="tablePrefixes"></param>
/// <param name="viewPrefixes"></param>
/// <param name="storedProcedurePredixes"></param>
/// <param name="fetchTables"></param>
/// <param name="fetchViews"></param>
/// <param name="fetchStroredProcedures"></param>
/// <returns></returns>
public static Database LoadNewDatabase(
string name,
DatabaseTypes databaseType,
BLL.ConnectionStringHelper connectionString,
List <string> tablePrefixes,
List <string> viewPrefixes,
List <string> storedProcedurePredixes,
bool fetchTables,
bool fetchViews,
bool fetchStroredProcedures)
{
Utility.ResetAllConnections();
Table.TablePrefixes = tablePrefixes ?? new List <string>();
View.ViewPrefixes = viewPrefixes ?? new List <string>();
StoredProcedure.StoredProcedurePrefixes = storedProcedurePredixes ?? new List <string>();
BLL.Table bllTable;
BLL.View bllView;
BLL.StoredProcedure bllStoredProcedure;
Table[] tables = null;
View[] views = null;
StoredProcedure[] storedProcedures = null;
if (fetchTables)
{
bllTable = new BLL.Table(databaseType, connectionString);
tables = bllTable.Tables;
}
if (fetchViews)
{
bllView = new BLL.View(databaseType, connectionString);
views = bllView.Views;
}
if (fetchStroredProcedures)
{
bllStoredProcedure = new BLL.StoredProcedure(databaseType, connectionString);
storedProcedures = bllStoredProcedure.StoredProcedures;
}
return(new Database(name, connectionString, databaseType, tables, views, storedProcedures));
}
public string Reconstitute(BLL.View oView)
{
StringBuilder SQL = new StringBuilder();
XmlDocument oDoc = new XmlDocument();
oDoc.LoadXml(oView.Configuration);
SQL.AppendLine("if exists(select 1 from sys.objects where type='v' and name='" + oView.Name + "') drop trigger " + oView.Name);
SQL.AppendLine("GO");
//Get procedure definition
XmlNode oDef = oDoc.SelectSingleNode("/schema/view/definition");
string sViewDef = oDef.InnerText;
SQL.AppendLine(sViewDef);
SQL.AppendLine("GO");
return(SQL.ToString());
}
public void UpdateSingleView(BLL.View oViewID)
{
List <SqlParameter> oParms = new List <SqlParameter>();
oParms.Add(new SqlParameter("@ViewID", oViewID.ViewID));
oParms.Add(new SqlParameter("@Name", oViewID.Name));
oParms.Add(new SqlParameter("@Description", oViewID.Description));
oParms.Add(new SqlParameter("@FirstFindVersion", oViewID.FindFirstVersion));
oParms.Add(new SqlParameter("@LastFindVersion", oViewID.FindLastVersion));
oParms.Add(new SqlParameter("@ScanInProgress", oViewID.ScanInProgress));
oParms.Add(new SqlParameter("@CurrentlyExists", oViewID.CurrentlyExists));
oParms.Add(new SqlParameter("@ChangedInLastScan", oViewID.ChangedInLastScan));
oParms.Add(new SqlParameter("@ChangeDateTime", oViewID.ChangeDateTime));
oParms.Add(new SqlParameter("@ChangeUID", oViewID.ChangeUID));
if (oViewID.ConfigurationUpdated == true)
{
SqlParameter oConfiguration = new SqlParameter("@configuration", SqlDbType.NVarChar, -1);
oConfiguration.Value = oViewID.Configuration;
oParms.Add(oConfiguration);
}
base.ExecuteNonQuery("UpdateView", 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.DBView_Factory oFactory = new DBL.DBView_Factory();
//Delete any View 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 Views (in the database) are being re-verified, in case a table has been dropped.
oFactory.MarkScanningInProgress(sDBID: m_DBID);
//Get list of Views from database
using (AGENT.DBViewAgent_Factory oViewAgent = new AGENT.DBViewAgent_Factory(sConnectionString: sConnectionString))
{
DataTable oAllViews = oViewAgent.GetList();
ScanCount(SQLAutoDocLib.UTIL.Constants.VIEWNODE, oAllViews.Rows.Count);
//For each View...
foreach (DataRow oView in oAllViews.Rows)
{
string sViewName = oView[0].ToString();
//Alert that we're beginning the scan
ScanBegin(SQLAutoDocLib.UTIL.Constants.VIEWNODE, sViewName);
//Open a connection to the target server
oViewAgent.OpenConnection();
//Create temporary stored Views
// These will go away when the connection is closed
oViewAgent.make_TemporaryStoredViews();
//Get an xml summary of table schema
string sXML = oViewAgent.GetViewSchema(oView[0].ToString());
//Close the connection to the target server
oViewAgent.CloseConnection();
//Record with same name and xml summary already exists in database?
bool bIsMatch = false;
Guid oFoundView = oFactory.FindWithSchemaMatch(
DBID: m_DBID,
sName: sViewName,
sSchemaXML: sXML,
IsMatch: out bIsMatch);
if (oFoundView == Guid.Empty)
{
// table not found
// create new View record
BLL.View oNewView = new BLL.View(DBID: m_DBID);
oNewView.Name = sViewName;
oNewView.Configuration = sXML;
oNewView.FindFirstVersion = m_VersionID;
oNewView.FindLastVersion = m_VersionID;
oNewView.ScanInProgress = false;
oNewView.ChangedInLastScan = true;
oNewView.Save();
// create new View version record
BLL.ViewHistory oNewTableHistory = new BLL.ViewHistory(ViewID: oNewView.ViewID, VersionID: m_VersionID);
oNewTableHistory.Configuration = sXML;
oNewTableHistory.Save();
this.ChangesFound = true;
ScanNotFound(SQLAutoDocLib.UTIL.Constants.VIEWNODE, sViewName);
}
else if (bIsMatch == false)
{
// View found but schema has changed
// update View record
BLL.View oNewView = new BLL.View(DBID: m_DBID, ViewID: oFoundView);
oNewView.Load();
oNewView.Configuration = sXML;
oNewView.FindLastVersion = m_VersionID;
oNewView.ScanInProgress = false;
oNewView.ChangedInLastScan = true;
oNewView.Save();
// create new table version record
BLL.ViewHistory oNewViewHistory = new BLL.ViewHistory(ViewID: oNewView.ViewID, VersionID: m_VersionID);
oNewViewHistory.Configuration = sXML;
oNewViewHistory.Save();
this.ChangesFound = true;
ScanChanged(SQLAutoDocLib.UTIL.Constants.VIEWNODE, sViewName);
}
else
{
// View found and schema is the same.
// update View record
BLL.View oNewView = new BLL.View(DBID: m_DBID, ViewID: oFoundView);
oNewView.Load();
oNewView.FindLastVersion = m_VersionID;
oNewView.ScanInProgress = false;
oNewView.ChangedInLastScan = false;
oNewView.Save();
}
}
}
//After scan is complete...
//Note that any tables not found have been dropped
DataTable oDT = oFactory.FindUnfound(DBID: m_DBID);
foreach (DataRow oDroppedView in oDT.Rows)
{
Guid oViewID = (Guid)oDroppedView["Viewid"];
// update View record
BLL.View oNewView = new BLL.View(DBID: m_DBID, ViewID: oViewID);
oNewView.Load();
oNewView.FindLastVersion = m_VersionID;
oNewView.ScanInProgress = false;
oNewView.ChangedInLastScan = true;
oNewView.CurrentlyExists = false;
oNewView.Save();
// create new View version record
BLL.ViewHistory oNewViewHistory = new BLL.ViewHistory(ViewID: oNewView.ViewID, VersionID: m_VersionID);
oNewViewHistory.Configuration = "";
oNewViewHistory.CurrentlyExists = false;
oNewViewHistory.Save();
this.ChangesFound = true;
ScanDeleted(SQLAutoDocLib.UTIL.Constants.VIEWNODE, oNewView.Name);
}
}
public string Reconstitute(BLL.View oView)
{
SCAN.AGENT.DBViewAgent_Factory oFactory = new SCAN.AGENT.DBViewAgent_Factory();
return(oFactory.Reconstitute(oView));
}
