Executing SQL from Code-Behind
Most non-trivial Web Forms pages execute SQL directly from code-behind rather than relying solely on declarative SqlDataSource controls, especially when a query result needs to be transformed, combined with business logic, or cached before display. A typical pattern opens a SqlConnection inside a using block, builds a SqlCommand with parameters, executes it (ExecuteReader for a grid, ExecuteScalar for a total, ExecuteNonQuery for a save), and assigns the result to a control's DataSource, calling DataBind() afterward. This logic is commonly triggered from Page_Load for initial display or from a button's Click event handler for actions like saving a form, and it should live in a dedicated data-access class or repository rather than being duplicated across every page's code-behind.
Cricket analogy: A team analyst pulling specific player statistics from a database before a match, filtering and combining them with recent form data before presenting to the coach, is like code-behind executing a SQL query and layering business logic on top before binding results to a GridView.
Parameterized Queries and SQL Injection
Every value that originates from user input — a textbox, a query string, a dropdown selection — must be passed to SQL through a SqlParameter rather than concatenated into the SQL string. SqlParameter handles type conversion and escaping correctly (a name containing an apostrophe like O'Brien won't break the query), and it closes off SQL injection, where an attacker crafts input such as '; DROP TABLE Users; -- to alter or destroy data. AddWithValue is the common shorthand for adding a parameter, though for precise control over type and size (important for matching a nvarchar(50) column exactly) many teams prefer the explicit Parameters.Add overload that specifies SqlDbType and size directly.
Cricket analogy: An umpire using a strict, standardized signal system that can't be misinterpreted, rather than accepting ambiguous hand gestures from the crowd, is like SqlParameter enforcing a strict type-safe channel for values instead of letting raw, unvalidated text flow straight into a query.
protected void btnSave_Click(object sender, EventArgs e)
{
string sql = @"INSERT INTO Orders (CustomerId, ProductId, Quantity, OrderDate)
VALUES (@CustomerId, @ProductId, @Quantity, @OrderDate)";
using (SqlConnection conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["AppDb"].ConnectionString))
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = customerId;
cmd.Parameters.Add("@ProductId", SqlDbType.Int).Value = productId;
cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = int.Parse(txtQty.Text);
cmd.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = DateTime.Now;
conn.Open();
cmd.ExecuteNonQuery();
}
}Stored Procedures
Calling a stored procedure from Web Forms code-behind requires setting cmd.CommandType = CommandType.StoredProcedure and cmd.CommandText to the procedure's name, then adding parameters that match its declared @parameters, including any output parameters marked with Direction = ParameterDirection.Output. Stored procedures offer several practical benefits over inline SQL: SQL Server precompiles and caches their execution plan, they centralize business logic (like a discount calculation) in one place instead of duplicating it across pages, and they let a DBA grant EXECUTE permission on the procedure without granting direct table access, reducing the attack surface. Many enterprise Web Forms codebases standardize on stored procedures for all writes, reserving inline SQL for simple, dynamic read queries.
Cricket analogy: A team running the exact same, well-drilled fielding routine for every rain-delay restart rather than improvising it fresh each time is like calling a precompiled stored procedure repeatedly instead of sending fresh inline SQL for the same operation.
Connection strings live in web.config's <connectionStrings> section, e.g. <add name="AppDb" connectionString="..." providerName="System.Data.SqlClient" />. Retrieve them via ConfigurationManager.ConnectionStrings["AppDb"].ConnectionString rather than duplicating the raw string across pages, which makes environment-specific configuration (dev vs. production) a one-line change.
Transactions
When a single logical operation involves multiple SQL statements — for example, deducting inventory and inserting an order row — those statements need to succeed or fail together, which is what SqlTransaction provides. You call conn.BeginTransaction() to start it, pass the resulting transaction object into each SqlCommand's Transaction property, and then call transaction.Commit() if every statement succeeded or transaction.Rollback() inside a catch block if anything failed, ensuring the database never ends up in a half-updated, inconsistent state. Transactions should be kept as short as possible because locks held by an open transaction can block other requests from reading or writing the same rows, and a long-running transaction under load is a common cause of application-wide slowdowns.
Cricket analogy: A DRS review where the on-field decision, third umpire check, and final call must all align before the outcome is confirmed, otherwise everything reverts to the original call, mirrors a SqlTransaction committing only if every step succeeds, or rolling back entirely.
Holding a SqlConnection or SqlTransaction open longer than necessary — for example, across a slow external API call inside the transaction — holds database locks the whole time and can exhaust the connection pool or cause widespread blocking under concurrent load. Keep transactions tight around just the SQL statements that must be atomic.
- Code-behind commonly executes SQL directly via ADO.NET when business logic, caching, or complex joins are needed beyond what SqlDataSource offers.
- Every user-supplied value must go through a SqlParameter, never string concatenation, to prevent SQL injection.
- Stored procedures centralize logic, benefit from precompiled execution plans, and let DBAs restrict direct table access.
- CommandType.StoredProcedure plus matching @parameters (including output parameters) is the pattern for calling a sproc.
- SqlTransaction with BeginTransaction/Commit/Rollback ensures multi-statement operations succeed or fail atomically.
- Keep transactions as short as possible to avoid holding locks and exhausting the connection pool under load.
- Connection strings belong in web.config, not hard-coded, so they can vary by environment.
Practice what you learned
1. What is the primary defense against SQL injection when accepting user input in a Web Forms page?
2. Which CommandType value must be set to call a stored procedure via SqlCommand?
3. What does SqlTransaction guarantee when several SQL statements must succeed or fail together?
4. Why might a team prefer stored procedures over inline SQL for write operations?
5. What is a key risk of holding a SqlTransaction open for a long time, such as across a slow external API call?
Was this page helpful?
You May Also Like
ADO.NET Basics
Learn how Classic ASP.NET Web Forms applications connect to and query relational databases using ADO.NET's connection, command, and disconnected DataSet objects.
Data Binding in Web Forms
Understand how Web Forms server controls bind to data sources, the difference between declarative and programmatic binding, and when to use Repeater, GridView, or ListView.
Caching in Web Forms
Learn how output caching, fragment caching, and the Cache API reduce database and rendering load in Classic ASP.NET Web Forms applications.
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