Introduction

Today, we are going to create an Azure SQL Database that we are going to use from a C# console application, using Entity Framework Code First approach.

Azure SQL vs SQL Server on a VM

Microsoft Azure offers two methods for using an SQL database:

  • Azure SQL Database – offered in a way that falls between Platform as a Service (PaaS) and Software as a Service (SaaS) – you develop your application using the built-in functionality and features of the database. You have services like logging, monitoring, scaling, geo-replication and you have the ability to pay-as-you-go.
  • SQL Server on a Virtual machine – Infrastructure as a Service (IaaS) – allows you to create an Azure VM with SQL Server installed and you are responsible for managing both the OS and the SQL Server.

The decision behind choosing PaaS or IaaS comes from the purpose and the usage of the database:

  • if the goal is to minimize the cost, the time of setting up and administering and to have a very easy way to scale the database, then Azure SQL may be the right choice.
  • if the goal is to have full control over the SQL Server and to build a fully customized way of handling the Server and the databases, using SQL Server on a VM might be the right choice.

Complete comparison between Azure SQL and SQL Server on a VM

Since our goal is to create a lightweight application and not customize the database, then Azure SQL is appropriate for our application.

Creating the Azure SQL Database

First, we need to create an Azure SQL Database. From Data+Storage, we select SQL Database.

Create Azure SQL Database

Then, we select a name for our database and a create a new server (here you are not creating a full SQL Server, but a way to manage grouped SQL Databases) or select an already-existing one.

Create Azure SQL Database

The name of the server has to be globally unique, while the name of the database has to be unique inside the server.

Next, you select the pricing tier (since this is not in production, I will choose the smallest database available – Basic – 5 DTUs) and various other settings, like collation (see more information about collation), subscription and resource groups.

For more information about SQL performance, visit this resource.

After creating the database, you should be able to edit and modify the properties of the database.

Complete SQL Database

Connecting to an Azure SQL from a Console Application

At this point, you can connect to this database from multiple programming languages (.NET, Node, Java, PHP). We will create a C# console application, so we will use the ADO.NET connection string.

Now we create a new C# console application from Visual Studio.

Visual Studio new Console Application

We are going to create a simple console application that will be able to Create, Read, Update and Delete objects of type Employee from a database (from a database table to be more specific).

We are going to use Entity Framework Code First approach. This means that we will first write the models, then, based on that models, Entity Framework (EF) will create a database with tables based on those models.

public class Employee
{
    public int EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Address { get; set; }
    public int Age { get; set; }
    public double Salary { get; set; }
}

We consider a very basic structure for our Employee class.

Now, we need to install the Entity Framework package using NuGet Package Manager. (For web applications, the package will already be installed)

Right click the solution –> Manage NuGet Packages.
Under Browse, search for Entity Framework and install the first package.

NuGet install EF

Next, we need to connect the SQL database we just created to our console application. We will achieve this with the help of a connection string.

 

We find the connection string for the database in the portal, in the main blade of the database, by clicking Show Database Connection Strings. This will show you all connection strings for connecting to all predefined environments.

For information about blades and an overview of portal.azure.com, see this resource.

Azure SQL Connection String

We will need the ADO.NET connection string. We copy the connection string for ADO.NET and we paste it in the connectionString key-value pair, and paste the code below in the App.config file from our solution.

<connectionStrings>
<clear />
<add name="EmployeeDbConnectionString"
providerName="System.Data.SqlClient"
connectionString="Server=tcp:{your_serverName_here}.database.windows.net,1433; Database={your_databaseName_here}; Connection Timeout=30; Encrypt=True; TrustServerCertificate=False;"
/>
 </connectionStrings>

Notice that your connection string will already have the database name, server and user name configured, you will have to provide your password.

Now we will create a context from which we will use the database. This context will simply be a class that inherits the DbContext class from Entity Framework.

So we have the EmployeeContext class that has a constructor (that actually uses the connection string we put in the App.config – notice that we give it the name of the property as a parameter) and a DbSet<Employee> property. You can think of this property simply as a List<Employee>. The difference is that it will not store Employee objects in memory, but will actually create SQL queries and update the database.

    public class EmployeeContext : DbContext
{
    public EmployeeContext() : base("name=EmployeeDbConnectionString")
    {
    }

    public DbSet<Employee> Employees { get; set; }
}

Entity Framework will generate a table for each DbSet property in the EmployeeContext. (You can add multiple properties in the same context – here we only have one for simplicity)

In order to actually use context we simply instantiate an object of type EmployeeContext where we need it (in our case, we will do this in the Main method).

    static void Main(string[] args)
{
    var employeeContext = new EmployeeContext();

    employeeContext.Employees.Add(new Employee()
    {
        FirstName = "Roger",
        LastName = "Federer",
        Address = "Basel, Switzerland",
        Age = 35,
        Salary = 4800
    });

    employeeContext.SaveChanges();


    foreach(var employee in employeeContext.Employees)
        Console.WriteLine(employee.LastName);
}

After we instantiate an EmployeeContext object, we can start executing CRUD operations (Create, Read, Update, Delete) on our database.

If you follow the example above, we add a new Employee object to our employeeContext, but the changes aren’t committed to the databased (actually pushed and visible to everybody else) until we execute the SaveChanges or SaveChangesAsync method (depending on wether the change will take a lot of time).

This is done because Entity Framework uses transactions. They may not be obvious in our example (due to the simplicity of it), but think of the following example:

You create the back-end for an online banking system. Client1 transfers $ 1 million to Client2. So you need to subtract $1 million from Client1’s account and add $1 million to Client2’s account. This will be done in two operations (since you have to manipulate two accounts).

What happens if you subtracted $ 1 million from Client1, but the system failed to add $ 1 million to Client2?

These two operations have to be completed inside a transaction.

If any of them fails, the transaction fails and neither of them gets executed. This is the utility of SaveChanges and SaveChangesAsync.

Another benefit for transactions is that for every independent
operation on the database, a new connection is opened between the application and the database server. So if you have some consecutive operations, it is much more economic (from the network point of view and not only) to execute all operations at once.

This behavior is best described using ACID Properties.

Similarly, we can execute Delete operations or find entries based on values:

var roger = employeeContext.Employees.Where(e => e.LastName == "Federer");

Another thing to note is that the Employee class has a EmployeeId property, but when adding an object to the database we don’t provide one. This is done because Entity Framework understands this property as a Primary Key and uses it as Identity for the table. This means the database will automatically keep track of the current EmployeeId and increment it accordingly for every new attempt to save in the database.

Viewing the data from Visual Studio

After we are done experimenting with the code, we can take a look at how(and where) the actual data is stored.

We start Server Explorer from Visual Studio (Edit –> Server Explorer).
We expand the Azure region and select SQL Databases.

Server Explorer

We right click the database and select to open it in SQL Server Object Explorer. Then, we expand the database, then the Tables region, then we right click the Employees table and select View Data.

View Data

At this point, we can see all entries in the database, edit or delete them.

SQL Data

We can also manually execute queries on the database that can Create, Read, Update or Delete objects.

SQL Visual Studio query

Conclusion

Our goal was to create an SQL database (in the easiest way) and use it to Create, Read, Update and Delete Employee objects from a console application.

We achieved this using Azure SQL Databases, a C# console application and Entity Framework Code First approach.

Depending on the specific scenario, there are multiple ways of achieving this, so we must have a very good understanding of the implications of every decision when choosing the database and the Entity Framework approach.

From now on, you are able to create (any) C# application using Azure SQL and Entity Framework.