CODEWITHSUNDEEP
c#sqlconnection
Takeshi
Takeshi

Reputation: 139

System.Data.SqlClient.SqlConnection unexpected result in Open() Method with incomplete ConnectionString

When calling the Open() method on a SqlConnection instance that got passed an incomplete connection string, the method does not throw an exception.

I have created the following example to showcase my problem. 

var success = true;

var connectionStrings = new[]
        {
            "Integrated Security=SSPI;",
            "Initial Catalog=awdemo;Integrated Security=SSPI;",
            "Data Source=.\\sql2016;Initial Catalog=awdemo;Integrated Security=SSPI;"
};

foreach (var connectionString in connectionStrings)
{
    var conn = new SqlConnection(connectionString);

    try
    {
         conn.Open();
    }
    catch (Exception)
    {
        success = false;
    }
    finally
    {
        conn.Close();
        Console.WriteLine($"{connectionString} - Success = {success}");
    }
}

Result: 

Integrated Security=SSPI; - Success = True
Initial Catalog=awdemo;Integrated Security=SSPI; - Success = True
Data Source=.\sql2016;Initial Catalog=awdemo;Integrated Security=SSPI; - Success = True

I would expect conn.Open() to throw an exception for the first two connection strings, since both of them are incomplete and not valid.

Why is there no exception thrown?

EDIT: as pointed out by Steve in the comments an exception is not thrown because the component connects to my default instance of SQL Server when not providing the server information.

Is there a way to force the SqlConnection component to throw an error on incomplete connection strings? In the application it is possible and allowed to create incomplete connection strings.

Upvotes: 1

Views: 495

Answers (1)

Scott Hannen
Scott Hannen

Reputation: 29222

There's no practical way to fully validate this. You could have a connection string that has all of the components you expect, including a valid server and database name, but it's the wrong server or wrong database.

So if the expectation is that the class should fail on conn.Open() if the connection string is incorrect (not invalid, but incorrect), you can't completely achieve that.

Does it matter? Imagine a few scenarios, all of which include incorrect connection strings:

  1. The connection string is "blarg!" and so attempting to open the connection throws an exception.
  2. The connection string doesn't contain a server or database name. You can open the connection but when you try to execute some command it fails.
  3. The connection string contains a server and database name, but it's the wrong server or database. It fails for the same reason as 2.

In each of the scenarios, what's the first thing you're going to do? You're going to look at the exception. Regardless of which line throws it, you're going to quickly deduce that you have the wrong connection string.

So a "validation" which can't actually validate the connection string before you open it is just going to add work. If the connection string is wrong, that truly is an "exceptional" condition so it's probably better to just let the code throw an exception where it does. You'll find the problem quickly.

All of that aside, suppose you just really want to be sure that your connection contains a server name and a database name before you try to open it. You could write an extension method like this:

// Maybe give it a better name.
public static void ValidateThatConnectionHasDataSourceAndDatabase(this SqlConnection connection)
{
    if (string.IsNullOrEmpty(connection.DataSource)) 
        throw new Exception("The connection has no datasource");
    if (string.IsNullOrEmpty(connection.Database)) 
        throw new Exception("The connection has no database");
}

After you create the connection and before you open it, call conn.ValidateThatConnectionHasDataSourceAndDatabase();

Upvotes: 1

Related Questions