When a developer first starts out with Visual Studio and intends to connection to a database in many cases does not know how to create a proper connection string. When way to create a proper connection is by using Connect to Database dialog. When using the Connect to Database dialog the Server name list may not populate so you must manually enter the server name.

Note
Seasoned developer can jump to the section Hacked dialog

Once finished we get a connection in Server Explorer.

To get the actual connection string, select the database then properties.

At this point, copy the connection string to a local variable in code, a setting in appconfig or appsettings.json.

Note
The connection string will fail as is if using a current version of SQL-Server data provider without adding Encrypt=False or Encrypt=optional

Hacked dialog

Microsoft once had the source code for the Connect to Database dialog but later remove the source code. As fate would have it I download the source code and modified the source just a little followed by creating a .NET tool which is accessed via Visual Studio external tools.

Modifications

Since the source code was in an older .NET Framework the Microsoft projects were upgraded to .NET Framework 4.8. Some might asked, why not convert to .NET Core? There is nothing to gain unless a developer wanted to use the dialog in a .NET Core project. The intent here is to be a tool to use in Visual Studio or standalone tool.

For the server name prompt, the underlying code has a context menu.

I added two menu items.

For the reader to modify the menu.

1. Select the project Microsoft.Data.ConnectionUI.Dialog

2. Select the control SqlConnectionUIControl.cs

3. Select the contextMenuStrip1 and make you changes. A menu item text will be the server you want to connect too.

Added an option for including Encrypt=False/True by modifying the form DataConnectionDialog in the project Microsoft.Data.ConnectionUI.Dialog and changed the modifier to public.

In the caller code appended Encrypt dependent on the checkbox.

if (dcd.UseEncryptionCheckBox.Checked)
{
    connectionString += ";Encrypt=True";
}
else
{
    connectionString += ";Encrypt=False";
}

Creating the tool

Created a new .NET Framework 4.8 Windows Form project.

Add the following references from the current solution

• Microsoft.Data.ConnectionUI
• Microsoft.Data.ConnectionUI.Dialog
• Microsoft.Data.DataConnectionConfiguration

The references have a small foot print in the current project.

Base code in the form. This code could had been placed in a class yet no benefits for this.

• RemoveConfigurationFile prevents accidentally saving the data provider selection. In the dialog there is a Always use this selection which if checked saves the provider selection in the debug folder in a xml file. If the reader rather always use the same provider remove RemoveConfigurationFile()

Xml file DataConnection.xml

<ConnectionDialog>
  <DataSourceSelection>
    <SelectedSource>MicrosoftSqlServer</SelectedSource>
    <SelectedProvider>System.Data.SqlClient</SelectedProvider>
  </DataSourceSelection>
</ConnectionDialog>

• Create an instance of the dialog
• Load configutations, see first bullet
• Invoke the dialog
• If OK was selected generate a connection string

public static string CreateConnectionString()
{
    RemoveConfigurationFile();

    var dialog = new DataConnectionDialog();

    var configuration = new DataConnectionConfiguration(null);

    configuration.LoadConfiguration(dialog);

    if (DataConnectionDialog.Show(dialog) != DialogResult.OK) return "Aborted";

    var connectionString = dialog.ConnectionString;

    if (dialog.UseEncryptionCheckBox.Checked)
    {
        connectionString += ";Encrypt=True";
    }
    else
    {
        connectionString += ";Encrypt=False";
    }

    return connectionString;
}

In a button Click event call the method above.

Generate both a raw connection string and one for appsettings.json using a template shown below from a project resource.

private void CreateButton_Click(object sender, EventArgs e)
{
    ResultTextBox.Text = "";
    ConnectionStringTextBox.Text = "";

    var connection = CreateConnectionString();

    ConnectionStringTextBox.Text = connection;
    var connectionString = Properties.Resources.BlankConntection
        .Replace("_TOKEN_", connection)
        .Replace("\\", "\\\\");

    ResultTextBox.Text = connectionString;

    File.WriteAllText("appsettings.json", ResultTextBox.Text);
}

Under project resource, named BlankConntection

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "_TOKEN_"
  }
}

Finished tool

• Create button invokes the dialog
• Open button opens Windows Explorer and select appsettings.json ready to copy to a project.

Using the tool in Visual Studio

Once the project has been built, place the contents of the debug folder in a folder e.g. C:\Tools\Connector then

From Visual Studio menu, select the Tools menu, External tools, add a new entry as per below.

Source code

Clone the following GitHub repository