C# SQL 连接字符串返回错误

Question

您好，我是刚接触服务器/数据库的新手，想知道为什么会返回错误。

我有一个带数据库的 FastHost 服务器。

我刚刚输入了一个示例 IP，但我一直在使用网站上我的控制面板上给出的 IP。

private void SQLTest_Click(object sender, RoutedEventArgs e)
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString =
                    "Data Source = 123.456.789.012" +
                    "Initial Catalog = DiscoverThePlanet" +
                    "User ID = TestUser" +
                    "Password = Test";
                try
                {
                    conn.Open();
                    MessageBox.Show("Connection Established!");
                    conn.Close();
                }

                catch (Exception ex)
                {
                    MessageBox.Show("Can not open Connection!");
                }
            }

这返回

Can not open Connection!" message.

我在我的代码中得到以下显示:'System.Data.SqlClient.SqlException' 类型的异常发生在 System.Data.dll 中，但未在用户代码中处理

其他信息:建立与 SQL Server 的连接时发生了与网络相关或特定于实例的错误。服务器未找到或无法访问。验证实例名称是否正确以及 SQL Server 是否配置为允许远程连接。 (提供程序:命名管道提供程序，错误:40 - 无法打开与 SQL Server 的连接)

我知道我的服务器没有问题，因为我已经在 SQL Server Management Studio 上连接到它并添加了表和数据。

Answer 1

你错过了几个;

conn.ConnectionString =
                    "Data Source = 123.456.789.012" +
                    ";Initial Catalog = DiscoverThePlanet" +
                    ";User ID = TestUser" +
                    ";Password = Test";

更好的解决方案是使用 ConnectionStringBuilder 。

System.Data.SqlClient.SqlConnectionStringBuilder builder =
  new System.Data.SqlClient.SqlConnectionStringBuilder();
builder["Data Source"] = "123.456.789.012";
builder["Initial Catalog"] = "DiscoverThePlanet";
builder["User ID"] = "TestUser";
builder["Password"] = "Test";
Console.WriteLine(builder.ConnectionString);

或者(如 @Fischermaen 所述)您可以使用属性而不是索引。它更具可读性!

    builder.DataSource = "123.456.789.012";
    builder.InitialCatalog = "DiscoverThePlanet";
    builder.UserID = "TestUser";
    builder.Password = "Test";

此外，在这种情况下，您没有使用任何用户输入，但在手动创建连接字符串时要注意 connection string injection。 ConnectionStringBuilder 可以帮助您避免这些。

A connection string injection attack can occur when dynamic string concatenation is used to build connection strings that are based on user input. If the string is not validated and malicious text or characters not escaped, an attacker can potentially access sensitive data or other resources on the server. For example, an attacker could mount an attack by supplying a semicolon and appending an additional value. The connection string is parsed by using a "last one wins" algorithm, and the hostile input is substituted for a legitimate value.

The connection string builder classes are designed to eliminate guesswork and protect against syntax errors and security vulnerabilities. They provide methods and properties corresponding to the known key/value pairs permitted by each data provider. Each class maintains a fixed collection of synonyms and can translate from a synonym to the corresponding well-known key name. Checks are performed for valid key/value pairs and an invalid pair throws an exception. In addition, injected values are handled in a safe manner.

最后(也是我认为最好的)替代方案是 move your connectionstring from code into a config 。这将使您更容易在不同环境中使用相同的代码。

conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString];

还有你的配置。

<connectionStrings>
    <add name="MyConnectionString" connectionString="[ConnectionString goes here]" providerName="System.Data.SqlClient" />
</connectionStrings>