Chapter 9: WebPages Databases
Web Pages – Databases (the exact lesson in W3Schools WP Tutorial menu: “WebPages Databases” or “ASP.NET Web Pages – Databases” at https://www.w3schools.com/asp/webpages_database.asp).
This is the chapter where your site goes from “displaying static or file-based data” to real dynamic, persistent data storage using a proper database. No more flat .txt or .csv files — now we talk SQL databases!
W3Schools keeps it simple and beginner-friendly: focus on displaying data from a database using the built-in Database helper (from WebMatrix.Data namespace). They show how to connect, query, and show results in a page — perfect stepping stone before CRUD (Create/Read/Update/Delete) in later topics or helpers like WebGrid.
1. What You’ll Learn in This Lesson (Teacher Overview)
- Connect to a database (mostly SQL Server Compact .sdf file — lightweight, file-based, no separate server needed)
- Use Database.Open(“connectionName”) — the magic one-liner
- Run SQL queries with Database.Query(…) or Database.QuerySingle(…)
- Loop over results (dynamic rows) and display in HTML (tables, lists)
- Later lessons build on this: insert, update, delete, WebGrid for sortable tables, etc.
Key point from W3Schools:
“With Web Pages, you can easily display data from a database. You can connect to an existing database, or create a new database from scratch.”
They usually use a sample SmallBakery database with a Product table.
2. Database Options in ASP.NET Web Pages (Quick Context)
- SQL Server Compact (.sdf file) → Default & easiest (file in App_Data, no install needed beyond WebMatrix/SQL CE tools)
- SQL Server Express → Bigger brother, full SQL Server
- SQL Server (full) or MySQL → Via connection string in Web.config
- SQLite or others → Possible but not in basic tutorial
W3Schools sticks to .sdf for simplicity — it’s in App_Data folder (protected, can’t be downloaded).
3. Step-by-Step: How to Connect & Display Data
Step 1: Have or Create the Database
In WebMatrix (or Visual Studio with Web Pages tools):
- Create new site or use existing
- In Database workspace → Create new SQL Server Compact database
- Name it SmallBakery.sdf (goes to App_Data automatically)
- Create table Product with columns: Id (int, identity), Name (nvarchar), Description (nvarchar), Price (money)
Sample data (insert manually or via code later):
| Id | Name | Description | Price |
|---|---|---|---|
| 1 | Bread | Fresh baked | 2.99 |
| 2 | Cake | Chocolate delight | 15.50 |
| 3 | Cookies | Chocolate chip | 4.99 |
Step 2: The Code – Classic W3Schools Example (Products.cshtml)
This is almost exactly what W3Schools shows — display all products sorted by name.
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
@{ // Step 1: Open the database by name (no .sdf extension!) var db = Database.Open("SmallBakery"); // Step 2: Write your SQL query (SELECT everything, order by Name) var selectQueryString = "SELECT * FROM Product ORDER BY Name"; // Step 3: Execute query → returns dynamic IEnumerable of rows var selectedData = db.Query(selectQueryString); // Optional: Get single row example (e.g. product with Id=1) // var oneProduct = db.QuerySingle("SELECT * FROM Product WHERE Id=1"); } <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <title>Products from Database</title> <style> table { border-collapse: collapse; width: 80%; } th, td { border: 1px solid #ddd; padding: 8px; text-align: left; } th { background-color: #f2f2f2; } </style> </head> <body> <h1>Our Bakery Products</h1> @if (selectedData.Count() == 0) { <p>No products found in database.</p> } else { <table> <thead> <tr> <th>Id</th> <th>Name</th> <th>Description</th> <th>Price (₹)</th> </tr> </thead> <tbody> @foreach (var row in selectedData) { <tr> <td>@row.Id</td> <td>@row.Name</td> <td>@row.Description</td> <td>@row.Price.ToString("C")</td> <!-- Formats as currency --> </tr> } </tbody> </table> <p>Total products: @selectedData.Count()</p> } </body> </html> |
Magic explained line-by-line:
- Database.Open(“SmallBakery”) → Looks in App_Data for SmallBakery.sdf → Or in Web.config for connection string named “SmallBakery” (useful when deploying)
- db.Query(…) → Runs any SELECT → returns collection of dynamic objects → Each row is like a bag: row.Name, row.Price (case-sensitive column names!)
- @foreach (var row in selectedData) → Loops over rows → @row.ColumnName prints value safely (Razor escapes HTML)
4. Alternative: Using Connection String (for SQL Server/MySQL)
If not using .sdf file (e.g., remote SQL Server):
Add to Web.config (in root):
|
0 1 2 3 4 5 6 7 8 9 10 |
<connectionStrings> <add name="MyRemoteDb" connectionString="Server=myServer;Database=MyDb;User Id=user;Password=pass;" providerName="System.Data.SqlClient" /> </connectionStrings> |
Then in page:
|
0 1 2 3 4 5 6 |
var db = Database.Open("MyRemoteDb"); // same code! |
→ Database.Open smartly uses name → file OR connection string.
5. Other Common Methods (Teacher Extras – Build on Tutorial)
- db.QuerySingle(“SELECT … WHERE Id=@0”, id) → one row
- db.QueryValue(“SELECT COUNT(*) FROM Product”) → single value (e.g. count)
- db.Execute(“INSERT INTO … VALUES (@0, @1)”, val1, val2) → insert/update/delete (returns rows affected)
Example insert (add to form submit):
|
0 1 2 3 4 5 6 7 8 9 10 |
if (IsPost) { var newName = Request["ProductName"]; db.Execute("INSERT INTO Product (Name, Description, Price) VALUES (@0, @1, @2)", newName, Request["Description"], Request["Price"].AsDecimal()); } |
6. Important Warnings & 2026 Reality Check
- SQL Injection → Always use parameters (@0, @1) — never concatenate strings like “WHERE Name='” + userInput + “‘”
- .sdf limitations → Max ~4GB, not for huge sites — migrate to full SQL Server when needed
- Modern path → ASP.NET Web Pages → merged into ASP.NET Core Razor Pages → Use Entity Framework Core or Dapper instead of WebMatrix.Data → But concepts (connect → query → display) are eternal!
Summary – Blackboard Close
ASP.NET Web Pages – Databases = bridge from files to real DBs:
- Database.Open(“name”) → connect
- db.Query(sql) → get rows
- Loop @foreach (var row in data) → show in tables/lists
- Store in App_Data (.sdf) or use connection strings
- Foundation for WebGrid (next lesson), forms with DB, security, etc.
This is where your site becomes truly dynamic — products, users, blog posts, all from database!
Next class questions?
- Want full insert/update/delete example with form?
- How to use WebGrid to show this data nicely (sortable, pageable)?
- Deploying with database?
- Or jump to WebPages Helpers?
Your call, Webliance — you’re crushing this series from Hyderabad! Keep going! 🚀🇮🇳
