Integrate Azure SQL Database With .NET Website

Romil Bheda
8y
3.7k
0
3

Article

Using Visual Studio 2013 or 2015, we will create website or front-end. For backend, Azure SQL Database is used.

Prerequisites

Microsoft Azure Subscription (MSDN subscribers or sign up for one month free trial)
Visual Studio 2013 or Visual Studio 2015. To download the web installer or ISO file, please click here.

You will learn:

How to create SQL Server
How to create SQL Database
How to connect SQL Database to Visual Studio

Getting Started

To learn how to create SQL database on Microsoft Azure. Please refer to the below article:

Getting Started with SQL Database on Microsoft Azure

Connect with Visual Studio

Step 1: Click on “All resources” & choose MySQL Server.

Note: If you do not see the option for Firewall on the blade you are looking at, go back and make sure that you are looking at the blade for the SQL Database Logical Server and not the blade for a SQL database.

Step 2: Click on “All Settings” option & select “Firewall” from the list. Add client ip to the firewall rules & save it.

Step 3: Now, select MySQL Database & click on “Show database Connection strings”. All connection strings are available here, like, ADO.NET, ODBC, PHP, JDBC.

Step 4: Start Visual Studio 2013 or 2015. Open Server Explorer (if not available select View Menu -> Server Explorer),

Right click on Data Connections & select “Add Connection…” option

Step 5: Choose Data source as Microsoft SQL Server. Copy Server name from Azure Portal, Enter SQL Server Authentication – Username & Password. Within a few seconds, database name will show in the drop menu.

Step 6: After a successful connection, we need to create Table. Right click on Tables folder & select “Add New Table” option.

Step 7: First, click on Id column & change the Identity to True value.

Complete Table Definition, available below.

Table

Update Database.

Just right click on Tables folder & select refresh option to get new table.

Step 8: Create New Project. Select Visual C# as a Template & Create ASP.Net Empty Web Application.

Step 9: In Empty Web Application, there are no pages or forms available. So, please right click on Project Name -> Add -> Web Form.

Enter Page Name.

Step 10: Open Index.aspx file & add below code.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index.aspx.cs" Inherits="SQL_ASPDOTNET.index" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Azure SQL Database - Sample</title>
<style type="text/css">
.style1 {
color: #FF0066;
text-decoration: underline;
font-weight: bold;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<table align="center" cellpadding="4" cellspacing="2" style="border: 1px solid #999999; font-family: Century">
<tr>
<td colspan="3" style="text-align: center;">Employee Form</td>
</tr>
<tr>
<td width="100px">Emp Id</td>
<td width="5px" rowspan="8" style="border-right-style: solid; border-right-width: 1px; border-right-color: #999999"></td>
<td width="200px">
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>Name</td>
<td>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>Education</td>
<td>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>E-mail</td>
<td>
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td> </td>
<td>
<asp:Literal ID="Literal1" runat="server"></asp:Literal>
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnSave" runat="server" Text="Insert" onclick="btnSave_Click" />
<asp:Button ID="btnClear" runat="server" onclick="Button1_Click" Text="Clear" /> </td>
</tr>
</table>
</form>
</body>
</html>

Now, open index.aspx.cs file.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace SQL_ASPDOTNET {
public partial class index: System.Web.UI.Page {
SqlConnection con = new SqlConnection("Data Source=servername.database.windows.net;Initial Catalog=mssql;User ID=username;Password=password");
protected void Page_Load(object sender, EventArgs e) {}
public void clear() {
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
}
protected void btnSave_Click(object sender, EventArgs e) {
SqlCommand cmd = new SqlCommand("insert into empTable(emp_id,name,education,email) values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "')", con);
cmd.CommandType = CommandType.Text;
try {
con.Open();
cmd.ExecuteNonQuery();
Literal1.Text = "Data inserted successfully";
con.Close();
clear();
} catch (Exception ex) {
Literal1.Text = ex.Message;
}
}
protected void Button1_Click(object sender, EventArgs e) {
clear();
Literal1.Text = "";
}
}
}