How do I connect to Google Cloud SQL Server using C#?

Viewed 1033

I just set up a SQL Server on the "Google Cloud Platform." I created a database and tables there. I used Microsoft's "SQL Server Management Studio" (SSMS) to connect and create the database and tables. That all worked. I set up a proxy address 127.0.0.1:1443 according to Google's Cloud SQL instructions. That all worked.

However, what I want to do is connect to this remote server using a C# application. I have no problem using the C# program to connect to the SQL Server on my machine, but I can't figure out how to configure to connect to the one on the remote one on the Google Cloud Platform.

For my code I'm using the System.Data.SqlClient library.

My connection string is below. The code below is hopefully enough to tell you what is going on. When I call loadInfoFromDatabase(), it fails when it hits `connection.Open(). I wrote "FAILS HERE" next to it in the code below.

The connection string is a bit of a guess after trying different things. Any help would be appreciated.

Thanks!

public string _connectionString = @"Server=127.0.0.1:1443;Database=MarsDatabase;User Id=sqlserver;Password=AAABBBCCC";

private void loadInfoFromDatabase()
{
    string firstName, middleName, lastName, email, password, assignedTables;
    string connectionString;
    SqlDataReader dataReader;

    connectionString = _connectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open(); //FAILS HERE... 

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction("loadInfoFromDatabse");

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {                  
            command.CommandText = "Select * From Info";

            // Attempt to commit the transaction.
            dataReader = command.ExecuteReader();
           
            while (dataReader.Read())
            {
               ...
            }

            dataReader.Close();
            command.Dispose();
            // connection.Close();
            transaction.Commit();
            Console.WriteLine("Selection from managers table worked.");
        }
        catch (Exception ex)
        {
           
        }
    }
}
1 Answers

I see that you're using Cloud SQL Proxy to connect to the SQL Server instance using TCP.

The problem is with your connection string because you're missing the TCP prefix. By looking at Server on ConnectionString property you can see the description where:

The TCP format must start with the prefix "tcp:". (ex. server=tcp:servername, portnumber)

To fix the issue, change your connection string to:

@"Server=tcp:127.0.0.1,1443;Database=MarsDatabase;User ID=sqlserver;Password=AAABBBCCC";
Related