Integrate Azure SQL Database With .NET Website

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:

Connect with Visual Studio

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

All resources

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.

All Settings

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

Show database Connection strings

Show database Connection strings

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

Add Connection

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.

Data source

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

Add New Table

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

Identity

Complete Table Definition, available below.

Table

Update Database.

Update Database

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

Tables folder

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

New Project

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

Web Form

Enter Page Name.

Enter Page Name

Step 10: Open Index.aspx file & add below code.
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index.aspx.cs" Inherits="SQL_ASPDOTNET.index" %>  
  2.     <!DOCTYPE html>  
  3.     <html xmlns="http://www.w3.org/1999/xhtml">  
  4.   
  5.     <head runat="server">  
  6.         <title>Azure SQL Database - Sample</title>  
  7.         <style type="text/css">  
  8.             .style1 {  
  9.                 color: #FF0066;  
  10.                 text-decoration: underline;  
  11.                 font-weight: bold;  
  12.             }  
  13.         </style>  
  14.     </head>  
  15.   
  16.     <body>  
  17.         <form id="form1" runat="server">  
  18.             <table align="center" cellpadding="4" cellspacing="2" style="border: 1px solid #999999; font-family: Century">  
  19.                 <tr>  
  20.                     <td colspan="3" style="text-align: center;">Employee Form</td>  
  21.                 </tr>  
  22.                 <tr>  
  23.                     <td width="100px">Emp Id</td>  
  24.                     <td width="5px" rowspan="8" style="border-right-style: solid; border-right-width: 1px; border-right-color: #999999"></td>  
  25.                     <td width="200px">  
  26.                         <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>  
  27.                     </td>  
  28.                 </tr>  
  29.                 <tr>  
  30.                     <td>Name</td>  
  31.                     <td>  
  32.                         <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>  
  33.                     </td>  
  34.                 </tr>  
  35.                 <tr>  
  36.                     <td>Education</td>  
  37.                     <td>  
  38.                         <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>  
  39.                     </td>  
  40.                 </tr>  
  41.                 <tr>  
  42.                     <td>E-mail</td>  
  43.                     <td>  
  44.                         <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>  
  45.                     </td>  
  46.                 </tr>  
  47.                 <tr>  
  48.                     <td> </td>  
  49.                     <td>  
  50.                         <asp:Literal ID="Literal1" runat="server"></asp:Literal>  
  51.                     </td>  
  52.                 </tr>  
  53.                 <tr>  
  54.                     <td></td>  
  55.                     <td>  
  56.                         <asp:Button ID="btnSave" runat="server" Text="Insert" onclick="btnSave_Click" />    
  57.                         <asp:Button ID="btnClear" runat="server" onclick="Button1_Click" Text="Clear" /> </td>  
  58.                 </tr>  
  59.             </table>  
  60.         </form>  
  61.     </body>  
  62.   
  63.     </html>  
Now, open index.aspx.cs file.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.SqlClient;  
  5. using System.Linq;  
  6. using System.Web;  
  7. using System.Web.UI;  
  8. using System.Web.UI.WebControls;  
  9. namespace SQL_ASPDOTNET {  
  10.     public partial class index: System.Web.UI.Page {  
  11.         SqlConnection con = new SqlConnection("Data Source=servername.database.windows.net;Initial Catalog=mssql;User ID=username;Password=password");  
  12.         protected void Page_Load(object sender, EventArgs e) {}  
  13.         public void clear() {  
  14.             TextBox1.Text = "";  
  15.             TextBox2.Text = "";  
  16.             TextBox3.Text = "";  
  17.             TextBox4.Text = "";  
  18.         }  
  19.         protected void btnSave_Click(object sender, EventArgs e) {  
  20.             SqlCommand cmd = new SqlCommand("insert into empTable(emp_id,name,education,email) values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "')", con);  
  21.             cmd.CommandType = CommandType.Text;  
  22.             try {  
  23.                 con.Open();  
  24.                 cmd.ExecuteNonQuery();  
  25.                 Literal1.Text = "Data inserted successfully";  
  26.                 con.Close();  
  27.                 clear();  
  28.             } catch (Exception ex) {  
  29.                 Literal1.Text = ex.Message;  
  30.             }  
  31.         }  
  32.         protected void Button1_Click(object sender, EventArgs e) {  
  33.             clear();  
  34.             Literal1.Text = "";  
  35.         }  
  36.     }  
  37. }  
Step 11: Now, run the Web Application.

Enter the record & click on Insert button.

Application
Step 12: To check the data, right click on Table & select “Show Table Data” option.

Show Table Data

Show Table Data

Congratulations! You have successfully inserted data using ASP.NET website, on Microsoft Azure!


Similar Articles