Database Access in Web Pages

Analogy categoryAnalogies

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.

csharp

@{
    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?

Forms and Validation in Web Pages

Security in Web Pages

Related Study Notes in Microsoft Technologies

Browse all study notes

Microsoft Technologies

Windows 10 / UWP Development Study Notes

.NET · 30 topics

Microsoft Technologies

Windows Batch Scripting Study Notes

Batch · 30 topics

Microsoft Technologies

MFC (Microsoft Foundation Classes) Study Notes

C++ · 30 topics

Microsoft Technologies

Silverlight Study Notes

.NET · 30 topics

Microsoft Technologies

XAML Study Notes

.NET · 30 topics

Microsoft Technologies

WPF (Windows Presentation Foundation) Study Notes

.NET · 30 topics

Topics covered

#NETFramework#ClassicASPNETWebFormsWebPagesStudyNotes#MicrosoftTechnologies#DatabaseAccessInWebPages#Database#Access#Web#Pages#SQL #WebDevelopment#StudyNotes#SkillVeris

The Database Helper Class

Querying Data

Inserting, Updating, and Deleting

Preventing SQL Injection

Practice what you learned

You May Also Like

Related Reading

Related Study Notes in Microsoft Technologies

Topics covered