/// <summary>
/// Exécute un procédure avec paramètres en vérifiant la concurrence
/// </summary>
/// <param name="commandText">Nom de la procédure</param>
/// <param name="connection">Connexion à la base de donnée</param>
/// <param name="parameters">Liste de paramètres</param>
/// <param name="actionReader">Méthode appelée lors de chaque itération sur le datareader</param>
/// <param name="actionReader2">Méthode appelée lors de chaque itération du second datareader</param>
public static void ExecuteProcWithReaderWithConcurrency(string commandText, DbConnection connection, List <DbParameter> parameters, Action <IDataReader> actionReader, Action <IDataReader> actionReader2)
{
// to check concurrency add parameter
// stored procedure must contain an output param named @nbModifiedLine
SqlServerConnectionHelper.AddParamToList(parameters, "@nbModifiedLine", 0, ParameterDirection.Output);
using (DbCommand command = ConnectionHelper.CreateCommand(connection, CommandType.StoredProcedure, commandText, parameters))
{
try
{
using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
// if we want to get output parameter we need to process and close the datareader.
// we pass the treatment on datareader as parameter
actionReader(reader);
}
reader.NextResult();
while (reader.Read())
{
// if we want to get output parameter we need to process and close the datareader.
// we pass the treatment on datareader as parameter
actionReader2(reader);
}
}
// Get output params AFTER we've processed and CLOSED the SqlDataReadeer
int count = (int)command.Parameters["@nbModifiedLine"].Value;
if (count <= 0)
{
throw new CustomException(CustomExceptionErrorCode.ConcurrentAccess);
}
}
catch (SqlException ex)
{
int errorNumber = ex.Number;
// Managing error code sent from procedure with the try catch
// If an error occured in the procedure, a message is sent with all the informations
// (the error code is at the beginning of the message)
if (errorNumber == 50000)
{
errorNumber = SqlServerConnectionHelper.GetNumberException(ex.Message);
}
// throw custom exception
SqlServerConnectionHelper.ManageExceptionByErrorNumber(errorNumber, ex);
// default
throw;
}
}
}
/// <summary>
/// Exécute un procédure avec paramètres
/// Si <see cref="checkConcurrency"/> est vrai on n'utilise pas la valeur en retour de <see cref="ExecuteNonQuery"/>.
/// <see cref="RowCount"/> doit être effectué manuellement dans toutes les procédures.
/// </summary>
/// <param name="commandText">Nom de la procédure</param>
/// <param name="connection">Connexion à la base de donnée</param>
/// <param name="parameters">Liste de paramètres</param>
/// <param name="checkConcurrency">Concurrence à vérifier oui non</param>
public static void ExecuteProc(string commandText, DbConnection connection, List <DbParameter> parameters, bool checkConcurrency)
{
// to check concurrency add parameter
// stored procedure must contain an output param named @nbModifiedLine
if (checkConcurrency)
{
SqlServerConnectionHelper.AddParamToList(parameters, "@nbModifiedLine", 0, ParameterDirection.Output);
}
using (DbCommand command = ConnectionHelper.CreateCommand(connection, CommandType.StoredProcedure, commandText, parameters))
{
try
{
// Command execution :
// dont use the return value of ExecuteNonQuery because NOCOUNT could be OFF or ON
// manage nb lines manually with @nbModifiedLine
command.ExecuteNonQuery();
if (checkConcurrency)
{
// read parameter to check concurreny
DbParameter paramCount = parameters.First(p => p.ParameterName == "@nbModifiedLine");
int count = int.Parse(paramCount.Value.ToString());
if (count <= 0)
{
throw new CustomException(CustomExceptionErrorCode.ConcurrentAccess);
}
}
}
catch (SqlException ex)
{
int errorNumber = ex.Number;
// Managing error code sent from procedure with the try catch
// If an error occured in the procedure, a message is sent with all the informations
// (the error code is at the beginning of the message)
if (errorNumber == 50000)
{
errorNumber = SqlServerConnectionHelper.GetNumberException(ex.Message);
}
// throw custom exception
SqlServerConnectionHelper.ManageExceptionByErrorNumber(errorNumber, ex);
// default
throw;
}
}
}
