使用 C# 获取 SQL-Server 存储过程方法（附源代码下载）

概述：需要Microsoft Visual Studio 2022NET 8 框架(SSMS中）SQL Server 管理 Studio当开发人员是与 SQL-Server 数据库存储过程交互的项目的唯一开发人员时，开发人员知道所有存储过程，但是当从另一个开发人员接管项目时，检查数据库中的所有存储过程可能会很乏味，甚至在开发人员具有适当权限的情况下，了解服务器上的所有存储过程可能会很乏味。无需打开（SSMS） SQL Server Management Studio，而是了解如何使用以下 Windows 窗体实用工具项目以及一个控制台项目按数据库查看整个服务器的存储过程，该项目演示如何在没有用户界

环境

Microsoft Visual Studio 2022
NET 8 框架
(SSMS中）SQL Server 管理 Studio

当开发人员是与 SQL-Server 数据库存储过程交互的项目的唯一开发人员时，开发人员知道所有存储过程，但是当从另一个开发人员接管项目时，检查数据库中的所有存储过程可能会很乏味，甚至在开发人员具有适当权限的情况下，了解服务器上的所有存储过程可能会很乏味。

无需打开（SSMS） SQL Server Management Studio，而是了解如何使用以下 Windows 窗体实用工具项目以及一个控制台项目按数据库查看整个服务器的存储过程，该项目演示如何在没有用户界面的情况下获取这些存储过程。

若要使此成为有用的实用工具项目，需要将存储过程定义保存到具有 .sql 扩展名的文件中，以便在 SSMS、VS-Code 或 Visual Studio 打开时使用语法着色，通常默认程序为 SSMS。

这些文件存储在应用程序文件夹\Scripts下，然后存储在包含今天日期的文件夹下。

用法

控制台和 Windows 窗体项目都有一个 appsettings.json 文件，如下所示。

将 Server\Name 更改为要从中检索存储过程的服务器。

{
  "ConnectionsConfiguration": {
    "ActiveEnvironment": "Development",
    "Development": "Data Source=.\\SQLEXPRESS;Initial Catalog=NorthWind2024;Integrated Security=True;Encrypt=False"
  },
  "Server": {
    "Name": ".\\SQLEXPRESS"
  }
}

代码

基本代码驻留在控制台和 Windows 窗体项目共享的两个类项目中，即 CommonLibrary 和 SqlServerLibrary，它们也可以在开发人员的项目中使用。请注意，SqlServerLibrary 类项目中有一些未使用的代码，读者可能会发现这些代码对其他任务很有用。

ConnectionReader 类

提供对来自任一前端项目appsettings.json服务器名称的读取器的访问

internal class ConnectionReader
{
    public static string Get(string dbName)
    {
        var _configuration = Configuration.JsonRoot();
        SqlConnectionStringBuilder builder = new()
        {
            DataSource = _configuration.GetValue<string>("Server:Name"),
            InitialCatalog = dbName,
            IntegratedSecurity = true,
            Encrypt = SqlConnectionEncryptOption.Optional
        };
        return builder.ConnectionString;
    }
}

DatabaseService 类

使用 Dapper 从 appsettings.json 中设置的 SQL-Server 实例中获取数据库名称

public class DatabaseService
{
    private readonly IDbConnection _cn = new SqlConnection(ConnectionString());
    public async Task<List<string>> DatabaseNames() 
        => ( await _cn.QueryAsync<string>(SqlStatements.GetDatabaseNames)).AsList();
    /// <summary>
    /// Get names of databases on selected server excluding system databases
    /// </summary>
    /// <returns></returns>
    public async Task<List<string>> DatabaseNamesFiltered() =>
        (await _cn.QueryAsync<string>(
            """
            SELECT name
            FROM sys.databases
            WHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb' )
            """))
        .AsList();
}

SqlStatements 类

包含设置为只读字符串的 SQL 语句，这些语句在两个前端项目中使用。

public class SqlStatements
{
    /// <summary>
    /// Provides column names from the description property for each column in a specified table name
    /// </summary>
    public static string DescriptionStatement =>
        """
        SELECT      col.COLUMN_NAME AS ColumnName,
                    col.ORDINAL_POSITION AS Position,
                    ISNULL(prop.value,'(none)') AS [Description]
          FROM      INFORMATION_SCHEMA.TABLES AS tbl
         INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col
            ON col.TABLE_NAME = tbl.TABLE_NAME
         INNER JOIN sys.columns AS sc
            ON sc.object_id   = OBJECT_ID(tbl.TABLE_SCHEMA + '.' + tbl.TABLE_NAME)
           AND sc.name        = col.COLUMN_NAME
          LEFT JOIN sys.extended_properties prop
            ON prop.major_id  = sc.object_id
           AND prop.minor_id  = sc.column_id
           AND prop.name      = 'MS_Description'
         WHERE      tbl.TABLE_NAME = @TableName
         ORDER BY col.ORDINAL_POSITION;
        """;
    /// <summary>
    /// Get default values for each column for tables using a connection object
    /// </summary>
    public static string GetDefaultValuesInDatabase =>
        """
        SELECT SO.[name] AS "TableName",
               SC.[name] AS "ColumnName",
               SM.[text] AS "DefaultValue"
        FROM dbo.sysobjects SO
            INNER JOIN dbo.syscolumns SC
                ON SO.id = SC.id
            LEFT JOIN dbo.syscomments SM
                ON SC.cdefault = SM.id
        WHERE SO.xtype = 'U'
              AND SO.[name] <> 'sysdiagrams'
              AND SM.[text] IS NOT NULL
        ORDER BY SO.[name],
                 SC.colid;
        """;
    /// <summary>
    /// Get all database names from master
    /// </summary>
    public static string GetDatabaseNames =>
        """
        SELECT TableName = DB_NAME(s_mf.database_id)
        FROM sys.master_files s_mf
        WHERE s_mf.state = 0 -- ONLINE
              AND HAS_DBACCESS(DB_NAME(s_mf.database_id)) = 1
              AND DB_NAME(s_mf.database_id) NOT IN ( 'master', 'tempdb', 'model', 'msdb' )
              AND DB_NAME(s_mf.database_id)NOT LIKE 'ReportServer%'
        GROUP BY s_mf.database_id
        ORDER BY 1;
        """;
    /// <summary>
    /// Get details for a table which requires a table name in the calling code
    /// </summary>
    public static string TableDetails =>
        """
        SELECT c.[name] 'ColumnName',
               t.[name] 'DataType',
               c.[max_length] 'MaxLength',
               c.[precision] 'Precision',
               c.scale 'Scale',
               c.is_nullable 'IsNullable',
               ISNULL(i.is_primary_key, 0) 'PrimaryKey'
        FROM sys.columns c
            INNER JOIN sys.types t
                ON c.user_type_id = t.user_type_id
            LEFT OUTER JOIN sys.index_columns ic
                ON ic.object_id = c.object_id
                   AND ic.column_id = c.column_id
            LEFT OUTER JOIN sys.indexes i
                ON ic.object_id = i.object_id
                   AND ic.index_id = i.index_id
        WHERE c.object_id = OBJECT_ID(@TableName);
        """;
    /// <summary>
    /// Get all computed columns in a database using a valid connection object
    /// </summary>
    public static string ComputedColumnDefinitions =>
        """
        SELECT SCHEMA_NAME(o.schema_id) 'SchemaName',
               c.name AS 'ColumnName',
               OBJECT_NAME(c.object_id) AS 'TableName',
               TYPE_NAME(c.user_type_id) AS 'DataType',
               c.definition 'Definition'
        FROM sys.computed_columns c
            JOIN sys.objects o
                ON o.object_id = c.object_id
        ORDER BY SchemaName,
                 TableName,
                 c.column_id;
        """;
    /// <summary>
    /// Determine if table exists in a data using a table name as a parameter
    /// </summary>
    public static string TableExists =>
        """
        SELECT CASE
            WHEN EXISTS
                 (
                     (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName)
                 ) THEN
                1
            ELSE
                0
        END;
        """;
    /// <summary>
    /// Get names of user stored procedures that start with usp or usp_
    /// </summary>
    public static string GetUserStoredProcedureNames =>
        """
        SELECT [name]
        FROM sys.procedures
        WHERE [name] LIKE 'usp%' OR [name] LIKE 'usp_%';
        """;
    public static string WhereInCustomers =>
        """
        SELECT C.CustomerIdentifier,
               C.CompanyName,
               C.Street,
               C.City,
               C.CountryIdentifier,
               CO.Name AS CountryName,
               C.Phone,
               C.ContactId,
               CT.FirstName,
               CT.LastName
        FROM dbo.Customers AS C
            INNER JOIN dbo.Contacts AS CT
                ON C.ContactId = CT.ContactId
            INNER JOIN dbo.Countries AS CO
                ON C.CountryIdentifier = CO.CountryIdentifier
        WHERE C.CustomerIdentifier IN ({0})
        ORDER BY C.CompanyName
        """;
    /// <summary>
    /// Get all date time columns for tables in database
    /// </summary>
    public static string GetAllDateTimeColumnsInDatabase =>
        """
        SELECT SCHEMA_NAME(t.schema_id) + '.' + t.name AS [TableName],
               c.column_id "ColumnId",
               c.name AS "ColumnName",
               TYPE_NAME(c.user_type_id) AS DataType,
               c.scale AS "Scale"
        FROM sys.columns c
            JOIN sys.tables t
                ON t.object_id = c.object_id
        WHERE TYPE_NAME(c.user_type_id) IN ( 'date', 'datetimeoffset', 'datetime2', 'smalldatetime', 'datetime', 'time' )
        ORDER BY [TableName],
                 c.column_id;
        """;
    /// <summary>
    /// Get details for database tables
    /// Table name, constraint name, primary key column name, foreign table, foreign key column update rule delete rule 
    /// </summary>
    public static string TableConstraintsForDatabase =>
        """
        SELECT
            PrimaryKeyTable = QUOTENAME(PK.CONSTRAINT_SCHEMA) + '.' + QUOTENAME(PK.TABLE_NAME),
            ConstraintName = C.CONSTRAINT_NAME,
            PrimaryKeyColumn = CCU.COLUMN_NAME,
            ForeignKeyTable = QUOTENAME(FK.CONSTRAINT_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME),
            ForeignKeyColumn = CU.COLUMN_NAME,
            UpdateRule = C.UPDATE_RULE,
            DeleteRule = C.DELETE_RULE
        FROM
            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
                C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND
                C.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG AND
                C.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
                C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME AND
                C.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG AND
                C.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON
                C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME AND
                C.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG AND
                C.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
            INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON
                PK.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME AND
                PK.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG AND
                PK.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
        WHERE
            FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
        ORDER BY
            PK.TABLE_NAME,
            FK.TABLE_NAME
        """;
    /// <summary>
    /// Select for Customers which requires parameters for CustomerIdentifier, PhoneType and ContactType
    /// </summary>
    public static string GetCustomers =>
        """
        SELECT Cust.CustomerIdentifier,
               Cust.CompanyName,
               Cust.City,
               Cust.PostalCode,
               C.ContactId,
               CO.CountryIdentifier,
               CO.Name AS Country,
               Cust.Phone,
               Devices.PhoneTypeIdentifier,
               Devices.PhoneNumber,
               Cust.ContactTypeIdentifier,
               C.FirstName,
               C.LastName,
               CT.ContactTitle
        FROM dbo.Customers AS Cust
            INNER JOIN dbo.ContactType AS CT
                ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
            INNER JOIN dbo.Countries AS CO
                ON Cust.CountryIdentifier = CO.CountryIdentifier
            INNER JOIN dbo.Contacts AS C
                ON Cust.ContactId = C.ContactId
            INNER JOIN dbo.ContactDevices AS Devices
                ON C.ContactId = Devices.ContactId
        WHERE (
                  Cust.CustomerIdentifier = @CustomerIdentifier
                  AND Devices.PhoneTypeIdentifier = @PhoneType
                  AND Cust.ContactTypeIdentifier = @ContactType
              );
        """;
    public static string TableNamesForDatabase(string databaseName) => 
        $"""
         SELECT TABLE_NAME AS TableName
          FROM [{databaseName}].INFORMATION_SCHEMA.TABLES
         WHERE TABLE_TYPE = 'BASE TABLE'
           AND TABLE_NAME <> 'sysdiagrams'
         ORDER BY TABLE_NAME;
         """;
    public static string DatabaseTablesRowCount => 
        """
        SELECT     QUOTENAME(SCHEMA_NAME(item.schema_id)) + '.' + QUOTENAME(item.name) AS [Name],
                   SUM(parts.[rows]) AS [RowCount]
         FROM      sys.objects AS item
        INNER JOIN sys.partitions AS parts
           ON item.object_id = parts.object_id
        WHERE      item.[type]     = 'U'
          AND      item.is_ms_shipped = 0x0
          AND      parts.index_id      < 2 -- 0:Heap, 1:Clustered
          AND  item.[name] <> 'sysdiagrams'
        GROUP BY item.schema_id,
                 item.[name]
        ORDER BY [Name];
        """;
    public static string DatabaseTablesRowCount1 =>
        """
        SELECT     TableSchema = s.name,
                   Name = t.name,
                   [RowCount] = p.rows
         FROM      sys.tables t
        INNER JOIN sys.schemas s
           ON t.schema_id = s.schema_id
        INNER JOIN sys.indexes i
           ON t.object_id = i.object_id
        INNER JOIN sys.partitions p
           ON i.object_id = p.object_id
          AND i.index_id  = p.index_id
        WHERE      t.is_ms_shipped = 0
        GROUP BY t.name,
                 s.name,
                 p.rows
        ORDER BY s.name,
                 t.name;
        """;
}

ListDictionary 类

此类用于存储数据库名称、表名和用户存储过程。请注意，.NET Framework 还有一个 ListDictionary 类，因此在其他项目中使用时要小心引用这个类，而不是 .NET Framework 版本。

public class ListDictionary
{
    private Dictionary<string, List<string>> _internalDictionary = new();
    public Dictionary<string, List<string>> Dictionary 
        => _internalDictionary;
    public bool HasItems => _internalDictionary.Count > 0;
    public void Add(string key, string value)
    {
        if (_internalDictionary.TryGetValue(key, out var item))
        {
            if (item.Contains(value) == false)
            {
                item.Add(value);
            }
        }
        else
        {
            List<string> list = [value];
            _internalDictionary.Add(key, list);
        }
    }
}

核心模型

这些模型用于存储每个数据库用户存储过程的信息

public class DatabaseContainer
{
    public string Database { get; set; }
    public List<ProcedureContainer> List { get; set; } = new();
    public override string ToString() => Database;
}
public class ProcedureContainer
{
    public string Procedure { get; set; }
    public string Definition { get; set; }
    public override string ToString() => Procedure;
}

代码循环遍历数据库

下面的代码使用上面显示的模型来存储存储过程。此代码为在其他项目中使用奠定了基础。

public static async Task<List<DatabaseContainer>> GetStoredProcedureDetails()
{
    ListDictionary listDictionary = new();
    StoredProcedureHelpers helpers = new();
    List<DatabaseContainer> databaseContainers = new();
    var service = new DatabaseService();
    List<string> dbNames = await service.DatabaseNamesFiltered();
    dbNames = dbNames.OrderBy(x => x).ToList();
    foreach (var dbName in dbNames)
    {
        var (hasStoredProcedures, list) = await helpers
            .GetStoredProcedureNameSafe(dbName, "'xp_', 'ms_'");
        if (hasStoredProcedures)
        {
            var root = databaseContainers.FirstOrDefault(x 
                => x.Database == dbName);
            DatabaseContainer container = new DatabaseContainer { Database = dbName };
            if (root is null)
            {
                container = new DatabaseContainer { Database = dbName };
            }
            foreach (var item in list)
            {
                var definition = await helpers.GetStoredProcedureDefinitionAsync(dbName, item);
                if (definition is not null && !item.Contains("diagram"))
                {
                    listDictionary.Add(dbName, item);
                    container.List.Add(new ProcedureContainer
                    {
                        Procedure = item, 
                        Definition = definition
                    });
                }
            }
            databaseContainers.Add(container);
        }
    }
    return databaseContainers.Where(x => x.List.Count > 0).ToList();
}

Windows 窗体项目

除了如何保存用户存储过程（如果需要）之外，这里实际上没有什么可更改的。

在对存储的用户存储过程执行生成后，在项目文件中，以下内容会在应用程序文件夹下创建一个 Scripts 文件夹。

<Target Name="MakeScriptsDir" AfterTargets="Build">
   <MakeDir Directories="$(OutDir)Scripts\$([System.DateTime]::Now.ToString(yyyy-MM-dd))" />
</Target>

注意
：更改上述内容需要更改 Form SaveButton 中的代码，该代码需要存在上述文件夹。

特别说明

每当开发人员以窗体编写代码时，请尽可能避免引用控件。在提供的代码中，BindingList 用于限制触摸控件。一个例外是代码将 RichTextBox 传递给类，在这种情况下，这样做是为了限制代码的形式，我的规则不超过 120 行代码。
代码重用，这是为表单中的事件完成的。总是想我怎么能不重复窗体或类中的代码。

保存存储过程

选择数据库后，单击保存按钮。

当前变量包含接受存储过程定义所需的一切。List 属性具有存储过程名称。
接下来的两个变量用于获取 SQL-Server 实例 nam
文件夹变量指向上述项目文件中定义的文件夹。
foreach 迭代存储过程名称将读回每个存储过程定义，并将每个定义写入文件。

private void SaveButton_Click(object sender, EventArgs e)
{
    var current = _bindingList[DatabaseComboBox.SelectedIndex];
    var _configuration = Configuration.JsonRoot();
    var serverName = _configuration.GetValue<string>("Server:Name").CleanFileName();
    var folder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Scripts",
        $"{Now.Year}-{Now.Month:d2}-{Now.Day:d2}" );
    foreach (var item in current.List)
    {
        File.WriteAllText(Path.Combine(folder, $"{serverName}_{current.Name}_{item}.sql"), 
            _helpers.GetStoredProcedureDefinition(DatabaseComboBox.Text, item));
    }
}

总结

所介绍的内容提供了一种简单的方法来发现和保存 SQL-Server 实例的用户存储过程。除了是一个有用的工具之外，开发人员还可以学习新技术和 SQL 用法。

为什么要使用存储过程

存储过程在 C# 中可能很有用，因为它们可以提高应用程序性能，使代码更易于维护，并将复杂的逻辑封装在数据库中。它们还可用于执行各种任务，包括检索、更新和删除数据。

存储过程不容易受到 SQL 注入攻击，并且在多个应用程序访问数据库时比动态 SQL 更安全。

源代码获取：公众号回复消息【code：84588】

本文PDF版本下载