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.
Prerequisites
To follow this tutorial and implement its recommendations efficiently, you will need the following:
- Visual Studio 2022 - Our IDE of choice. If you don't have it on your machine, visit the official website to download and install the free Community Edition.
- dotConnect for SQL Server - An enhanced data provider that builds on ADO.NET and SqlClient to present a complete solution for developing SQL Server-based database applications.
- Cloud SQL for SQL Server - A sample Google Cloud database provided for learning and testing.
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.
Create a .NET project
First, we create a simple console application in Visual Studio that will connect to Cloud SQL for SQL Server. We'll use this application in our tutorial to illustrate the use cases.
1. Launch Visual Studio, select Create a new project, and choose Console App (.NET Core). Click Next.
2. Name the project, specify the path to its folder, and click Create. In this tutorial, we name our project GoogleSqlTest.
3. In the Solution Explorer, right-click the created project and select Manage NuGet Packages.
4. Click Browse tab and find and install the Devart.Data.SqlServer package.
Optionally, you can use the terminal and run the following command:
dotnet add package Devart.Data.SqlServer
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.