Dotnet Galaxy: Deploy ASP.NET web application in cloud with SQL Azure Connectivity

Today I am taking you to the complete deployment of an ASP.NET web application with database connectivity in Microsoft Azure. Since database is in cloud we have to do some configuration changes while doing the deployment. In this article we will do the sample development in local system and we will do only the deployment in Microsoft Azure. As you are aware once deploy the application in Microsoft Azure by using SQL Azure as database everything will be online and people will be able to access the application over the net.
Lets go through the complete procedure you have to follow to do the web application deployment with SQL Azure.

First we will create a simple table and a stored procedure to use it in the web application.
Below is the create table SQL script. You need to make sure that your table must have a primary key. SQL Azure doesn’t allow you to have table without primary key.

CREATE TABLE [dbo].[tblCustomerInfo](

    [CustomerID] [numeric](18, 0) NOT NULL,

    [CustomerName] [varchar](100) NULL,

    [CustomerAddress] [nvarchar](500) NULL,

    [City] [varchar](50) NULL,

 CONSTRAINT [PK_tblCustomerInfo] PRIMARY KEY CLUSTERED

    [CustomerID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now we will create a simple stored procedure to fetch the data from this table.

CREATE PROCEDURE [dbo].[sp_GetCustomerInfo]

AS

BEGIN

    SET NOCOUNT ON;

    SELECT * from tblCustomerInfo

END

GO

Below Screen print help you to see my database details.

Now we will go to ASP.NE web application development sample to display the data from the above table using stored procedure. Here I am creating a simple ASP.NET web application.
I am using a GridView to display the data nothing much. So you just need to drag one GridView.
Below is the whole page design,

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Customer.aspx.cs" Inherits="SQLAzureDemo.Customer" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <table class="auto-style1">

            <tr>

                <td>

                    <asp:Label ID="Label1" runat="server" Text="Customer Details"></asp:Label>

                </td>

                <td>&nbsp;</td>

            </tr>

            <tr>

                <td>

                    <asp:GridView ID="GridView1" runat="server">

                    </asp:GridView>

                </td>

                <td></td>

            </tr>

            <tr>

                <td>

                    <asp:Label ID="lblMsg" runat="server"></asp:Label>

                </td>

                <td></td>

            </tr>

        </table>

    </div>

    </form>

</body>

</html>

Now we will write the code to fetch the data from the database.
First we will have web.config entry for connection string.

  <connectionStrings>

    <add name="SQLConnection" connectionString="Data Source=&lt;ServerName>;Initial Catalog=DemoAzure78;Integrated Security=False;User ID=<userid>;password=<password>;"

      providerName="System.Data.SqlClient" />

  </connectionStrings>

Below will the whole code to display the data from Database to Gridview,

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

namespace SQLAzureDemo

    public partial class Customer : System.Web.UI.Page

        protected void Page_Load(object sender, EventArgs e)

            DataSet dsCustomer = GetCustomerList();

            if (dsCustomer.Tables[0].Rows.Count > 0)

                GridView1.DataSource = dsCustomer;

                GridView1.DataBind();

            else

                lblMsg.Text = "Oops... No record Exits in Cloud!!!";

        public DataSet GetCustomerList()

            DataSet objDataSet = new DataSet();

            SqlDataAdapter objDataAdatpter;

            string strSQLconstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnection"].ToString();

            SqlConnection objConnection = new SqlConnection(strSQLconstring);

            SqlCommand _Command = new SqlCommand("sp_GetCustomerInfo", objConnection);

try

                if (objConnection.State == ConnectionState.Closed)

                    objConnection.Open();

                _Command.CommandType = CommandType.StoredProcedure;

                _Command.Prepare();

                objDataAdatpter = new SqlDataAdapter(_Command);

                objDataAdatpter.Fill(objDataSet);

            catch (Exception ex)

                String sqlError = "SQL Query failed: " + ex.Message;

            finally

                objDataAdatpter = null;

                _Command = null;

                objConnection.Close();

            return objDataSet;

We will just run the application and see everything works fine or not,

So everything is ready now and we will start the steps to deploy this in Microsoft Azure.

Once you click on the Publish it will open a pop up named “Publish Web”. Since we are doing this deployment first time we have to provide the publish profile. There are two options for you here, either you can create a new profile selecting new profile option from the dropdown or you can import the publish profile from cloud directly.
You may use first option for local deployment. In our case since this is a new deployment we will click on Import button and create a new one in cloud.

Once you click on the Import button it will open up another pop up widow to select/create the import publish profile
Click on the dropdown to select the existing website in case if you want to deploy it on the existing site, otherwise click on the new button

Once you click NEW button you will find an option to enter Site name, select the database name and also you have to enter the password. After entering all the required details click on the Create button.

Once you click on the Create button you will be redirected to the Connection tab with all the required details auto filled.
You can now see that all the information has been populated automatically on all the fields, you are not supposed to change anything in this window as it is directly connected with your Microsoft Azure account and the sites you created, we just need to click next button to proceed with the database configuration.
In next screen we can select the database we have used in the application. Cloud configuration is smart enough to find the connection string and the database name from the connection string we are using for the application. Just click on the ApplicationDbContect dropdown to find the database name. Selecting the database will add the complete connection string in that field.
Make sure to check the “Use this connection string at runtime (Update destination web.config)” so that while doing the deployment in Azure system will automatically replace the connection string with the SQL Azure connection string.
Once you click on the SQLConnection dropdown you will find the same database “DemoAzure78”, and also the new Database created in cloud when we created the site.
Select the new database created in cloud “sqlazuredemodotnetgalaxy795”.

When you select the newly created cloud database it will add the cloud connection string automatically. Click on Next button to display the preview.

Click On Start Preview to show the preview of files which are going to deploy in Microsoft Azure.

Preview will show all the files if it is for the first time and it show only the changed files if it is for the re-deployment.

You can click on the “Preview Database” to see the deployment script for the SQL database deployment.

Close the preview and then Click On Publish button to Publish the website with the Database. You can see that status of the publish in your visual studio, once it completes successfully site will be opened in your default web browser.

One thing you have to remember in this case data will not be transferred along with the deployment. You have to enter the data manually.
In case you are looking for a database migration with data then you have to migrate the whole database from SQL server directly.
Now in our case I will be adding the data manually in the database what I have entered earlier and will see whether data shows in the site or not. If you wanted to know how to connect SQL Azure database and enter the data you may read my earlier article “How to create Database in SQL Azure”.
I am just entering few data in the database,