Connect to Azure SQL Database using C#

Working with cloud databases is a common task in modern .NET development, and Microsoft Azure SQL Database is among the popular options, with its scalable, secure, and high-performance relational databases, ideal for both small projects and enterprise applications.

This step-by-step guide will describe how to connect your .NET application to an Azure SQL Database database using Devart dotConnect for SQL Server and perform the basic operations with data.

Why dotConnect for SQL Server?

Azure SQL Database is a fully managed, intelligent, relational database service built on the SQL Server engine and hosted in the Microsoft Azure cloud. Devart's dotConnect for SQL Server provides full support for Azure SQL Database, enabling direct access to your data from .NET applications of all levels.

dotConnect for SQL Server eliminates the need for additional client libraries while ensuring a secure and reliable connection. It is fully compliant with ADO.NET standards and integrates seamlessly with Visual Studio, making the .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 - A data provider that builds on ADO.NET and SqlClient to present a complete solution for developing SQL Server-based database applications.
A test database on AzureSQL - We use the sakila database to illustrate connecting to our .NET application as shown further.

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

Download and install dotConnect for SQL Server directly on your machine, or install the Devart.Data.SQL Server NuGet package.

No license key is required, 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.

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 that connects to a SQL Server database in Visual Studio to use it in our tutorial.

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 it SqlServerTest.
3. In the Solution Explorer, right-click the created project and select Manage NuGet Packages.
4. Go to the 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 Azure SQL Database 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.

Connect to SQL Server using Data Explorer

Enter the necessary credentials and click Connect.

Enter the necessary SQL Server credentials

Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.

View the data in the Data Explorer

Connect and retrieve data from the database

Now that the project is ready, we can connect to Azure SQL Database to access and manipulate data.

Create DatabaseConnection.cs (connection strings)

This class keeps connection details separate for better maintainability.

using System;
using Devart.Data.SqlServer;

public static class DatabaseConnection
{
    public static SqlConnection CreateOpen()
    {
        var connectionString = Environment.GetEnvironmentVariable("AZURE_SQL_CONN_STR") ??
            "" +
            "User Id=TestUser;" +
            "Password=TestPassword;" +
            "Data Source=test.database.windows.net;" +
            "Initial Catalog=TestDatabase;" +
            "License key=**********";

        var connection = new SqlConnection(connectionString);
        connection.Open();
        return connection;
    }
}

Update the connection string

Replace the placeholders in the connection string with your actual SQL Server database credentials.

Note

If you own a paid license of dotConnect for SQL Server, include the license key in the connection strings.

Add the code below to the Program.cs file.

try
{
    using var connection = DatabaseConnection.CreateOpen();
    Console.WriteLine($"Connection State: {connection.State}\n");

    const string sql = "SELECT * FROM Actor";
    using var cmd = new SqlCommand(sql, connection);
    using var reader = (SqlDataReader)cmd.ExecuteReader();

    if (!reader.HasRows)
    {
        Console.WriteLine("No rows found.");
    }
    else
    {
        for (int i = 0; i < reader.FieldCount; i++)
        {
            Console.Write(i == 0 ? reader.GetName(i) : $", {reader.GetName(i)}");
        }
        Console.WriteLine();

        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                var value = reader.IsDBNull(i) ? "NULL" : reader.GetValue(i);
                Console.Write(i == 0 ? value : $", {value}");
            }
            Console.WriteLine();
        }
    }
}
catch (SqlException ex)
{
    Console.WriteLine($"SQL error: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"Unexpected error: {ex.Message}");
}

Run the application

Build and run your application: select Start from the menu or click F5. It displays the data retrieved from the database.

Get data from the SQL Server database in Azure

Insert new records into a table in Azure SQL Database

Let's add a new record to the database. In our test case, we want to insert a new row into the Actor table using standard ADO.NET classes in the application.

try
{
    using var connection = DatabaseConnection.CreateOpen();
    Console.WriteLine($"Connection State: {connection.State}\n");

    const string insertSql =
        "INSERT INTO Actor (FirstName, LastName)\n" +
        "OUTPUT INSERTED.*\n" +
        "VALUES (@FirstName, @LastName);";

    using (var insertCmd = new SqlCommand(insertSql, connection))
    {
        var pFirst = new SqlParameter("@FirstName", SqlType.VarChar, 100)
        {
            Direction = ParameterDirection.Input,
            Value = "Michael"
        };
        insertCmd.Parameters.Add(pFirst);

        var pLast = new SqlParameter("@LastName", SqlType.VarChar, 100)
        {
            Direction = ParameterDirection.Input,
            Value = "Anderson"
        };
        insertCmd.Parameters.Add(pLast);

        using var insertedReader = (SqlDataReader)insertCmd.ExecuteReader();
        Console.WriteLine("Newly inserted row:");
        if (insertedReader.HasRows)
        {
            for (int i = 0; i < insertedReader.FieldCount; i++)
            {
                Console.Write(i == 0 ? insertedReader.GetName(i) : $", {insertedReader.GetName(i)}");
            }
            Console.WriteLine();

            while (insertedReader.Read())
            {
                for (int i = 0; i < insertedReader.FieldCount; i++)
                {
                    var value = insertedReader.IsDBNull(i) ? "NULL" : insertedReader.GetValue(i);
                    Console.Write(i == 0 ? value : $", {value}");
                }
                Console.WriteLine();
            }
        }
        else
        {
            Console.WriteLine("Insert did not return a row.");
        }
    }
}
catch (SqlException ex)
{
    Console.WriteLine($"SQL error: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"Unexpected error: {ex.Message}");
}

View the results:

Insert a new record into the database table in Azure SQL

Update rows in Azure SQL Database

Now let's update an existing record: we can do it directly from the application. We want to modify the first and last name of an actor.

try
{
    using var connection = DatabaseConnection.CreateOpen();
    Console.WriteLine($"Connection State: {connection.State}\n");

    const string updateSql =
        "UPDATE Actor\n" +
        "SET FirstName = @FirstName, LastName = @LastName\n" +
        "OUTPUT INSERTED.*\n" +
        "WHERE ActorID = @ActorID;";

    using (var updateCmd = new SqlCommand(updateSql, connection))
    {

        var pId = new SqlParameter("@ActorID", SqlType.Int)
        {
            Direction = ParameterDirection.Input,
            Value = 12
        };
        updateCmd.Parameters.Add(pId);

        var pFirst = new SqlParameter("@FirstName", SqlType.VarChar, 100)
        {
            Direction = ParameterDirection.Input,
            Value = "Ethan"
        };
        updateCmd.Parameters.Add(pFirst);

        var pLast = new SqlParameter("@LastName", SqlType.VarChar, 100)
        {
            Direction = ParameterDirection.Input,
            Value = "Whitmore"
        };
        updateCmd.Parameters.Add(pLast);

        using var updatedReader = (SqlDataReader)updateCmd.ExecuteReader();
        Console.WriteLine("Updated row:");
        if (updatedReader.HasRows)
        {
            for (int i = 0; i < updatedReader.FieldCount; i++)
            {
                Console.Write(i == 0 ? updatedReader.GetName(i) : $", {updatedReader.GetName(i)}");
            }
            Console.WriteLine();

            while (updatedReader.Read())
            {
                for (int i = 0; i < updatedReader.FieldCount; i++)
                {
                    var value = updatedReader.IsDBNull(i) ? "NULL" : updatedReader.GetValue(i);
                    Console.Write(i == 0 ? value : $", {value}");
                }
                Console.WriteLine();
            }
        }
        else
        {
            Console.WriteLine("No row was updated.");
        }
    }
}
catch (SqlException ex)
{
    Console.WriteLine($"SQL error: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"Unexpected error: {ex.Message}");
}

The application shows us updated data:

Update rows in a database table in Azure SQL

Delete rows from Azure SQL Database

To wrap up the data operations, let us remove the record we previously added. Use the below code:

try
{
    using var connection = DatabaseConnection.CreateOpen();
    Console.WriteLine($"Connection State: {connection.State}\n");

    const string deleteSql =
        "DELETE FROM Actor\n" +
        "OUTPUT DELETED.*\n" +
        "WHERE ActorID = @ActorID;";

    using (var deleteCmd = new SqlCommand(deleteSql, connection))
    {

        var pId = new SqlParameter("@ActorID", SqlType.Int)
        {
            Direction = ParameterDirection.Input,
            Value = 12
        };
        deleteCmd.Parameters.Add(pId);

        using var deletedReader = (SqlDataReader)deleteCmd.ExecuteReader();
        Console.WriteLine("Deleted row:");
        if (deletedReader.HasRows)
        {
            for (int i = 0; i < deletedReader.FieldCount; i++)
            {
                Console.Write(i == 0 ? deletedReader.GetName(i) : $", {deletedReader.GetName(i)}");
            }
            Console.WriteLine();

            while (deletedReader.Read())
            {
                for (int i = 0; i < deletedReader.FieldCount; i++)
                {
                    var value = deletedReader.IsDBNull(i) ? "NULL" : deletedReader.GetValue(i);
                    Console.Write(i == 0 ? value : $", {value}");
                }
                Console.WriteLine();
            }
        }
        else
        {
            Console.WriteLine("No row was deleted.");
        }
    }
}
catch (SqlException ex)
{
    Console.WriteLine($"SQL error: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"Unexpected error: {ex.Message}");
}

The operation is successful.

Delete records from the database table in Azure SQL Database

Conclusion

Connecting a .NET application to Azure SQL Database and performing basic data operations are foundational tasks for any application that deals with data stored in this relational database.

As you continue building applications with .NET, you can expand on this and proceed to more complex challenges, such as handling exceptions, working with stored procedures, or integrating data access into larger application architectures to develop more complex, data-driven applications.