Interfacing with Azure SQL Server

For one of the many many side projects I am working on, I wanted to implement a database for a list of music associated with their dance style (Waltz, Tango, etc). This was the perfect opportunity to learn about creating an Azure database and connecting to it in various ways. I don’t like writing direct step by step how to’s because they are typically obsolete before they are published. I like to provide the progress and any pitfalls I found along the way.

One thing that I’ve found about ASP.Net Core is that the documentation, at least at this moment in time, is all over the place. There have been so many different versions that the documentation is scattered between all of them. It also doesn’t help that a lot of the documentation uses selections from Visual Studio. Which is great once you understand what it is doing, but I like to do things the hard way first so that I know what the automated stuff is doing. This way when things go wrong I have an understanding of why it failed.

I have been doing this all from my Chromebook using VS Code. I’m sure there is an easier way to execute SQL statements on the Azure SQL Server instance, however, I found this way and stuck with it for now. I have the Azure VSCode extensions installed that makes it easy to connect and monitor the Azure Databases and other Azure products

The first thing I needed to do was create the tables for my database. I don’t know much about optimizations or database architecture, but this is pretty simple and doesn’t have to be fancy.

-- Create a new database called 'DanceMusic'
-- Connect to the 'master' database to run this snippet
USE master
GO
-- Create the new database if it does not exist already
IF NOT EXISTS (
   SELECT name
       FROM sys.databases
       WHERE name = N'DanceMusic'
)
CREATE DATABASE DanceMusic
GO
–Create this schema first
CREATE SCHEMA DanceMusic
– Create a new table called 'DanceStyles' in schema 'DanceMusic'
– Drop the table if it already exists
IF OBJECT_ID('DanceMusic.DanceStyles', 'U') IS NOT NULL
DROP TABLE DanceMusic.DanceStyles
GO
– Create the table in the specified schema
CREATE TABLE DanceMusic.DanceStyles
(
DanceStylesId INT IDENTITY(1,1) PRIMARY KEY, – primary key column
Style [NVARCHAR](256) NOT NULL
);
GO
– Insert rows into table 'DanceStyles'
INSERT INTO DanceMusic.DanceStyles
( – columns to insert data into
Style
)
VALUES
( – first row: values for the columns in the list above
'Waltz'
)
GO
– Create a new table called 'Music' in schema 'DanceMusic'
– Drop the table if it already exists
IF OBJECT_ID('DanceMusic.Music', 'U') IS NOT NULL
DROP TABLE DanceMusic.Music
GO
– Create the table in the specified schema
CREATE TABLE DanceMusic.Music
(
MusicId INT IDENTITY(1,1) PRIMARY KEY, – primary key column
Title [NVARCHAR](256) NOT NULL,
Artist [NVARCHAR](256) NOT NULL,
DanceStylesId INT FOREIGN KEY REFERENCES  DanceMusic.DanceStyles(DanceStylesId)
– specify more columns here
);
GO
– Insert rows into table 'DanceMusic.Music'
INSERT INTO DanceMusic.Music
( – columns to insert data into
Title, Artist, DanceStylesId
)
VALUES
( – first row: values for the columns in the list above
'Tennessee Waltz', 'Patti Page', 1
),
( – second row: values for the columns in the list above
'Take it to the limit', 'The Eagles', 1
)
– add more rows here
GO
– query examples
SELECT Music.Title, DanceStyles.Style from DanceMusic.Music
INNER JOIN DanceMusic.DanceStyles
ON Music.DanceStyleId=DanceStyles.DanceStylesId
Select DanceStyles.DanceStylesId FROM DanceMusic.DanceStyles Where DanceStyles.Style='Waltz'
GO

Populating the Database

Once the tables were created, I needed to add my CSV of dance music to the database. I used NodeJS to do this part because it is quick to develop. To access the database, use the tedious module which is the MS recommended package to use. First it loads the CSV using the CSV module. Then it loops through the songs and builds the style set to be added to the DanceStyles table. After the DanceStyles table is populated, the DanceMusic table can be populated with the foreign key for the DanceStylesId set to the correct style. I set up the NodeJS project to query all of the styles, and grab the assigned ids. Great! All of the data is now in the database. How do we use it? I already had the code to make queries in NodeJS. I can use the sql commands from the VS Code Execution.

Integrating with ASP.Net Core

The goal is to integrate this database into my asp.net core web project. I started with a simple class that used the Microsoft.Data.SqlClient NuGet package.

With this library you can create a basic SqlConnection and execute SqlCommands directly. You can then execute basically any SQL statement and parse the response. The response back is a reader that reads each row at a time.

string sql = $"SELECT Artist, Title FROM DanceMusic.Music WHERE DanceStylesId={style_id}";
using (SqlConnection connection = new SqlConnection(_connection_string))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        using (SqlDataReader reader = await command.ExecuteReaderAsync())
        {                           
            var song_list = new List<Song>();
            while (reader.Read())
            {
                song_list.Add(new Song(){Artist=reader.GetString(0), Title=reader.GetString(1)});
            }
            return song_list;
        }
    }                    
}

I refactored that code a bit to encapsulate the connection code

private static async Task<R> ExecuteDatabaseAction<R>(string sql, Func<SqlDataReader, R> callback){
    try{
        using (SqlConnection connection = new SqlConnection(_connection_string))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                using (SqlDataReader reader = await command.ExecuteReaderAsync())
                {                           
                    return callback(reader);
                }
            }                    
        }
    }
    catch{
        return default(R);
    }
}
public Task<List<Song>> GetSongsByStyleId(int style_id){
    string sql = $"SELECT Artist, Title FROM DanceMusic.Music WHERE DanceStylesId={style_id}";
    return await ExecuteDatabaseAction<List<Song>>(sql, (reader)=>{
        var song_list = new List<Song>();
        while (reader.Read())
        {
            song_list.Add(new Song(){Artist=reader.GetString(0), Title=reader.GetString(1)});
        }
        return song_list;
    });
}

Using this basic connection I was able to implement the end point to test the SQL query and send back a JSON response of my data! However it was clunky, and not really ideal since it requires sending SQL strings that have to be handled correctly.

Entity Framework Core

I have read about Entity Framework Core in the past and so I went to dig into this and give it a try. It seemed to be the preferred way to access database objects. This lets .Net handle the translation from a database object to a C# object.

A lot of the examples assumes you are starting from the C# side defining the objects and then using that to migrate the tables and columns to a table. I wanted to go the other way, connecting the C# side to existing tables and columns. Add the Microsoft.EntityFrameworkCore.SqlServer NuGet package, or equivalent for your backend database server type. It turns out to be pretty straight forward. Add the DataAnnotations annotations to the C# classes I wanted to represent the database rows. The next part was defining the DBContext. I once again found documentation that differed on how to configure them. Class itself is again not complex after finding the bare minimum.

In the ConfigureServices method of the Startup class, add the DBContext for use by dependency injection. It took me a while to understand how the dependency injection system works. When the DBContext is added to the service collection, it can then be used from other parts of the code, such as the Controllers. If the controller constructor has a parameter that matches a dependency, it will be passed in automatically.

services.AddDbContext<Contexts.DanceMusicDbContext>(options=>{
    var builder = new SqlConnectionStringBuilder();
    builder.DataSource = "[url].database.windows.net"; 
    builder.UserID = "[userid]";            
    builder.Password = "[password]";     
    builder.InitialCatalog = "[database]";
    builder.Encrypt=true;
    options.UseSqlServer(builder.ConnectionString);
});

Getting the DBContext through dependency injection. This happens magically.

        public DanceSequenceController(DanceMusicDbContext dance_db, DanceSequenceService dance_service, IWebHostEnvironment env, IHubContext<DanceSequenceUpdateHub> real_time_hub)
        {
            _env = env;
            _vote_update_hub = real_time_hub;
            _dance_serivce = dance_service;
            _dance_db = dance_db;
        }

The DBContext makes it simple to get data from the database using the LINQ syntax. I heard about the Fluent API syntax, but I haven’t gotten that far yet. I wanted the database query to be asynchronous so I wrapped them in a Task. It wasn’t quite clear to me whether or not there is a way to use LINQ with an await, but I knew I could do it this way with the task.

public Task<List<Song>> GetSongsByStyleId(int style_id){
  var task = new Task<List<Song>>(()=>{
      return _dance_db.Songs.Where(song=>song.DanceStylesId == style_id).Cast<Song>().ToList();
  });
  return task;
}

During initial testing, I was using the SqlConnectionBuilder to build the connection string required to connect to the database. This included hardcoding the username and password which is not acceptable. To make it safe and deployable to the Azure app service, there is a programmatic way to access the connection string from the environment. Again throughout the documentation there are multiple ways of storing and getting connection strings. There are several different files and locations, and classes for accessing.

dotnet user-secrets init  
dotnet user-secrets set ConnectionStrings:[Name] “Value”

If you are on linux and you happen to have special characters in your connection string, use single quotes instead of double quotes.

dotnet user-secrets set ConnectionStrings:[Name] ‘Value’

Took me some googling to finally figure that out. Using escape characters with the double quotes make it store, however the \ remains in the string. Single quotes works without the need of escape characters

I noticed a difference between the SqlConnectionStringBuilder and the Connection string from the Azure configuration page for the database. The DataSource and the Server parameter are equivalent. Apparently there are several parameters that have synonyms. Just to add to the confusion.

services.AddDbContext<Contexts.DanceMusicDbContext>(options=>{
    options.UseSqlServer(Configuration.GetConnectionString("[Name]"));
});

All there is left to do is deploy it and add the connection string to the app service configuration page. Make sure you click the save button after adding the string. The app will then reload and magically read the string just like in the local development environment.

Using sqlcmd

I wanted a way to access the database from a command line, but when I was getting things going, I was content with just executing the sql commands from VS Code. Somewhere along the line I must have installed Microsoft Sql Server because I have access to sqlcmd. This allows you to connect to the Sql Server database and make queries. Though it does not seem intuitive at first.

sqlcmd -S [serverurl].database.windows.net -d [database] -U [username] -P [password]

Once logged in you should be greeted with 1> You can list the tables with the command

select * from sysobjects where xtype='U'
go

From here it becomes a matter of knowing how to use the SQL Server style of SQL. It seems like every statement ends in a go. I’m glad I figured this out, because now I can play around with making new databases, and continue my learning journey.