Use of SQL Azure In Visual Studio 2015 Step By Step Guide

In this article we have to learn all about SQL Azure, it’s very simple to use and also a powerful tool provided by Microsoft.



Microsoft made a revolution in Cloud History by Microsoft Azure. Microsoft does a lot of changes in Azure day by day providing best possibility for every user or developer. In Azure, we have lot of support for database such as NoSQL, SQL Server and more. In Microsoft Azure we can use SQL database by the following two ways:

1. Microsoft SQL Server
   
   We can use Microsoft SQL server in Microsoft Azure Virtual Machines (Azure VM) which is used as IAAS (Infrastructure as a Service) where we have full access by RDA (Remote Desktop Access) on this VM where we can use any Microsoft SQL Server.
   
   
2. SQL Azure
   
   Sql Azure in the second option to use Sql Database on cloud which behave like PAAS and it’s very simple or easy to access by web portal of Microsoft Azure or in Visual Studio Azure SDK.
   
   

What is SQL Azure Database?

In short, SQL Azure database is simply a way to get connected in Cloud Services where we can store our database into Cloud. Microsoft SQL Services and Microsoft SQL Data Services are now known as Microsoft SQL Azure and SQL Azure Database. Microsoft Azure is the best way to use PAAS (Platform as a Service) where we can host multiple database on the same Account.



Microsoft SQL Azure has the same feature of SQL Server, i.e. high availability, scalability and security in the core.

Built-in Automatic Backup in Microsoft Azure SQL Database

Microsoft Azure SQL Database have a best feature, it automatically creates backups of every active database. Every hour a backup is taken and geo-replicated to enable the 1 hour recovery point objective (RPO) for Geo-Restore. Additionally, transaction log backups are taken every 5 minutes to enable Point in Time Restore.

Backup Storage

As per MSDN:

"Backup storage is the storage associated with your automated database backups that are used for Point in Time Restore and Geo-Restore. Azure SQL Database provides up to 200% of your maximum provisioned database storage of backup storage at no additional cost. For example, if you have a database in the Standard service tier with a provisioned size of 250 GB, you will be provided with 500 GB of backup storage at no additional charge. If your database exceeds the provided backup storage, you can choose to reduce the retention period by contacting Azure Support or pay for the extra backup storage billed at standard Read-Access Geographically Redundant Storage (RA-GRS) rate. For more information on RA-GRS billing, see Storage Pricing Details."

Now let’s see how to create SQL Azure Step by Step

In your Azure Account Portal you have a link to use your Sql Databases on SQL Azure, so go to the Link.

Login now and go to portal Link where you can see Sql Database,



If you click on this section, you’ll find all the already created databases and will get the link to go to servers, every database exist in any physical server, but for SQL Azure we don’t need any hardware or software to use that because it’s a PAAS on Cloud Services provided by Microsoft Azure but still you have to create a server where you want to use your datacentre or replica of your database. So first we want to create a server, now let’s see how to create Data Server, but one thing you should remember, we don’t have RDA (Remote Desktop Access) permission on this server we can use this database in our Visual Studio or in our Microsoft SQL Server Management Studio so that we can create table and can execute our .sql script file for database management. So finally in this article we’ll learn all the things step by step which is divided into the following seven parts:

1. How to Create Servers in Microsoft Azure.
2. How to Configure Server for Client access by IP Addresses.
3. How to create Database on specified Server.
4. How to get the connection strings.
5. How to Use SQL Azure in Visual Studio.
6. How to Use SQL Azure Table for CRUD Operations in ADO.Net.
7. How to Use SQL Azure Table for CRUD Operations by Entity Framework.

So I hope you’ll enjoy this article, now it’s time to do everything by yourself, firstly login into your Microsoft Azure Account if you don’t know more about Microsoft Azure. So read my last article where we learned how to start with Azure link:

• Getting Started With Microsoft Windows Azure

Now let’s see How to Create Servers in Microsoft Azure

We need a server first to create any Sql DB to host on SQL Azure, so click on SQL Databases link in main menu and get all the database already created and on the top there is a link. Servers find that link as I have shown in the following image:



After clicking on Servers, you’ll get all the server which already running on the cloud service. Now the next step is find the button Add to add or create a new Server as inthe following image.



Now fill this form to set all the necessary details for a server like Login Name, Password, Confirm Password and region for selection of Datacentre and after filling all the information press enter to submit all the details and then that will create a server by a logical server name. It will be a combination of alphabetic and numeric digits which we have to use to create a database.



After submitting all the information about our server it will take a few seconds to create it.



After that now server is ready to use with a logical name but if you use this server to host your database, you don’t have permission to use on any client IP, so we need to configure this server for specified client IP addresses and go to the next step.



Now let’s see How to Configure Server for Client access by IP Addresses

Every server is a must be configure for every client system IP address, so let’s see, first click on the server logical name to open the server dashboard or configuration sections. Now open the configuration section, so click as per the following screenshot:



In this section there is an option to add details for every client where you need to login this server. Add all IP addresses with the rule name and you want to access the same system, so there is directly a link to add current system in the list as in the following image.



Now your system and all those systems are able to get connected with this server and those IP address is added in the list.



At last, just save the setting which changed before leaving this section or creating new database.

Now let’s see How to create Database on specified Server

Now the time is to create a SQL Azure Database on cloud as PAAS, so now find the +New Button on the left side bottom in your Web portal of Azure and click that.



Now go to Data Services, then SQL Databases and Quick Create or Custom Create.



If you select Quick create, you have to enter only the name of database and select the server by the logical name of the server and click “Create SQL Database” button.



If you select Custom Create, you have many options to fill or select Database Name, Subscription Type, Max Size and Server Name and then click ok.



After submitting all the details it takse few seconds to be ready for use and you can see the progress in the Progress bar.



Now your server is ready to use from your Visual Studio or SQL Server Management Studio.



Now let’s see How to get connection Strings

Now the most important part is if you want to connect this database to store your data as per need in any Application by any framework or technologies like in php or java and more. So the first thing which we need is Connections String to connect, so let’s see how to get the connection string. Firstly, go to the Dashboard of the database.



In the Dashboard section there is an option in quick glance “Show Connection Strings” and below that there is the server name by which you have to connect in Visual Studio Server Explorer. So click on the link of Connection Strings.



Now you will see the window of connection string where we have all maximum connection string for every developer for every technology ADO.Net, JDBC, PHP and ODBC, so you can use it anywhere just add password in the connection string.



Now let’s see How to Use SQL Azure in Visual Studio

Now let’s see how to use SQL DB to your Visual Studio Server Explorer or may be in SQL Server Management Studio. Open your visual studio first.



Open Server Explorer to connect SQL Azure Database.



Right click on Data Connections and add Connections, then there is a window, fill all the details and copy the Server Name from Database Dashboard in Azure web portal and paste in this window. Now enter the user name which was entered by you at the time of server creation like “ServerUserLoginName@ServerLogicalName” and password. Select your database name from the list and click ok or Test Connection button if you want to check your connection is valid or not.



This is the same process for Microsoft SQL Server Management Studio, the people who don’t have Visual Studio like DBA, they can use there .sql script file over there.



So let’s see with Visual Studio I connect my Azure Database server in the Server Explorer.



Now if you want to add a new table, right click on Tables and find two options Add new Table and New Query. Select any one.



If you want to create table by query, select New Query or if I want to add by wizard, click on Add new Table and then you get a window on your screen where you have to add columns and add constraint if required. After writing your query or adding columns, just click on Update Button. It will confirm and save the schema.



In this window we have two options: Click Generate Script if you want to create Script file and it will save automatically in the solution or any specified address. The second option is to Update Database if you want to update all schema without saving query file, it will update SQL Azure Database with new Schema.



You can see the update of progress in the Data Tools Operations.



Now open server explorer and refresh connection, now you can see your table in Tables options.



Now use this table anywhere with the connection string that we get by our Microsoft Azure portal.

Now let’s see How to Use SQL Azure Table for CRUD Operations in ADO.NET

If you want to connect this use your SQL Azure table in any application such as Windows Form, WPF Application, Console Application or any web application. This is the same process so I am using a web application project in Visual Studio. Open Visual Studio, File, New Project, then select Web and click ASP.NET Empty Web Application.

Give a name and location and press OK.



Now I have an empty Web Application Solution, so add any page to show the data or use CRUD operations in SQL Azure Table. I just added a page default.aspx to do that.



Now add a new Connection String into Connections String Sections in your web.config, copy connection string from Azure Web portal and paste with password in Web.config like the following image:



Use some control like GridView and other control like TextBox and Button event.



I just use a simple code of ADO.NET with SqlClient provider, on the button I run command and if it runs successfully, I called a function where I bind the grid from my azure SQL Client.

Code:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public void BindGrid()
{
    SqlDataAdapter da = new SqlDataAdapter("Select * from Employee", ConfigurationManager
        .ConnectionStrings["MyDB"].ConnectionString);
    DataSet ds = new DataSet();
    da.Fill(ds);
    GridView1.DataSource = ds.Tables[0];
    GridView1.DataBind();
}
public int RunCommand(string Query)
{
    SqlConnection con = new SqlConnection(ConfigurationManager
        .ConnectionStrings["MyDB"].ConnectionString);
    SqlCommand com = new SqlCommand(Query,con);
    con.Open();
    int res=com.ExecuteNonQuery();
    con.Close();
    return res;
}
protected void Button1_Click(object sender, EventArgs e)
{
    string qry = "Insert into Employee Values ('" + TextBox1.Text + "','"
        + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "')";
    if (RunCommand(qry) > 0)
    {
        BindGrid();
    }
}

Here's the output



Now let’s see How to Use SQL Azure Table for CRUD Operations by Entity Framework Database First approach

In the last point of this article we’ll learn how to use SQL Azure Table in your Application with Entity Framework code first approach. So let’s start, just create a basic empty application as the previous point.



In Empty Solution, firstly, add an ADO.NET Entity Data Model.



Give a name to your model and click OK.



In this window, select Generate from database and click Next.



Now make a connection with SQL Azure Data server to ADO.NET Entity Model.



Enter all the details as per the previous point where we discussed how to connect in Server Explorer.



Click Next if the Next Button is enabled and if not, select a Radio Button, which is “yes include the sensitive data to connection string” and give a name to your Entity class name. Name it anything you want like I just gave a name MyDB. After that click next.



Now select how many tables you want, select them and press Finish Button.



Now your Model and Entity File is ready to use.



Again create a view for the same as before for performing the CRUD operations on Entity model.



On the button click, I just created the object of my entity class and added this into entity collection and call the “SaveChange()” function of DbSet Class to save or update all the data to SQL Azure Table to live data.

Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace TestSQLAzureByEF
{
    public partial class _default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                Bind();
        }
        MyDB db = new MyDB();
        private void Bind()
        {
            GridView1.DataSource = db.Employees.Select(p=>p).ToList();
            GridView1.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Employee emp = new Employee()
            {
                Id=Convert.ToInt32(TextBox1.Text),
                Name=TextBox2.Text,
                Salary=Convert.ToInt64(TextBox3.Text),
                City=TextBox4.Text
            };
            db.Employees.Add(emp);
            db.SaveChanges();
            Bind();
        }
    }
}

Output:

Initial output:



After button click:



After that go to Server Explorer, right click on table and show the table data.



Here's the data in table in Visual Studio.



I hope you enjoyed this article and thanks for reading this article.