Connect Azure SQL Database to a .NET project

Question

I have built a web API using dotnet and this is my first time using Azure (or any other cloud platform) to host a web application. I use EntityFramework and MySQL database to build my project.

I use DbConnectionString = "Server=localhost;Database=hms;Uid='{root-user}';Pwd={pw};" as the connection string for my SQL database now I want to know how I can connect it with the Azure SQL database I created. I added my IP address in firewall access to the Azure server and tried changing the connection string to DbConnectionString = "Server=server.database.windows.net:1433;Database=hms;Uid='{root -user}';Pwd= {pw};" But it gave the error An exception was thrown, probably due to a transient failure. When I try to update the database after adding the migration, consider enabling transient error resilience by adding "EnableRetryOnFailure()" to the "UseMySql" call..

I'd like to know what I'm doing wrong or what else I need to do to get it running. Tia.

Answer 1

1. I created an Azure SQL database with a table.
2. The data in the table is as follows

3. Create a console application using C#.

4. Add package Microsoft.Data.SqlClient to the project.

5. Added the following code in the Program.cs file,

[Taking reference from here](https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-dotnet-visual-studio?view=azuresql), I created a replica of the program.cs file as shown:

using System;
using Microsoft.Data.SqlClient;
using System.Text;
namespace sqltest
{
class Program
 {
  static void Main(string[] args)
  {
try
 {
 SqlConnectionStringBuilder builder  = new SqlConnectionStringBuilder();
 builder.DataSource = "yourservername.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");

//String sql = "SELECT * FROM dbo.Persons";
String sql = "SELECT LastName, FirstName FROM dbo.Persons";
using (SqlCommand command = new SqlCommand(sql, connection))
{
 connection.Open();
 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();
   }
  }
}

6. Using the above code, I connect to Azure SQL and retrieve data from the database.

7. Run the application and get the data from the Azure Sql database as shown below,

Reference link:

https://learn.microsoft.com/en-us/visualstudio/azure/azure-sql-database-add-connected-service?view=vs-2022