public string GenerateWikiMarkup(string connStr, EntityBase entity, Level1Types level1Type, Level2Types level2Type)
{
switch (level1Type)
{
case Level1Types.Table:
switch (level2Type)
{
case Level2Types.NULL:
return(GenerateWikiMarkupForTablesOrView(connStr, entity, level1Type));
case Level2Types.Index:
case Level2Types.Constraint:
return(CreateWikiMarkupForIndexesOnTable(connStr, entity));
default:
throw new ArgumentException();
}
case Level1Types.Procedure:
return(GenerateWikiMarkupForStoredProcedures(connStr, entity as StoredProcedureMetaData));
case Level1Types.View:
return(GenerateWikiMarkupForTablesOrView(connStr, entity, level1Type));
default:
throw new ArgumentException();
}
}
private static void FillCommand(DbCommand cmd, SaveModes saveMode, Level1Types level1Type, string descriptionValue, string objectSchema, string level1Name, Level2Types level2Type, string level2Name)
{
cmd.Connection.Open();
if (saveMode != SaveModes.Delete)
{
cmd.Parameters.Add(CommandFactory.CreateParameter("@value", descriptionValue));
}
cmd.Parameters.Add(CommandFactory.CreateParameter("@name", ApplicationSettings.Default.ExtendedPropKey));
cmd.Parameters.Add(CommandFactory.CreateParameter("@level0type", "SCHEMA"));
cmd.Parameters.Add(CommandFactory.CreateParameter("@level0name", objectSchema));
cmd.Parameters.Add(CommandFactory.CreateParameter("@level1type", level1Type.ToString()));
cmd.Parameters.Add(CommandFactory.CreateParameter("@level1name", level1Name));
if (level2Type != Level2Types.NULL)
{
cmd.Parameters.Add(CommandFactory.CreateParameter("@level2type", level2Type.ToString()));
cmd.Parameters.Add(CommandFactory.CreateParameter("@level2name", level2Name));
}
}
protected bool SaveDescription(SaveModes saveMode, string connStr, Level1Types level1Type, string descriptionValue, string objectSchema, string level1Name, Level2Types level2Type, string level2Name)
{
string sql = GetSQLTextForSaveMode(saveMode);
try
{
using (DbCommand cmd = CommandFactory.Create(sql, connStr, System.Data.CommandType.StoredProcedure))
{
FillCommand(cmd, saveMode, level1Type, descriptionValue, objectSchema, level1Name, level2Type, level2Name);
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="tableSchema"></param>
/// <param name="connStr"></param>
/// <returns></returns>
public ObservableCollection <ColumnMetaData> GetColumnMetadata(string tableName, string tableSchema, string connStr, Level1Types belongToType)
{
int indexCount = 0;
ObservableCollection <ColumnMetaData> columns = new ObservableCollection <ColumnMetaData>();
string sql = string.Format(@"SELECT
column_name
, VALUE
, CASE WHEN VALUE IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS HasDescription
FROM
INFORMATION_SCHEMA.COLUMNS iss
OUTER APPLY
(
SELECT VALUE FROM fn_listextendedProperty('{3}',
'SCHEMA', iss.TABLE_SCHEMA
,'{2}', iss.TABLE_NAME
, 'COLUMN', iss.Column_Name)
) descr
WHERE
table_name = '{0}'
AND table_schema = '{1}'" , tableName, tableSchema, belongToType, ApplicationSettings.Default.ExtendedPropKey);
using (DbCommand cmd = CommandFactory.Create(sql, connStr))
{
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
columns.Add(new ColumnMetaData(
tableName,
reader.GetString(reader.GetOrdinal("column_name")),
tableSchema,
reader.GetStringOrEmpty(reader.GetOrdinal("value")),
!reader.GetBoolean(reader.GetOrdinal("HasDescription")),
indexCount++,
belongToType));
}
reader.Close();
}
return(columns);
}
public ColumnMetaData(string level1Name, string level2Name, string schema, string description, bool isNewDescription, int index, Level1Types belongToType)
: base(level1Name, level2Name, schema, description, isNewDescription)
{
this.Index = index;
this.Level1Type = belongToType;
this.Level2Type = Level2Types.Column;
}
/// <summary>
///
/// </summary>
/// <param name="connStr"></param>
/// <param name="item"></param>
/// <returns></returns>
private string GenerateWikiMarkupForTablesOrView(string connStr, EntityBase item, Level1Types level1Type)
{
if (level1Type != Level1Types.View && level1Type != Level1Types.Table)
{
throw new ArgumentException("item must be View or Table (metadata)");
}
StringBuilder sb = new StringBuilder();
string result = string.Empty;
string sql = string.Format(@"
;
with InformationSchemaTables as
(
SELECT
name as Table_name,
SCHEMA_NAME(schema_id ) as Table_Schema
FROM
sys.objects
where
type in ( case when 'Table' = '{0}' then 'u' when 'View' = '{0}' then 'v' end )
and is_ms_shipped = 0
)
SELECT
WikiMarkup FROM
InformationSchemaTables iss
CROSS APPLY(
SELECT 'h2. ' + iss.TABLE_SCHEMA + '.' + iss.TABLE_NAME + ' {0}' WikiMarkup UNION ALL
SELECT ISNULL(
( SELECT CAST(VALUE AS NVARCHAR(MAX)) FROM fn_listextendedProperty('{0}',
'SCHEMA', iss.TABLE_SCHEMA
,'{1}', iss.TABLE_NAME
, NULL, NULL))
, '{{color:#ff0000}}{{*}}DESCRIPTION MISSING{{*}}{{color}}') + ' \\' UNION ALL
SELECT '||COLUMN_NAME|' + '|IS_NULLABLE|' + '|DATA_TYPE|' + '|CHARACTER_MAXIMUM_LENGTH|' + '|COLUMN_DEFAULT|' + '|Description|' UNION ALL
SELECT '|' + ISNULL(COLUMN_NAME,'') + '|' + ISNULL(IS_NULLABLE,'') + '|' + ISNULL(DATA_TYPE, '') + '|' + ISNULL(CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)), 'N/A') + '|' + ISNULL(COLUMN_DEFAULT, 'none') + '|' +
ISNULL(
(SELECT CAST(VALUE AS NVARCHAR(MAX)) FROM fn_listextendedProperty('{1}',
'SCHEMA', iss.TABLE_SCHEMA
,'{1}', iss.TABLE_NAME
, 'COLUMN', Column_Name))
, '{{color:#ff0000}}{{*}}DESCRIPTION MISSING{{*}}{{color}}') +' |'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = iss.TABLE_NAME
) AllTheGoodies
", level1Type.ToString(), ApplicationSettings.Default.ExtendedPropKey);
//sql = string.Format(sql, level1Type.ToString());
string whereStatement = "";
if (item != null)
{
whereStatement = string.Concat("WHERE iss.Table_Name = '", item.Level1Name, "' and iss.table_schema = '", item.Schema, "'");
sql = string.Concat(sql, whereStatement);
}
sql = string.Concat(sql, "ORDER BY iss.Table_Schema, iss.Table_Name");
using (DbCommand cmd = CommandFactory.Create(sql, connStr))
{
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sb.AppendLine(reader[0].ToString());
}
}
return(sb.ToString());
}
public string GenerateWikiMarkupForTablesAndViews(string ConnectionString, Entities.EntityBase tmd, Level1Types level1Type)
{
return(wiki.GenerateWikiMarkup(ConnectionString, tmd, level1Type, Level2Types.NULL));
}
public string GenerateSQLStatementForTable(string ConnectionString, TableMetadata table, Level1Types level1Types)
{
if (table != null)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(mab.GetSQLQueryText(table, ConnectionString));
foreach (var col in table.Columns)
{
sb.AppendLine(mab.GetSQLQueryText(col, ConnectionString));
}
foreach (var ix in table.Indexes)
{
sb.AppendLine(mab.GetSQLQueryText(ix, ConnectionString));
}
return(sb.ToString());
}
else
{
return("No Selected Table");
}
}
