Muokkaa

Jaa

Quickstart: Use .NET (C#) to query a database

  • Artikkeli

Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

In this quickstart, you'll use .NET and C# code to connect to a database. You'll then run a Transact-SQL statement to query data. This quickstart is applicable to Windows, Linux, and macOS and leverages the unified .NET platform.

Tip

This free Learn module shows you how to Develop and configure an ASP.NET application that queries a database in Azure SQL Database

Prerequisites

To complete this quickstart, you need:

Create a new .NET project

  1. Open a command prompt and create a folder named sqltest. Navigate to this folder and run this command.

    dotnet new console

    This command creates new app project files, including an initial C# code file (Program.cs), an XML configuration file (sqltest.csproj), and needed binaries.

  2. At the command prompt used above, run this command.

    dotnet add package Microsoft.Data.SqlClient

    This command adds the Microsoft.Data.SqlClient package to the project.

Insert code to query the database in Azure SQL Database

  1. In a text editor such as Visual Studio Code, open Program.cs.

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

Note

To use an ADO.NET connection string, replace the 4 lines in the code setting the server, database, username, and password with the line below. In the string, set your username and password.

builder.ConnectionString="<connection-string>";

using Microsoft.Data.SqlClient;
using System;
using System.Threading.Tasks;

namespace sqltest
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var builder = new SqlConnectionStringBuilder
            {
                DataSource = "<your_server.database.windows.net>",
                UserID = "<your_username>",
                Password = "<password>",
                InitialCatalog = "<your_database>"
            };

            var connectionString = builder.ConnectionString;

            try
            {
                await using var connection = new SqlConnection(connectionString);
                Console.WriteLine("\nQuery data example:");
                Console.WriteLine("=========================================\n");

                await connection.OpenAsync();

                var sql = "SELECT name, collation_name FROM sys.databases";
                await using var command = new SqlCommand(sql, connection);
                await using var reader = await command.ExecuteReaderAsync();

                while (await reader.ReadAsync())
                {
                    Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
                }
            }
            catch (SqlException e) when (e.Number == /* specific error number */)
            {
                Console.WriteLine($"SQL Error: {e.Message}");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            Console.WriteLine("\nDone. Press enter.");
            Console.ReadLine();
        }
    }
}

Remember to replace <your_server.database.windows.net>, <your_username>, <password>, and <your_database> with your actual SQL Server details. Also, replace /* specific error number */ with the actual SQL error number you want to handle.

Run the code

  1. At the prompt, run the following commands.

    dotnet restore
dotnet run

  2. Verify that the rows are returned, your output might include other values.

    Query data example:
=========================================

master    SQL_Latin1_General_CP1_CI_AS
tempdb    SQL_Latin1_General_CP1_CI_AS
WideWorldImporters    Latin1_General_100_CI_AS

Done. Press enter.

  3. Choose Enter to close the application window.

Related content