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