private static void BuildGetViewsQuery(ref IStatementBuilder pStatement)
{
pStatement.Append("select obj.name as Name, 'View' as Type ");
pStatement.Append("from sys.objects obj ");
pStatement.Append("inner join sys.schemas sch on obj.schema_id = sch.schema_id ");
pStatement.Append("where type = 'V' ");
//pStatement.Append("and sch.name = 'dbo'");
}
private void BuildInsertQuery(ref IStatementBuilder pStatement, DateTime startDate, ELogLevel maxLevel, string title, string data)
{
pStatement.Append($"insert into {targetTable}({targetDateColumn},{targetLevelColumn},{targetTitleColumn},{targetDataColumn})");
pStatement.Append($"values(@{targetDateColumn},@{targetLevelColumn},@{targetTitleColumn},@{targetDataColumn})");
pStatement.AddParameter(targetDateColumn, startDate, DbType.DateTime);
pStatement.AddParameter(targetDateColumn, maxLevel, DbType.String);
pStatement.AddParameter(targetDateColumn, title, DbType.String);
pStatement.AddParameter(targetDateColumn, data, DbType.String);
}
private void BuildInsertQuery(ref IStatementBuilder pStatement, DateTime date, ELogLevel level, string data,string tag,string memberName = null, string filePath = null, int lineNumber = 0)
{
pStatement.Append($"insert into {targetTable}({targetDateColumn},{targetLevelColumn},{targetDataColumn},{targetTagColumn},{targetMemberNameColumn},{targetFilePathColumn},{targetLineNumberColumn})");
pStatement.Append($"values(@{targetDateColumn},@{targetLevelColumn},@{targetDataColumn},@{targetTagColumn},@{targetMemberNameColumn},@{targetFilePathColumn},@{targetLineNumberColumn})");
pStatement.AddParameter(targetDateColumn, date, DbType.DateTime);
pStatement.AddParameter(targetLevelColumn, level, DbType.String);
pStatement.AddParameter(targetDataColumn, data, DbType.String);
pStatement.AddParameter(targetTagColumn, tag, DbType.String);
pStatement.AddParameter(targetMemberNameColumn, memberName, DbType.String);
pStatement.AddParameter(targetFilePathColumn, filePath, DbType.String);
pStatement.AddParameter(targetLineNumberColumn, lineNumber, DbType.Int32);
}
private void BuildGetAllStoredProcedureParametersQuery(ref IStatementBuilder pStatement, string name)
{
pStatement.Append("SELECT ");
pStatement.Append("replace(P.name,'@','') as Name, ");
pStatement.Append("TYPE_NAME(P.user_type_id) as Type, ");
pStatement.Append("P.max_length as Size, ");
pStatement.Append("P.precision as Precision, ");
pStatement.Append("P.scale as Scale ");
pStatement.Append("FROM sys.objects AS SO ");
pStatement.Append("INNER JOIN sys.parameters AS P ");
pStatement.Append("ON SO.OBJECT_ID = P.OBJECT_ID ");
pStatement.Append("WHERE SO.OBJECT_ID IN(SELECT OBJECT_ID ");
pStatement.Append("FROM sys.objects ");
pStatement.Append("WHERE TYPE IN('P', 'FN')) ");
pStatement.Append("AND SO.name = @name ");
pStatement.Append("ORDER BY SO.name, P.parameter_id ");
pStatement.AddParameter("name",name,DbType.String);
}
private static void BuildGetAllQuery(ref IStatementBuilder pStatement)
{
pStatement.Append("select ROUTINE_NAME as Name from information_schema.routines ");
pStatement.Append("where routine_type = 'PROCEDURE'");
}
private static void BuildGetViewKeysQuery(ref IStatementBuilder pStatement, string pViewName)
{
pStatement.Append("select distinct isc.table_name ");
pStatement.Append(", isc.COLUMN_NAME as 'Name' ");
pStatement.Append(", upper(isc.DATA_TYPE) AS 'Type' ");
pStatement.Append(", CASE WHEN isc.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND isc.DATA_TYPE not in ('TEXT','NTEXT','IMAGE') THEN CASE WHEN CAST(isc.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ='-1' THEN '-2' ELSE ISNULL(NULLIF(CAST(isc.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),''),-1) END ELSE '-1' END as 'Length'");
pStatement.Append(", col.ISNULLABLE as 'Nullable'");
pStatement.Append(", ISNULL(ids.is_identity,0) as IsIdentity");
pStatement.Append(", max(CASE WHEN rc.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END) AS IsForeign ");
pStatement.Append(", max(CASE WHEN cu.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END) AS IsPrimary ");
pStatement.Append("from INFORMATION_SCHEMA.COLUMNS isc ");
pStatement.Append("inner join sys.objects obj on obj.name = isc.Table_NAME and obj.type = 'V' ");
pStatement.Append("inner join syscolumns col on col.name = isc.COLUMN_NAME and obj.object_id = col.id ");
pStatement.Append("left outer join sys.identity_columns ids on ids.name = isc.COLUMN_NAME and obj.object_id = ids.object_id ");
pStatement.Append("left outer join INFORMATION_SCHEMA.Table_CONSTRAINTS tc on tc.table_name = isc.Table_name and tc.constraint_type = 'PRIMARY KEY' ");
pStatement.Append("left outer join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu on cu.constraint_name = tc.constraint_name and cu.column_name = isc.COLUMN_NAME ");
pStatement.Append("left outer join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc on cc.Table_name = isc.Table_name and cc.column_name = isc.column_name ");
pStatement.Append("left outer join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on cc.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME ");
pStatement.Append("where isc.Table_NAME = @ViewName ");
pStatement.Append("group by isc.Table_name ");
pStatement.Append(", isc.COLUMN_NAME ");
pStatement.Append(", upper(isc.DATA_TYPE) ");
pStatement.Append(", CASE WHEN isc.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND isc.DATA_TYPE not in ('TEXT','NTEXT','IMAGE') THEN CASE WHEN CAST(isc.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ='-1' THEN '-2' ELSE ISNULL(NULLIF(CAST(isc.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),''),-1) END ELSE '-1' END ");
pStatement.Append(", col.ISNULLABLE ");
pStatement.Append(", ISNULL(ids.is_identity,0) ");
pStatement.Append("order by isc.COLUMN_NAME ");
pStatement.AddParameter("ViewName", pViewName, DbType.String);
}