25392814637

Committed 05 May 2026 05:49PM UTC coverage: 87.11% (+0.01%) from 87.098%

Build # 25392814637

Build Type

push

github

Committed by

pwelter34

Commit Message

Refactor schema readers

Coverage Stats

1074 of 1424 branches covered (75.42%)

Branch coverage included in aggregate %.

79 of 117 new or added lines in 6 files covered. (67.52%)

3 existing lines in 2 files now uncovered.

5677 of 6326 relevant lines covered (89.74%)

222.98 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

69.55

/src/SchemaSaurus.SqlServer/SqlServerSchemaReader.Routines.cs

using Microsoft.Data.SqlClient;

using SchemaSaurus.Metadata;
using SchemaSaurus.Metadata.Builders;
using SchemaSaurus.Metadata.Extensions;
using SchemaSaurus.Metadata.Provider;

namespace SchemaSaurus.SqlServer;

public sealed partial class SqlServerSchemaReader
{
    /// <inheritdoc />
    protected override async Task ReadStoredProceduresAsync(
        SqlConnection connection,
        DatabaseModelBuilder builder,
        SchemaReaderOptions options,
        CancellationToken cancellationToken)
    {
        var schemaFilter = BuildSchemaFilter(options.Schemas, "SCHEMA_NAME(p.schema_id)");

        var schemaWhere = schemaFilter is not null ? $"\n    AND {schemaFilter}" : "";

        var procedures = new Dictionary<int, StoredProcedureBuilder>();

        var sql = $"""
            SELECT
                p.object_id,
                SCHEMA_NAME(p.schema_id)            AS schema_name,
                p.name                              AS proc_name,
                m.definition,
                CAST(ep.value AS NVARCHAR(4000))    AS description
            FROM sys.procedures p
            LEFT JOIN sys.sql_modules m ON p.object_id = m.object_id
            LEFT JOIN sys.extended_properties ep
                ON ep.major_id = p.object_id AND ep.minor_id = 0
                AND ep.class = 1 AND ep.name = 'MS_Description'
            WHERE p.is_ms_shipped = 0{schemaWhere}
            ORDER BY SCHEMA_NAME(p.schema_id), p.name
            """;

        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = sql;

            using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);

            const int objectIdOrdinal = 0;
            const int schemaOrdinal = 1;
            const int nameOrdinal = 2;
            const int definitionOrdinal = 3;
            const int descriptionOrdinal = 4;

            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            {
                var objectId = reader.GetInt32(objectIdOrdinal);
                var schema = reader.GetString(schemaOrdinal);
                var name = reader.GetString(nameOrdinal);
                var definition = reader.GetStringNull(definitionOrdinal);
                var description = reader.GetStringNull(descriptionOrdinal);

                var storedProcedureBuilder = new StoredProcedureBuilder()
                    .WithSchemaQualifiedName(schema, name)
                    .WithDefinition(definition)
                    .WithDescription(description);

                ApplyExtendedProperties((1, objectId, 0), storedProcedureBuilder);

                procedures[objectId] = storedProcedureBuilder;
            }
        }

        if (procedures.Count == 0)
            return;

        await ReadParametersAsync(connection, procedures, "o.type = 'P'", cancellationToken).ConfigureAwait(false);

        foreach (var (_, spb) in procedures)
            builder.AddStoredProcedure(spb.Build());
    }

    /// <inheritdoc />
    protected override async Task ReadScalarFunctionsAsync(
        SqlConnection connection,
        DatabaseModelBuilder builder,
        SchemaReaderOptions options,
        CancellationToken cancellationToken)
    {
        var schemaFilter = BuildSchemaFilter(options.Schemas, "SCHEMA_NAME(o.schema_id)");

        var schemaWhere = schemaFilter is not null ? $"\n    AND {schemaFilter}" : "";

        var functions = new Dictionary<int, ScalarFunctionBuilder>();

        var sql = $"""
            SELECT
                o.object_id,
                SCHEMA_NAME(o.schema_id)    AS schema_name,
                o.name                      AS func_name,
                m.definition,
                CAST(ep.value AS NVARCHAR(4000)) AS description
            FROM sys.objects o
            LEFT JOIN sys.sql_modules m ON o.object_id = m.object_id
            LEFT JOIN sys.extended_properties ep
                ON ep.major_id = o.object_id AND ep.minor_id = 0
                AND ep.class = 1 AND ep.name = 'MS_Description'
            WHERE o.type IN ('FN', 'FS') AND o.is_ms_shipped = 0{schemaWhere}
            ORDER BY SCHEMA_NAME(o.schema_id), o.name
            """;

        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = sql;

            using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);

            const int objectIdOrdinal = 0;
            const int schemaOrdinal = 1;
            const int nameOrdinal = 2;
            const int definitionOrdinal = 3;
            const int descriptionOrdinal = 4;

            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            {
                var objectId = reader.GetInt32(objectIdOrdinal);
                var schema = reader.GetString(schemaOrdinal);
                var name = reader.GetString(nameOrdinal);
                var definition = reader.GetStringNull(definitionOrdinal);
                var description = reader.GetStringNull(descriptionOrdinal);

                var functionBuilder = new ScalarFunctionBuilder()
                    .WithSchemaQualifiedName(schema, name)
                    .WithDefinition(definition)
                    .WithDescription(description);

                ApplyExtendedProperties((1, objectId, 0), functionBuilder);

                functions[objectId] = functionBuilder;
            }
        }

        if (functions.Count == 0)
            return;

        await ReadScalarFunctionParametersAsync(connection, functions, cancellationToken).ConfigureAwait(false);

        foreach (var (_, fb) in functions)
            builder.AddScalarFunction(fb.Build());
    }

    private async Task ReadScalarFunctionParametersAsync(
        SqlConnection connection,
        Dictionary<int, ScalarFunctionBuilder> functions,
        CancellationToken cancellationToken)
    {
        const string sql = """
            SELECT
                par.object_id,
                par.name                        AS param_name,
                par.parameter_id,
                st.name                         AS system_type_name,
                TYPE_NAME(par.user_type_id)     AS user_type_name,
                par.max_length,
                par.precision,
                par.scale,
                par.is_output
            FROM sys.parameters par
            INNER JOIN sys.objects o ON par.object_id = o.object_id
            INNER JOIN sys.types st
                ON par.system_type_id = st.system_type_id
                AND st.system_type_id = st.user_type_id
            WHERE o.type IN ('FN', 'FS') AND o.is_ms_shipped = 0
            ORDER BY par.object_id, par.parameter_id
            """;

        using var cmd = connection.CreateCommand();
        cmd.CommandText = sql;

        using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);

        const int objectIdOrdinal = 0;
        const int nameOrdinal = 1;
        const int paramIdOrdinal = 2;
        const int sysTypeOrdinal = 3;
        const int userTypeOrdinal = 4;
        const int maxLenOrdinal = 5;
        const int precisionOrdinal = 6;
        const int scaleOrdinal = 7;
        const int outputOrdinal = 8;

        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
        {
            var objectId = reader.GetInt32(objectIdOrdinal);

            if (!functions.TryGetValue(objectId, out var functionBuilder))
                continue;

            var paramName = reader.GetString(nameOrdinal);
            var paramId = reader.GetInt32(paramIdOrdinal);
            var systemTypeName = reader.GetString(sysTypeOrdinal);
            var userTypeName = reader.GetStringNull(userTypeOrdinal) ?? systemTypeName;
            var maxLength = reader.GetInt16(maxLenOrdinal);
            var precision = reader.GetByte(precisionOrdinal);
            var scale = reader.GetByte(scaleOrdinal);
            var isOutput = reader.GetBoolean(outputOrdinal);

            var direction = isOutput
                ? Metadata.ParameterDirection.Output
                : Metadata.ParameterDirection.Input;

            var maxLengthValue = NormalizeMaxLength(systemTypeName, maxLength);
            byte? precisionValue = HasPrecision(systemTypeName) ? precision : null;
            var scaleValue = HasScale(systemTypeName) ? (int?)scale : null;

            var (dbType, sqlDbType, systemType, isUnicode, isFixedLength) = SqlServerTypeMapper.MapNativeType(systemTypeName);
            var nativeTypeName = FormatNativeTypeName(systemTypeName, userTypeName, maxLength, precision, scale);

            if (paramId == 0)
            {
                TypeMapping returnType = new()
                {
                    DbType = dbType,
                    NativeTypeName = nativeTypeName,
                    SystemType = systemType,
                    MaxLength = maxLengthValue,
                    Precision = precisionValue,
                    Scale = scaleValue,
                    IsUnicode = isUnicode,
                    IsFixedLength = isFixedLength,
                };
                functionBuilder.WithReturnType(returnType);
            }
            else
            {
                functionBuilder.AddParameter(parameterBuilder =>
                {
                    parameterBuilder
                        .WithName(paramName)
                        .WithOrdinal(paramId)
                        .WithDirection(direction)
                        .WithNativeTypeName(nativeTypeName)
                        .WithDbType(dbType)
                        .WithSystemType(systemType)
                        .WithMaxLength(maxLengthValue)
                        .WithPrecision(precisionValue)
                        .WithScale(scaleValue)
                        .WithIsUnicode(isUnicode)
                        .WithIsFixedLength(isFixedLength);

                    ApplyExtendedProperties((2, objectId, paramId), parameterBuilder);
                    parameterBuilder.WithAnnotation(SqlServerAnnotations.SqlDbType, sqlDbType.ToString());
                });
            }
        }
    }

    /// <inheritdoc />
    protected override async Task ReadTableValuedFunctionsAsync(
        SqlConnection connection,
        DatabaseModelBuilder builder,
        SchemaReaderOptions options,
        CancellationToken cancellationToken)
    {
        var schemaFilter = BuildSchemaFilter(options.Schemas, "SCHEMA_NAME(o.schema_id)");

        var schemaWhere = schemaFilter is not null ? $"\n    AND {schemaFilter}" : "";

        var functions = new Dictionary<int, TableValuedFunctionBuilder>();

        var sql = $"""
            SELECT
                o.object_id,
                SCHEMA_NAME(o.schema_id)    AS schema_name,
                o.name                      AS func_name,
                m.definition,
                CAST(ep.value AS NVARCHAR(4000)) AS description
            FROM sys.objects o
            LEFT JOIN sys.sql_modules m ON o.object_id = m.object_id
            LEFT JOIN sys.extended_properties ep
                ON ep.major_id = o.object_id AND ep.minor_id = 0
                AND ep.class = 1 AND ep.name = 'MS_Description'
            WHERE o.type IN ('TF', 'IF', 'FT') AND o.is_ms_shipped = 0{schemaWhere}
            ORDER BY SCHEMA_NAME(o.schema_id), o.name
            """;

        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = sql;

            using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);

            const int objectIdOrdinal = 0;
            const int schemaOrdinal = 1;
            const int nameOrdinal = 2;
            const int defOrdinal = 3;
            const int descriptionOrdinal = 4;

            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            {
                var objectId = reader.GetInt32(objectIdOrdinal);
                var schema = reader.GetString(schemaOrdinal);
                var name = reader.GetString(nameOrdinal);
                var definition = reader.GetStringNull(defOrdinal);
                var description = reader.GetStringNull(descriptionOrdinal);

                var functionBuilder = new TableValuedFunctionBuilder()
                    .WithSchemaQualifiedName(schema, name)
                    .WithDefinition(definition)
                    .WithDescription(description);

                ApplyExtendedProperties((1, objectId, 0), functionBuilder);

                functions[objectId] = functionBuilder;
            }
        }

        if (functions.Count == 0)
            return;

        await ReadParametersAsync(connection, functions, "o.type IN ('TF', 'IF', 'FT')", cancellationToken).ConfigureAwait(false);

        await ReadTableValuedFunctionColumnsAsync(connection, functions, cancellationToken).ConfigureAwait(false);

        foreach (var (_, fb) in functions)
            builder.AddTableValuedFunction(fb.Build());
    }

    private async Task ReadTableValuedFunctionColumnsAsync(
        SqlConnection connection,
        Dictionary<int, TableValuedFunctionBuilder> funcs,
        CancellationToken cancellationToken)
    {
        const string sql = """
            SELECT
                c.object_id,
                c.column_id,
                c.name                      AS column_name,
                st.name                     AS system_type_name,
                TYPE_NAME(c.user_type_id)   AS user_type_name,
                c.max_length,
                c.precision,
                c.scale,
                c.is_nullable
            FROM sys.columns c
            INNER JOIN sys.objects o ON c.object_id = o.object_id
            INNER JOIN sys.types st
                ON c.system_type_id = st.system_type_id
                AND st.system_type_id = st.user_type_id
            WHERE o.type IN ('TF', 'IF', 'FT') AND o.is_ms_shipped = 0
            ORDER BY c.object_id, c.column_id
            """;

        using var cmd = connection.CreateCommand();
        cmd.CommandText = sql;

        using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);

        const int objectIdOrdinal = 0;
        const int columnIdOrdinal = 1;
        const int columnNameOrdinal = 2;
        const int sysTypeOrdinal = 3;
        const int userTypeOrdinal = 4;
        const int maxLenOrdinal = 5;
        const int precisionOrdinal = 6;
        const int scaleOrdinal = 7;
        const int nullableOrdinal = 8;

        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
        {
            var objectId = reader.GetInt32(objectIdOrdinal);
            if (!funcs.TryGetValue(objectId, out var functionBuilder))
                continue;

            var systemTypeName = reader.GetString(sysTypeOrdinal);
            var userTypeName = reader.GetStringNull(userTypeOrdinal) ?? systemTypeName;
            var maxLength = reader.GetInt16(maxLenOrdinal);
            var precision = reader.GetByte(precisionOrdinal);
            var scale = reader.GetByte(scaleOrdinal);
            var columnName = reader.GetString(columnNameOrdinal);
            var columnId = reader.GetInt32(columnIdOrdinal);
            var isNullable = reader.GetBoolean(nullableOrdinal);

            var (dbType, sqlDbType, systemType, isUnicode, isFixedLength) = SqlServerTypeMapper.MapNativeType(systemTypeName);
            var nativeTypeName = FormatNativeTypeName(systemTypeName, userTypeName, maxLength, precision, scale);
            var maxLengthValue = NormalizeMaxLength(systemTypeName, maxLength);
            byte? precisionValue = HasPrecision(systemTypeName) ? precision : null;
            var scaleValue = HasScale(systemTypeName) ? (int?)scale : null;

            ReturnColumn returnColumn = new()
            {
                Name = columnName,
                OrdinalPosition = columnId,
                IsNullable = isNullable,
                DbType = dbType,
                NativeTypeName = nativeTypeName,
                SystemType = systemType,
                MaxLength = maxLengthValue,
                Precision = precisionValue,
                Scale = scaleValue,
                IsUnicode = isUnicode,
                IsFixedLength = isFixedLength,
                Annotations = new Dictionary<string, object?>
                {
                    [SqlServerAnnotations.SqlDbType] = sqlDbType.ToString(),
                },
            };

            functionBuilder.AddReturnColumn(returnColumn);
        }
    }
}

1	using Microsoft.Data.SqlClient;
2
3	using SchemaSaurus.Metadata;
4	using SchemaSaurus.Metadata.Builders;
5	using SchemaSaurus.Metadata.Extensions;
6	using SchemaSaurus.Metadata.Provider;
7
8	namespace SchemaSaurus.SqlServer;
9
10	public sealed partial class SqlServerSchemaReader
11	{
12	/// <inheritdoc />
13	protected override async Task ReadStoredProceduresAsync(
14	SqlConnection connection,
15	DatabaseModelBuilder builder,
16	SchemaReaderOptions options,
17	CancellationToken cancellationToken)
18	{
19	var schemaFilter = BuildSchemaFilter(options.Schemas, "SCHEMA_NAME(p.schema_id)");	6✔
20
21	var schemaWhere = schemaFilter is not null ? $"\n AND {schemaFilter}" : "";	6!
22
23	var procedures = new Dictionary<int, StoredProcedureBuilder>();	6✔
24
25	var sql = $"""	6✔
26	SELECT	6✔
27	p.object_id,	6✔
28	SCHEMA_NAME(p.schema_id) AS schema_name,	6✔
29	p.name AS proc_name,	6✔
30	m.definition,	6✔
31	CAST(ep.value AS NVARCHAR(4000)) AS description	6✔
32	FROM sys.procedures p	6✔
33	LEFT JOIN sys.sql_modules m ON p.object_id = m.object_id	6✔
34	LEFT JOIN sys.extended_properties ep	6✔
35	ON ep.major_id = p.object_id AND ep.minor_id = 0	6✔
36	AND ep.class = 1 AND ep.name = 'MS_Description'	6✔
37	WHERE p.is_ms_shipped = 0{schemaWhere}	6✔
38	ORDER BY SCHEMA_NAME(p.schema_id), p.name	6✔
39	""";	6✔
40
41	using (var cmd = connection.CreateCommand())	6✔
42	{
43	cmd.CommandText = sql;	6✔
44
45	using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);	6✔
46
47	const int objectIdOrdinal = 0;
48	const int schemaOrdinal = 1;
49	const int nameOrdinal = 2;
50	const int definitionOrdinal = 3;
51	const int descriptionOrdinal = 4;
52
53	while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))	24✔
54	{
55	var objectId = reader.GetInt32(objectIdOrdinal);	18✔
56	var schema = reader.GetString(schemaOrdinal);	18✔
57	var name = reader.GetString(nameOrdinal);	18✔
58	var definition = reader.GetStringNull(definitionOrdinal);	18✔
59	var description = reader.GetStringNull(descriptionOrdinal);	18✔
60
61	var storedProcedureBuilder = new StoredProcedureBuilder()	18✔
62	.WithSchemaQualifiedName(schema, name)	18✔
63	.WithDefinition(definition)	18✔
64	.WithDescription(description);	18✔
65
66	ApplyExtendedProperties((1, objectId, 0), storedProcedureBuilder);	18✔
67
68	procedures[objectId] = storedProcedureBuilder;	18✔
69	}
70	}	6✔
71
72	if (procedures.Count == 0)	6!
73	return;	×
74
75	await ReadParametersAsync(connection, procedures, "o.type = 'P'", cancellationToken).ConfigureAwait(false);	6✔
76
77	foreach (var (_, spb) in procedures)	48✔
78	builder.AddStoredProcedure(spb.Build());	18✔
79	}	6✔
80
81	/// <inheritdoc />
82	protected override async Task ReadScalarFunctionsAsync(
83	SqlConnection connection,
84	DatabaseModelBuilder builder,
85	SchemaReaderOptions options,
86	CancellationToken cancellationToken)
87	{
88	var schemaFilter = BuildSchemaFilter(options.Schemas, "SCHEMA_NAME(o.schema_id)");	6✔
89
90	var schemaWhere = schemaFilter is not null ? $"\n AND {schemaFilter}" : "";	6!
91
92	var functions = new Dictionary<int, ScalarFunctionBuilder>();	6✔
93
94	var sql = $"""	6✔
95	SELECT	6✔
96	o.object_id,	6✔
97	SCHEMA_NAME(o.schema_id) AS schema_name,	6✔
98	o.name AS func_name,	6✔
99	m.definition,	6✔
100	CAST(ep.value AS NVARCHAR(4000)) AS description	6✔
101	FROM sys.objects o	6✔
102	LEFT JOIN sys.sql_modules m ON o.object_id = m.object_id	6✔
103	LEFT JOIN sys.extended_properties ep	6✔
104	ON ep.major_id = o.object_id AND ep.minor_id = 0	6✔
105	AND ep.class = 1 AND ep.name = 'MS_Description'	6✔
106	WHERE o.type IN ('FN', 'FS') AND o.is_ms_shipped = 0{schemaWhere}	6✔
107	ORDER BY SCHEMA_NAME(o.schema_id), o.name	6✔
108	""";	6✔
109
110	using (var cmd = connection.CreateCommand())	6✔
111	{
112	cmd.CommandText = sql;	6✔
113
114	using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);	6✔
115
116	const int objectIdOrdinal = 0;
117	const int schemaOrdinal = 1;
118	const int nameOrdinal = 2;
119	const int definitionOrdinal = 3;
120	const int descriptionOrdinal = 4;
121
122	while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))	12✔
123	{
124	var objectId = reader.GetInt32(objectIdOrdinal);	6✔
125	var schema = reader.GetString(schemaOrdinal);	6✔
126	var name = reader.GetString(nameOrdinal);	6✔
127	var definition = reader.GetStringNull(definitionOrdinal);	6✔
128	var description = reader.GetStringNull(descriptionOrdinal);	6✔
129
130	var functionBuilder = new ScalarFunctionBuilder()	6✔
131	.WithSchemaQualifiedName(schema, name)	6✔
132	.WithDefinition(definition)	6✔
133	.WithDescription(description);	6✔
134
135	ApplyExtendedProperties((1, objectId, 0), functionBuilder);	6✔
136
137	functions[objectId] = functionBuilder;	6✔
138	}
139	}	6✔
140
141	if (functions.Count == 0)	6!
142	return;	×
143
144	await ReadScalarFunctionParametersAsync(connection, functions, cancellationToken).ConfigureAwait(false);	6✔
145
146	foreach (var (_, fb) in functions)	24✔
147	builder.AddScalarFunction(fb.Build());	6✔
148	}	6✔
149
150	private async Task ReadScalarFunctionParametersAsync(
151	SqlConnection connection,
152	Dictionary<int, ScalarFunctionBuilder> functions,
153	CancellationToken cancellationToken)
154	{
155	const string sql = """
156	SELECT
157	par.object_id,
158	par.name AS param_name,
159	par.parameter_id,
160	st.name AS system_type_name,
161	TYPE_NAME(par.user_type_id) AS user_type_name,
162	par.max_length,
163	par.precision,
164	par.scale,
165	par.is_output
166	FROM sys.parameters par
167	INNER JOIN sys.objects o ON par.object_id = o.object_id
168	INNER JOIN sys.types st
169	ON par.system_type_id = st.system_type_id
170	AND st.system_type_id = st.user_type_id
171	WHERE o.type IN ('FN', 'FS') AND o.is_ms_shipped = 0
172	ORDER BY par.object_id, par.parameter_id
173	""";
174
175	using var cmd = connection.CreateCommand();	6✔
176	cmd.CommandText = sql;	6✔
177
178	using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);	6✔
179
180	const int objectIdOrdinal = 0;
181	const int nameOrdinal = 1;
182	const int paramIdOrdinal = 2;
183	const int sysTypeOrdinal = 3;
184	const int userTypeOrdinal = 4;
185	const int maxLenOrdinal = 5;
186	const int precisionOrdinal = 6;
187	const int scaleOrdinal = 7;
188	const int outputOrdinal = 8;
189
190	while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))	36✔
191	{
192	var objectId = reader.GetInt32(objectIdOrdinal);	30✔
193
194	if (!functions.TryGetValue(objectId, out var functionBuilder))	30✔
195	continue;
196
197	var paramName = reader.GetString(nameOrdinal);	30✔
198	var paramId = reader.GetInt32(paramIdOrdinal);	30✔
199	var systemTypeName = reader.GetString(sysTypeOrdinal);	30✔
200	var userTypeName = reader.GetStringNull(userTypeOrdinal) ?? systemTypeName;	30!
201	var maxLength = reader.GetInt16(maxLenOrdinal);	30✔
202	var precision = reader.GetByte(precisionOrdinal);	30✔
203	var scale = reader.GetByte(scaleOrdinal);	30✔
204	var isOutput = reader.GetBoolean(outputOrdinal);	30✔
205
206	var direction = isOutput	30✔
207	? Metadata.ParameterDirection.Output	30✔
208	: Metadata.ParameterDirection.Input;	30✔
209
210	var maxLengthValue = NormalizeMaxLength(systemTypeName, maxLength);	30✔
211	byte? precisionValue = HasPrecision(systemTypeName) ? precision : null;	30!
212	var scaleValue = HasScale(systemTypeName) ? (int?)scale : null;	30!
213
214	var (dbType, sqlDbType, systemType, isUnicode, isFixedLength) = SqlServerTypeMapper.MapNativeType(systemTypeName);	30✔
215	var nativeTypeName = FormatNativeTypeName(systemTypeName, userTypeName, maxLength, precision, scale);	30✔
216
217	if (paramId == 0)	30✔
218	{
219	TypeMapping returnType = new()	6!
220	{	6✔
221	DbType = dbType,	6✔
222	NativeTypeName = nativeTypeName,	6✔
223	SystemType = systemType,	6✔
224	MaxLength = maxLengthValue,	6✔
225	Precision = precisionValue,	6✔
226	Scale = scaleValue,	6✔
227	IsUnicode = isUnicode,	6✔
228	IsFixedLength = isFixedLength,	6✔
229	};	6✔
230	functionBuilder.WithReturnType(returnType);	6✔
231	}
232	else
233	{
234	functionBuilder.AddParameter(parameterBuilder =>	24✔
235	{	24✔
236	parameterBuilder	24✔
237	.WithName(paramName)	24✔
238	.WithOrdinal(paramId)	24✔
239	.WithDirection(direction)	24✔
240	.WithNativeTypeName(nativeTypeName)	24✔
241	.WithDbType(dbType)	24✔
242	.WithSystemType(systemType)	24✔
243	.WithMaxLength(maxLengthValue)	24✔
244	.WithPrecision(precisionValue)	24✔
245	.WithScale(scaleValue)	24✔
246	.WithIsUnicode(isUnicode)	24✔
247	.WithIsFixedLength(isFixedLength);	24✔
248		24✔
249	ApplyExtendedProperties((2, objectId, paramId), parameterBuilder);	24✔
250	parameterBuilder.WithAnnotation(SqlServerAnnotations.SqlDbType, sqlDbType.ToString());	24✔
251	});	24✔
252	}
253	}
254	}	6✔
255
256	/// <inheritdoc />
257	protected override async Task ReadTableValuedFunctionsAsync(
258	SqlConnection connection,
259	DatabaseModelBuilder builder,
260	SchemaReaderOptions options,
261	CancellationToken cancellationToken)
262	{
263	var schemaFilter = BuildSchemaFilter(options.Schemas, "SCHEMA_NAME(o.schema_id)");	7✔
264
265	var schemaWhere = schemaFilter is not null ? $"\n AND {schemaFilter}" : "";	7!
266
267	var functions = new Dictionary<int, TableValuedFunctionBuilder>();	7✔
268
269	var sql = $"""	7✔
270	SELECT	7✔
271	o.object_id,	7✔
272	SCHEMA_NAME(o.schema_id) AS schema_name,	7✔
273	o.name AS func_name,	7✔
274	m.definition,	7✔
275	CAST(ep.value AS NVARCHAR(4000)) AS description	7✔
276	FROM sys.objects o	7✔
277	LEFT JOIN sys.sql_modules m ON o.object_id = m.object_id	7✔
278	LEFT JOIN sys.extended_properties ep	7✔
279	ON ep.major_id = o.object_id AND ep.minor_id = 0	7✔
280	AND ep.class = 1 AND ep.name = 'MS_Description'	7✔
281	WHERE o.type IN ('TF', 'IF', 'FT') AND o.is_ms_shipped = 0{schemaWhere}	7✔
282	ORDER BY SCHEMA_NAME(o.schema_id), o.name	7✔
283	""";	7✔
284
285	using (var cmd = connection.CreateCommand())	7✔
286	{
287	cmd.CommandText = sql;	7✔
288
289	using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);	7✔
290
291	const int objectIdOrdinal = 0;
292	const int schemaOrdinal = 1;
293	const int nameOrdinal = 2;
294	const int defOrdinal = 3;
295	const int descriptionOrdinal = 4;
296
297	while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))	7!
298	{
299	var objectId = reader.GetInt32(objectIdOrdinal);	×
300	var schema = reader.GetString(schemaOrdinal);	×
301	var name = reader.GetString(nameOrdinal);	×
302	var definition = reader.GetStringNull(defOrdinal);	×
303	var description = reader.GetStringNull(descriptionOrdinal);	×
304
305	var functionBuilder = new TableValuedFunctionBuilder()	×
306	.WithSchemaQualifiedName(schema, name)	×
307	.WithDefinition(definition)	×
308	.WithDescription(description);	×
309
310	ApplyExtendedProperties((1, objectId, 0), functionBuilder);	×
311
312	functions[objectId] = functionBuilder;	×
313	}
314	}	7✔
315
316	if (functions.Count == 0)	7!
317	return;	7✔
318
319	await ReadParametersAsync(connection, functions, "o.type IN ('TF', 'IF', 'FT')", cancellationToken).ConfigureAwait(false);	×
320
321	await ReadTableValuedFunctionColumnsAsync(connection, functions, cancellationToken).ConfigureAwait(false);	×
322
323	foreach (var (_, fb) in functions)	×
324	builder.AddTableValuedFunction(fb.Build());	×
325	}	7✔
326
327	private async Task ReadTableValuedFunctionColumnsAsync(
328	SqlConnection connection,
329	Dictionary<int, TableValuedFunctionBuilder> funcs,
330	CancellationToken cancellationToken)
331	{
332	const string sql = """
333	SELECT
334	c.object_id,
335	c.column_id,
336	c.name AS column_name,
337	st.name AS system_type_name,
338	TYPE_NAME(c.user_type_id) AS user_type_name,
339	c.max_length,
340	c.precision,
341	c.scale,
342	c.is_nullable
343	FROM sys.columns c
344	INNER JOIN sys.objects o ON c.object_id = o.object_id
345	INNER JOIN sys.types st
346	ON c.system_type_id = st.system_type_id
347	AND st.system_type_id = st.user_type_id
348	WHERE o.type IN ('TF', 'IF', 'FT') AND o.is_ms_shipped = 0
349	ORDER BY c.object_id, c.column_id
350	""";
351
352	using var cmd = connection.CreateCommand();	×
353	cmd.CommandText = sql;	×
354
355	using var reader = await cmd.ExecuteReaderAsync(SequentialResultBehavior, cancellationToken).ConfigureAwait(false);	×
356
357	const int objectIdOrdinal = 0;
358	const int columnIdOrdinal = 1;
359	const int columnNameOrdinal = 2;
360	const int sysTypeOrdinal = 3;
361	const int userTypeOrdinal = 4;
362	const int maxLenOrdinal = 5;
363	const int precisionOrdinal = 6;
364	const int scaleOrdinal = 7;
365	const int nullableOrdinal = 8;
366
367	while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))	×
368	{
369	var objectId = reader.GetInt32(objectIdOrdinal);	×
370	if (!funcs.TryGetValue(objectId, out var functionBuilder))	×
371	continue;
372
373	var systemTypeName = reader.GetString(sysTypeOrdinal);	×
374	var userTypeName = reader.GetStringNull(userTypeOrdinal) ?? systemTypeName;	×
375	var maxLength = reader.GetInt16(maxLenOrdinal);	×
376	var precision = reader.GetByte(precisionOrdinal);	×
377	var scale = reader.GetByte(scaleOrdinal);	×
378	var columnName = reader.GetString(columnNameOrdinal);	×
379	var columnId = reader.GetInt32(columnIdOrdinal);	×
380	var isNullable = reader.GetBoolean(nullableOrdinal);	×
381
382	var (dbType, sqlDbType, systemType, isUnicode, isFixedLength) = SqlServerTypeMapper.MapNativeType(systemTypeName);	×
383	var nativeTypeName = FormatNativeTypeName(systemTypeName, userTypeName, maxLength, precision, scale);	×
384	var maxLengthValue = NormalizeMaxLength(systemTypeName, maxLength);	×
385	byte? precisionValue = HasPrecision(systemTypeName) ? precision : null;	×
386	var scaleValue = HasScale(systemTypeName) ? (int?)scale : null;	×
387
NEW 388	ReturnColumn returnColumn = new()	×
389	{	×
NEW 390	Name = columnName,	×
NEW 391	OrdinalPosition = columnId,	×
NEW 392	IsNullable = isNullable,	×
NEW 393	DbType = dbType,	×
NEW 394	NativeTypeName = nativeTypeName,	×
NEW 395	SystemType = systemType,	×
NEW 396	MaxLength = maxLengthValue,	×
NEW 397	Precision = precisionValue,	×
NEW 398	Scale = scaleValue,	×
NEW 399	IsUnicode = isUnicode,	×
NEW 400	IsFixedLength = isFixedLength,	×
NEW 401	Annotations = new Dictionary<string, object?>	×
NEW 402	{	×
NEW 403	[SqlServerAnnotations.SqlDbType] = sqlDbType.ToString(),	×
NEW 404	},	×
UNCOV 405	};	×
406
NEW 407	functionBuilder.AddReturnColumn(returnColumn);	×
408	}
409	}	×
410	}

loresoft / SchemaSaurus / 25392814637

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous