Working with SQL in Web Forms

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.

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.

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.

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.