public List <SQLAutoDocLib.BLL.Table> ListAllTablesInDatabase(Guid DBID, string VersionID, bool ChangedOnly)
{
List <SQLAutoDocLib.BLL.Table> oList = new List <SQLAutoDocLib.BLL.Table>();
DataTable oIDs = m_DBFactory.AllTables(DBID: DBID, VersionID: VersionID, ChangedOnly: ChangedOnly);
foreach (DataRow oID in oIDs.Rows)
{
BLL.Table oTable = new BLL.Table(DBID: DBID, TableID: (Guid)oID["TableID"]);
oTable.LoadFromRow(oID);
oList.Add(oTable);
}
return(oList);
}
public List <SQLAutoDocLib.BLL.Table> ListAllTablesInDatabase(Guid DBID)
{
List <SQLAutoDocLib.BLL.Table> oList = new List <SQLAutoDocLib.BLL.Table>();
DataTable oIDs = m_DBFactory.AllTables(DBID: DBID);
foreach (DataRow oID in oIDs.Rows)
{
BLL.Table oTable = new BLL.Table(DBID: DBID, TableID: (Guid)oID["TableID"]);
oTable.Load();
oList.Add(oTable);
}
return(oList);
}
public string Reconstitute(BLL.Table oTable)
{
StringBuilder SQL = new StringBuilder();
XmlDocument oDoc = new XmlDocument();
oDoc.LoadXml(oTable.Configuration);
SQL.AppendLine("if exists(select 1 from sys.objects where type='u' and name='" + oTable.Name + "') drop proc " + oTable.Name);
SQL.AppendLine("GO");
SQL.AppendLine("create table [" + oTable.Name + "] (");
//Add columns
XmlNodeList oColumns = oDoc.SelectNodes("/schema/table/columns/column");
for (int i = 0; i < oColumns.Count; i++)
{
XmlNode oColumn = oColumns[i];
SQL.Append(FormatColumn(oColumn));
if (i < oColumns.Count - 1)
{
SQL.AppendLine(",");
}
else
{
SQL.AppendLine();
}
}
SQL.AppendLine(")");
//indexes
XmlNodeList oIndexes = oDoc.SelectNodes("/schema/table/indexes/index");
foreach (XmlNode oIndex in oIndexes)
{
//index attributes
SQL.AppendLine(FormatIndex(oTable.Name, oIndex));
}
SQL.AppendLine("GO");
return(SQL.ToString());
}
public void InsertSingleTable(BLL.Table oTableID)
{
List <SqlParameter> oParms = new List <SqlParameter>();
oParms.Add(new SqlParameter("@DBID", oTableID.DBID));
oParms.Add(new SqlParameter("@TableID", oTableID.TableID));
oParms.Add(new SqlParameter("@Name", oTableID.Name));
oParms.Add(new SqlParameter("@Description", oTableID.Description));
oParms.Add(new SqlParameter("@Configuration", oTableID.Configuration));
oParms.Add(new SqlParameter("@FirstFindVersion", oTableID.FindFirstVersion));
oParms.Add(new SqlParameter("@LastFindVersion", oTableID.FindLastVersion));
oParms.Add(new SqlParameter("@ScanInProgress", oTableID.ScanInProgress));
oParms.Add(new SqlParameter("@CurrentlyExists", oTableID.CurrentlyExists));
oParms.Add(new SqlParameter("@ChangedInLastScan", oTableID.ChangedInLastScan));
oParms.Add(new SqlParameter("@ChangeDateTime", oTableID.ChangeDateTime));
oParms.Add(new SqlParameter("@ChangeUID", oTableID.ChangeUID));
base.ExecuteNonQuery("InsertTable", 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 void LoadSingleTable(BLL.Table oTableID)
{
StringBuilder sSQL = new StringBuilder();
sSQL.AppendLine("select")
.AppendLine(" *")
.AppendLine(" from")
.AppendLine(" [dbtable] a")
.AppendLine(" where")
.AppendFormat(" a.[TableID]='{0}'", oTableID.TableID);
DataTable oDT = base.ExecuteSQLToDatatable(sSQL.ToString());
if (oDT.Rows.Count > 0)
{
oTableID.LoadFromRow(oDT.Rows[0]);
}
else
{
oTableID = null;
}
}
public void UpdateSingleTable(BLL.Table oTableID)
{
List <SqlParameter> oParms = new List <SqlParameter>();
oParms.Add(new SqlParameter("@TableID", oTableID.TableID));
oParms.Add(new SqlParameter("@Name", oTableID.Name));
oParms.Add(new SqlParameter("@Description", oTableID.Description));
oParms.Add(new SqlParameter("@FirstFindVersion", oTableID.FindFirstVersion));
oParms.Add(new SqlParameter("@LastFindVersion", oTableID.FindLastVersion));
oParms.Add(new SqlParameter("@ScanInProgress", oTableID.ScanInProgress));
oParms.Add(new SqlParameter("@CurrentlyExists", oTableID.CurrentlyExists));
oParms.Add(new SqlParameter("@ChangedInLastScan", oTableID.ChangedInLastScan));
oParms.Add(new SqlParameter("@ChangeDateTime", oTableID.ChangeDateTime));
oParms.Add(new SqlParameter("@ChangeUID", oTableID.ChangeUID));
if (oTableID.ConfigurationUpdated == true)
{
SqlParameter oConfiguration = new SqlParameter("@configuration", SqlDbType.NVarChar, -1);
oConfiguration.Value = oTableID.Configuration;
oParms.Add(oConfiguration);
}
base.ExecuteNonQuery("UpdateTable", 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.DBTable_Factory oFactory = new DBL.DBTable_Factory();
//Delete any table history records associated with incomplete versions.
//These records would be left over from a previously uncompleted scan.
oFactory.DeleteIncompleteHistory(DBID: m_DBID);
//Indicate that all known tables (in the database) are being re-verified, in case a table has been dropped.
oFactory.MarkScanningInProgress(DBID: m_DBID);
//Get list of tables from database
using (AGENT.DBTableAgent_Factory oTableAgent = new AGENT.DBTableAgent_Factory(sConnectionString: sConnectionString))
{
DataTable oAllTables = oTableAgent.GetList();
ScanCount(SQLAutoDocLib.UTIL.Constants.TABLENODE, oAllTables.Rows.Count);
//For each table...
foreach (DataRow oTable in oAllTables.Rows)
{
string sTableName = oTable[0].ToString();
//Alert that we're beginning the scan
ScanBegin(SQLAutoDocLib.UTIL.Constants.TABLENODE, sTableName);
//Open a connection to the target server
oTableAgent.OpenConnection();
//Create temporary stored procedures
// These will go away when the connection is closed
oTableAgent.make_TemporaryStoredProcedures();
//Get an xml summary of table schema
string sXML = oTableAgent.GetTableSchema(sTableName);
//Close the connection to the target server
oTableAgent.CloseConnection();
//Record with same name and xml summary already exists in database?
bool bIsMatch = false;
Guid oFoundTable = oFactory.FindWithSchemaMatch(
DBID: m_DBID,
sName: sTableName,
sSchemaXML: sXML,
IsMatch: out bIsMatch);
if (oFoundTable == Guid.Empty)
{
// table not found
// create new table record
BLL.Table oNewTable = new BLL.Table(DBID: m_DBID);
oNewTable.Name = sTableName;
oNewTable.Configuration = sXML;
oNewTable.FindFirstVersion = m_VersionID;
oNewTable.FindLastVersion = m_VersionID;
oNewTable.ScanInProgress = false;
oNewTable.ChangedInLastScan = true;
oNewTable.Save();
// create new table version record
BLL.TableHistory oNewTableHistory = new BLL.TableHistory(TableID: oNewTable.TableID, VersionID: m_VersionID);
oNewTableHistory.Configuration = sXML;
oNewTableHistory.CurrentlyExists = true;
oNewTableHistory.Save();
this.ChangesFound = true;
ScanNotFound(SQLAutoDocLib.UTIL.Constants.TABLENODE, sTableName);
}
else if (bIsMatch == false)
{
// table found but schema has changed
// update table record
BLL.Table oNewTable = new BLL.Table(DBID: m_DBID, TableID: oFoundTable);
oNewTable.Load();
oNewTable.Configuration = sXML;
oNewTable.FindLastVersion = m_VersionID;
oNewTable.ScanInProgress = false;
oNewTable.ChangedInLastScan = true;
oNewTable.Save();
// create new table version record
BLL.TableHistory oNewTableHistory = new BLL.TableHistory(TableID: oNewTable.TableID, VersionID: m_VersionID);
oNewTableHistory.Configuration = sXML;
oNewTableHistory.CurrentlyExists = true;
oNewTableHistory.Save();
this.ChangesFound = true;
ScanChanged(SQLAutoDocLib.UTIL.Constants.TABLENODE, sTableName);
}
else
{
// table found and schema is the same.
// update table record
BLL.Table oNewTable = new BLL.Table(DBID: m_DBID, TableID: oFoundTable);
oNewTable.Load();
oNewTable.FindLastVersion = m_VersionID;
oNewTable.ScanInProgress = false;
oNewTable.ChangedInLastScan = false;
oNewTable.Save();
}
}
}
//After scan is complete...
//Note that any tables not found have been dropped
DataTable oDT = oFactory.FindUnfound(DBID: m_DBID);
foreach (DataRow oDroppedTable in oDT.Rows)
{
Guid oTableID = (Guid)oDroppedTable["tableid"];
// update table record
BLL.Table oNewTable = new BLL.Table(DBID: m_DBID, TableID: oTableID);
oNewTable.Load();
oNewTable.FindLastVersion = m_VersionID;
oNewTable.ScanInProgress = false;
oNewTable.ChangedInLastScan = true;
oNewTable.CurrentlyExists = false;
oNewTable.Save();
// create new table version record
BLL.TableHistory oNewTableHistory = new BLL.TableHistory(TableID: oNewTable.TableID, VersionID: m_VersionID);
oNewTableHistory.Configuration = "";
oNewTableHistory.CurrentlyExists = false;
oNewTableHistory.Save();
this.ChangesFound = true;
ScanDeleted(SQLAutoDocLib.UTIL.Constants.TABLENODE, oNewTable.Name);
}
}
public string Reconstitute(BLL.Table oTable)
{
SCAN.AGENT.DBTableAgent_Factory oFactory = new SCAN.AGENT.DBTableAgent_Factory();
return(oFactory.Reconstitute(oTable));
}
