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); }