/// <summary> /// Exécute un procédure avec paramètres /// </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> /// <returns><see cref="DbDataReader"/> alimenté avec le résultat de la requête</returns> public static DbDataReader ExecuteProcWithReader(string commandText, DbConnection connection, List <DbParameter> parameters) { DbDataReader reader = null; using (DbCommand command = ConnectionHelper.CreateCommand(connection, CommandType.StoredProcedure, commandText, parameters)) { try { reader = command.ExecuteReader(CommandBehavior.CloseConnection); } 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; } } return(reader); }
/// <summary> /// Exécute une requête de type INSERT, UPDATE or DELETE /// </summary> /// <param name="commandText">Requête texte</param> /// <param name="commandType">Requête type</param> /// <param name="connexion">Connexion courante</param> /// <param name="parameters">Liste de paramètres</param> /// <returns>Nombre de lignes impactées</returns> public static int ExecuteNonQuery(string commandText, CommandType commandType, DbConnection connexion, List <DbParameter> parameters) { using (DbCommand command = ConnectionHelper.CreateCommand(connexion, commandType, commandText, parameters)) { // Command execution return(command.ExecuteNonQuery()); } }
/// <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> /// Obtenir le résultat d'une requête Select /// </summary> /// <param name="commandText">Requête texte</param> /// <param name="connexion">Connexion courante</param> /// <param name="parameters">Liste de paramètres</param> /// <returns>Reader du jeu de résultat</returns> public static DbDataReader GetReader(string commandText, DbConnection connexion, List <DbParameter> parameters) { DbDataReader reader = null; using (DbCommand command = ConnectionHelper.CreateCommand(connexion, CommandType.Text, commandText, parameters)) { reader = command.ExecuteReader(CommandBehavior.CloseConnection); } return(reader); }
/// <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; } } }
/// <summary> /// Requête de type scalaire /// </summary> /// <typeparam name="T">Type attendu en retour de la requête</typeparam> /// <param name="commandText">Requête texte</param> /// <param name="commandType">Requête type</param> /// <param name="connexion">Connexion courante</param> /// <param name="parameters">Liste de paramètres</param> /// <returns>Résultat de la requête</returns> public static T ExecuteScalar <T>(string commandText, CommandType commandType, DbConnection connexion, List <DbParameter> parameters) { // result of type T T result; // Command creation using (DbCommand command = ConnectionHelper.CreateCommand(connexion, commandType, commandText, parameters)) { // Query Execution result = (T)command.ExecuteScalar(); } // It returns the result return(result); }
/// <summary> /// Obtenir le résultat d'une requête Select avec gestion de l'absence de données /// </summary> /// <param name="commandText">Requête texte</param> /// <param name="connexion">Connexion courante</param> /// <param name="parameters">Liste de paramètres</param> /// <returns>Reader du jeu de résultat</returns> public static DbDataReader GetReaderWithNoData(string commandText, DbConnection connexion, List <DbParameter> parameters) { DbDataReader reader = null; using (DbCommand command = ConnectionHelper.CreateCommand(connexion, CommandType.Text, commandText, parameters)) { reader = command.ExecuteReader(CommandBehavior.CloseConnection); } if (!reader.HasRows) { throw new CustomException(CustomExceptionErrorCode.NoDataFound); } return(reader); }
/// <summary> /// Retourne un DataSet depuis un SqlDataAdapter /// </summary> /// <param name="commandText">Requête à exécuter</param> /// <param name="connection">Connexion à la base</param> /// <param name="parameters">Liste de paramètres</param> /// <returns>Dataset alimenté depuis la liste de résultat</returns> public static DataSet GetDataSet(string commandText, DbConnection connection, List <DbParameter> parameters) { DataSet dataSet = new DataSet(); // init DataAdapter using (DbDataAdapter tempAdapter = new System.Data.SqlClient.SqlDataAdapter()) { // init Command using (DbCommand command = ConnectionHelper.CreateCommand(connection, CommandType.Text, commandText, parameters)) { // apply select on adapter tempAdapter.SelectCommand = command; // Filling the Dataset tempAdapter.Fill(dataSet); } } // return dataset return(dataSet); }