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)); }