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.
- Open the Visual Studio and click on the Create a new Project button.
- Select the ASP.NET Web Application(.NET Framework) and click the Next button on the Create a new project window.
3. On the Configure your new project window, provide the project name, select the latest framework, and click on the Next button.
4. On the Create a new ASP.NET Web Application, select Empty and then click on the Create button.
Now you can see below the Project was created successfully.
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>
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>
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)
{
}
}
}
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.
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 !!!
I am Rajkishore, and I am a Microsoft Certified IT Consultant. I have over 14 years of experience in Microsoft Azure and AWS, with good experience in Azure Functions, Storage, Virtual Machines, Logic Apps, PowerShell Commands, CLI Commands, Machine Learning, AI, Azure Cognitive Services, DevOps, etc. Not only that, I do have good real-time experience in designing and developing cloud-native data integrations on Azure or AWS, etc. I hope you will learn from these practical Azure tutorials. Read more.
