private static void WriteUserDefinedDataTypesList()
{
string xmlfile = "UserDefinedDataTypeList.xml";
string xslFile = "UserDefinedDataTypeList.xsl";
string htmFile = "UserDefinedDataTypeList.htm";
DataSet ds = new DataSet("UserDefinedDataTypeList");
ds.Tables.Add(userDefinedDataTypes);
File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
HTMLTransfomer ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
ht.TableTransformer();
}
private static void WriteTriggerList()
{
string xmlfile = "TriggerList.xml";
string xslFile = "TriggerList.xsl";
string htmFile = "TriggerList.htm";
DataSet ds = new DataSet("TriggerList");
ds.Tables.Add(triggers.Copy());
File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
HTMLTransfomer ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
ht.TableTransformer();
}
private static void WriteTableList()
{
string xmlfile = "TableList.xml";
string xslFile = "TableList.xsl";
string htmFile = "TableList.htm";
DataSet ds = new DataSet("TableList");
DataView dv = new DataView(tables);
dv.Sort = "TABLE_NAME ASC";
dv.RowFilter = "TABLE_TYPE = 'BASE TABLE'";
ds.Tables.Add(dv.ToTable());
File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
HTMLTransfomer ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
ht.TableTransformer();
}
private static void WriteFunctionList()
{
//Name, Schema, Description, Input, Output, Encrypted, Creation Date
//SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA
//ROUTINE_NAME, ROUTINE_TYPE, CREATED, LAST_ALTERED
string xmlfile = "FunctionList.xml";
string xslFile = "FunctionList.xsl";
string htmFile = "FunctionList.htm";
DataSet ds = new DataSet("FunctionList");
ds.Tables.Add(functions);
File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
HTMLTransfomer ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
ht.TableTransformer();
}
private static void WriteProcedureDetails()
{
string xmlfile = string.Empty;
string xslFile = string.Empty;
string htmFile = string.Empty;
DataSet ds = new DataSet();
#region "Define DataTable for ProcedureProperties"
DataTable procedureProperties = new DataTable("ProcedureProperties");
procedureProperties.Columns.Add("NAME", typeof(String));
procedureProperties.Columns.Add("VALUE", typeof(String));
ds.Tables.Add(procedureProperties);
#endregion
#region "Define DataTable for IN Parameters"
DataTable tableINParameters = ProcedureParametersHelper.ProcedureParameters.Clone();
tableINParameters.TableName = "ProcedureINParameters";
ds.Tables.Add(tableINParameters);
#endregion
#region "Define DataTable for OUT Parameters"
DataTable tableOUTParameters = ProcedureParametersHelper.ProcedureParameters.Clone();
tableOUTParameters.TableName = "ProcedureOUTParameters";
ds.Tables.Add(tableOUTParameters);
#endregion
#region "Define DataTable for SQL"
DataTable tableSQL = new DataTable("SQL");
tableSQL.Columns.Add("SQL", typeof(String));
ds.Tables.Add(tableSQL);
#endregion
foreach (DataRow row in procedures.Rows)
{
string tableName = row["SPECIFIC_NAME"].ToString();
xmlfile = tableName + ".xml";
xslFile = "ProcedureDetails.xsl";
htmFile = tableName + ".htm";
#region "Fill TableProperties"
DataRow dr;
//DataRow dr = procedureProperties.NewRow();
//dr["NAME"] = "Description";
//dr["VALUE"] = row["TABLE_DESCRIPTION"].ToString();
//procedureProperties.Rows.Add(dr);
dr = procedureProperties.NewRow();
dr["NAME"] = "Name";
dr["VALUE"] = tableName;
procedureProperties.Rows.Add(dr);
dr = procedureProperties.NewRow();
dr["NAME"] = "Schema";
dr["VALUE"] = row["ROUTINE_SCHEMA"].ToString();
procedureProperties.Rows.Add(dr);
dr = procedureProperties.NewRow();
dr["NAME"] = "Creation Date";
dr["VALUE"] = row["CREATED"].ToString();
procedureProperties.Rows.Add(dr);
dr = procedureProperties.NewRow();
dr["NAME"] = "Last Altered";
dr["VALUE"] = row["LAST_ALTERED"].ToString();
procedureProperties.Rows.Add(dr);
#endregion
#region "Fill IN Parameters"
DataRow[] inRows = (ProcedureParametersHelper.ProcedureParameters.Select("(SPECIFIC_NAME='" + row["SPECIFIC_NAME"] + "' AND PARAMETER_MODE='IN') OR (SPECIFIC_NAME='" + row["SPECIFIC_NAME"] + "' AND PARAMETER_MODE='INOUT')"));
foreach (DataRow tempDr in inRows)
{
tableINParameters.ImportRow(tempDr);
}
#endregion
#region "Fill OUT Parameters"
DataRow[] outRows = (ProcedureParametersHelper.ProcedureParameters.Select("(SPECIFIC_NAME='" + row["SPECIFIC_NAME"] + "' AND PARAMETER_MODE='OUT') OR (SPECIFIC_NAME='" + row["SPECIFIC_NAME"] + "' AND PARAMETER_MODE='INOUT')"));
foreach (DataRow tempDr in outRows)
{
tableOUTParameters.ImportRow(tempDr);
}
#endregion
#region "T-SQL"
DataRow[] sqlRows = (procedures.Select("SPECIFIC_NAME = '" + row["SPECIFIC_NAME"] + "'"));
foreach (DataRow tempDr in sqlRows)
{
DataRow sql = tableSQL.NewRow();
sql["SQL"] = tempDr["STOREDPROCEDURE_SCRIPT"];
tableSQL.Rows.Add(sql);
}
#endregion
File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
HTMLTransfomer ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
ht.TableTransformer();
procedureProperties.Clear();
tableINParameters.Clear();
tableOUTParameters.Clear();
tableSQL.Clear();
}
}
private static void WriteTriggerDetails()
{
string xmlfile = string.Empty;
string xslFile = string.Empty;
string htmFile = string.Empty;
DataSet ds = new DataSet();
#region "Define DataTable for TableProperties"
DataTable tableProperties = new DataTable("TableProperties");
tableProperties.Columns.Add("NAME", typeof(String));
tableProperties.Columns.Add("VALUE", typeof(String));
ds.Tables.Add(tableProperties);
#endregion
#region "Define DataTable for Type"
DataTable tableType = new DataTable("TableType");
tableType.Columns.Add("InsteadOf", typeof(String));
tableType.Columns.Add("Insert", typeof(String));
tableType.Columns.Add("Update", typeof(String));
tableType.Columns.Add("Delete", typeof(String));
ds.Tables.Add(tableType);
#endregion
#region "Define DataTable for SQL"
DataTable tableSQL = new DataTable("SQL");
tableSQL.Columns.Add("SQL", typeof(String));
ds.Tables.Add(tableSQL);
#endregion
foreach (DataRow row in triggers.Rows)
{
string tableName = row["Name"].ToString();
xmlfile = tableName + ".xml";
xslFile = "TriggerDetails.xsl";
htmFile = tableName + ".htm";
#region "Fill TableProperties"
DataRow dr = tableProperties.NewRow();
dr["NAME"] = "Description";
dr["VALUE"] = row["Description"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Name";
dr["VALUE"] = tableName;
tableProperties.Rows.Add(dr);
//dr = tableProperties.NewRow();
//dr["NAME"] = "Schema";
//dr["VALUE"] = row["Schema"].ToString();
//tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "CreateDate";
dr["VALUE"] = row["CreateDate"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "IsEnabled";
dr["VALUE"] = row["IsEnabled"].ToString();
tableProperties.Rows.Add(dr);
#endregion
#region "Fill Columns"
DataRow[] colRows = triggers.Select("Name = '" + row["Name"] + "'");
foreach (DataRow tempDr in colRows)
{
DataRow type = tableType.NewRow();
type["InsteadOf"] = tempDr["InsteadOf"];
type["Insert"] = tempDr["Insert"];
type["Update"] = tempDr["Update"];
type["Delete"] = tempDr["Delete"];
tableType.Rows.Add(type);
}
#endregion
#region "SQL"
DataRow[] sqlRows = (triggers.Select("Name = '" + row["Name"] + "'"));
foreach (DataRow tempDr in sqlRows)
{
DataRow sql = tableSQL.NewRow();
sql["SQL"] = tempDr["Script"];
tableSQL.Rows.Add(sql);
}
#endregion
File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
HTMLTransfomer ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
ht.TableTransformer();
tableProperties.Clear();
tableType.Clear();
tableSQL.Clear();
}
}
private static void WriteUserDefinedDataTypesDetails()
{
string xmlfile = string.Empty;
string xslFile = string.Empty;
string htmFile = string.Empty;
DataSet ds = new DataSet();
#region "Define DataTable for TableProperties"
DataTable tableProperties = new DataTable("TableProperties");
tableProperties.Columns.Add("NAME", typeof(String));
tableProperties.Columns.Add("VALUE", typeof(String));
ds.Tables.Add(tableProperties);
#endregion
#region "Define DataTable for Columns Defined on"
DataTable columnsDefinedOn = new DataTable("ColumnsDefinedOn");
columnsDefinedOn.Columns.Add("Name", typeof(String));
columnsDefinedOn.Columns.Add("Parent", typeof(String));
columnsDefinedOn.Columns.Add("Description", typeof(String));
ds.Tables.Add(columnsDefinedOn);
#endregion
#region "Define DataTable for SQL"
DataTable tableSQL = new DataTable("SQL");
tableSQL.Columns.Add("SQL", typeof(String));
ds.Tables.Add(tableSQL);
#endregion
foreach (DataRow row in userDefinedDataTypes.Rows)
{
string tableName = row["Name"].ToString();
xmlfile = tableName + ".xml";
xslFile = "UserDefinedDataTypeDetails.xsl";
htmFile = tableName + ".htm";
#region "Fill TableProperties"
DataRow dr = tableProperties.NewRow();
dr["NAME"] = "Description";
dr["VALUE"] = row["Description"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Name";
dr["VALUE"] = tableName;
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Schema";
dr["VALUE"] = row["Schema"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "SystemType";
dr["VALUE"] = row["SystemType"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Nullable";
dr["VALUE"] = row["Nullable"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Rule";
dr["VALUE"] = row["Rule"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Default";
dr["VALUE"] = row["Default"].ToString();
tableProperties.Rows.Add(dr);
#endregion
#region "Fill Columns"
DataRow[] colRows = userDefinedDataTypes.Select("Name = '" + row["Name"] + "'");
foreach (DataRow tempDr in colRows)
{
DataRow type = columnsDefinedOn.NewRow();
type["Name"] = tempDr["Name"];
type["Parent"] = tempDr["Parent"];
type["Description"] = tempDr["Description"];
columnsDefinedOn.Rows.Add(type);
}
#endregion
#region "SQL"
DataRow[] sqlRows = (userDefinedDataTypes.Select("Name = '" + row["Name"] + "'"));
foreach (DataRow tempDr in sqlRows)
{
DataRow sql = tableSQL.NewRow();
sql["SQL"] = tempDr["Script"];
tableSQL.Rows.Add(sql);
}
#endregion
File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
HTMLTransfomer ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
ht.TableTransformer();
tableProperties.Clear();
columnsDefinedOn.Clear();
tableSQL.Clear();
}
}
private static void WriteViewDetails()
{
string xmlfile = string.Empty;
string xslFile = string.Empty;
string htmFile = string.Empty;
DataSet ds = new DataSet();
#region "Define DataTable for ViewProperties"
DataTable tableProperties = new DataTable("TableProperties");
tableProperties.Columns.Add("NAME", typeof(String));
tableProperties.Columns.Add("VALUE", typeof(String));
ds.Tables.Add(tableProperties);
#endregion
#region "Define DataTable for Resultset"
DataTable tableColumns = ColumnsHelper.Columns.Clone();
ds.Tables.Add(tableColumns);
#endregion
#region "Define DataTable for Indexes"
DataTable tableIndexes = IndexesHelper.Indexes.Clone();
ds.Tables.Add(tableIndexes);
#endregion
#region "Define DataTable for SQL"
DataTable tableSQL = new DataTable("SQL");
tableSQL.Columns.Add("SQL", typeof(String));
ds.Tables.Add(tableSQL);
#endregion
foreach (DataRow row in views.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
xmlfile = tableName + ".xml";
xslFile = "ViewDetails.xsl";
htmFile = tableName + ".htm";
#region "Fill TableProperties"
DataRow dr = tableProperties.NewRow();
dr["NAME"] = "Description";
dr["VALUE"] = row["TABLE_DESCRIPTION"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Name";
dr["VALUE"] = tableName;
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Schema";
dr["VALUE"] = row["TABLE_SCHEMA"].ToString();
tableProperties.Rows.Add(dr);
#endregion
#region "Fill Columns"
string cloumnTable = string.Empty;
DataRow[] colRows = (ColumnsHelper.Columns.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
foreach (DataRow tempDr in colRows)
{
// TODO: currently, adding description to the views columns is buggy inplementation
DataRow[] viewRows = (ViewColumnsHelper.ViewColumns.Select("VIEW_NAME = '" + row["TABLE_NAME"] + "' AND COLUMN_NAME = '" + tempDr["COLUMN_NAME"] + "'"));
if (viewRows.Length > 0)
{
cloumnTable = viewRows[0]["TABLE_NAME"].ToString();
}
DataRow[] rows = (ColumnsHelper.Columns.Select("TABLE_NAME = '" + cloumnTable + "' AND COLUMN_NAME = '" + tempDr["COLUMN_NAME"] + "'"));
if (rows.Length > 0)
{
tempDr["COLUMN_DESCRIPTION"] = rows[0]["COLUMN_DESCRIPTION"];
}
tableColumns.ImportRow(tempDr);
}
#endregion
#region "Fill Indexes"
DataRow[] indexRows = (IndexesHelper.Indexes.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
foreach (DataRow tempDr in indexRows)
{
tableIndexes.ImportRow(tempDr);
}
#endregion
#region "SQL"
DataRow[] sqlRows = (views.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
foreach (DataRow tempDr in sqlRows)
{
DataRow sql = tableSQL.NewRow();
sql["SQL"] = tempDr["TABLE_SCRIPT"];
tableSQL.Rows.Add(sql);
}
#endregion
File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
HTMLTransfomer ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
ht.TableTransformer();
tableProperties.Clear();
tableColumns.Clear();
tableIndexes.Clear();
tableSQL.Clear();
}
}
public static void WriteHTML(string xmlfile, string xslFile, string htmlFile)
{
HTMLTransfomer ht = new HTMLTransfomer(xmlfile, xslFile, htmlFile);
ht.TableTransformer();
}
private static void WriteTableDetails()
{
string xmlfile = string.Empty;
string xslFile = string.Empty;
string htmFile = string.Empty;
DataSet ds = new DataSet();
#region "Define DataTable for TableProperties"
DataTable tableProperties = new DataTable("TableProperties");
tableProperties.Columns.Add("NAME", typeof(String));
tableProperties.Columns.Add("VALUE", typeof(String));
ds.Tables.Add(tableProperties);
#endregion
#region "Define DataTable for Columns"
DataTable tableColumns = ColumnsHelper.Columns.Clone();
ds.Tables.Add(tableColumns);
#endregion
#region "Define DataTable for Indexes"
DataTable tableIndexes = IndexesHelper.Indexes.Clone();
ds.Tables.Add(tableIndexes);
#endregion
#region "Define DataTable for Indexes"
DataTable foreignKeys = ForeignKeysHelper.ForeignKeys.Clone();
ds.Tables.Add(foreignKeys);
#endregion
#region "Define DataTable for SQL"
DataTable tableSQL = new DataTable("SQL");
tableSQL.Columns.Add("SQL", typeof(String));
ds.Tables.Add(tableSQL);
#endregion
foreach (DataRow row in tables.Rows)
{
if (row["TABLE_TYPE"].ToString().Equals("BASE TABLE"))
{
string tableName = row["TABLE_NAME"].ToString();
xmlfile = tableName + ".xml";
xslFile = "TableDetails.xsl";
htmFile = tableName + ".htm";
#region "Fill TableProperties"
DataRow dr = tableProperties.NewRow();
dr["NAME"] = "Description";
dr["VALUE"] = row["TABLE_DESCRIPTION"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Name";
dr["VALUE"] = tableName;
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Schema";
dr["VALUE"] = row["TABLE_SCHEMA"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Data Size";
dr["VALUE"] = row["TABLE_DATA"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Index Size";
dr["VALUE"] = row["TABLE_INDEX_SIZE"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Rows";
dr["VALUE"] = row["TABLE_ROWS"].ToString();
tableProperties.Rows.Add(dr);
dr = tableProperties.NewRow();
dr["NAME"] = "Columns";
dr["VALUE"] = row["TABLE_COLUMNSCOUNT"].ToString();
tableProperties.Rows.Add(dr);
#endregion
#region "Fill Columns"
DataRow[] colRows = (ColumnsHelper.Columns.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
foreach (DataRow tempDr in colRows)
{
tableColumns.ImportRow(tempDr);
}
#endregion
#region "Fill Indexes"
DataRow[] indexRows = (IndexesHelper.Indexes.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
foreach (DataRow tempDr in indexRows)
{
tableIndexes.ImportRow(tempDr);
}
#endregion
#region "Fill Indexes"
DataRow[] foreignKeyRows = (ForeignKeysHelper.ForeignKeys.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
foreach (DataRow tempDr in foreignKeyRows)
{
foreignKeys.ImportRow(tempDr);
}
#endregion
#region "SQL"
DataRow[] sqlRows = (tables.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
foreach (DataRow tempDr in sqlRows)
{
DataRow sql = tableSQL.NewRow();
sql["SQL"] = tempDr["TABLE_SCRIPT"];
tableSQL.Rows.Add(sql);
}
#endregion
File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
HTMLTransfomer ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
ht.TableTransformer();
tableProperties.Clear();
tableColumns.Clear();
tableIndexes.Clear();
foreignKeys.Clear();
tableSQL.Clear();
}
}
}
