C# Application for Azure SQL Database

By:   |   Updated: 2022-01-20   |   Comments   |   Related: > Azure


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

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.

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

Getting Started

This article includes the following:

  1. Creating an Azure SQL Database
  2. 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.

create an azure resource

In Databases go to SQL Database and press Create.

SQL Database 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.
Azure SQL options

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.

azure sql credentials

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

Search azure 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.

Azure sql server and 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.

azure admin name and firewall 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.

azure add client ip

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.

azure server name and Query editor

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

Azure login information query editor

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.

File new project Visual Studio

We will create a Console application to retrieve the data.

Console application in c#

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:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips


Article Last Updated: 2022-01-20

Comments For This Article





download














get free sql tips
agree to terms