The Database Helper Class
The WebMatrix.Data.Database class is the primary way ASP.NET Web Pages talks to a database without requiring a full ORM like Entity Framework. Calling Database.Open("StarterSite") opens a connection using a connection string named StarterSite from web.config, while Database.OpenConnectionString lets a page supply a raw connection string directly, useful for quick prototypes against SQL Server Compact or SQL Server Express files stored in App_Data. The returned db object exposes lightweight methods, Query, QuerySingle, QueryValue, and Execute, that map SQL text almost directly onto C# without an intervening mapping layer or migrations file.
Cricket analogy: A team manager pulling player stats straight from the scorer's official ledger rather than through a translator mirrors Database.Open connecting directly to a named connection string without an ORM layer in between.
Querying Data
Reading data uses db.Query() to return a sequence of dynamic rows for iteration in a foreach loop, db.QuerySingle() to fetch exactly one row such as a user profile by id, and db.QueryValue() to pull a single scalar like a COUNT(*) result. Every one of these methods accepts parameterized SQL using positional placeholders like @0, @1, so a call such as db.Query("SELECT * FROM Products WHERE CategoryId = @0", categoryId) keeps the SQL text static while safely substituting the parameter value at execution time, which is central to avoiding SQL injection.
Cricket analogy: A scorer flipping through a stack of individual player cards one at a time is like db.Query() returning a sequence of dynamic rows iterated in a foreach loop.
Inserting, Updating, and Deleting
Writing changes uses db.Execute(), which runs INSERT, UPDATE, or DELETE statements and returns the number of affected rows, for example db.Execute("UPDATE Products SET Price = @0 WHERE Id = @1", newPrice, productId). After an INSERT, db.GetLastInsertId() retrieves the identity value SQL Server just generated, which is commonly needed immediately afterward to redirect a user to a newly created record's detail page or to insert related rows into a child table. Wrapping several related Execute calls that must all succeed together typically requires an explicit transaction, since the Database helper does not implicitly wrap unrelated calls in one.
Cricket analogy: A scorer updating the scoreboard after every run scored is like db.Execute() applying an UPDATE, while noting the exact ball number of a fresh wicket mirrors db.GetLastInsertId() capturing a new record's generated id.
Preventing SQL Injection
Because db.Query, db.QuerySingle, and db.Execute all accept parameters through @0-style placeholders, the SQL text sent to the server never contains user-supplied values directly, which is what prevents SQL injection: a malicious string like "'; DROP TABLE Users; --" typed into a search box is treated purely as data, not as executable SQL, when passed as a parameter. Building queries instead by concatenating strings, such as "SELECT * FROM Users WHERE Name = '" + userInput + "'", reopens that exact vulnerability, so string concatenation of user input into SQL text must never appear in a Web Pages application.
Cricket analogy: A stadium's security checkpoint that scans every bag through the same X-ray procedure regardless of who's carrying it mirrors parameterized queries treating every input value through the same safe placeholder mechanism, never trusting it as raw commands.
@{
var db = Database.Open("StarterSite");
var categoryId = Request["category"].AsInt();
var products = db.Query(
"SELECT Id, Name, Price FROM Products WHERE CategoryId = @0",
categoryId);
if (IsPost) {
var name = Request["Name"];
var price = Request["Price"].AsDecimal();
db.Execute(
"INSERT INTO Products (Name, Price, CategoryId) VALUES (@0, @1, @2)",
name, price, categoryId);
var newId = db.GetLastInsertId();
Response.Redirect("~/product-detail?id=" + newId);
}
}
<ul>
@foreach (var p in products) {
<li>@p.Name - @p.Price.ToString("C")</li>
}
</ul>Never build SQL by concatenating user input into the query string, for example "WHERE Name = '" + userInput + "'". Always pass values as @0, @1 parameters to db.Query, db.QuerySingle, or db.Execute so the database engine treats them strictly as data, not executable SQL.
- Database.Open("Name") connects using a named connection string from web.config; Database.OpenConnectionString accepts one directly.
- db.Query() returns multiple rows for a foreach loop; db.QuerySingle() returns exactly one row.
- db.QueryValue() returns a single scalar, such as a COUNT(*) result.
- db.Execute() runs INSERT/UPDATE/DELETE statements and returns the number of affected rows.
- db.GetLastInsertId() retrieves the identity value generated by the most recent INSERT.
- All methods accept @0-style positional parameters, which is the core defense against SQL injection.
- String concatenation of user input into SQL text must never be used.
Practice what you learned
1. Which method opens a database connection using a named connection string from web.config?
2. Which Database helper method should you use to fetch exactly one row, such as a single user by id?
3. What does db.GetLastInsertId() return?
4. Why do db.Query and db.Execute use @0-style placeholders instead of string concatenation?
5. What does db.Execute() return after running an UPDATE statement?
Was this page helpful?
You May Also Like
Forms and Validation in Web Pages
Learn how ASP.NET Web Pages reads posted form data and enforces validation rules using the Validation helper, from server checks to optional client-side hints.
Security in Web Pages
Explore the WebSecurity helper for authentication and roles, and the built-in protections ASP.NET Web Pages provides against XSS and CSRF.
Deploying Web Pages Sites
Understand how to publish an ASP.NET Web Pages site to production using FTP or Web Deploy, configure web.config for release, and set correct IIS and App_Data permissions.
Related Reading
Related Study Notes in Microsoft Technologies
Browse all study notesWindows 10 / UWP Development Study Notes
.NET · 30 topics
Microsoft TechnologiesWindows Batch Scripting Study Notes
Batch · 30 topics
Microsoft TechnologiesMFC (Microsoft Foundation Classes) Study Notes
C++ · 30 topics
Microsoft TechnologiesSilverlight Study Notes
.NET · 30 topics
Microsoft TechnologiesXAML Study Notes
.NET · 30 topics
Microsoft TechnologiesWPF (Windows Presentation Foundation) Study Notes
.NET · 30 topics