CODEWITHSUNDEEP
c#sqlconnection
Jennifer
Jennifer

Reputation: 41

How can i modify a sql connection string at runtime?

I query a database as follows:

string connString = "Data Source=ServerName;Initial Catalog=AdventureWorks;User 
     id=UserName;Password=Secret;";    
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand("select * from Orders", connString);
conn.Open();

The issue is that the server may require the settings TrustServerCertificate and Encrypt

So if i run the above it will fail with error

  SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. 
  (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)]

but if i have the connString as

"Data Source=ServerName;Initial Catalog=AdventureWorks;User 
 id=UserName;Password=Secret;Encrypt=true;TrustServerCertificate=true");

then it will connect without a problem and the select will run.

So I may need to change the connection string on the fly Is there a smart way I can make a modification to the above code to check that if the error is returned I then retry the select with the new , modified connection string ?

Upvotes: -1

Views: 510

Answers (2)

Safee
Safee

Reputation: 88

Enhancing the solution provided by @ɐsɹǝʌ ǝɔıʌ:

    string connectionString = "Data Source=ServerName;Initial Catalog=AdventureWorks;Userid=UserName;Password=Secret;";
    string queryString = "SELECT * FROM Orders;";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        try
        {
            connection.Open();
        }
        catch (SqlException)
        {
            command.Connection.ConnectionString = "Data Source=ServerName;Initial Catalog=AdventureWorks;Userid=UserName;Password=Secret;Encrypt=true;TrustServerCertificate=true";
            connection.Open();
           
        }
        finally
        {
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine(String.Format("{0}", reader[0]));
                }
            }
        }
    }

Upvotes: 0

ɐsɹǝʌ ǝɔıʌ
ɐsɹǝʌ ǝɔıʌ

Reputation: 4512

Place your code into a try block and catch the SqlException. Then, change your connection string and retry the connection.

string connectionString = "Data Source=ServerName;Initial Catalog=AdventureWorks;Userid=UserName;Password=Secret;";
string queryString = "SELECT * FROM Orders;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    try
    {
        connection.Open();
        using(SqlDataReader reader = command.ExecuteReader())
        {
           while (reader.Read())
           {
              Console.WriteLine(String.Format("{0}", reader[0]));
           }
        }
    }
    catch (SqlException)
    {
        command.Connection.ConnectionString = "Data Source=ServerName;Initial Catalog=AdventureWorks;Userid=UserName;Password=Secret;Encrypt=true;TrustServerCertificate=true";
        connection.Open();
        using(SqlDataReader reader = command.ExecuteReader())
        {
           while (reader.Read())
           {
              Console.WriteLine(String.Format("{0}", reader[0]));
           }
        }
    }
    catch (Exception ex)
    {
        Console.Write($"Error: {ex.Message}")
    }
 }

Upvotes: 2

Related Questions