Azure SQL Database is a cloud-based relational database service that provides a scalable and secure platform for storing and managing data. In this article, we will discuss how to create a C# console app connected to an Azure SQL database.

Step 1: Create an Azure SQL Database

The first step is to create an Azure SQL database. You can do this by logging into the Azure portal and navigating to the SQL databases section. Click on the "Add" button to create a new database. You will need to provide a name for the database, select a pricing tier, and configure the server settings.

For further information, you can follow this link!

Step 2: Get the Connection String

• Once the database is created, go to the "SQL databases" section in the Azure portal.

• Click on your newly created database.

• In the "Overview" tab, click on "Show database connection strings."

• Copy the ADO.NET connection string.

Step 3: Create a C# Console App

Next, you need to create a C# console app. Open Visual Studio and create a new project. Select "Console App (.NET Framework)" as the project type and provide a name for the project. Click on the "Create" button to create the project.

Follow this step by step

• Open Visual Studio and create a new project.

• Select "Console App (.NET Core)" or "Console App (.NET)" as the project template.

• Name your project and click "Create."

Step 4: Install the required NuGet packages:

• In the Solution Explorer, right-click on your project and select "Manage NuGet Packages."

• Click on "Browse" and search for "System. Data.SqlClient."

• Install the "System.Data.SqlClient" package.

Step 5: Coding Part

Before starting, we will create a new folder in the project that will contain all our classes with the name "Library".

a. Add a class file to the project and name it "ConnectionStringBuilder" and write down this piece of code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Azure_SQL_CLI.Library
{
    public class ConnectionStringBuilder
    {
        private string _servername="", _database="", _username="", _password="";

        public string Servername 
        {
            get
            {
                return _servername;
            }

            set
            {
                _servername = value;
            }
        }
        public string Database 
        {
            get
            {
                return _database;
            }

            set
            {
                _database = value;
            }
        }
        public string Username 
        {
            get
            {
                return _username;
            }

            set
            {
                _username = value;
            }
        }
        public string Password
        {
            get
            {
                return _password;
            }

            set
            {
                _password = value;
            }
        }

        public override string ToString()
        {
            return
                string.Format
                (
                    "Data Source ={0};Initial Catalog={1};User ID={2};Password= {3};",
                    _servername, _database, _username, _password
                );
        }
    }
}

b. Then add a class file to the project and name it "ConnectionInitializer" and write down this piece of code

using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Azure_SQL_CLI.Library;

namespace Azure_SQL_CLI.Library
{
    public class ConnectionInitalizer
    {
        static SqlConnection cnx;
        ConnectionStringBuilder connector = new();

        public ConnectionInitalizer()
        {
            connector.Servername = "yourservername.database.windows.net";
            connector.Database = "yourazuredatabasename";
            connector.Username = "yourazureusername";
            connector.Password = "yourazurepassword";
        }
        public object getInstance()
        {
            return connector;
        }
    }
}

c. In the Program.cs file, add the following using statement at the top of the file

using Azure_SQL_CLI.Library;
using System.Data.SqlClient;

static void Main(string[] args)
{
    static SqlConnection cnx;
    ConnectionStringBuilder connector = new();

    cnx = new SqlConnection(connector.getInstance().ToString());
    try
       {
           if (cnx.State == ConnectionState.Closed)
              {
                  cnx.Open();
                  Console.WriteLine("Connected to the Azure SQL Database.");
               }
        // Perform database operations here
        connection.Close();
    }
}

After this, you can now add other queries and transactions like

string query = "SELECT * FROM yourtablename";
SqlCommand command = new SqlCommand(query, cnx);

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader["columnname"]);
}

Last step: Run your application

Press F5 or click "Start" in Visual Studio to run the console application.

If everything is set up correctly, you should see the "Connected to the Azure SQL Database." message in the console.

Now you have a C# console application connected to an Azure SQL Database. You can perform database operations like querying, inserting, updating, or deleting data using SqlCommand and SqlDataReader classes.

That's it! You now have a C# console application connected to a SQL Azure database. You can modify the SQL query to perform any database operations you need.

Note: Make sure to replace the placeholders in the connection string with your actual server name, database name, and login credentials.