Skip to content

Latest commit

 

History

History
147 lines (112 loc) · 5.55 KB

connect-query-dotnet-core.md

File metadata and controls

147 lines (112 loc) · 5.55 KB
Raw
title titleSuffix description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic ms.custom ms.devlang monikerRange
Use .NET to connect and query a database on Windows, Linux, or macOS
Azure SQL Database & SQL Managed Instance
This article shows you how to use .NET to create a program that connects to a database in Azure SQL Database, or Azure SQL Managed Instance, and queries it using Transact-SQL statements.
dzsquared
drskwier
wiassaf, mathoma
09/26/2023
sql-db-mi
connect
quickstart
sqldbrb=2, devx-track-csharp, mode-other, linux-related-content
csharp
= azuresql || = azuresql-db || = azuresql-mi

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

[!INCLUDEappliesto-sqldb-sqlmi]

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="<your_ado_net_connection_string>";

using Microsoft.Data.SqlClient;

namespace sqltest
{
    class Program
    {
        static void Main(string[] args)
        {
            try 
            { 
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                builder.DataSource = "<your_server.database.windows.net>"; 
                builder.UserID = "<your_username>";            
                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();       

                    String sql = "SELECT name, collation_name FROM sys.databases";

                    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.WriteLine("\nDone. Press enter.");
            Console.ReadLine(); 
        }
    }
}

Run the code

  1. At the prompt, run the following commands.

    dotnet restore
dotnet run

  2. Verify that the rows are returned, your output may 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.

Next steps