SqlHelper Overview

SqlHelper provides a set of methods for making simple one-line database queries. With each query the SqlHelper class opens and disposes a SqlConnection. This does not incur an excessive performance penalty due to connection pooling.

SqlHelper Class

Each method described below has an additional overload that takes a System.Data.IsolationLevel parameter to specify what transaction level to use. The default is ReadCommitted.

  • The Scalar method opens a db connection, creates a SqlCommand, performs ExecuteScalar, closes the connection and returns the result.
    • object Scalar(string connectionStringKey, string query, params SqlParameter[] parameters)
      • connectionStringKey – The name of the connection string in App.config
      • query – The query to execute (must evaluate to a single value i.e. one column and one row)
      • parameters – An optional set of parameters to pass to the DB engine with the query.
  • The Query method returns an enumerable result for a query with multiple roles. Query returns an IEnumerable<SqlDataReader>. This means that to process the data from the query you can use any of the .NET enumeration tools. However, because it yields the underlying data reader, some care must be taken. SqlHelper.Query takes care of invoking SqlDataReader.Read() and SqlDataReader.Close() internally, so invoking these methods on the returned result may cause unexpected behavior or errors. In addition, if you fail to enumerate the entire collection it's possible for the reader and sql connection to be left in an open state, consuming resource from the connection pool.
    • IEnumerable<SqlDataReader> Query(string connectionStringKey, string query, params SqlParameter[] parameters)
  • The NonQuery method executes an INSERT, UPDATE, or DELETE command.
    • void NonQuery(string connectionStringKey, string query, params SqlParameter[] parameters)

SqlQuote Class

When constructing dynamic queries it’s easy to protect against malicious or accidental injection errors in the values by using SqlParameter. However, in some cases it’s necessary to construct table or column names dynamically. In these cases you can use the SqlQuote class to use square braces to properly escape database, table, and column names. SqlQuote provides logic based on the TSQL QUOTENAME function.

string quote(string val)

This method wraps val in square braces and escapes internal ']' characters as needed. If val is already wrapped it is returned unchanged.

string quote(string val, bool splitDots)

If this method is called with splitDots as false the behavior is identical to the previous. If splitDots is true, val is first split on the '.' character and each component is escaped separately, then recombined. This is useful when a column name must have a qualifier; for example, when using a schema other than dbo, or when performing a query across multiple tables.

Copyright © SoftwareIDM