🗄️ ASP.NET – Database Access – Connect, Read & Write with ADO.NET
🧲 Introduction – Why Database Access in ASP.NET?
Most real-world web applications require a database to store, retrieve, and manage data. Whether it’s a login form, product catalog, or user profile, database access is essential.
In ASP.NET Web Forms, the most common approach for data access is using ADO.NET, which connects your ASP.NET app to databases like SQL Server, MySQL, or Oracle.
🎯 In this guide, you’ll learn:
- How to connect to SQL Server using ADO.NET
- How to insert, read, and display data in a GridView
- How
SqlConnection,SqlCommand, andSqlDataAdapterwork - Step-by-step explanations of each code block
📂 ASP.NET Database Access – File Overview
| File Name | Purpose |
|---|---|
DbAccessDemo.aspx | Web Form UI with data grid |
DbAccessDemo.aspx.cs | Code-behind logic to connect and fetch |
web.config | Stores the database connection string |
| SQL Server DB | A sample database with a Users table |
🏗️ Step 1: Prepare the SQL Server Database
Create a Users table in your SQL Server:
CREATE TABLE Users (
Id INT PRIMARY KEY IDENTITY,
FullName NVARCHAR(100),
Email NVARCHAR(100)
);
INSERT INTO Users (FullName, Email) VALUES
('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com');
⚙️ Step 2: Add Connection String in web.config
<configuration>
<connectionStrings>
<add name="MyDb"
connectionString="Data Source=localhost;Initial Catalog=MyWebsiteDB;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
🔍 Explanation:
| Line | Purpose |
|---|---|
name="MyDb" | Gives a name to the connection string |
Data Source=localhost | Connects to local SQL Server |
Initial Catalog=MyWebsiteDB | Uses the database named MyWebsiteDB |
Integrated Security=True | Uses Windows authentication (change if needed) |
📄 Step 3: Create Markup File – DbAccessDemo.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DbAccessDemo.aspx.cs" Inherits="DbAccessDemo" %>
<html>
<body>
<form id="form1" runat="server">
<h3>🗄️ ASP.NET Database Access (Users Table)</h3>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" />
</form>
</body>
</html>
🔍 Markup Explanation:
| Element | What It Does |
|---|---|
<asp:GridView /> | Displays user data in tabular format |
AutoGenerateColumns="true" | Automatically shows all fields from the query result |
🧠 Step 4: Code-Behind File – DbAccessDemo.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
public partial class DbAccessDemo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadUsers();
}
}
private void LoadUsers()
{
string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["MyDb"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
string query = "SELECT * FROM Users";
SqlDataAdapter da = new SqlDataAdapter(query, conn);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
🔍 Line-by-Line Code Explanation
| Line | What It Does |
|---|---|
Page_Load(...) | Runs when the page is loaded. Only fetches data if it’s the first load (not a postback). |
ConnectionStrings["MyDb"] | Reads your SQL Server connection info from web.config. |
new SqlConnection(...) | Opens a connection to the database. |
SqlDataAdapter | Fills the result of the SQL query into a DataTable. |
GridView1.DataSource = dt | Binds the user data to the GridView. |
GridView1.DataBind() | Renders the data on the web page. |
🖥️ Output in Browser
🗄️ ASP.NET Database Access (Users Table)
+----+------------+---------------------+
| Id | FullName | Email |
+----+------------+---------------------+
| 1 | John Doe | john@example.com |
| 2 | Jane Smith | jane@example.com |
+----+------------+---------------------+
✅ You’ve successfully connected to SQL Server and displayed data in a GridView using ADO.NET.
📌 Summary – Recap & Takeaways
🔍 Key Learnings:
- ADO.NET allows direct control over SQL queries in ASP.NET
- Use
SqlConnection,SqlCommand, andSqlDataAdapterfor common DB tasks - Connection strings go in
web.configfor centralized config - GridView is a powerful server control for tabular data display
✅ Ideal for scenarios like login forms, admin dashboards, CMS panels, etc.
❓ Frequently Asked Questions (FAQs)
❓ Can I use stored procedures instead of SQL queries?
✅ Yes. Replace your SqlCommand query with CommandType.StoredProcedure and set CommandText to the procedure name.
❓ What if I use SQL Authentication?
✅ Update your connection string like:
connectionString="Data Source=.;Initial Catalog=MyDb;User ID=sa;Password=yourPassword"
❓ Is it safe to use ADO.NET directly?
✅ For small apps, yes. For large apps, consider using Entity Framework or Dapper for abstraction and security.
❓ How do I insert new data from a form?
✅ Use SqlCommand with an INSERT INTO query and call ExecuteNonQuery().
Share Now :
