private async Task <ExecuteProcedureResult <T> > ExecuteProcedure <T>(SqlQueryResultType resultType, SqlQuery query,
Func <IDataReader, T> getQueryDataResult = null, Func <IDictionary <string, object>, T> getQueryOutParamsResult = null, bool getReturnedValue = false)
{
try
{
using (var con = new SqlConnection(_connectionString))
{
using (var cmd = con.CreateCommand())
{
if (getReturnedValue)
{
query.Params.Add(new SqlParameter(ReturnParameterName, SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
});
}
SetupCommand(cmd, query);
await con.OpenAsync();
var result = new ExecuteProcedureResult <T>();
switch (resultType)
{
case SqlQueryResultType.Data:
{
var reader = await cmd.ExecuteReaderAsync();
result.SelectedResult = getQueryDataResult(reader);
reader.Close();
break;
}
case SqlQueryResultType.OutParams:
{
await cmd.ExecuteNonQueryAsync();
var outParams = GetOutputParametrs(cmd.Parameters);
result.SelectedResult = getQueryOutParamsResult(outParams);
break;
}
case SqlQueryResultType.None:
{
await cmd.ExecuteNonQueryAsync();
break;
}
default:
throw new NotImplementedException();
}
if (getReturnedValue)
{
result.ReturnedCode = GetReturnedValue(cmd.Parameters);
}
return(result);
}
}
}
catch (Exception e)
{
SqlException sqlException = e as SqlException;
if (sqlException != null)
{
//customize this as please
switch (sqlException.Number)
{
case 2627: // Unique constraint error
case 547: // Constraint check violation
case 2601: // Duplicated key row error
throw new DuplicateNameException();
case 50000: //validation exception
throw new NotSupportedException(e.Message);
case 60001:
throw new NotSupportedException(e.Message);
default:
throw;
}
}
Console.WriteLine(e);
throw;
}
}
/// <summary>
/// Executes and returns the SQL RETURN code.
/// </summary>
public async Task <int?> ExecuteAndGetReturnedCodeAsync(SqlQuery query)
{
ExecuteProcedureResult <bool> result = await ExecuteProcedure <bool>(SqlQueryResultType.None, query, getReturnedValue : true);
return(result.ReturnedCode);
}
/// <summary>
/// Returns stored procedure data and returns the SQL RETURN code.
/// </summary>
public async Task <ExecuteProcedureResult <T> > ExecuteAndGetReturnedCodeAsync <T>(SqlQuery query, Func <IDataReader, T> getQueryResult)
{
ExecuteProcedureResult <T> result = await ExecuteProcedure(SqlQueryResultType.Data, query, getQueryDataResult : getQueryResult, getReturnedValue : true);
return(result);
}
/// <summary>
/// Returns only stored procedure data.
/// </summary>
public async Task <T> ExecuteAsync <T>(SqlQuery query, Func <IDataReader, T> getQueryResult)
{
ExecuteProcedureResult <T> result = await ExecuteProcedure(SqlQueryResultType.Data, query, getQueryDataResult : getQueryResult);
return(result.SelectedResult);
}
/// <summary>
/// Returns stored procedure out parameters and returns the SQL RETURN code.
/// </summary>
public async Task <ExecuteProcedureResult <T> > ExecuteAndGetReturnedCodeAsync <T>(SqlQuery query, Func <IDictionary <string, object>, T> getQueryResult)
{
ExecuteProcedureResult <T> result = await ExecuteProcedure(SqlQueryResultType.OutParams, query, getQueryOutParamsResult : getQueryResult, getReturnedValue : true);
return(result);
}
/// <summary>
/// Returns only stored procedure out parameters.
/// </summary>
public async Task <T> ExecuteAsync <T>(SqlQuery query, Func <IDictionary <string, object>, T> getQueryResult)
{
ExecuteProcedureResult <T> result = await ExecuteProcedure(SqlQueryResultType.OutParams, query, getQueryOutParamsResult : getQueryResult);
return(result.SelectedResult);
}