Azure Databases: Getting into doing in 90 minutes

The story behind

Here’s our scenario (and actually the reason why I am writing this):

You’re going to have to build a kind of complex project that requires interaction with data (your own application, a project, a contest or even your own business software, all fit here). After some thinking you realise that it would be inneficient to use files to save the data as your best idea until now was holding data as CSV/XML. You remember that you have a coleague that brags all the time about DataBases and you start reading about it. After half an hour you say “Hey, it’s a good idea! You have fast access to the resources, you really can hold tons of structured data and it does not seem that complicated to do opperations with it”. Now you’re all into creating a DataBase.

 

The decision

As our god, the Internet, is infinite, you notice that there are SO MANY ways of making a Database. (A lot of blah blah, languages, Relational vs Non Relational and such) You realise that you don’t want to spend 10 hours just to read on HOW to create the DataBase, you want to get going as fast as possible. Here comes Azure, letting you build your T-SQL Cluster (A table is contained in a DataBase that is contained in a Cluster). (you can read more about it there, my intention is to get you going as fast as possbile)what you will use for development, until next time!

 

Getting into Azure

About creating an account FREE, here is how you do it (They explain it better than I do, You need to bind a credit card but they won’t tax you if you chose the FREE plans and you can always go to premium if need). After you’re all done we move to azure portal. Below are the image steps: You click on “Create a resource” -> SQL Database -> Create your setup (You can find mine in the pictures) -> Create. And we are all set. Now we wait to for it to finnish and then we are ready to connect to it.

            

Connecting to the DataBase

First things first, we need something called Connection String to get to our Database. For that (no pictures, sorry) you need to click on your newly created datbase  on the Dashboard, but it should already open. Then, on the DB’s menu, you will see a section called “Connection strings”, click it. For “ADO.NET” (maybe I’ll write a blog post about it too, maybe) you’ll se something like this:

Server=tcp:{server_link},1433;Initial Catalog={starting_database};Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Tha is what we will be using to connect to the database, I’ll show you exactly how to do that both on Windows using SSMS and on Linux/Unix using DataGrip. What you’ll see with DataGrip is on an iMac running High Sierra, but the UI is similar. There are many other tools that you can use, but (in my oppinion) SSMS is best for Windows and JetBrains give free premium subscriptions to students and great deals to everyone else, so that is what I’ll advise you to use.

Using SSMS

You need to open SSMS, enter your data as shown bellow, add you Azure account, also add your current IP (if you are using it for business you may want to add an entire range if IPs, rather than 1 by 1) and you’re all SET.

Using DataGrip

You need to open DataGrip -> File -> Data Sources… -> Click on the “+” -> Click on Azure -> Do the Setup as show below (If you are missing a driver click on “Get Missing Driver”) and you’re done.

 

SQL Time

The Setup

 

For the sake of art you can:

  1. Create Tables to your newly created DataBase and add Data to them (takes some time and that it’s not this blog’s purpose)
  2. Restore your DB from a backup, you can do that anytime
  3. Create a new DataBase from a backup (that’s what I did and I used AdventureWorks2017)

The Basics

In the folloing I’m going to show you the basics of SQL, using the AdventureWorks2017 DataBase.

SELECT

Select it’s an instruction that Gets data. I am going to write a kind of complex instruction to show most of the power, but you can always get:

  • ALL Columns (*)
  • Some Columns (You specify their Name comma separated)
  • Data based on some criteria (you use WHERE)
  • Special data, called aggregates (Sum for some columns, min, max, average and so on)
  • Data Grouped
SELECT TOP (1000) [BusinessEntityID]
 ,[Title]
 ,[FirstName]
 ,[MiddleName]
 ,[LastName]
FROM [AdventureWorks2017].[Person].[Person]
WHERE [Title] IS NOT NULL
ORDER BY [LastName]
 ,[FirstName]

Here I wanted to get the TOP 1000 records for the Persons that have a Title and order them by the Family Name and then by their Given Name.

SELECT [ProductID]
 ,SUM([OrderQty])
 ,AVG([UnitPrice])
FROM [AdventureWorks2017].[Purchasing].[PurchaseOrderDetail]
GROUP BY [ProductID]
ORDER BY [ProductID]

Here I wanted to see for all the orders what product was ordered, in what quantity and what was the average price, ordered by the ID of the Product.

JOIN

Join is used with SELECT and it is used to get data from multiple sources in order to gather more complete Data.

SELECT TOP (1000) P.[BusinessEntityID]
  ,P.[Title]
  ,P.[FirstName]
  ,P.[MiddleName]
  ,P.[LastName]
  ,A.[AddressLine1]
  ,A.[AddressLine2]
  ,A.[City]
FROM [AdventureWorks2017].[Person].[Person] AS P
INNER JOIN [AdventureWorks2017].[Person].[BusinessEntity] AS B ON P.BusinessEntityID = B.BusinessEntityID
JOIN [AdventureWorks2017].[Person].[Address] AS A ON A.AddressID = B.BusinessEntityID

Here I wanted to expand my knowledge of Persons and I wanted to figure out their address.

INSERT, UPDATE and DELETE

As their name tells those instructions either INSERT data, or UPDATE data or DELETE data. UPDATE and DELETE SHOULD (i think I should have said MUST) have a condition when they are use because if they do not… (Everything in that table gets updated/deleted)

LEARN MORE SQL

If you are in Bucharest, we’re holding SQL Server classes at Microsoft Accademy. If you are not, W3Schools and, of course, MSDN Documentation.

 

Conclusion

If you want to set up your public DB really fast, you can Use Azure Databases combined with dedicated tools to aid you build your way to success.

 

 

Useful links:

Microsoft Romania – Technical Evangelism

More about Xamarin on My Blog!

About the author

Add a Comment

Your email address will not be published. Required fields are marked *