🔗 ASP.NET – ADO.NET: Database Connectivity Explained

ADO.NET (ActiveX Data Objects for .NET) is a core part of the .NET Framework that provides a set of classes to interact with relational databases like SQL Server, Oracle, MySQL, and others. It is disconnected, scalable, and designed to support the needs of web-based ASP.NET applications.

This guide introduces you to the fundamental concepts, components, and examples of using ADO.NET with ASP.NET for seamless database operations.

---

🧠 What is ADO.NET?

ADO.NET is a data access technology that enables ASP.NET applications to communicate with databases. It uses connection-oriented and disconnected data access models and is part of System.Data namespace.

---

🧱 ADO.NET Architecture

ADO.NET consists of two key models:

Component	Description
Connection	Manages connection to a database (e.g., SqlConnection)
Command	Executes SQL queries (SqlCommand)
DataReader	Fast, forward-only read of data
DataAdapter	Acts as a bridge between DataSet and database
DataSet	In-memory representation of data
DataTable	Represents a single table of in-memory data

---

🔌 Connecting to a Database

Use SqlConnection to connect to a SQL Server database.

using System.Data.SqlClient;

string connectionString = "Server=.;Database=TestDB;Trusted_Connection=True;";
SqlConnection con = new SqlConnection(connectionString);

con.Open();
// Use the connection
con.Close();

---

📥 Reading Data with DataReader

Use SqlCommand and SqlDataReader to read data efficiently.

SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", con);
SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    string name = reader["Name"].ToString();
    Response.Write(name + "<br>");
}
reader.Close();

✅ DataReader is fast but forward-only and read-only.

---

📤 Inserting, Updating, Deleting Data

Use SQL statements with ExecuteNonQuery() for write operations.

string query = "INSERT INTO Employees (Name, Age) VALUES (@Name, @Age)";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@Name", "Alice");
cmd.Parameters.AddWithValue("@Age", 30);

int rows = cmd.ExecuteNonQuery();
Response.Write(rows + " row(s) inserted.");

---

📦 Working with DataSet and DataTable

Use SqlDataAdapter to fill a DataSet for disconnected data access.

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Employees", con);
DataSet ds = new DataSet();
da.Fill(ds);

GridView1.DataSource = ds;
GridView1.DataBind();

✅ You can bind DataSet or DataTable to GridView, DropDownList, etc.

---

🔄 Executing Stored Procedures

Call stored procedures using CommandType.StoredProcedure.

SqlCommand cmd = new SqlCommand("GetEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    Response.Write(reader["Name"] + "<br>");
}
reader.Close();

---

🧪 Full Example: Display SQL Data in ASP.NET

ASP.NET (C#) Web Forms Example

<!-- Default.aspx -->
<asp:GridView ID="GridView1" runat="server"></asp:GridView>

// Default.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid();
    }
}

void BindGrid()
{
    string conStr = "Server=.;Database=TestDB;Trusted_Connection=True;";
    using (SqlConnection con = new SqlConnection(conStr))
    {
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Employees", con);
        DataTable dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

---

✅ Summary

• ADO.NET is the standard for accessing databases in ASP.NET.
• It uses SqlConnection, SqlCommand, SqlDataReader, DataSet, and DataAdapter.
• Supports connected (DataReader) and disconnected (DataSet) models.
• Great for secure, fast, and scalable data access in web apps.

---

❓ FAQs

Q1. Is ADO.NET still used in modern ASP.NET Core?
ADO.NET is supported in .NET Core, but Entity Framework Core is preferred for higher abstraction.

Q2. When should I use DataReader over DataSet?
Use DataReader for performance when you only need to read data quickly without modifications.

Q3. What is the use of parameters in SqlCommand?
Parameters prevent SQL injection and allow secure query execution.

Q4. Can I use ADO.NET with databases other than SQL Server?
Yes, with the corresponding provider (e.g., OleDb, Odbc, MySql.Data).

---

Share Now :