Use .NET Core (C#) to query an Azure SQL database

This quickstart demonstrates how to use .NET Core on Windows/Linux/macOS to create a C# program to connect to an Azure SQL database and use Transact-SQL statements to query data.

Prerequisites

To complete this quickstart, make sure you have the following:

SQL server connection information

Get the connection information needed to connect to the Azure SQL database. You will need the fully qualified server name, database name, and login information in the next procedures.

  1. Sign in to the Azure portal.
  2. Select SQL Databases from the left-hand menu, and click your database on the SQL databases page.
  3. On the Overview page for your database, review the fully qualified server name as shown in the following image. You can hover over the server name to bring up the Click to copy option.

    server-name

  4. If you forget your server login information, navigate to the SQL Database server page to view the server admin name. If necessary, reset the password.

For ADO.NET

  1. Continue by clicking Show database connection strings.

  2. Review the complete ADO.NET connection string.

    ADO.NET connection string

Important

You must have a firewall rule in place for the public IP address of the computer on which you perform this tutorial. If you are on a different computer or have a different public IP address, create a server-level firewall rule using the Azure portal.

Create a new .NET project

  1. Open a command prompt and create a folder named sqltest. Navigate to the folder you created and run the following command:

    dotnet new console
    
  2. Open sqltest.csproj with your favorite text editor and add System.Data.SqlClient as a dependency using the following code:

    <ItemGroup>
        <PackageReference Include="System.Data.SqlClient" Version="4.4.0" />
    </ItemGroup>
    

Insert code to query SQL database

  1. In your development environment or favorite text editor open Program.cs

  2. Replace the contents with the following code and add the appropriate values for your server, database, user, and password.

using System;
using System.Data.SqlClient;
using System.Text;

namespace sqltest
{
    class Program
    {
        static void Main(string[] args)
        {
            try 
            { 
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = "your_server.database.windows.net"; 
                builder.UserID = "your_user";            
                builder.Password = "your_password";     
                builder.InitialCatalog = "your_database";

                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {
                    Console.WriteLine("\nQuery data example:");
                    Console.WriteLine("=========================================\n");

                    connection.Open();       
                    StringBuilder sb = new StringBuilder();
                    sb.Append("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName ");
                    sb.Append("FROM [SalesLT].[ProductCategory] pc ");
                    sb.Append("JOIN [SalesLT].[Product] p ");
                    sb.Append("ON pc.productcategoryid = p.productcategoryid;");
                    String sql = sb.ToString();

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
                            }
                        }
                    }                    
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
            Console.ReadLine();
        }
    }
}

Run the code

  1. At the command prompt, run the following commands:

    dotnet restore
    dotnet run
    
  2. Verify that the top 20 rows are returned and then close the application window.

Next steps