| title | description | author | manager | ms.technology | ms.topic | ms.date | ms.author | monikerRange |
|---|---|---|---|---|---|---|---|---|
Add a connection to Azure SQL Database |
Connect Azure SQL Database to your application by using Connected Services in Visual Studio on Windows and add a connected service. |
AngelosP |
jmartens |
vs-azure |
conceptual |
05/15/2023 |
angelpe |
>= vs-2019 |
[!INCLUDE Visual Studio]
With Visual Studio, you can connect any of the following to Azure SQL Database by using the Connected Services feature:
- .NET Framework console app
- ASP.NET MVC (.NET Framework)
- ASP.NET Core
- .NET Core (including console app, WPF, Windows Forms, class library)
- .NET Core Worker Role
- Azure Functions
- Universal Windows Platform App
- Xamarin
- Cordova
The connected service functionality adds all the needed references and connection code to your project, and modifies your configuration files appropriately.
Note
This topic applies to Visual Studio on Windows. For Visual Studio for Mac, see Connected services in Visual Studio for Mac.
- Visual Studio with the Azure workload installed.
- A project of one of the supported types
-
Open your project in Visual Studio.
-
In Solution Explorer, right-click the Connected Services node, and, from the context menu, select Add to open the menu of available services.
-
Choose SQL Server Database. The Connect to dependency page appears. You should see several options:
- SQL Server Express LocalDB, the built-in SQL database offering installed with Visual Studio
- SQL Server Database on a local container on your machine
- SQL Server Database, a on-premises SQL server on the local network
- Azure SQL database, for the SQL database running as an Azure service
You can reduce cost and simplify early development by starting with a local database. You can migrate to the live service in Azure later by repeating these steps and choosing another option. If you create a database locally that you want to recreate in Azure, you can migrate your database to Azure at that time.
If you want to connect to the Azure service, continue to the next step, or if you aren't signed in already, sign into your Azure account before continuing. If you don't have an Azure account, you can sign up for a free trial.
-
In the Configure Azure SQL Database screen, select an existing Azure SQL Database, and select Next.
If you need to create a new component, go to the next step. Otherwise, skip to step 7.
-
To create an Azure SQL Database:
-
Select Create New by the green plus sign.
-
Fill out the Azure SQL Database: Create new screen, and select Create.
-
When the Configure Azure SQL Database screen is displayed, the new database appears in the list. Select the new database in the list, and select Next.
-
-
Enter a connection string name, or choose the default, and choose whether you want the connection string stored in a local secrets file, or in Azure Key Vault.
-
The Summary of changes screen shows all the modifications that will be made to your project if you complete the process. If the changes look OK, choose Finish.
If prompted to set a firewall rules, choose Yes.
-
In Solution Explorer, double-click on the Connected Services node to open the Connected Services tab. The connection appears under the Service Dependencies section:
If you click on the three dots next to the dependency you added, you can see various options such as Connect to reopen the wizard and change the connection. You can also click the three dots at the top right of the window to see options to start local dependencies, change settings, and more.
Learn how to store secrets safely by following Safe storage of app secrets in development in ASP.NET Core. In particular, to read the connection string from the secrets store, you can add code as in Read the secret via the configuration API. See also Dependency injection in ASP.NET Core.
It might be convenient to work with a local data store during early development, but with Entity Framework Core, when you're ready to move to the cloud, you can use Visual Studio's support for Entity Framework migration to move your database, or merge changes with a remote data store. See Migrations overview.
On the Connected Services tab, you can find the migration commands by clicking on the three dots, as shown in the screenshot:
Commands are available there to create new migrations, apply them directly, or generate SQL scripts that apply the migrations.
:::moniker range=">=vs-2022"
When a data model change is introduced, you can use Entity Framework Core tools to add a corresponding migration that describes in code the updates necessary to keep the database schema in sync. Entity Framework Core compares the current model against a snapshot of the old model to determine the differences, and generates migration source files. The files are added to your project, usually in a folder called Migrations and can be tracked in your project's source control like any other source file.
When you choose this option, you're asked to provide the context class name that represents the database schema you want to migrate.
After a migration has been created, it can be applied to a database. Entity Framework updates your database and your schema with the changes specified in the migration code. When you choose this option, you're asked to provide the context class name that represents the database schema you want to migrate.
The recommended way to deploy migrations to a production database is by generating SQL scripts. The advantages of this strategy include the following:
- SQL scripts can be reviewed for accuracy; this is important since applying schema changes to production databases is a potentially dangerous operation that could involve data loss.
- In some cases, the scripts can be tuned to fit the specific needs of a production database.
- SQL scripts can be used in conjunction with a deployment technology, and can even be generated as part of your CI process.
- SQL scripts can be provided to a DBA, and can be managed and archived separately.
When you use this option, you're asked the database context class and the location for the script file.
For convenience, this command lets you jump to the SQL Server Object Explorer, so you can view tables and other database entities, and work directly with your data. See Object explorer.
:::moniker-end
You can continue with the quickstarts for Azure SQL database, but instead of starting from the beginning, you can start after the initial connection is set up. If you're using Entity Framework, you can start at Add the code to connect to Azure SQL Database. If you're using SqlClient or ADO.NET data classes, you can start at Add the code to connect to Azure SQL Database.
Your code won't exactly match what is used in the quickstarts, which use a different way of getting the connection string. The connection strings are secrets and are securely stored as explained in Safe storage of app secrets in development in ASP.NET Core. In particular, to read the connection string from the secrets store, you can add code as in Read the secret via the configuration API. In ASP.NET Core projects, the connection string created by Connected Services is available in a configuration object. You can access it by a property on the WebApplicationBuilder class (builder in many project templates), as in the following example:
var connection = builder.Configuration["ConnectionStrings:ConnectionString1"];