internal string GetSQLQueryText(EntityBase entity, string ConnectionString)
{
SaveModes saveMode = SaveModes.New;
if (entity.IsNewDescription)
{
saveMode = SaveModes.New;
}
else
{
saveMode = SaveModes.Update;
}
if (string.IsNullOrEmpty(entity.Description))
{
saveMode = SaveModes.Delete;
}
if (entity.HasChanges)
{
string sql = GetSQLTextForSaveMode(saveMode);
using (DbCommand cmd = CommandFactory.Create(sql, ConnectionString, System.Data.CommandType.StoredProcedure))
{
FillCommand(cmd, saveMode, entity.Level1Type, entity.Description, entity.Schema, entity.Level1Name, entity.Level2Type, entity.Level2Name);
return(SqlCommandDumper.GetCommandText((System.Data.SqlClient.SqlCommand)cmd));
}
}
else
{
return("");
}
}
///// <summary>
/////
///// </summary>
///// <param name="tmd"></param>
///// <param name="connStr"></param>
//public void SaveTableMetaData(TableMetadata tmd, string connStr)
//{
// if (tmd != null)
// {
// SaveMetadata(tmd, connStr);
// ColumnMetadataAccess cma = new ColumnMetadataAccess();
// cma.SaveColumnMetadata(tmd, connStr);
// IndexEntityMetadataAccess iema = new IndexEntityMetadataAccess();
// iema.SaveIndexMetadata(tmd, connStr);
// }
//}
///// <summary>
/////
///// </summary>
///// <param name="connStr"></param>
///// <returns></returns>
//public string GenerateWikiMarkupForTables(string connStr)
//{
// return GenerateWikiMarkupForTables(connStr, null);
//}
/// <summary>
/// Get list of all tables and their Descriptions. Will only include user defined tables and are not ms_shipped
/// </summary>
/// <param name="connStr"></param>
/// <returns></returns>
public ObservableCollection <TableMetadata> GetMasterTableMetadata(string connStr)
{
ObservableCollection <TableMetadata> tables = new ObservableCollection <TableMetadata>();
string sql = string.Format(@"
;
with InformationSchemaTables as
(
SELECT
name as Table_name,
SCHEMA_NAME(schema_id ) as Table_Schema
FROM
sys.objects
where
type = 'u' and is_ms_shipped = 0
)
SELECT TABLE_NAME
, TABLE_SCHEMA
, VALUE
, CASE
WHEN VALUE IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)
END AS HasDescription
FROM
InformationSchemaTables iss
OUTER APPLY
(
SELECT VALUE FROM fn_listextendedProperty('{0}',
'SCHEMA', iss.TABLE_SCHEMA
,'TABLE', iss.TABLE_NAME
, NULL, NULL)
) descr
order by table_schema, table_name", ApplicationSettings.Default.ExtendedPropKey);
using (DbCommand cmd = CommandFactory.Create(sql, connStr))
{
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
tables.Add(new TableMetadata(
reader.GetString(reader.GetOrdinal("Table_Name")),
"NULL",
reader.GetString(reader.GetOrdinal("TABLE_SCHEMA")),
reader.GetStringOrEmpty(reader.GetOrdinal("Value")),
!reader.GetBoolean(reader.GetOrdinal("HasDescription"))));
}
}
return(tables);
}
/// <summary>
/// Get list of all Views and their Descriptions. Will only include user defined Views and are not ms_shipped
/// </summary>
/// <param name="connStr"></param>
/// <returns></returns>
public ObservableCollection <ViewMetadata> GetMasterViewMetadata(string connStr)
{
ObservableCollection <ViewMetadata> Views = new ObservableCollection <ViewMetadata>();
string sql = string.Format(@"
;
with InformationSchemaViews as
(
SELECT
TABLE_NAME as View_name,
table_schema as View_Schema
FROM
INFORMATION_SCHEMA.views
)
SELECT View_NAME
, View_SCHEMA
, VALUE
, CASE
WHEN VALUE IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)
END AS HasDescription
FROM
InformationSchemaViews iss
OUTER APPLY
(
SELECT VALUE FROM fn_listextendedProperty('{0}',
'SCHEMA', iss.View_SCHEMA
,'View', iss.View_NAME
, NULL, NULL)
) descr
order by View_schema, View_name", ApplicationSettings.Default.ExtendedPropKey);
using (DbCommand cmd = CommandFactory.Create(sql, connStr))
{
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Views.Add(new ViewMetadata(
reader.GetString(reader.GetOrdinal("View_Name")),
"NULL",
reader.GetString(reader.GetOrdinal("View_SCHEMA")),
reader.GetStringOrEmpty(reader.GetOrdinal("Value")),
!reader.GetBoolean(reader.GetOrdinal("HasDescription"))));
}
}
return(Views);
}
/// <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 void GetStoredProcedureMetaDataDetails(StoredProcedureMetaData storedProcedureMetadata, string connStr)
{
int indexCount = 0;
ObservableCollection <ParameterMetaData> parameters = new ObservableCollection <ParameterMetaData>();
string sql = string.Format(@"select
PARAMETER_NAME
, value as Description
, CASE WHEN VALUE IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS HasDescription
from INFORMATION_SCHEMA.PARAMETERS
outer apply
(
select value from fn_listextendedProperty('{2}',
'SCHEMA', SPECIFIC_SCHEMA
, 'PROCEDURE', SPECIFIC_NAME
, 'PARAMETER', PARAMETER_NAME)
) descr
where SPECIFIC_NAME = '{0}' and SPECIFIC_SCHEMA = '{1}' ", storedProcedureMetadata.Level1Name, storedProcedureMetadata.Schema, ApplicationSettings.Default.ExtendedPropKey);
using (DbCommand cmd = CommandFactory.Create(sql, connStr))
{
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
parameters.Add(new ParameterMetaData
(
storedProcedureMetadata.Level1Name,
reader.GetString(reader.GetOrdinal("PARAMETER_NAME")),
storedProcedureMetadata.Schema,
reader.GetStringOrEmpty(reader.GetOrdinal("Description")),
!reader.GetBoolean(reader.GetOrdinal("HasDescription")),
indexCount++
));
}
reader.Close();
}
storedProcedureMetadata.Parameters = parameters;
}
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="connStr"></param>
/// <returns></returns>
public ObservableCollection <StoredProcedureMetaData> GetStoredProcedureMetaData(string connStr)
{
ObservableCollection <StoredProcedureMetaData> spmd = new ObservableCollection <StoredProcedureMetaData>();
string sql = string.Format(@"select name ,
SCHEMA_NAME(schema_id) as SchemaName
, Value as Description
, CASE WHEN VALUE IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS HasDescription
from sys.objects
outer apply
(
select value from fn_listextendedProperty('{0}',
'SCHEMA', SCHEMA_NAME(schema_id)
,'PROCEDURE', name, NULL, NULL)
) descr
where type = 'p' and is_ms_shipped = 0
order by schema_id, name", ApplicationSettings.Default.ExtendedPropKey);
using (DbCommand cmd = CommandFactory.Create(sql, connStr))
{
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
spmd.Add(new StoredProcedureMetaData
(
reader.GetString(reader.GetOrdinal("Name")),
"NULL",
reader.GetString(reader.GetOrdinal("SchemaName")),
reader.GetStringOrEmpty(reader.GetOrdinal("Description")),
!reader.GetBoolean(reader.GetOrdinal("HasDescription"))
));
}
return(spmd);
}
}
/// <summary>
/// Creates wikimarkup string for the specified table's indexes
/// </summary>
/// <param name="tmd">use NULL to create markup for indexes on ALL tables</param>
/// <returns></returns>
private string CreateWikiMarkupForIndexesOnTable(string connStr, EntityBase tmd)
{
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,
object_id
FROM
sys.objects
where
type = 'u' and is_ms_shipped = 0
)
SELECT
WikiMarkup FROM
InformationSchemaTables iss
CROSS APPLY(
SELECT '||' + iss.TABLE_SCHEMA + '.' + iss.TABLE_NAME + ' || Index Name || Index Type || Unique || Primary key || Key Columns || Included Columns ||' as WikiMarkup
UNION ALL
SELECT '|' + ISNULL(
( SELECT CAST(VALUE AS NVARCHAR(MAX)) FROM fn_listextendedProperty('{0}',
'SCHEMA', iss.TABLE_SCHEMA
,'TABLE', iss.TABLE_NAME
, CASE is_unique WHEN 1 THEN 'CONSTRAINT' ELSE 'INDEX' END, name))
, ' ')
+ '|' + ind.name
+ '|' + cast(type_desc as varchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS
+ '|' + case is_unique when 1 then 'Yes' else 'No' end
+ '|' + case is_primary_key when 1 then 'Yes' else 'No' end
+ '|' + isnull(substring(KeyColumns, 0, len(KeyColumns)) , ' ')
+ '|' + isnull(substring(IncludedColumns, 0, len(IncludedColumns)) , ' ')
+ '|'
from
sys.indexes ind
CROSS APPLY ( SELECT col.name + ', '
FROM sys.index_columns cols
INNER JOIN sys.columns col
ON cols.object_id = col.object_id and cols.column_id = col.column_id
WHERE cols.index_id = ind.index_id and cols.object_id = ind.object_id and cols.is_included_column = 0 --and key_ordinal > 0
ORDER BY cols.key_ordinal
FOR XML PATH('') ) D ( KeyColumns )
CROSS APPLY
( SELECT col.name + ', '
FROM sys.index_columns cols
INNER JOIN sys.columns col
ON cols.object_id = col.object_id and cols.column_id = col.column_id
WHERE cols.index_id = ind.index_id and cols.object_id = ind.object_id and cols.is_included_column = 1 --and key_ordinal = 0
ORDER BY cols.index_column_id
FOR XML PATH('') ) DE ( IncludedColumns )
where
object_id = iss.object_id
and ind.type_desc <> 'HEAP'
union all
select '\\'
) AllTheGoodies
", ApplicationSettings.Default.ExtendedPropKey);
string whereStatement = "";
if (tmd != null)
{
whereStatement = string.Concat("WHERE iss.Table_Name = '", tmd.Level1Name, "' and iss.table_schema = '", tmd.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());
}
/// <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());
}
/// <summary>
///
/// </summary>
/// <param name="connStr"></param>
/// <returns></returns>
private string GenerateWikiMarkupForStoredProcedures(string connStr, StoredProcedureMetaData spMetadata)
{
string result = string.Empty;
StringBuilder sb = new StringBuilder();
string sql = @"
SELECT
WikiMarkup
FROM
sys.objects so
outer APPLY
(
SELECT (
'h2. ' + SCHEMA_NAME(so.schema_id) + '.' + so.Name + '
\\' +
ISNULL( (SELECT CAST(VALUE AS NVARCHAR(MAX))
FROM
fn_listextendedProperty(
@ExtendedPropertyName
, 'SCHEMA', SCHEMA_NAME(so.schema_id)
,'PROCEDURE', so.Name
, NULL, NULL)), '{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}')
) AS WikiMarkup
UNION ALL
SELECT CASE
WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.parameters
where SPECIFIC_NAME = so.name and SPECIFIC_SCHEMA = schema_name(so.schema_id)
)
THEN
'||Parameter Name|Datatype|Default value?|Is Output column|Description|'
ELSE
'No parameters'
END
UNION ALL
SELECT
'|' + p.name + '|' +
--(,Scale)
CASE
WHEN system_type_id IN (231,167,175,239) THEN
CAST(TYPE_NAME(p.system_type_id) AS NVARCHAR(100)) + '(' + CAST(max_length AS NVARCHAR(50)) + ')'
WHEN system_type_id = 106 THEN
CAST(TYPE_NAME(p.system_type_id) AS NVARCHAR(100)) + '(' + CAST(PRECISION AS NVARCHAR(50)) + ', ' + CAST(Scale AS NVARCHAR(50)) + ')'
ELSE
TYPE_NAME(p.system_type_id)
END
+ '|' +
CAST(ISNULL(p.default_value, 'N/a') AS NVARCHAR(100)) + '|' +
CASE p.is_output WHEN 1 THEN 'Yes' ELSE 'No' END + '|' +
ISNULL(CAST(VALUE AS NVARCHAR(MAX)), '{color:#ff0000}{*}DESCRIPTION MISSING{*}{color}') + '|'
FROM
sys.parameters p
outer APPLY
fn_listextendedProperty(
@ExtendedPropertyName
,'SCHEMA', SCHEMA_NAME(so.schema_id)
,'PROCEDURE', so.Name
,'PARAMETER', p.name)
WHERE
p.object_id = so.object_id
UNION ALL
SELECT '
\\\
'
) spInfo
WHERE
so.type IN ('p', 'fn', 'if', 'tf')
and is_ms_shipped = 0
and
(@SPName IS NULL or (name = @SPName and schema_id(@SPSchema) = schema_id))
ORDER BY
so.name";// ApplicationSettings.Default.ExtendedPropKey);
using (DbCommand cmd = CommandFactory.Create(sql, connStr))
{
// If we are requesting wikimarkup for a specific stored procedure then this sql code need to be included in the search clause.
if (spMetadata != null)
{
cmd.Parameters.Add(CommandFactory.CreateParameter("SPName", spMetadata.Level1Name));
cmd.Parameters.Add(CommandFactory.CreateParameter("SPSchema", spMetadata.Schema));
cmd.Parameters.Add(CommandFactory.CreateParameter("ExtendedPropertyName", ApplicationSettings.Default.ExtendedPropKey));
}
else
{
cmd.Parameters.Add(CommandFactory.CreateParameter("SPName", DBNull.Value));
cmd.Parameters.Add(CommandFactory.CreateParameter("SPSchema", DBNull.Value));
cmd.Parameters.Add(CommandFactory.CreateParameter("ExtendedPropertyName", ApplicationSettings.Default.ExtendedPropKey));
}
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sb.AppendLine(reader[0].ToString());
}
}
return(sb.ToString());
}
public ObservableCollection <IndexEntity> GetIndexMetadata(string tableName, string tableSchema, string connStr)
{
ObservableCollection <IndexEntity> indexes = new ObservableCollection <IndexEntity>();
string sql = string.Format(@"
SELECT
name ,
index_id ,
type_desc ,
is_unique ,
is_primary_key ,
is_unique_constraint ,
fill_factor ,
has_filter ,
filter_definition,
VALUE,
CASE WHEN VALUE IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS HasDescription
FROM
sys.indexes
OUTER APPLY
(
SELECT VALUE FROM FN_LISTEXTENDEDPROPERTY('{2}',
'SCHEMA', '{1}'
,'TABLE', '{0}'
,'Index', name)
) descr
WHERE
object_id = OBJECT_ID('{1}.{0}')
AND name IS NOT NULL
", tableName, tableSchema, ApplicationSettings.Default.ExtendedPropKey);
using (DbCommand cmd = CommandFactory.Create(sql, connStr))
{
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
indexes.Add(new IndexEntity(
tableName,
reader.GetString(reader.GetOrdinal("name")),
tableSchema,
reader.GetStringOrEmpty(reader.GetOrdinal("value")),
!reader.GetBoolean(reader.GetOrdinal("HasDescription")),
reader.GetInt32(reader.GetOrdinal("index_id")),
reader.GetString(reader.GetOrdinal("type_desc")),
reader.GetBoolean(reader.GetOrdinal("is_unique")),
reader.GetBoolean(reader.GetOrdinal("is_primary_key")),
reader.GetBoolean(reader.GetOrdinal("is_unique_constraint")),
reader.GetByte(reader.GetOrdinal("fill_factor")),
reader.GetBoolean(reader.GetOrdinal("has_filter")),
reader.GetStringOrEmpty(reader.GetOrdinal("filter_definition"))
));
}
reader.Close();
}
return(indexes);
}