📌 Introduction

When we build a web application in C#, we need to connect our application with a database.

ADO.NET helps us connect, read, insert, update, and delete data from SQL Server.

In this article, we will learn:

✔ What is ADO.NET
✔ How to connect ASP.NET Web App with SQL Server
✔ CRUD Operations (Create, Read, Update, Delete)
✔ Step-by-step explanation
✔ Beginner-friendly example

We will build a simple Student Management Web Application.

🧠 What is ADO.NET?

ADO.NET is a technology in .NET Framework used to connect applications with databases like:

  • SQL Server

  • Oracle

  • MySQL

It provides classes like:

  • SqlConnection

  • SqlCommand

  • SqlDataAdapter

  • SqlDataReader

🛠 Step 1: Create Database and Table

Open SQL Server and run this query:

CREATE DATABASE StudentDB;

USE StudentDB;

CREATE TABLE Students
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100),
    Email NVARCHAR(100),
    City NVARCHAR(100)
);

🖥 Step 2: Create ASP.NET Web Application

  1. Open Visual Studio

  2. Click New Project

  3. Select ASP.NET Web Application (.NET Framework)

  4. Choose Web Forms

  5. Click Create

🔗 Step 3: Add Connection String in Web.config

Open Web.config file and add:

<connectionStrings>
  <add name="dbcs" 
       connectionString="Data Source=YOUR_SERVER_NAME;Initial Catalog=StudentDB;Integrated Security=True" 
       providerName="System.Data.SqlClient"/>
</connectionStrings>

👉 Replace YOUR_SERVER_NAME with your SQL Server name.

🧾 Step 4: Design UI (Default.aspx)

Add this inside <form runat="server">:

Name:
<asp:TextBox ID="txtName" runat="server"></asp:TextBox><br />

Email:
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox><br />

City:
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox><br /><br />

<asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />
<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" />

<br /><br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true"></asp:GridView>

💻 Step 5: Write ADO.NET Code (Default.aspx.cs)

🔹 Import Namespaces

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

📌 Insert Operation

protected void btnInsert_Click(object sender, EventArgs e)
{
    string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;

    using (SqlConnection con = new SqlConnection(cs))
    {
        string query = "INSERT INTO Students(Name,Email,City) VALUES(@Name,@Email,@City)";
        SqlCommand cmd = new SqlCommand(query, con);

        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
        cmd.Parameters.AddWithValue("@City", txtCity.Text);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    LoadData();
}

🔎 Explanation

  • SqlConnection → Connects to database

  • SqlCommand → Executes SQL query

  • ExecuteNonQuery() → Used for Insert/Update/Delete

📌 Load Data (Read Operation)

public void LoadData()
{
    string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;

    using (SqlConnection con = new SqlConnection(cs))
    {
        string query = "SELECT * FROM Students";
        SqlDataAdapter da = new SqlDataAdapter(query, con);
        DataTable dt = new DataTable();

        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

Call this inside Page_Load:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        LoadData();
    }
}

📌 Update Operation

protected void btnUpdate_Click(object sender, EventArgs e)
{
    string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;

    using (SqlConnection con = new SqlConnection(cs))
    {
        string query = "UPDATE Students SET Email=@Email, City=@City WHERE Name=@Name";
        SqlCommand cmd = new SqlCommand(query, con);

        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
        cmd.Parameters.AddWithValue("@City", txtCity.Text);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    LoadData();
}

📌 Delete Operation

protected void btnDelete_Click(object sender, EventArgs e)
{
    string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;

    using (SqlConnection con = new SqlConnection(cs))
    {
        string query = "DELETE FROM Students WHERE Name=@Name";
        SqlCommand cmd = new SqlCommand(query, con);

        cmd.Parameters.AddWithValue("@Name", txtName.Text);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

    LoadData();
}

🖼 Expected Output

When you run the application:

  • Enter student details

  • Click Insert

  • Data will display in GridView

  • You can Update and Delete

Simple working CRUD web application 🎉

🔥 Why We Use Parameters?

We used:

cmd.Parameters.AddWithValue()

This prevents:

❌ SQL Injection

❌ Security issues

Always use parameters in real projects.

🏁 Conclusion

In this article, we learned:

✔ What is ADO.NET

✔ How to connect ASP.NET Web Forms to SQL Server

✔ How to perform CRUD operations

✔ Basic explanation of each class

This is the foundation of database programming in ASP.NET.