Simple C# Application to retrieve data from Azure SQL Database

By: Daniel Calbimonte | Updated: 2022-01-20 | Comments | Related: > Azure SQL Database

Problem

Is it possible to use C# to connect to Azure SQL Database? Yes it is and in this article we cover the steps to create a simple C# console app and connect it to Azure SQL Database.

Solution

In this article we cover the steps to create a simple app to connect to Azure SQL Database using C#.

Requirements

The following are used for this example walk through.

A machine with Visual Studio (VS) with any supported version.
An Azure Account to create Azure SQL Databases

Getting Started

This article includes the following:

Creating an Azure SQL Database
Using Visual Studio, create an application and retrieve data from Azure SQL Database

Create an Azure SQL Database

We will first create an Azure SQL database.

Go to Create a Resource.

In Databases go to SQL Database and press Create.

After clicking Create, you will get the screen below and will need to enter the following:

You need to select the subscription. You might have an individual subscription or use your company’s account.
The resource group is just a storage group. If you do not have one, you can create one.
The database name is the name that will be used for the Azure SQL Database. In this example the database name is MSSQLTIPS.
For the Server, you can choose an Azure SQL Server if you have one, but if not, you need to create a new one.
The Compute + storage is the resource settings such as vCores and memory.

If you do not have a Server, you will need to select the Create new option. You need to specify the Server name which cannot already exist and a location.

In Authentication, you can use the Azure Active Directory or SQL Authentication or both. We will use SQL Authentication.

Specify your Server admin login name and password and the press OK.

Then you will need to press the Review + Create button to create the Azure SQL database.

Once created, you will receive a successful creation message. Alternately, you can use the search text box to search for Azure SQL resources.

You will have 2 resources. The server whose name is mssqltipsserver in this example, but the name can be any name you chose when creating the Azure SQL Server. The other resource is the database.

If you click on the Azure Server, you will see the administrator’s name which was created when we created the Azure Server. We also have a Show firewall settings that is really important if you want to access the Azure SQL Database. Click the Show firewall settings link.

The Portal will detect your current IP and then press the Add client IP to add your current IP and then press save. This option will allow our local Visual Studio to access the Azure SQL Database.

Now, click on the Azure SQL Database. Check the Azure Server name which will be used in C# to connect and then press the Query editor to write some T-SQL commands.

The Portal will ask for the login and password of the SQL user created.

On the query editor, we will create a table named dbo.location and insert data by running this SQL code.

CREATE TABLE [dbo].[Location](
   [LocationID] [smallint] IDENTITY(1,1) NOT NULL,
   [Name] varchar(500),
   [CostRate] [smallmoney] NOT NULL,
   [Availability] [decimal](8, 2) NOT NULL,
   [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Location_LocationID] PRIMARY KEY CLUSTERED 
(
   [LocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Location] ON 
 
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (1, N'Tool Crib', 0.0000, CAST(0.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (2, N'Sheet Metal Racks', 0.0000, CAST(0.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (3, N'Paint Shop', 0.0000, CAST(0.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (4, N'Paint Storage', 0.0000, CAST(0.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (5, N'Metal Storage', 0.0000, CAST(0.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (6, N'Miscellaneous Storage', 0.0000, CAST(0.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (7, N'Finished Goods Storage', 0.0000, CAST(0.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (10, N'Frame Forming', 22.5000, CAST(96.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (20, N'Frame Welding', 25.0000, CAST(108.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (30, N'Debur and Polish', 14.5000, CAST(120.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (40, N'Paint', 15.7500, CAST(120.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (45, N'Specialized Paint', 18.0000, CAST(80.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (50, N'Subassembly', 12.2500, CAST(120.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))
INSERT [dbo].[Location] ([LocationID], [Name], [CostRate], [Availability], [ModifiedDate]) VALUES (60, N'Final Assembly', 12.2500, CAST(120.00 AS Decimal(8, 2)), CAST(N'2008-04-30T00:00:00.000' AS DateTime))

Working with C#

We previously created an Azure SQL Server, an Azure Database with a login and a password. The table that we will access is dbo.location, however, you can modify the query to any table that you have.

In visual Studio, select New > Project.

We will create a Console application to retrieve the data.

In the application, we will add the following code to retrieve data.

static void Main(string[] args)
    {
        try
        {
            SqlConnectionStringBuilder conn = new SqlConnectionStringBuilder();
            //Azure SQL Server Name 
            conn.DataSource = "mssqltipsserver.database.windows.net";
            //User to connect to Azure
            conn.UserID = "admindaniel";
            //Password used in Azure
            conn.Password = "mypws@#&*234!";
            //Azure database name
            conn.InitialCatalog = "mymssqltips";

            using (SqlConnection connection = new SqlConnection(conn.ConnectionString))
            {

                //Query used in the code
                String sql = "SELECT name,costrate,availability from dbo.location";
                //Connect to Azure SQL using the connection
                using (SqlCommand sqlcommand = new SqlCommand(sql, connection))
                {
                    //Open the connection
                    connection.Open();
                    //Execute the reader function to read the information
                    using (SqlDataReader reader = sqlcommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {   
                            //Read information from column 0,1 and 2. Column 0 is string and column 1 and 2 are decimals
                            Console.WriteLine("\t{0}\t{1}\t{2}", reader.GetString(0), reader.GetDecimal(1), reader.GetDecimal(2));
                        }
                    }
                }
            }
        }
        //If it fails write the error message exception
        catch (SqlException e)
        {
            //Write the error message
            Console.WriteLine(e.ToString());
        }
        Console.ReadLine();
    }
}

Code Explanation

The overall code is inside a try...catch to handle errors.

Then we provide the Azure SQL Server name, the SQL Azure administrator user for the connection and the password. In the initialCatalog, we specify the Azure SQL Database name.

SqlConnectionStringBuilder conn = new SqlConnectionStringBuilder();
//Azure SQL Server Name 
conn.DataSource = "mssqltipsserver.database.windows.net";
//User to connect to Azure
conn.UserID = "admindaniel";
//Password used in Azure
conn.Password = "mypws@#&*234!";
//Azure database name
conn.InitialCatalog = "mymssqltips";

We then connect and send the query to retrieve data. This query is a SQL statement to read the dbo.location table.

using (SqlConnection connection = new SqlConnection(conn.ConnectionString))
                {
 
                    //Query used in the code
                    String sql = "SELECT name,costrate,availability from dbo.location";
                    //Connect to Azure SQL using the connection
                    using (SqlCommand sqlcommand = new SqlCommand(sql, connection))

We, open the connection and then read the data using SqlDataReader. We are displaying in the console the column 0, 1 and 2 of the query which are the name, costrate and availability.

connection.Open();
//Execute the reader function to read the information
using (SqlDataReader reader = sqlcommand.ExecuteReader())
{
    while (reader.Read())
    {   
        //Read information from column 0,1 and 2. Column 0 is string and column 1 and 2 are decimals
        Console.WriteLine("\t{0}\t{1}\t{2}", reader.GetString(0), reader.GetDecimal(1), reader.GetDecimal(2));
    }
}

The catch section is just to display error messages if there are any.

catch (SqlException e)
{
    //Write the error message
    Console.WriteLine(e.ToString());
}

Now we can run the C# application and you should be able to connect and retrieve the data.

Next Steps

For more information refer to the following:

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-01-20

C# Application for Azure SQL Database