Asp.Net Connect To Azure SQL Database

This article will discuss how to connect to Azure SQL Database using Asp.Net.

How to connect to Azure SQL Database using Asp.Net

As part of this topic what we will do is we will do CRUD Operations in Azure SQL DB using ADO.Net 

As part of the functionality, what we will do is we will create an ASP.Net web application, and we will add a few controls like Gridview, text box, and button.

So, the overall idea is to insert the data into the Azure SQL table, get it from the table, bind it to the grid view, and display it.

So now let’s see the implementation step by step as below

We will use the latest version of Visual Studio, i.e., Visual Studio 2019, as the IDE.

  1. Open the Visual Studio and click on the Create a new Project button.
  2. Select the ASP.NET Web Application(.NET Framework) and click the Next button on the Create a new project window.
How to Use SQL Azure Table for CRUD Operations in ADO.NET

3. On the Configure your new project window, provide the project name, select the latest framework, and click on the Next button.

How to Connect to Azure SQL Database ADO.Net

4. On the Create a new ASP.NET Web Application, select Empty and then click on the Create button.

Connect to Azure SQL Database ADO.Net

Now you can see below the Project was created successfully.

Performing CRUD Operations On Azure SQL Database

5. Now open the Web. config file and add the connection string

 <connectionStrings>
    <add name="MynewConnection"
         connectionString="Server=tcp:mytestdb236.database.windows.net,1433;Database=MyDemoDB;User ID=rajkishore;Password=tamanna@19;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
Perform CRUD Operations On Azure SQL Database

6. Now the next step is I have added a file i.e. WebForm1.aspx, on the design pat I have added the below code

<Table>
            <tr>
                <td>
                    <asp:GridView ID="GridView1" runat="server" OnSelectedIndexChanged="GridView1_SelectedIndexChanged"></asp:GridView>
                </td>
            </tr>
            <tr>
                <br />
            </tr>
            <tr>
                <td>
                    FirstName:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
               <br />
                LastName: <asp:TextBox ID="TextBox2" runat="server"> </asp:TextBox>

                </td>
            </tr>
            <tr>
                <td>
                     <asp:Button ID="Button1" runat="server" Text="Save" OnClick="Button1_Click1" />
                </td>
            </tr>
            </Table>
How to Perform CRUD Operations On Azure SQL Database

Now open the WebForm1.aspx.cs file and add the below line of code

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;
using System.Configuration;

namespace MyDemoWebApp
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
      public void BindGridView()
        {
            SqlDataAdapter daAdopter = new SqlDataAdapter("Select * from EmployeeDetails", ConfigurationManager
                .ConnectionStrings["MynewConnection"].ConnectionString);
            DataSet myds = new DataSet();
            daAdopter.Fill(myds);
            GridView1.DataSource = myds.Tables[0];
            GridView1.DataBind();
        }
        public int ExecuteCommand(string myQuery)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager
                .ConnectionStrings["MynewConnection"].ConnectionString);
            SqlCommand sqlcom = new SqlCommand(myQuery, sqlcon);
            sqlcon.Open();
            int res = sqlcom.ExecuteNonQuery();
            sqlcon.Close();
            return res;
        }
       protected void Button1_Click1(object sender, EventArgs e)
        {
            string qry = "Insert into EmployeeDetails Values ('" + TextBox1.Text + "','" + TextBox2.Text + "')";
            if (ExecuteCommand(qry) > 0)
            {
                BindGridView();
            }
        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}
Use of SQL Azure In Visual Studio 2019 Step By Step Guide
Use of SQL Azure In Visual Studio 2019 Step By Step

Now run the application by pressing F5 or Debug –> Start Debugging. You can see below we got the expected output. Check out the screenshot below.

How to use SQL Azure In Visual Studio 2019 Step By Step

You can check out How to get Azure SQL database connection string

You may also like following the articles below

Conclusion

This article discussed how to connect to Azure SQL Database using Asp.Net. Thanks for reading this article !!!