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.
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
Scalarmethod 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.
Querymethod 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
yieldsthe underlying data reader, some care must be taken.
SqlHelper.Querytakes 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)
NonQuerymethod executes an INSERT, UPDATE, or DELETE command.
void NonQuery(string connectionStringKey, string query, params SqlParameter parameters)
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.