How to Connect to Google Cloud SQL for SQL Server in .NET With C#
Google Cloud SQL for SQL Server is a fully managed relational database service that hosts Microsoft SQL Server in the cloud. It enables users to continue working with familiar SQL Server tools and utilize their skills while automating routine database management tasks. The service also integrates with other Google Cloud offerings, such as Google Kubernetes Engine (GKE) and Cloud Functions.
For organizations that rely on Google Cloud services but want to retain the benefits of SQL Server without maintaining their own infrastructure, Cloud SQL for SQL Server offers a strong alternative to Azure SQL Database.
This guide explains how to connect .NET applications to SQL Server instances hosted on Google Cloud and perform essential data operations. To ensure a smooth, secure, and reliable connection without complex configurations or additional client libraries, we'll use Devart's dotConnect for SQL Server, a high-performance data provider.
Why dotConnect for SQL Server?
Devart's dotConnect for SQL Server provides full support for Google's Cloud SQL for SQL Server, enabling direct access to your data in the cloud from .NET applications of all levels. It is fully compliant with ADO.NET standards and integrates seamlessly with Visual Studio, making .NET development much more straightforward and controlled.
Download and activate dotConnect for SQL Server
Depending on your conditions, you may choose a fully-functional free trial or a full version.
30-day free trial version
You can start using dotConnect for SQL Server immediately with a 30-day free trial. Choose one of the following options:
- Installer: Download and install dotConnect for SQL Server directly on your machine.
- NuGet: Add the Devart.Data.SQL Server NuGet package to your project in Visual Studio.
No license key is necessary, and you can start exploring the product immediately.
Full version
After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.
To activate a connection in your application, add the License Key to your connection string.
Connect to Google's Cloud SQL for SQL Server with Data Explorer
To connect to SQL Server using the built-in Data Explorer, click Tools and select Connect to Database. Then choose SQL Server as the data source.
Enter the necessary credentials and click Connect.
Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.
Connect and retrieve data from the database
Bow we can connect our project to Cloud SQL for SQL Server from the .NET application and access our data in the cloud.
Create DatabaseConnection.cs (connection strings)
This class keeps connection details separate for better maintainability.
using Devart.Data.SqlServer;
namespace GoogleSqlTest
{
public static class DatabaseConnection
{
public static SqlConnection CreateConnection()
{
string connectionString =
"Data Source=127.0.0.1;" +
"User Id=TestUser;" +
"Password=TestPassword;" +
"Initial Catalog=TestDb;" +
"Encrypt=True;" +
"TrustServerCertificate=True;" +
"License Key=**********";
return new SqlConnection(connectionString);
}
}
}
Update the connection string
Replace the placeholders in the connection string with your actual database credentials.
Add the code below to the Program.cs file.
using Devart.Data.SqlServer;
partial class Program
{
static void Main(string[] args)
{
try
{
using (var connection = GoogleSqlTest.DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Successfully connected to SQL Server!\n");
using (SqlCommand command = new SqlCommand("SELECT TOP (10) * FROM actor", connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"Actor ID: {reader["actor_id"]}, " +
$"Name: {reader["first_name"]} {reader["last_name"]}");
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine($"Error connecting to database: {ex.Message}");
}
}
}
Run the application
Press F5 or select Start from the menu. You will see the data retrieved from the database.
Insert new records into a table in Cloud SQL for SQL Server
Let's add a new record to the Actor table using ADO.NET classes.
using System;
using Devart.Data.SqlServer;
partial class Program
{
static void Main(string[] args)
{
var first = args.Length > 0 ? args[0] : "Chris";
var last = args.Length > 1 ? args[1] : "Pratt";
try
{
using (var connection = GoogleSqlTest.DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Successfully connected to SQL Server!\n");
string sql =
"INSERT INTO actor (first_name, last_name) " +
"OUTPUT inserted.actor_id, inserted.first_name, inserted.last_name " +
"VALUES (@first, @last);";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add(new SqlParameter("@first", first));
command.Parameters.Add(new SqlParameter("@last", last));
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
Console.WriteLine("Inserted actor:");
Console.WriteLine($"Actor ID: {reader["actor_id"]}, " +
$"Name: {reader["first_name"]} {reader["last_name"]}");
}
else
{
Console.WriteLine("Insert succeeded but no row was returned.");
}
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine($"Error connecting to database: {ex.Message}");
}
}
}
The operation is successful, and we can view the results in the application:
Update rows in Cloud SQL for SQL Server
Our next task is to update an existing record. We can do it directly from the application in the following way:
using System;
using Devart.Data.SqlServer;
partial class Program
{
static void Main(string[] args)
{
try
{
using (var connection = GoogleSqlTest.DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Successfully connected to SQL Server!\n");
string sql =
"UPDATE actor " +
"SET first_name = @first, last_name = @last " +
"WHERE actor_id = 51;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add(new SqlParameter("@first", "Denzel"));
command.Parameters.Add(new SqlParameter("@last", "Washington"));
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
{
Console.WriteLine("Successfully updated actor with ID 51 to Denzel Washington.");
}
else
{
Console.WriteLine("No actor found with ID 51, or the name was already Denzel Washington.");
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine($"Error connecting to database: {ex.Message}");
}
}
}
The application shows us the updated data:
Delete rows from Cloud SQL for SQL Server
Finally, let us remove the record we previously added. Use the below code:
using System;
using Devart.Data.SqlServer;
partial class Program
{
static void Main(string[] args)
{
try
{
using (var connection = GoogleSqlTest.DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Successfully connected to SQL Server!\n");
string sql = "DELETE FROM actor WHERE actor_id = @actor_id;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add(new SqlParameter("@actor_id", 51));
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
{
Console.WriteLine("Successfully deleted actor with ID 51.");
}
else
{
Console.WriteLine("No actor found with ID 51.");
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine($"Error connecting to database: {ex.Message}");
}
}
}
The appilcation shows that the record has been successfully deleted.
Conclusion
Cloud SQL for SQL Server is a popular choice for users who want secure, reliable SQL Server performance in the cloud while working within Google's ecosystem. We successfully connected to Cloud SQL for SQL Server using dotConnect for SQL Server and demonstrated how to perform essential data operations with SQL Server data hosted on Google Cloud.
From here, you can build new .NET applications, migrate existing ones to the cloud, and explore more advanced data operations.