开发SQL Server Management Studio克隆版Windows软件：如何实现SQL Server连接与查询功能？

Hey there! Let's tackle building those core features for your SSMS clone—connecting to SQL Server, listing databases, and running queries. I’ll walk you through practical, code-focused steps using C# (the standard for Windows desktop apps) with .NET’s built-in data libraries.

1. Connecting to the SQL Server Database Engine #

The foundation of your app is establishing a reliable connection to SQL Server. You’ll use the SqlConnection class (go with Microsoft.Data.SqlClient for modern .NET/.NET Core apps, or System.Data.SqlClient if you’re targeting .NET Framework).

First, you need a valid connection string. Here are the two most common scenarios:

• Windows Authentication (Trusted Connection): Uses your Windows credentials to log in

  string connectionString = @"Server=YOUR_SERVER_NAME;Integrated Security=True;";
  

• SQL Server Authentication: Uses a SQL-specific username and password

  string connectionString = @"Server=YOUR_SERVER_NAME;User Id=YOUR_USERNAME;Password=YOUR_PASSWORD;";
  

Here’s a reusable method to test and establish a connection:

using Microsoft.Data.SqlClient; // Or System.Data.SqlClient for .NET Framework

public bool EstablishConnection(string connectionString)
{
    try
    {
        // Using statement ensures the connection is properly disposed after use
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            return true; // Connection succeeded
        }
    }
    catch (Exception ex)
    {
        // Handle errors gracefully (e.g., show a user-friendly message dialog)
        MessageBox.Show($"Connection failed: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        return false;
    }
}

2. Fetching and Displaying the Database List #

Once connected, you can query SQL Server’s system catalog to get a list of databases. The sys.databases view contains all databases on the server—you can filter out system databases (master, tempdb, etc.) to match SSMS’s default behavior.

Here’s how to retrieve the list:

public List<string> GetDatabaseList(string connectionString)
{
    List<string> databaseNames = new List<string>();
    // Query to get non-system databases; remove the WHERE clause to include all
    string query = @"SELECT name FROM sys.databases 
                     WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(query, connection);
        connection.Open();

        // Read results from the query
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                databaseNames.Add(reader["name"].ToString());
            }
        }
    }
    return databaseNames;
}

To display this in your UI (e.g., a TreeView like SSMS’s Object Explorer or a ListBox):

// WinForms example: Populate a TreeView node with databases
List<string> dbs = GetDatabaseList(connectionString);
foreach (string db in dbs)
{
    treeViewObjectExplorer.Nodes["Databases"].Nodes.Add(db);
}

3. Running Queries on a Selected Database #

To run a query against a specific database, you’ll need to update your connection string to target that database (using Initial Catalog), then execute the query with SqlCommand.

For SELECT Queries (Returning Results) #

Use a SqlDataAdapter to fill a DataTable, which you can bind directly to a UI control like a DataGridView:

public DataTable RunSelectQuery(string connectionString, string selectedDatabase, string userQuery)
{
    // Modify connection string to target the selected database
    SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder(connectionString);
    connBuilder.InitialCatalog = selectedDatabase;
    string dbSpecificConnString = connBuilder.ToString();

    DataTable resultsTable = new DataTable();
    using (SqlConnection connection = new SqlConnection(dbSpecificConnString))
    {
        SqlCommand command = new SqlCommand(userQuery, connection);
        connection.Open();

        // Fill the DataTable with query results
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        adapter.Fill(resultsTable);
    }
    return resultsTable;
}

Bind the results to a DataGridView (WinForms example):

DataTable queryResults = RunSelectQuery(connectionString, selectedDbName, textBoxQuery.Text);
dataGridViewResults.DataSource = queryResults;

For Non-SELECT Queries (INSERT/UPDATE/DELETE) #

Use ExecuteNonQuery() to get the number of rows affected:

public int RunNonSelectQuery(string connectionString, string selectedDatabase, string userQuery)
{
    SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder(connectionString);
    connBuilder.InitialCatalog = selectedDatabase;
    string dbSpecificConnString = connBuilder.ToString();

    using (SqlConnection connection = new SqlConnection(dbSpecificConnString))
    {
        SqlCommand command = new SqlCommand(userQuery, connection);
        connection.Open();
        return command.ExecuteNonQuery(); // Returns count of affected rows
    }
}

Key Notes #

• Always use using statements for connections, commands, and readers—this ensures resources are cleaned up properly, preventing memory leaks.
• Add error handling around query execution (e.g., catch SqlException to handle SQL-specific errors like invalid syntax or permission issues).
• For WPF apps, use ObservableCollection instead of List for database lists to enable automatic UI updates, and bind to controls using XAML.

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