Class QueryExecutionHelper

Helper class for executing queries. This class encapsulates the process of getting a connection, making a command, running it, and cleaning up afterwards.

Inheritance

object

QueryExecutionHelper

Implements

IDisposable

Inherited Members

object.ToString()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

object.GetType()

Namespace: SuperOffice.Data

Assembly: SoDataBase.dll

Syntax

public sealed class QueryExecutionHelper : IDisposable

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

Constructors

QueryExecutionHelper(SqlCommand)

Construct a query execution helper, and run the query. This constructor sets a Reader execution type.

Declaration

public QueryExecutionHelper(SqlCommand command)

Parameters

Type	Name	Description
SqlCommand	command	SQL command to run

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

QueryExecutionHelper(SqlCommand, ExecutionType, bool)

Construct a query execution helper, and run the query. You can specify whether sentry should be ignored.

Declaration

public QueryExecutionHelper(SqlCommand command, QueryExecutionHelper.ExecutionType executionType, bool ignoreSentry)

Parameters

Type	Name	Description
SqlCommand	command	SQL command to run
QueryExecutionHelper.ExecutionType	executionType	Desired execution type
bool	ignoreSentry	Ignore sentry?

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

QueryExecutionHelper(SqlCommand, ExecutionType, bool, SoConnection)

Construct a query execution helper, and run the query. You can specify whether sentry should be ignored.

Declaration

public QueryExecutionHelper(SqlCommand command, QueryExecutionHelper.ExecutionType executionType, bool ignoreSentry, SoConnection explicitConnection)

Parameters

Type	Name	Description
SqlCommand	command	SQL command to run
QueryExecutionHelper.ExecutionType	executionType	Desired execution type
bool	ignoreSentry	Ignore sentry?
SoConnection	explicitConnection

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

QueryExecutionHelper(SqlCommand, SoConnection)

Construct a query execution helper, and run the query. This constructor sets a Reader execution type.

Declaration

public QueryExecutionHelper(SqlCommand command, SoConnection explicitConnection)

Parameters

Type	Name	Description
SqlCommand	command	SQL command to run
SoConnection	explicitConnection

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

QueryExecutionHelper(SqlCommand, bool)

Construct a query execution helper, and run the query. This constructor sets a Reader execution type. You can specify whether sentry should be ignored.

Declaration

public QueryExecutionHelper(SqlCommand command, bool IgnoreSentry)

Parameters

Type	Name	Description
SqlCommand	command	SQL command to run
bool	IgnoreSentry

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

Properties

IsSentryIgnored

Has ignoreSentry = true been specified for this query execution helper. Note that ignore sentry may be in effect as a consequence of earlier actions, such as a call to LocalPrincipal.BeginIgnoreSentryCheck; this is NOT reflected in the value you get here.

Declaration

public bool IsSentryIgnored { get; }

Property Value

Type	Description
bool

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

Reader

The SoDataReader that represents the results of the query, if it is a select

Declaration

public SoDataReader Reader { get; }

Property Value

Type	Description
SoDataReader

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

RowsAffected

Helper class for executing queries. This class encapsulates the process of getting a connection, making a command, running it, and cleaning up afterwards.

Declaration

public int RowsAffected { get; }

Property Value

Type	Description
int

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

Methods

Dispose()

Clean up sentry check, reader, command and connection

Declaration

public void Dispose()

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteNonQuery(SqlCommand)

Execute a command that has no result (such as an insert or update, maybe a stored-proc call). Clean up immediately. In this overload, sentry is ON.

Declaration

public static int ExecuteNonQuery(SqlCommand command)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute

Returns

Type	Description
int	Number of rows affected, as reported by database server

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteNonQuery(SqlCommand, bool)

Execute a command that has no result (such as an insert or update, maybe a stored-proc call). Clean up immediately.

Declaration

public static int ExecuteNonQuery(SqlCommand command, bool ignoreSentry)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute
bool	ignoreSentry	Should sentry be ignored

Returns

Type	Description
int	Number of rows affected, as reported by database server

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteNonQuery(SqlCommand, bool, SoConnection)

Execute a command that has no result (such as an insert or update, maybe a stored-proc call). Clean up immediately.

Declaration

public static int ExecuteNonQuery(SqlCommand command, bool ignoreSentry, SoConnection explicitConnection)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute
bool	ignoreSentry	Should sentry be ignored
SoConnection	explicitConnection

Returns

Type	Description
int

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteNonQueryAsync(SqlCommand, bool, SoConnection, CancellationToken)

Execute a command that has no result (such as an insert or update, maybe a stored-proc call). Clean up immediately.

Declaration

public static Task<int> ExecuteNonQueryAsync(SqlCommand command, bool ignoreSentry = false, SoConnection explicitConnection = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute
bool	ignoreSentry
SoConnection	explicitConnection
CancellationToken	cancellationToken

Returns

Type	Description
Task<int>

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteReaderAsync(SqlCommand, bool, bool, SoConnection, CancellationToken)

Helper class for executing queries. This class encapsulates the process of getting a connection, making a command, running it, and cleaning up afterwards.

Declaration

public static Task<QueryExecutionHelper> ExecuteReaderAsync(SqlCommand command, bool ignoreSentry = false, bool useUnauthenticatedConnection = false, SoConnection explicitConnection = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
SqlCommand	command
bool	ignoreSentry
bool	useUnauthenticatedConnection
SoConnection	explicitConnection
CancellationToken	cancellationToken

Returns

Type	Description
Task<QueryExecutionHelper>

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteScalar(SqlCommand)

Execute a command that has a single-valued (scalar) result, and immediately clean up. Sentry will be ON.

Declaration

public static object ExecuteScalar(SqlCommand command)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute

Returns

Type	Description
object	Scalar result, which could be DBNull or a database value

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteScalar(SqlCommand, bool)

Execute a command that has a single-valued (scalar) result, and immediately clean up

Declaration

public static object ExecuteScalar(SqlCommand command, bool ignoreSentry)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute
bool	ignoreSentry	Should Sentry be ignored?

Returns

Type	Description
object	Scalar result, which could be DBNull or a database value

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteScalarAsync(SqlCommand, bool, bool, SoConnection, CancellationToken)

Execute a command that has a single-valued (scalar) result, and immediately clean up

Declaration

public static Task<object> ExecuteScalarAsync(SqlCommand command, bool ignoreSentry = false, bool useUnauthenticatedConnection = false, SoConnection explicitConnection = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute
bool	ignoreSentry	Should Sentry be ignored?
bool	useUnauthenticatedConnection	This value shall only be true when performing queries during the logon process.
SoConnection	explicitConnection
CancellationToken	cancellationToken

Returns

Type	Description
Task<object>	Scalar result, which could be DBNull or a database value

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteTypedScalarAsync<ReturnType>(SqlCommand, bool, bool, SoConnection, CancellationToken)

Execute a command that has a single-valued (scalar) result, and immediately clean up. Sentry can be ignored.

The return value is converted to the given type, if possible. If the result of the query was empty or DBNull, default(ReturnType) is returned (null fro strings, 0 for integers, etc).

Declaration

public static Task<ReturnType> ExecuteTypedScalarAsync<ReturnType>(SqlCommand command, bool ignoreSentry = false, bool useUnauthenticatedConnection = false, SoConnection explicitConnection = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute
bool	ignoreSentry	Should Sentry be ignored?
bool	useUnauthenticatedConnection
SoConnection	explicitConnection
CancellationToken	cancellationToken

Returns

Type	Description
Task<ReturnType>	Scalar result, correctly cast; or default(ReturnType)

Type Parameters

Name	Description
ReturnType

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteTypedScalar<ReturnType>(SqlCommand)

Execute a command that has a single-valued (scalar) result, and immediately clean up. Sentry will be ON.

The return value is converted to the given type, if possible. If the result of the query was empty or DBNull, default(ReturnType) is returned (null fro strings, 0 for integers, etc).

Declaration

public static ReturnType ExecuteTypedScalar<ReturnType>(SqlCommand command)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute

Returns

Type	Description
ReturnType	Scalar result, correctly cast; or default(ReturnType)

Type Parameters

Name	Description
ReturnType

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteTypedScalar<ReturnType>(SqlCommand, SoConnection)

Execute a command that has a single-valued (scalar) result, and immediately clean up. Sentry will be ON.

The return value is converted to the given type, if possible. If the result of the query was empty or DBNull, default(ReturnType) is returned (null fro strings, 0 for integers, etc).

Declaration

public static ReturnType ExecuteTypedScalar<ReturnType>(SqlCommand command, SoConnection explicitConnection)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute
SoConnection	explicitConnection

Returns

Type	Description
ReturnType	Scalar result, correctly cast; or default(ReturnType)

Type Parameters

Name	Description
ReturnType

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

ExecuteTypedScalar<ReturnType>(SqlCommand, bool)

Execute a command that has a single-valued (scalar) result, and immediately clean up.

The return value is converted to the given type, if possible. If the result of the query was empty or DBNull, default(ReturnType) is returned (null fro strings, 0 for integers, etc).

Declaration

public static ReturnType ExecuteTypedScalar<ReturnType>(SqlCommand command, bool ignoreSentry)

Parameters

Type	Name	Description
SqlCommand	command	Command to execute
bool	ignoreSentry	Should sentry be ignored

Returns

Type	Description
ReturnType	Scalar result, correctly cast; or default(ReturnType)

Type Parameters

Name	Description
ReturnType

Remarks

This class is most useful in cases where you need to run just one query. This usually calls for a series of operations, and try/catch code to make it exception proof to leave no dangling connections or readers. When used together with a 'using' statement as in the example, a good cleanup is guaranteed. To use this class, do something similar to this:

Select appntQuery = S.NewSelect();
AppointmentTableInfo appntInfo = TablesInfo.GetAppointmentTableInfo();
appntQuery.ReturnFields.Add( appntInfo.ActiveDate, appntInfo.FreeBusy );
appntQuery.Restriction = appntInfo.AssociateId.Equal( S.Parameter( taskInfo._associateId ) ).
	And( appntInfo.ActiveDate.Between( S.Parameter( taskInfo._startDate ), S.Parameter( taskInfo._endDate ) ) ).
	And( appntInfo.Type.In( 
		S.Parameter( AppointmentType.inDiary ), 
		S.Parameter( AppointmentType.inChecklist ),
		S.Parameter( AppointmentType.BookingForDiary ),
		S.Parameter( AppointmentType.BookingForChecklist ) ) );

using( QueryExecutionHelper qeh = new QueryExecutionHelper( appntQuery ) )
{
	while( qeh.Reader.Read() )
	{
		ActivitySummary item = taskInfo._items[qeh.Reader.GetDateTime( appntInfo.ActiveDate ).Date];
		bool isFree = qeh.Reader.GetInt16( appntInfo.FreeBusy ) == 1;
		if( isFree )
			++item.NumFreeActivities;
		else
			++item.NumBusyActivities;
	}
}

You can also use the class to run insert/update/delete queries, by using one of the constructors that takes an ExecutionType as an argument.

RepeatIfDeadlockAsync(Func<Task>, bool, bool)

Perform the action, and if we get a "you are the deadlock victim", wait a little while and try again a few times before giving up

Declaration

public static Task RepeatIfDeadlockAsync(Func<Task> a, bool throwOnUnresolvedDeadlock = true, bool throwOnOtherException = true)

Parameters

Type	Name	Description
Func<Task>	a
bool	throwOnUnresolvedDeadlock
bool	throwOnOtherException

Returns

Type	Description
Task

Remarks

Being selected as a deadlock victim means none of your work was done, so you can retry it. In the general case this might be an iffy thing to do, since the reason/content for our work might be impacted by whatever the other party did. However, in cases where this is not thought to be a problem, a simply 'try the same thing again' strategy works, and is implemented here.
Typical call:

QueryExecutionHelper.RepeatIfDeadlock(() => rows.Save());

Note the use of a no-parameter lambda that captures the 'rows' variable from its scope

Implements

IDisposable

Extension Methods

EnumUtil.MapEnums<From, To>(From)

QueryExectionExtensions.GetFieldPropertyRead(QueryExecutionHelper, FieldInfo)

Converters.MapEnums<From, To>(From)