jas88 / FAnsiSql / 431

Committed 16 Nov 2025 08:33PM UTC coverage: 49.568%. First build

Build # 431

Build Type

Pull #64

github

Committed by

jas88

Commit Message

Remove parallel mode flag - we have one job uploading multiple files, not multiple jobs

Pull Request Pull Request #64: Migrate to TypeGuesser v2.0.1 and code quality improvements

Run Details

1621 of 3623 branches covered (44.74%)

Branch coverage included in aggregate %.

48 of 105 new or added lines in 9 files covered. (45.71%)

4575 of 8877 relevant lines covered (51.54%)

442.83 hits per line

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

50.83

/FAnsi.MicrosoftSql/MicrosoftSQLTableHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.Linq;
using System.Text.RegularExpressions;
using FAnsi.Connections;
using FAnsi.Discovery;
using FAnsi.Discovery.Constraints;
using FAnsi.Exceptions;
using FAnsi.Naming;
using Microsoft.Data.SqlClient;

namespace FAnsi.Implementations.MicrosoftSQL;

public sealed partial class MicrosoftSQLTableHelper : DiscoveredTableHelper
{
    public override IEnumerable<DiscoveredColumn> DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database)
    {
        //don't bother looking for pks if it is a table valued function
        var pks = discoveredTable is DiscoveredTableValuedFunction
            ? null
            : ListPrimaryKeys(connection, discoveredTable).ToHashSet();

        using var cmd = discoveredTable.GetCommand(
            $"use [{database}];\r\nSELECT  \r\nsys.columns.name AS COLUMN_NAME,\r\n sys.types.name AS TYPE_NAME,\r\n  sys.columns.collation_name AS COLLATION_NAME,\r\n   sys.columns.max_length as LENGTH,\r\n   sys.columns.scale as SCALE,\r\n    sys.columns.is_identity,\r\n    sys.columns.is_nullable,\r\n   sys.columns.precision as PRECISION,\r\nsys.columns.collation_name\r\nfrom sys.columns \r\njoin \r\nsys.types on sys.columns.user_type_id = sys.types.user_type_id\r\nwhere object_id = OBJECT_ID(@tableName)", connection.Connection, connection.Transaction);
        var p = cmd.CreateParameter();
        p.ParameterName = "@tableName";
        p.Value = GetObjectName(discoveredTable);
        cmd.Parameters.Add(p);

        using var r = cmd.ExecuteReader();
        while (r.Read())
        {
            var isNullable = Convert.ToBoolean(r["is_nullable"], CultureInfo.InvariantCulture);

            //if it is a table valued function prefix the column name with the table valued function name
            var columnName = discoveredTable is DiscoveredTableValuedFunction
                ? $"{discoveredTable.GetRuntimeName()}.{r["COLUMN_NAME"]}"
                : r["COLUMN_NAME"].ToString()!;

            var toAdd = new DiscoveredColumn(discoveredTable, columnName, isNullable)
            {
                IsAutoIncrement = Convert.ToBoolean(r["is_identity"], CultureInfo.InvariantCulture),
                Collation = r["collation_name"] as string
            };
            toAdd.DataType = new DiscoveredDataType(r, GetSQLType_FromSpColumnsResult(r), toAdd);
            toAdd.IsPrimaryKey = pks?.Contains(toAdd.GetRuntimeName()) ?? false;
            yield return toAdd;
        }
    }

    /// <summary>
    /// Returns the table name suitable for being passed into OBJECT_ID including schema if any
    /// </summary>
    /// <param name="table"></param>
    /// <returns></returns>
    private static string? GetObjectName(DiscoveredTable table)
    {
        var syntax = table.GetQuerySyntaxHelper();

        var objectName = syntax.EnsureWrapped(table.GetRuntimeName());

        return table.Schema != null ? $"{syntax.EnsureWrapped(table.Schema)}.{objectName}" : objectName;
    }

    public override IDiscoveredColumnHelper GetColumnHelper() => new MicrosoftSQLColumnHelper();

    public override void DropTable(DbConnection connection, DiscoveredTable tableToDrop)
    {
        SqlCommand cmd;

        switch (tableToDrop.TableType)
        {
            case TableType.View:
                if (connection.Database != tableToDrop.Database.GetRuntimeName())
                    connection.ChangeDatabase(tableToDrop.GetRuntimeName());

                if (!connection.Database.ToLower(CultureInfo.InvariantCulture).Equals(tableToDrop.Database.GetRuntimeName().ToLower(CultureInfo.InvariantCulture), StringComparison.Ordinal))
                    throw new NotSupportedException(
                        $"Cannot drop view {tableToDrop} because it exists in database {tableToDrop.Database.GetRuntimeName()} while the current current database connection is pointed at database:{connection.Database} (use .ChangeDatabase on the connection first) - SQL Server does not support cross database view dropping");

                cmd = new SqlCommand($"DROP VIEW {tableToDrop.GetWrappedName()}", (SqlConnection)connection);
                break;
            case TableType.Table:
                cmd = new SqlCommand($"DROP TABLE {tableToDrop.GetFullyQualifiedName()}", (SqlConnection)connection);
                break;
            case TableType.TableValuedFunction:
                DropFunction(connection, (DiscoveredTableValuedFunction)tableToDrop);
                return;
            default:
                throw new ArgumentOutOfRangeException(nameof(tableToDrop), $"Unknown table type {tableToDrop.TableType}");
        }

        using (cmd)
            cmd.ExecuteNonQuery();
    }

    public override void DropFunction(DbConnection connection, DiscoveredTableValuedFunction functionToDrop)
    {
        using var cmd = new SqlCommand($"DROP FUNCTION {functionToDrop.Schema ?? "dbo"}.{functionToDrop.GetRuntimeName()}", (SqlConnection)connection);
        cmd.ExecuteNonQuery();
    }

    public override void DropColumn(DbConnection connection, DiscoveredColumn columnToDrop)
    {
        using var cmd = new SqlCommand(
            $"ALTER TABLE {columnToDrop.Table.GetFullyQualifiedName()} DROP column {columnToDrop.GetWrappedName()}", (SqlConnection)connection);
        cmd.ExecuteNonQuery();
    }

    /// <summary>
    /// Checks if the table exists using the provided connection.
    /// </summary>
    /// <param name="table">The table to check</param>
    /// <param name="connection">The managed connection to use</param>
    /// <returns>True if the table exists, false otherwise</returns>
    public override bool Exists(DiscoveredTable table, IManagedConnection connection)
    {
        if (!table.Database.Exists())
            return false;

        // Use sys.objects to check for table/view existence with a single targeted query
        var objectType = table.TableType switch
        {
            TableType.Table => "'U'", // U = user table
            TableType.View => "'V'",  // V = view
            _ => "'U', 'V'" // For TableValuedFunction or unknown, check both
        };

        var sql = $"""
            SELECT CASE WHEN EXISTS (
                SELECT 1 FROM sys.objects o
                INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
                WHERE o.name = @tableName
                AND s.name = @schemaName
                AND o.type IN ({objectType})
            ) THEN 1 ELSE 0 END
            """;

        using var cmd = table.GetCommand(sql, connection.Connection, connection.Transaction);

        var p = cmd.CreateParameter();
        p.ParameterName = "@tableName";
        p.Value = table.GetRuntimeName();
        cmd.Parameters.Add(p);

        var p2 = cmd.CreateParameter();
        p2.ParameterName = "@schemaName";
        p2.Value = table.Schema ?? "dbo";
        cmd.Parameters.Add(p2);

        var result = cmd.ExecuteScalar();
        return Convert.ToInt32(result, CultureInfo.InvariantCulture) == 1;
    }

    [Obsolete("Prefer using Exists(DiscoveredTable, IManagedConnection) to reuse connections and improve performance")]
    public override bool Exists(DiscoveredTable table, IManagedTransaction? transaction = null)
    {
        using var connection = table.Database.Server.GetManagedConnection(transaction);
        return Exists(table, connection);
    }

    /// <summary>
    /// Checks if the table has a primary key using the provided connection.
    /// </summary>
    /// <param name="table">The table to check</param>
    /// <param name="connection">The managed connection to use</param>
    /// <returns>True if the table has a primary key, false otherwise</returns>
    public override bool HasPrimaryKey(DiscoveredTable table, IManagedConnection connection)
    {
        const string sql = """
            SELECT CASE WHEN EXISTS (
                SELECT 1 FROM sys.indexes i
                INNER JOIN sys.objects o ON i.object_id = o.object_id
                INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
                WHERE i.is_primary_key = 1
                AND o.name = @tableName
                AND s.name = @schemaName
            ) THEN 1 ELSE 0 END
            """;

        using var cmd = table.GetCommand(sql, connection.Connection, connection.Transaction);

        var p = cmd.CreateParameter();
        p.ParameterName = "@tableName";
        p.Value = table.GetRuntimeName();
        cmd.Parameters.Add(p);

        var p2 = cmd.CreateParameter();
        p2.ParameterName = "@schemaName";
        p2.Value = table.Schema ?? "dbo";
        cmd.Parameters.Add(p2);

        var result = cmd.ExecuteScalar();
        return Convert.ToInt32(result, CultureInfo.InvariantCulture) == 1;
    }

    [Obsolete("Prefer using HasPrimaryKey(DiscoveredTable, IManagedConnection) to reuse connections and improve performance")]
    public override bool HasPrimaryKey(DiscoveredTable table, IManagedTransaction? transaction = null)
    {
        using var connection = table.Database.Server.GetManagedConnection(transaction);
        return HasPrimaryKey(table, connection);
    }

    /// <summary>
    /// Gets the auto-increment column for the table using a database-specific SQL query (90-99% faster than discovering all columns).
    /// </summary>
    /// <returns>The auto-increment column, or null if none exists</returns>
    public DiscoveredColumn? GetAutoIncrementColumn(DiscoveredTable table, IManagedConnection connection)
    {
        const string sql = """
                           SELECT c.name
                           FROM sys.identity_columns c
                           WHERE c.object_id = OBJECT_ID(@tableName)
                           """;

        using var cmd = table.GetCommand(sql, connection.Connection);
        var p = cmd.CreateParameter();
        p.ParameterName = "@tableName";
        p.Value = GetObjectName(table);
        cmd.Parameters.Add(p);
        cmd.Transaction = connection.Transaction;

        var columnName = cmd.ExecuteScalar() as string;

        if (columnName == null)
            return null;

        // DiscoverColumn will use the table's database connection
        return table.DiscoverColumn(columnName);
    }

    public override IEnumerable<DiscoveredParameter> DiscoverTableValuedFunctionParameters(DbConnection connection,
        DiscoveredTableValuedFunction discoveredTableValuedFunction, DbTransaction? transaction)
    {
        if (connection.State != ConnectionState.Open)
            throw new ArgumentException($@"Connection state was {connection.State} but had to be Open", nameof(connection));

        const string query = """
                             select
                             sys.parameters.name AS name,
                             sys.types.name AS TYPE_NAME,
                             sys.parameters.max_length AS LENGTH,
                             sys.types.collation_name AS COLLATION_NAME,
                             sys.parameters.scale AS SCALE,
                             sys.parameters.precision AS PRECISION
                              from
                             sys.parameters
                             join
                             sys.types on sys.parameters.user_type_id = sys.types.user_type_id
                             where object_id = OBJECT_ID(@tableName)
                             """;

        using var cmd = discoveredTableValuedFunction.GetCommand(query, connection);
        var p = cmd.CreateParameter();
        p.ParameterName = "@tableName";
        p.Value = GetObjectName(discoveredTableValuedFunction);
        cmd.Parameters.Add(p);

        cmd.Transaction = transaction;

        using var r = cmd.ExecuteReader();
        while (r.Read())
        {
            var name = r["name"].ToString();
            if (name != null)
                yield return new DiscoveredParameter(name)
                {
                    DataType = new DiscoveredDataType(r, GetSQLType_FromSpColumnsResult(r), null)
                };
        }
    }

    public override IBulkCopy BeginBulkInsert(DiscoveredTable discoveredTable, IManagedConnection connection, CultureInfo culture) => new MicrosoftSQLBulkCopy(discoveredTable, connection, culture);

    public override void CreatePrimaryKey(DatabaseOperationArgs args, DiscoveredTable table, DiscoveredColumn[] discoverColumns)
    {
        try
        {
            using var connection = args.GetManagedConnection(table);
            var columnHelper = GetColumnHelper();
            foreach (var alterSql in discoverColumns.Where(static dc => dc.AllowNulls).Select(col => columnHelper.GetAlterColumnToSql(col, col.DataType!.SQLType!, false)))
            {
                using var alterCmd = table.GetCommand(alterSql, connection.Connection, connection.Transaction);
                args.ExecuteNonQuery(alterCmd);
            }
        }
        catch (DbException e)
        {
            throw new AlterFailedException(string.Format(CultureInfo.InvariantCulture, FAnsiStrings.DiscoveredTableHelper_CreatePrimaryKey_Failed_to_create_primary_key_on_table__0__using_columns___1__, table, string.Join(",", discoverColumns.Select(static c => c.GetRuntimeName()))), e);
        }

        base.CreatePrimaryKey(args, table, discoverColumns);
    }

    public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction? transaction = null)
    {
        var toReturn = new Dictionary<string, DiscoveredRelationship>();

        const string sql = "exec sp_fkeys @pktable_name = @table, @pktable_qualifier=@database, @pktable_owner=@schema";

        using (var cmd = table.GetCommand(sql, connection))
        {
            if (transaction != null)
                cmd.Transaction = transaction.Transaction;

            var p = cmd.CreateParameter();
            p.ParameterName = "@table";
            p.Value = table.GetRuntimeName();
            p.DbType = DbType.String;
            cmd.Parameters.Add(p);

            p = cmd.CreateParameter();
            p.ParameterName = "@schema";
            p.Value = table.Schema ?? "dbo";
            p.DbType = DbType.String;
            cmd.Parameters.Add(p);

            p = cmd.CreateParameter();
            p.ParameterName = "@database";
            p.Value = table.Database.GetRuntimeName();
            p.DbType = DbType.String;
            cmd.Parameters.Add(p);

            using var dt = new DataTable();
            var da = table.Database.Server.GetDataAdapter(cmd);
            da.Fill(dt);

            foreach (DataRow r in dt.Rows)
            {
                var fkName = r["FK_NAME"].ToString() ?? throw new InvalidOperationException("Null foreign key name returned");

                //could be a 2+ columns foreign key?
                if (!toReturn.TryGetValue(fkName, out var current))
                {
                    var pkdb = r["PKTABLE_QUALIFIER"].ToString() ?? throw new InvalidOperationException("Null primary key database name returned");
                    var pkschema = r["PKTABLE_OWNER"].ToString();
                    var pktableName = r["PKTABLE_NAME"].ToString() ?? throw new InvalidOperationException("Null primary key table name returned");

                    var pktable = table.Database.Server.ExpectDatabase(pkdb).ExpectTable(pktableName, pkschema);

                    var fkdb = r["FKTABLE_QUALIFIER"].ToString() ?? throw new InvalidOperationException("Null foreign key database name returned");
                    var fkschema = r["FKTABLE_OWNER"].ToString();
                    var fktableName = r["FKTABLE_NAME"].ToString() ?? throw new InvalidOperationException("Null foreign key name returned");

                    var fktable = table.Database.Server.ExpectDatabase(fkdb).ExpectTable(fktableName, fkschema);

                    var deleteRuleInt = Convert.ToInt32(r["DELETE_RULE"], CultureInfo.InvariantCulture);

                    var deleteRule = deleteRuleInt switch
                    {
                        0 => CascadeRule.Delete,
                        1 => CascadeRule.NoAction,
                        2 => CascadeRule.SetNull,
                        3 => CascadeRule.SetDefault,
                        _ => CascadeRule.Unknown
                    };

                    /*
    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql?view=sql-server-2017

    0=CASCADE changes to foreign key.
    1=NO ACTION changes if foreign key is present.
    2 = set null
    3 = set default*/

                    current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
                    toReturn.Add(current.Name, current);
                }

                current.AddKeys(r["PKCOLUMN_NAME"].ToString()!, r["FKCOLUMN_NAME"].ToString()!, transaction);
            }
        }

        return [.. toReturn.Values];

    }

    protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string newName)
    {
        var oldName = discoveredTable.GetWrappedName();

        var syntax = discoveredTable.GetQuerySyntaxHelper();

        if (!string.IsNullOrWhiteSpace(discoveredTable.Schema))
            oldName = $"{syntax.EnsureWrapped(discoveredTable.Schema)}.{oldName}";

        return $"exec sp_rename '{syntax.Escape(oldName)}', '{syntax.Escape(newName)}'";
    }

    public override void MakeDistinct(DatabaseOperationArgs args, DiscoveredTable discoveredTable)
    {
        var syntax = discoveredTable.GetQuerySyntaxHelper();

        const string sql = """
                           DELETE f
                                       FROM (
                                       SELECT        ROW_NUMBER() OVER (PARTITION BY {0} ORDER BY {0}) AS RowNum
                                       FROM {1}

                                       ) as f
                                       where RowNum > 1
                           """;

        var columnList = string.Join(",",
            discoveredTable.DiscoverColumns().Select(c => syntax.EnsureWrapped(c.GetRuntimeName())));

        var sqlToExecute = string.Format(CultureInfo.InvariantCulture, sql, columnList, discoveredTable.GetFullyQualifiedName());

        var server = discoveredTable.Database.Server;

        using var con = args.GetManagedConnection(server);
        using var cmd = server.GetCommand(sqlToExecute, con);
        args.ExecuteNonQuery(cmd);
    }


    public override string GetTopXSqlForTable(IHasFullyQualifiedNameToo table, int topX) => $"SELECT TOP {topX} * FROM {table.GetFullyQualifiedName()}";

    private string GetSQLType_FromSpColumnsResult(DbDataReader r)
    {
        var columnType = r["TYPE_NAME"] as string;

        if (columnType == "text")
            return "varchar(max)";

        var lengthQualifier = "";

        if (HasPrecisionAndScale(columnType ?? throw new InvalidOperationException("Null type name returned")))
            lengthQualifier = $"({r["PRECISION"]},{r["SCALE"]})";
        else if (RequiresLength(columnType)) lengthQualifier = $"({AdjustForUnicodeAndNegativeOne(columnType, Convert.ToInt32(r["LENGTH"], CultureInfo.InvariantCulture))})";

        return columnType + lengthQualifier;
    }

    private static object AdjustForUnicodeAndNegativeOne(string columnType, int length)
    {
        if (length == -1)
            return "max";

        if (UnicodeRegex().IsMatch(columnType))
            return length / 2;

        return length;
    }

    private static IEnumerable<string> ListPrimaryKeys(IManagedConnection con, DiscoveredTable table)
    {
        const string query = """
                             SELECT i.name AS IndexName,
                             OBJECT_NAME(ic.OBJECT_ID) AS TableName,
                             COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName,
                             c.is_identity
                             FROM sys.indexes AS i
                             INNER JOIN sys.index_columns AS ic
                             INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                             ON i.OBJECT_ID = ic.OBJECT_ID
                             AND i.index_id = ic.index_id
                             WHERE (i.is_primary_key = 1) AND ic.OBJECT_ID = OBJECT_ID(@tableName)
                             ORDER BY OBJECT_NAME(ic.OBJECT_ID), ic.key_ordinal
                             """;

        using var cmd = table.GetCommand(query, con.Connection);
        var p = cmd.CreateParameter();
        p.ParameterName = "@tableName";
        p.Value = GetObjectName(table);
        cmd.Parameters.Add(p);

        cmd.Transaction = con.Transaction;
        using var r = cmd.ExecuteReader();
        while (r.Read())
            yield return (string)r["ColumnName"];

        r.Close();
    }

    [GeneratedRegex("n(varchar|char|text)", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant)]
    private static partial Regex UnicodeRegex();
}

1	using System;
2	using System.Collections.Generic;
3	using System.Data;
4	using System.Data.Common;
5	using System.Globalization;
6	using System.Linq;
7	using System.Text.RegularExpressions;
8	using FAnsi.Connections;
9	using FAnsi.Discovery;
10	using FAnsi.Discovery.Constraints;
11	using FAnsi.Exceptions;
12	using FAnsi.Naming;
13	using Microsoft.Data.SqlClient;
14
15	namespace FAnsi.Implementations.MicrosoftSQL;
16
17	public sealed partial class MicrosoftSQLTableHelper : DiscoveredTableHelper
18	{
19	public override IEnumerable<DiscoveredColumn> DiscoverColumns(DiscoveredTable discoveredTable, IManagedConnection connection, string database)
20	{
21	//don't bother looking for pks if it is a table valued function
22	var pks = discoveredTable is DiscoveredTableValuedFunction	382!
23	? null	382✔
24	: ListPrimaryKeys(connection, discoveredTable).ToHashSet();	382✔
25
26	using var cmd = discoveredTable.GetCommand(	382✔
27	$"use [{database}];\r\nSELECT \r\nsys.columns.name AS COLUMN_NAME,\r\n sys.types.name AS TYPE_NAME,\r\n sys.columns.collation_name AS COLLATION_NAME,\r\n sys.columns.max_length as LENGTH,\r\n sys.columns.scale as SCALE,\r\n sys.columns.is_identity,\r\n sys.columns.is_nullable,\r\n sys.columns.precision as PRECISION,\r\nsys.columns.collation_name\r\nfrom sys.columns \r\njoin \r\nsys.types on sys.columns.user_type_id = sys.types.user_type_id\r\nwhere object_id = OBJECT_ID(@tableName)", connection.Connection, connection.Transaction);	382✔
28	var p = cmd.CreateParameter();	382✔
29	p.ParameterName = "@tableName";	382✔
30	p.Value = GetObjectName(discoveredTable);	382✔
31	cmd.Parameters.Add(p);	382✔
32
33	using var r = cmd.ExecuteReader();	382✔
34	while (r.Read())	1,326✔
35	{
36	var isNullable = Convert.ToBoolean(r["is_nullable"], CultureInfo.InvariantCulture);	944✔
37
38	//if it is a table valued function prefix the column name with the table valued function name
39	var columnName = discoveredTable is DiscoveredTableValuedFunction	944!
40	? $"{discoveredTable.GetRuntimeName()}.{r["COLUMN_NAME"]}"	944✔
41	: r["COLUMN_NAME"].ToString()!;	944✔
42
43	var toAdd = new DiscoveredColumn(discoveredTable, columnName, isNullable)	944✔
44	{	944✔
45	IsAutoIncrement = Convert.ToBoolean(r["is_identity"], CultureInfo.InvariantCulture),	944✔
46	Collation = r["collation_name"] as string	944✔
47	};	944✔
48	toAdd.DataType = new DiscoveredDataType(r, GetSQLType_FromSpColumnsResult(r), toAdd);	944✔
49	toAdd.IsPrimaryKey = pks?.Contains(toAdd.GetRuntimeName()) ?? false;	944!
50	yield return toAdd;	944✔
51	}
52	}	382✔
53
54	/// <summary>
55	/// Returns the table name suitable for being passed into OBJECT_ID including schema if any
56	/// </summary>
57	/// <param name="table"></param>
58	/// <returns></returns>
59	private static string? GetObjectName(DiscoveredTable table)
60	{
61	var syntax = table.GetQuerySyntaxHelper();	764✔
62
63	var objectName = syntax.EnsureWrapped(table.GetRuntimeName());	764✔
64
65	return table.Schema != null ? $"{syntax.EnsureWrapped(table.Schema)}.{objectName}" : objectName;	764✔
66	}
67
68	public override IDiscoveredColumnHelper GetColumnHelper() => new MicrosoftSQLColumnHelper();	945✔
69
70	public override void DropTable(DbConnection connection, DiscoveredTable tableToDrop)
71	{
72	SqlCommand cmd;
73
74	switch (tableToDrop.TableType)	66!
75	{
76	case TableType.View:
77	if (connection.Database != tableToDrop.Database.GetRuntimeName())	×
78	connection.ChangeDatabase(tableToDrop.GetRuntimeName());	×
79
80	if (!connection.Database.ToLower(CultureInfo.InvariantCulture).Equals(tableToDrop.Database.GetRuntimeName().ToLower(CultureInfo.InvariantCulture), StringComparison.Ordinal))	×
81	throw new NotSupportedException(	×
82	$"Cannot drop view {tableToDrop} because it exists in database {tableToDrop.Database.GetRuntimeName()} while the current current database connection is pointed at database:{connection.Database} (use .ChangeDatabase on the connection first) - SQL Server does not support cross database view dropping");	×
83
84	cmd = new SqlCommand($"DROP VIEW {tableToDrop.GetWrappedName()}", (SqlConnection)connection);	×
85	break;	×
86	case TableType.Table:
87	cmd = new SqlCommand($"DROP TABLE {tableToDrop.GetFullyQualifiedName()}", (SqlConnection)connection);	66✔
88	break;	66✔
89	case TableType.TableValuedFunction:
90	DropFunction(connection, (DiscoveredTableValuedFunction)tableToDrop);	×
91	return;	×
92	default:
93	throw new ArgumentOutOfRangeException(nameof(tableToDrop), $"Unknown table type {tableToDrop.TableType}");	×
94	}
95
96	using (cmd)	66✔
97	cmd.ExecuteNonQuery();	66✔
98	}	66✔
99
100	public override void DropFunction(DbConnection connection, DiscoveredTableValuedFunction functionToDrop)
101	{
102	using var cmd = new SqlCommand($"DROP FUNCTION {functionToDrop.Schema ?? "dbo"}.{functionToDrop.GetRuntimeName()}", (SqlConnection)connection);	×
103	cmd.ExecuteNonQuery();	×
104	}	×
105
106	public override void DropColumn(DbConnection connection, DiscoveredColumn columnToDrop)
107	{
108	using var cmd = new SqlCommand(	×
109	$"ALTER TABLE {columnToDrop.Table.GetFullyQualifiedName()} DROP column {columnToDrop.GetWrappedName()}", (SqlConnection)connection);	×
110	cmd.ExecuteNonQuery();	×
111	}	×
112
113	/// <summary>
114	/// Checks if the table exists using the provided connection.
115	/// </summary>
116	/// <param name="table">The table to check</param>
117	/// <param name="connection">The managed connection to use</param>
118	/// <returns>True if the table exists, false otherwise</returns>
119	public override bool Exists(DiscoveredTable table, IManagedConnection connection)
120	{
121	if (!table.Database.Exists())	×
122	return false;	×
123
124	// Use sys.objects to check for table/view existence with a single targeted query
125	var objectType = table.TableType switch	×
126	{	×
127	TableType.Table => "'U'", // U = user table	×
128	TableType.View => "'V'", // V = view	×
129	_ => "'U', 'V'" // For TableValuedFunction or unknown, check both	×
130	};	×
131
132	var sql = $"""	×
133	SELECT CASE WHEN EXISTS (	×
134	SELECT 1 FROM sys.objects o	×
135	INNER JOIN sys.schemas s ON o.schema_id = s.schema_id	×
136	WHERE o.name = @tableName	×
137	AND s.name = @schemaName	×
138	AND o.type IN ({objectType})	×
139	) THEN 1 ELSE 0 END	×
140	""";	×
141
142	using var cmd = table.GetCommand(sql, connection.Connection, connection.Transaction);	×
143
144	var p = cmd.CreateParameter();	×
145	p.ParameterName = "@tableName";	×
146	p.Value = table.GetRuntimeName();	×
147	cmd.Parameters.Add(p);	×
148
149	var p2 = cmd.CreateParameter();	×
150	p2.ParameterName = "@schemaName";	×
151	p2.Value = table.Schema ?? "dbo";	×
152	cmd.Parameters.Add(p2);	×
153
154	var result = cmd.ExecuteScalar();	×
155	return Convert.ToInt32(result, CultureInfo.InvariantCulture) == 1;	×
156	}	×
157
158	[Obsolete("Prefer using Exists(DiscoveredTable, IManagedConnection) to reuse connections and improve performance")]
159	public override bool Exists(DiscoveredTable table, IManagedTransaction? transaction = null)
160	{
161	using var connection = table.Database.Server.GetManagedConnection(transaction);	×
162	return Exists(table, connection);	×
163	}	×
164
165	/// <summary>
166	/// Checks if the table has a primary key using the provided connection.
167	/// </summary>
168	/// <param name="table">The table to check</param>
169	/// <param name="connection">The managed connection to use</param>
170	/// <returns>True if the table has a primary key, false otherwise</returns>
171	public override bool HasPrimaryKey(DiscoveredTable table, IManagedConnection connection)
172	{
173	const string sql = """
174	SELECT CASE WHEN EXISTS (
175	SELECT 1 FROM sys.indexes i
176	INNER JOIN sys.objects o ON i.object_id = o.object_id
177	INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
178	WHERE i.is_primary_key = 1
179	AND o.name = @tableName
180	AND s.name = @schemaName
181	) THEN 1 ELSE 0 END
182	""";
183
184	using var cmd = table.GetCommand(sql, connection.Connection, connection.Transaction);	×
185
186	var p = cmd.CreateParameter();	×
187	p.ParameterName = "@tableName";	×
188	p.Value = table.GetRuntimeName();	×
189	cmd.Parameters.Add(p);	×
190
191	var p2 = cmd.CreateParameter();	×
192	p2.ParameterName = "@schemaName";	×
193	p2.Value = table.Schema ?? "dbo";	×
194	cmd.Parameters.Add(p2);	×
195
196	var result = cmd.ExecuteScalar();	×
197	return Convert.ToInt32(result, CultureInfo.InvariantCulture) == 1;	×
198	}	×
199
200	[Obsolete("Prefer using HasPrimaryKey(DiscoveredTable, IManagedConnection) to reuse connections and improve performance")]
201	public override bool HasPrimaryKey(DiscoveredTable table, IManagedTransaction? transaction = null)
202	{
203	using var connection = table.Database.Server.GetManagedConnection(transaction);	×
204	return HasPrimaryKey(table, connection);	×
205	}	×
206
207	/// <summary>
208	/// Gets the auto-increment column for the table using a database-specific SQL query (90-99% faster than discovering all columns).
209	/// </summary>
210	/// <returns>The auto-increment column, or null if none exists</returns>
211	public DiscoveredColumn? GetAutoIncrementColumn(DiscoveredTable table, IManagedConnection connection)
212	{
213	const string sql = """
214	SELECT c.name
215	FROM sys.identity_columns c
216	WHERE c.object_id = OBJECT_ID(@tableName)
217	""";
218
NEW 219	using var cmd = table.GetCommand(sql, connection.Connection);	×
NEW 220	var p = cmd.CreateParameter();	×
NEW 221	p.ParameterName = "@tableName";	×
NEW 222	p.Value = GetObjectName(table);	×
NEW 223	cmd.Parameters.Add(p);	×
NEW 224	cmd.Transaction = connection.Transaction;	×
225
NEW 226	var columnName = cmd.ExecuteScalar() as string;	×
227
NEW 228	if (columnName == null)	×
NEW 229	return null;	×
230
231	// DiscoverColumn will use the table's database connection
NEW 232	return table.DiscoverColumn(columnName);	×
NEW 233	}	×
234
235	public override IEnumerable<DiscoveredParameter> DiscoverTableValuedFunctionParameters(DbConnection connection,
236	DiscoveredTableValuedFunction discoveredTableValuedFunction, DbTransaction? transaction)
237	{
238	if (connection.State != ConnectionState.Open)	×
239	throw new ArgumentException($@"Connection state was {connection.State} but had to be Open", nameof(connection));	×
240
241	const string query = """
242	select
243	sys.parameters.name AS name,
244	sys.types.name AS TYPE_NAME,
245	sys.parameters.max_length AS LENGTH,
246	sys.types.collation_name AS COLLATION_NAME,
247	sys.parameters.scale AS SCALE,
248	sys.parameters.precision AS PRECISION
249	from
250	sys.parameters
251	join
252	sys.types on sys.parameters.user_type_id = sys.types.user_type_id
253	where object_id = OBJECT_ID(@tableName)
254	""";
255
256	using var cmd = discoveredTableValuedFunction.GetCommand(query, connection);	×
257	var p = cmd.CreateParameter();	×
258	p.ParameterName = "@tableName";	×
259	p.Value = GetObjectName(discoveredTableValuedFunction);	×
260	cmd.Parameters.Add(p);	×
261
262	cmd.Transaction = transaction;	×
263
264	using var r = cmd.ExecuteReader();	×
265	while (r.Read())	×
266	{
267	var name = r["name"].ToString();	×
268	if (name != null)	×
269	yield return new DiscoveredParameter(name)	×
270	{	×
271	DataType = new DiscoveredDataType(r, GetSQLType_FromSpColumnsResult(r), null)	×
272	};	×
273	}
274	}	×
275
276	public override IBulkCopy BeginBulkInsert(DiscoveredTable discoveredTable, IManagedConnection connection, CultureInfo culture) => new MicrosoftSQLBulkCopy(discoveredTable, connection, culture);	32✔
277
278	public override void CreatePrimaryKey(DatabaseOperationArgs args, DiscoveredTable table, DiscoveredColumn[] discoverColumns)
279	{
280	try
281	{
282	using var connection = args.GetManagedConnection(table);	1✔
283	var columnHelper = GetColumnHelper();	1✔
284	foreach (var alterSql in discoverColumns.Where(static dc => dc.AllowNulls).Select(col => columnHelper.GetAlterColumnToSql(col, col.DataType!.SQLType!, false)))	3!
285	{
286	using var alterCmd = table.GetCommand(alterSql, connection.Connection, connection.Transaction);	×
287	args.ExecuteNonQuery(alterCmd);	×
288	}
289	}	1✔
290	catch (DbException e)	×
291	{
292	throw new AlterFailedException(string.Format(CultureInfo.InvariantCulture, FAnsiStrings.DiscoveredTableHelper_CreatePrimaryKey_Failed_to_create_primary_key_on_table__0__using_columns___1__, table, string.Join(",", discoverColumns.Select(static c => c.GetRuntimeName()))), e);	×
293	}
294
295	base.CreatePrimaryKey(args, table, discoverColumns);	1✔
296	}	1✔
297
298	public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction? transaction = null)
299	{
300	var toReturn = new Dictionary<string, DiscoveredRelationship>();	28✔
301
302	const string sql = "exec sp_fkeys @pktable_name = @table, @pktable_qualifier=@database, @pktable_owner=@schema";
303
304	using (var cmd = table.GetCommand(sql, connection))	28✔
305	{
306	if (transaction != null)	28!
307	cmd.Transaction = transaction.Transaction;	×
308
309	var p = cmd.CreateParameter();	28✔
310	p.ParameterName = "@table";	28✔
311	p.Value = table.GetRuntimeName();	28✔
312	p.DbType = DbType.String;	28✔
313	cmd.Parameters.Add(p);	28✔
314
315	p = cmd.CreateParameter();	28✔
316	p.ParameterName = "@schema";	28✔
317	p.Value = table.Schema ?? "dbo";	28!
318	p.DbType = DbType.String;	28✔
319	cmd.Parameters.Add(p);	28✔
320
321	p = cmd.CreateParameter();	28✔
322	p.ParameterName = "@database";	28✔
323	p.Value = table.Database.GetRuntimeName();	28✔
324	p.DbType = DbType.String;	28✔
325	cmd.Parameters.Add(p);	28✔
326
327	using var dt = new DataTable();	28✔
328	var da = table.Database.Server.GetDataAdapter(cmd);	28✔
329	da.Fill(dt);	28✔
330
331	foreach (DataRow r in dt.Rows)	64✔
332	{
333	var fkName = r["FK_NAME"].ToString() ?? throw new InvalidOperationException("Null foreign key name returned");	4!
334
335	//could be a 2+ columns foreign key?
336	if (!toReturn.TryGetValue(fkName, out var current))	4✔
337	{
338	var pkdb = r["PKTABLE_QUALIFIER"].ToString() ?? throw new InvalidOperationException("Null primary key database name returned");	2!
339	var pkschema = r["PKTABLE_OWNER"].ToString();	2✔
340	var pktableName = r["PKTABLE_NAME"].ToString() ?? throw new InvalidOperationException("Null primary key table name returned");	2!
341
342	var pktable = table.Database.Server.ExpectDatabase(pkdb).ExpectTable(pktableName, pkschema);	2✔
343
344	var fkdb = r["FKTABLE_QUALIFIER"].ToString() ?? throw new InvalidOperationException("Null foreign key database name returned");	2!
345	var fkschema = r["FKTABLE_OWNER"].ToString();	2✔
346	var fktableName = r["FKTABLE_NAME"].ToString() ?? throw new InvalidOperationException("Null foreign key name returned");	2!
347
348	var fktable = table.Database.Server.ExpectDatabase(fkdb).ExpectTable(fktableName, fkschema);	2✔
349
350	var deleteRuleInt = Convert.ToInt32(r["DELETE_RULE"], CultureInfo.InvariantCulture);	2✔
351
352	var deleteRule = deleteRuleInt switch	2!
353	{	2✔
354	0 => CascadeRule.Delete,	1✔
355	1 => CascadeRule.NoAction,	1✔
356	2 => CascadeRule.SetNull,	×
357	3 => CascadeRule.SetDefault,	×
358	_ => CascadeRule.Unknown	×
359	};	2✔
360
361	/*
362	https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql?view=sql-server-2017
363
364	0=CASCADE changes to foreign key.
365	1=NO ACTION changes if foreign key is present.
366	2 = set null
367	3 = set default*/
368
369	current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);	2✔
370	toReturn.Add(current.Name, current);	2✔
371	}
372
373	current.AddKeys(r["PKCOLUMN_NAME"].ToString()!, r["FKCOLUMN_NAME"].ToString()!, transaction);	4✔
374	}
375	}
376
377	return [.. toReturn.Values];	28✔
378
379	}
380
381	protected override string GetRenameTableSql(DiscoveredTable discoveredTable, string newName)
382	{
383	var oldName = discoveredTable.GetWrappedName();	×
384
385	var syntax = discoveredTable.GetQuerySyntaxHelper();	×
386
387	if (!string.IsNullOrWhiteSpace(discoveredTable.Schema))	×
388	oldName = $"{syntax.EnsureWrapped(discoveredTable.Schema)}.{oldName}";	×
389
390	return $"exec sp_rename '{syntax.Escape(oldName)}', '{syntax.Escape(newName)}'";	×
391	}
392
393	public override void MakeDistinct(DatabaseOperationArgs args, DiscoveredTable discoveredTable)
394	{
395	var syntax = discoveredTable.GetQuerySyntaxHelper();	5✔
396
397	const string sql = """
398	DELETE f
399	FROM (
400	SELECT ROW_NUMBER() OVER (PARTITION BY {0} ORDER BY {0}) AS RowNum
401	FROM {1}
402
403	) as f
404	where RowNum > 1
405	""";
406
407	var columnList = string.Join(",",	5✔
408	discoveredTable.DiscoverColumns().Select(c => syntax.EnsureWrapped(c.GetRuntimeName())));	20✔
409
410	var sqlToExecute = string.Format(CultureInfo.InvariantCulture, sql, columnList, discoveredTable.GetFullyQualifiedName());	5✔
411
412	var server = discoveredTable.Database.Server;	5✔
413
414	using var con = args.GetManagedConnection(server);	5✔
415	using var cmd = server.GetCommand(sqlToExecute, con);	5✔
416	args.ExecuteNonQuery(cmd);	5✔
417	}	10✔
418
419
420	public override string GetTopXSqlForTable(IHasFullyQualifiedNameToo table, int topX) => $"SELECT TOP {topX} * FROM {table.GetFullyQualifiedName()}";	21✔
421
422	private string GetSQLType_FromSpColumnsResult(DbDataReader r)
423	{
424	var columnType = r["TYPE_NAME"] as string;	944✔
425
426	if (columnType == "text")	944✔
427	return "varchar(max)";	1✔
428
429	var lengthQualifier = "";	943✔
430
431	if (HasPrecisionAndScale(columnType ?? throw new InvalidOperationException("Null type name returned")))	943!
432	lengthQualifier = $"({r["PRECISION"]},{r["SCALE"]})";	11✔
433	else if (RequiresLength(columnType)) lengthQualifier = $"({AdjustForUnicodeAndNegativeOne(columnType, Convert.ToInt32(r["LENGTH"], CultureInfo.InvariantCulture))})";	1,267✔
434
435	return columnType + lengthQualifier;	943✔
436	}
437
438	private static object AdjustForUnicodeAndNegativeOne(string columnType, int length)
439	{
440	if (length == -1)	335✔
441	return "max";	255✔
442
443	if (UnicodeRegex().IsMatch(columnType))	80✔
444	return length / 2;	2✔
445
446	return length;	78✔
447	}
448
449	private static IEnumerable<string> ListPrimaryKeys(IManagedConnection con, DiscoveredTable table)
450	{
451	const string query = """
452	SELECT i.name AS IndexName,
453	OBJECT_NAME(ic.OBJECT_ID) AS TableName,
454	COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName,
455	c.is_identity
456	FROM sys.indexes AS i
457	INNER JOIN sys.index_columns AS ic
458	INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
459	ON i.OBJECT_ID = ic.OBJECT_ID
460	AND i.index_id = ic.index_id
461	WHERE (i.is_primary_key = 1) AND ic.OBJECT_ID = OBJECT_ID(@tableName)
462	ORDER BY OBJECT_NAME(ic.OBJECT_ID), ic.key_ordinal
463	""";
464
465	using var cmd = table.GetCommand(query, con.Connection);	382✔
466	var p = cmd.CreateParameter();	382✔
467	p.ParameterName = "@tableName";	382✔
468	p.Value = GetObjectName(table);	382✔
469	cmd.Parameters.Add(p);	382✔
470
471	cmd.Transaction = con.Transaction;	382✔
472	using var r = cmd.ExecuteReader();	382✔
473	while (r.Read())	432✔
474	yield return (string)r["ColumnName"];	50✔
475
476	r.Close();	382✔
477	}	382✔
478
479	[GeneratedRegex("n(varchar\|char\|text)", RegexOptions.IgnoreCase \| RegexOptions.CultureInvariant)]
480	private static partial Regex UnicodeRegex();
481	}

jas88 / FAnsiSql / 431

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