如何通过C#将SQL Server数据库导入Visual Studio空数据库项目

Got it, let's walk through exactly how to replicate Visual Studio's manual "Import Database" workflow programmatically in your C# VS extension. This approach uses the same underlying APIs that VS itself relies on, so you'll get identical script output and folder structure to the manual process:

1. 必备依赖组件 #

First, you'll need to reference these Visual Studio SDK and SQL Server packages via NuGet—make sure versions are compatible with VS2017:

• Microsoft.VisualStudio.Data.Tools.Design: Handles database project design-time operations
• Microsoft.SqlServer.DacFx (v14.x for VS2017): The core framework for extracting database object scripts
• Microsoft.VisualStudio.Shell.15.0: Base VS extension utilities

2. 核心步骤实现 #

步骤1：定位目标空数据库项目 #

First, grab the reference to the empty database project you created. You can use the VS DTE object to scan the solution:

using EnvDTE;
using Microsoft.VisualStudio.Shell;

// Get the DTE instance (VS's automation object)
var dte = Package.GetGlobalService(typeof(DTE)) as DTE;
Project targetDbProject = null;

// Loop through solution projects to find the database project
foreach (Project proj in dte.Solution.Projects)
{
    // Database projects have a specific GUID identifier
    if (proj.Kind == "{A9ACE9BB-CECE-4e62-9AA4-C7E7C5BD2124}")
    {
        targetDbProject = proj;
        break;
    }
}

if (targetDbProject == null)
{
    throw new InvalidOperationException("Couldn't locate the target database project.");
}

步骤2：用DacFx提取数据库对象 #

DacFx is the same tool VS uses under the hood to generate scripts. It will extract all database objects (tables, views, stored procs, etc.) with the same detail as the manual import:

using Microsoft.SqlServer.Dac;
using Microsoft.SqlServer.Dac.Model;

// Your SQL Server connection string
string connectionString = "Data Source=YourServerName;Initial Catalog=YourDatabase;Integrated Security=True;";
var dacServices = new DacServices(connectionString);

// Extract the full database model into a temporary DACPAC
var databaseModel = dacServices.Extract(
    Path.GetTempFileName() + ".dacpac", // Temp file path (can delete later)
    "YourDatabase",
    "YourDatabase",
    new Version(1, 0, 0, 0),
    null,
    new ExtractOptions
    {
        ExtractUsageProperties = true,
        ExtractPermissions = true,
        ExtractApplicationScopedObjectsOnly = false, // Include server-level objects if needed
        IgnoreExtendedProperties = false,
        IgnorePermissions = false
    });

步骤3：同步模型到数据库项目 #

Now convert the extracted model into individual .sql files, organized into the same folder structure as VS's manual import:

using Microsoft.VisualStudio.Data.Tools.Design.SchemaModel.Sql;

// Get the schema model for the target project
var schemaModelStore = SqlSchemaModelStore.Instance;
var projectSchemaModel = schemaModelStore.GetSchemaModel(targetDbProject.FullName);

// Loop through every object in the extracted database model
foreach (var modelElement in databaseModel.GetObjects(DacQueryScopes.All))
{
    // Generate the object's T-SQL script
    string script = modelElement.Script();
    
    // Define folder and file name (matches VS's default structure)
    string objectFolder = GetObjectTypeFolder(modelElement.ObjectType);
    string fileName = $"{modelElement.Name.Parts[0]}.sql";
    string fullFilePath = Path.Combine(targetDbProject.FullName, objectFolder, fileName);
    
    // Create folder if it doesn't exist
    Directory.CreateDirectory(Path.GetDirectoryName(fullFilePath));
    
    // Write the script to file
    File.WriteAllText(fullFilePath, script);
    
    // Add the file to the VS project
    targetDbProject.ProjectItems.AddFromFile(fullFilePath);
}

// Helper to map object types to VS's default folder names
private string GetObjectTypeFolder(ObjectType objectType)
{
    return objectType.Name switch
    {
        "Table" => "Tables",
        "View" => "Views",
        "StoredProcedure" => "Stored Procedures",
        "Function" => "Functions",
        "Trigger" => "Triggers",
        "User" => "Security\\Users",
        // Add more mappings as needed for other object types
        _ => "Other Objects"
    };
}

步骤4：刷新项目并整理结构 #

Finally, refresh the project to make VS recognize the new files, and run VS's built-in command to match the folder structure exactly like manual import:

// Refresh the project to load new files
targetDbProject.Refresh();

// Run VS's command to organize files into folders (matches manual import behavior)
dte.ExecuteCommand("Project.RenameFilesMatchingFolderStructure");

3. Key Notes #

• Permissions: Ensure your extension has access to the SQL Server database and write permissions to the project directory.
• Version Compatibility: Stick to DacFx v14.x for VS2017—newer versions may cause compatibility issues.
• Error Handling: Add try/catch blocks to handle connection failures, file write errors, and missing objects.
• Filtering: If you want to exclude system objects or specific object types, add checks in the loop over modelElement or adjust ExtractOptions.

内容的提问来源于stack exchange，提问作者Akshara