Accueil > .Net, SGBDR, Sql Server > [C# SqlServer] Moteur de requêtage

[C# SqlServer] Moteur de requêtage

Dans le cadre de mes tests unitaires, je dois exploiter une base de données.
Comprendre que testant des scénarios, je dois vérifier les données qui sont insérées en base et valider leur cohérence par rapport aux données fournies en entrée.
Accessoirement, je dois aussi exporter les données afin de pouvoir les retrouver facilement et ne pas me retrouver comme un couillon devant la base de développement nouvellement rechargée depuis la production et me dire que je voudrais bien comparer avant / après.

Dans ce billet, je vais donner le code d’un que j’ai utilisé pour exécuter mes requêtes, donc ça va être un peu brut.

J’avais besoin d’un moteur de base de données pour exécuter mes requêtes (simplistes, il faut avouer).
Mais…la base n’est pas réellement trop normalisée. Enfin, ça dépend des tables et de qui les a faites.
Du coup, il y a des champs qui auraient dû (dans un monde meilleur) être des clefs étrangères (nullables) mais qui ne le sont pas.
Mais comme il faudrait une étude d’impact avant de modifier ça…et bah…c’est pas fait ! ^^

Les ORM, c’est bien mort pour mon cas.
A moins de devoir sortir une artillerie beaucoup trop lourde (rapport au projet, temps passé et à passer…).
D’autant plus que je ne sais pas dans quel état est la base de données, comprendre que mes tests doivent avoir, en pré-conditions, la validation de l’état de la base afin de répondre à la question : tous les scripts sont-ils passés ?

Et comme, là, comme ça, je ne connaissais pas d’ORM qui puisse satisfaire toutes mes contraintes et que je devais quand même avancer (autrement dit, je n’avais pas le temps de faire des POCs et…tester ^^), je me suis fais (en réalité, j’ai ressortis des cartons) la classe suivante :

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

namespace Business.Toolbox.Database
{
    /// <summary>
    /// Classe permettant la gestion de la connexion à la base de données.
    /// <para>Dans le cas d'une connexion avec transaction, la connexion est ouverte à la création de l'objet courant. Il faut appeler la méthode 
    /// <see cref="CommitTransaction"/> pour commiter et fermer la connexion. Dans le cas contraire, la transaction sera Rollback à sa fermeture.</para>
    /// </summary>
    public class ConnectionHandler : IDisposable
    {
        #region Variables privées
        /// <summary>
        /// Chaîne de connexion vers la base de données.
        /// </summary>
        private String ConnectionString { get; set; }

        /// <summary>
        /// Connexion vers la base de données.
        /// </summary>
        private SqlConnection Connexion { get; set; }

        /// <summary>
        /// Méthode permettant de loguer toutes les requêtes.
        /// </summary>
        private Action<String> Log { get; set; }

        /// <summary>
        /// Transaction liée à la connexion courante.
        /// </summary>
        private SqlTransaction Transaction { get; set; }

        /// <summary>
        /// Indique si une transaction doit être prise en compte.
        /// </summary>
        private Boolean HasTransaction { get; set; }

        #endregion

        #region Constructeur
        /// <summary>
        /// Constructeur.
        /// </summary>
        /// <param name="connectionString">Chaîne de connexion.</param>
        private ConnectionHandler(String connectionString)
        {
            ConnectionString = connectionString;
        }

        /// <summary>
        /// Constructeur.
        /// </summary>
        /// <param name="connectionString">Châine de connexion.</param>
        /// <param name="log">Méthode permettant de loguer toutes les requêtes.</param>
        private ConnectionHandler(String connectionString, Action<String> log)
            : this(connectionString)
        {
            Log = log;
        }
        #endregion

        #region Méthodes protected
        /// <summary>
        /// Permet d'ouvrir la connexion, si besoin.
        /// </summary>
        protected void OpenIfNeeded()
        {
            if (Connexion != null)
                if (Connexion.State != System.Data.ConnectionState.Open)
                    Connexion.Open();
        }

        /// <summary>
        /// Permet de fermer la connexion, si besoin.
        /// </summary>
        protected void CloseIfNeeded()
        {
            // S'il y a une transaction courante, il faut laisser la connexion ouverte.
            if (Connexion != null && !HasTransaction)
                if (Connexion.State != System.Data.ConnectionState.Closed)
                    Connexion.Close();
        }
        #endregion

        #region Méthodes publiques
        /// <summary>
        /// Permet d'exécuter une requête retournant une ou plusieurs lignes de données (SELECT).
        /// </summary>
        /// <param name="commandText">Requête SQL.</param>
        /// <param name="fillMethod">Méthode permettant de gérer la récupération des données.</param>
        /// <param name="emptyResult">Méthode permettant de gérer le fait qu'aucune ligne ne soit remontée.</param>
        /// <param name="desactivateLog">Permet de forcer la désactivation de la fonctionnalité de log.</param>
        public Boolean ExecuteReader(String commandText, 
            Action<SqlDataReader> fillMethod = null,
            Action emptyResult = null,
            Boolean desactivateLog = false)
        {
            Boolean hasResult = false;
            try
            {
                SqlCommand command = CreateCommandText(commandText, desactivateLog);
                OpenIfNeeded();
                SqlDataReader reader = command.ExecuteReader();
                hasResult = ManageDataReader(reader, fillMethod, emptyResult);
                // TRES IMPORTANT ! 
                // Dans le cas d'une transaction, la ligne suivante doit être présente sous peine de plantage.
                // Mais en règle générale, c'est aussi bien de l'avoir.
                reader.Close();
            }
            finally
            {
                CloseIfNeeded();
            }
            return hasResult;
        }

        /// <summary>
        /// Permet d'exécuter une requête retournant un seul et unique champ de résultat (SELECT).
        /// </summary>
        /// <param name="commandText">Requête SQL.</param>
        /// <param name="desactivateLog">Permet de forcer la désactivation de la fonctionnalité de log.</param>
        /// <returns>Résultat de la requête (doit être casté).</returns>
        public Object ExecuteScalar(String commandText,
            Boolean desactivateLog = false)
        {
            Object result = null;
            try
            {
                SqlCommand command = CreateCommandText(commandText, desactivateLog);
                OpenIfNeeded();
                result = command.ExecuteScalar();
            }
            finally
            {
                CloseIfNeeded();
            }
            return result;
        }

        /// <summary>
        /// Permet d'exécuter une requête ne retournant pas de résultats mais un nombre de lignes impactées (UPDATE, DELETE, INSERT).
        /// </summary>
        /// <param name="commandText">Requête SQL.</param>
        /// <param name="desactivateLog">Permet de désactiver la fonctionnalité de log.</param>
        /// <returns>Nombre de lignes impactées.</returns>
        public Int32 ExecuteNonQuery(String commandText,
            Boolean desactivateLog = false)
        {
            Int32 result = 0;
            try
            {
                SqlCommand command = CreateCommandText(commandText, desactivateLog);
                OpenIfNeeded();
                result = command.ExecuteNonQuery();
            }
            finally
            {
                CloseIfNeeded();
            }
            return result;
        }

        /// <summary>
        /// Pemret d'exécuter une procédure stockée ne retournant aucun résultat.
        /// </summary>
        /// <param name="storeprocedure">Nom de la procédure stockée.</param>
        /// <param name="populateParameters">Paramètres en entrée (IN) de la procédure.</param>
        /// <param name="recoverParameters">Récupération des paramètres de sortie (OUT) de la procédure.</param>
        /// <param name="desactivateLog">Permet de forcer la désactivation de la fonctionnalité de log.</param>
        public void ExecuteStoreProcedure(String storeprocedure,
            Action<SqlParameterCollection> populateParameters = null,
            Action<SqlParameterCollection> recoverParameters = null,
            Boolean desactivateLog = false)
        {
            try
            {
                SqlCommand command = CreateCommandStoredProc(storeprocedure);

                if (populateParameters != null)
                    populateParameters(command.Parameters);

                if (!desactivateLog)
                    LogProcedure(command);

                OpenIfNeeded();
                Int32 result = command.ExecuteNonQuery();

                if (recoverParameters != null)
                    recoverParameters(command.Parameters);
            }
            finally
            {
                CloseIfNeeded();
            }
        }

        /// <summary>
        /// Pemret d'exécuter une procédure stockée ne retournant avec résultat.
        /// </summary>
        /// <param name="storeprocedure">Nom de la procédure stockée.</param>
        /// <param name="populateParameters">Paramètres en entrée (IN) de la procédure.</param>
        /// <param name="fillMethod">Méthode déléguée permettant de gérer la récupération des données.</param>
        /// <param name="emptyResult">Méthode déléguée permettant de gérer le fait qu'aucune ligne ne soit remontée.</param>
        /// <param name="desactivateLog">Permet de forcer la désactivation de la fonctionnalité de log.</param>
        public void ExecuteStoreProcedure(String storeprocedure,
            Action<SqlParameterCollection> populateParameters = null,
            Action<SqlDataReader> fillMethod = null,
            Action emptyResult = null,
            Boolean desactivateLog = false)
        {
            try
            {
                SqlCommand command = CreateCommandStoredProc(storeprocedure);

                if (populateParameters != null)
                    populateParameters(command.Parameters);

                if (!desactivateLog)
                    LogProcedure(command);

                OpenIfNeeded();
                SqlDataReader reader = command.ExecuteReader();

                ManageDataReader(reader, fillMethod, emptyResult);
            }
            finally
            {
                CloseIfNeeded();
            }
        }

        /// <summary>
        /// Commit la transaction et ferme la connexion courante.
        /// </summary>
        public void CommitTransaction()
        {
            if (HasTransaction)
            {
                Transaction.Commit();
                Transaction.Dispose();
                HasTransaction = false;
                CloseIfNeeded();
            }
        }
        #endregion

        #region Membres IDisposable
        /// <summary>
        /// Indique si les objets managés sont "disposés".
        /// </summary>
        protected bool _disposed;

        /// <summary>
        /// Permet de fermer (si besoin) et de libérer les ressources.
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// Dispose de l'objet courant.
        /// </summary>
        /// <param name="disposing">Indique s'il faut faire un Dispose sur les objets managés.</param>
        protected void Dispose(Boolean disposing)
        {
            if (_disposed)
                return;

            if (disposing)
            {
                // Libérer les ressources managée qui implémentent IDisposable.
                CloseIfNeeded();
                Connexion.Dispose();
            }

            // Libérer les ressources NON managées et mettre leurs références à null.
            _disposed = true;
        }

        /// <summary>
        /// Destructeur.
        /// </summary>
        ~ConnectionHandler()
        {
            Dispose(false);
        }
        #endregion

        #region Méthodes privées
        /// <summary>
        /// Méthode permettant de loguer les requêtes, dans le cas où une méthode de log est alimentée.
        /// </summary>
        /// <param name="query"></param>
        private void LogQuery(String query)
        {
            if (Log != null)
                Log(query);
        }

        /// <summary>
        /// Méthode permettant de loguer les informations d'une procédure stockée, dans le cas où une méthode de log est alimentée.
        /// </summary>
        /// <param name="command">Command à exécuter.</param>
        private void LogProcedure(SqlCommand command)
        {
            if (Log != null)
            {
                StringBuilder strBuilder = new StringBuilder();
                strBuilder.AppendFormat("{0} | ", command.CommandText);
                for (Int32 index = 0; index < command.Parameters.Count; index++)
                {
                    SqlParameter param = command.Parameters[index];
                    strBuilder.AppendFormat("{0} {1} {2} '{3}' | ",
                        param.ParameterName, param.DbType, param.Direction, param.Value);
                }
                LogQuery(strBuilder.ToString());
            }
        }

        /// <summary>
        /// Méthode permettant de créer une commande au format texte.
        /// </summary>
        /// <param name="commandText">Requête sous forme de string.</param>
        /// <param name="desactivateLog">Permet de forcer la désactivation de la fonctionnalité de log.</param>
        /// <returns>Commande.</returns>
        private SqlCommand CreateCommandText(String commandText, Boolean desactivateLog)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = Connexion;
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = commandText;

            if (HasTransaction)
                command.Transaction = Transaction;

            if (!desactivateLog)
                LogQuery(commandText);

            return command;
        }

        /// <summary>
        /// Méthode permettant de créer une commande au format texte.
        /// </summary>
        /// <param name="storeprocedure">Nom de la procédure stockée.</param>
        /// <returns>Commande.</returns>
        private SqlCommand CreateCommandStoredProc(String storeprocedure)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = Connexion;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = storeprocedure;
            return command;
        }

        /// <summary>
        /// Permet de récupérer les données depuis un SqlDataReader.
        /// </summary>
        /// <param name="reader">Reader contenant les données.</param>
        /// <param name="fillMethod">Méthode pour gérer le cas où le reader contient des données.</param>
        /// <param name="emptyResult">Méthode pour gérer le cas où le reader ne contient pas de données.</param>
        /// <returns>Indique si le reader contenait des données.</returns>
        private Boolean ManageDataReader(SqlDataReader reader, 
            Action<SqlDataReader> fillMethod = null,
            Action emptyResult = null)
        {
            Boolean hasResult = false;
            if (reader.HasRows)
            {
                hasResult = true;
                if (fillMethod != null)
                    fillMethod(reader);
            }
            else
            {
                if (emptyResult != null)
                    emptyResult();
            }
            return hasResult;
        }
        #endregion

        #region Création de connexion
        /// <summary>
        /// Permet de créer la connexion vers la base de données.
        /// <para>Utilise la chaîne de connexion SgbdSaga dans l'app.config.</para>
        /// <para>Dans le cas d'une connexion avec transaction, la connexion est ouverte à la création de l'objet courant. Il faut appeler la méthode 
        /// <see cref="CommitTransaction"/> pour commiter et fermer la connexion. Dans le cas contraire, la transaction sera Rollback à sa fermeture.</para>
        /// </summary>
        /// <param name="ConnectionString">Chaîne de connexion à la base.</param>
        /// <param name="transaction">Rend disponible une transaction pour la connexion courante.</param>
        /// <param name="log">Méthode permettant de loguer les requêtes.</param>
        public static ConnectionHandler CreateConnexion(String ConnectionString, 
            Boolean transaction = false, 
            Action<String> log = null)
        {
            ConnectionHandler connexion = new ConnectionHandler(ConnectionString, log)
                { Connexion = new SqlConnection(ConnectionString) };

            // Dans le cas où la connexion doit également gérer une transaction.
            if (transaction)
            {
                connexion.OpenIfNeeded();
                connexion.Transaction = connexion.Connexion.BeginTransaction();
                connexion.HasTransaction = true;
            }

            return connexion;
        }
        #endregion
    }
}

Bon, ça fait plus de 400 lignes de code, mais c’est commenté ^^

Déjà, pour l’implémentation du IDisposable, voir ici : IDisposable, Done Right.

J’ai préféré l’approche méthode statique de création d’objet pour faire des traitements spécifiques (ici non présents) que je trouvais un peu trop déplacés dans un constructeur.

A l’origine, j’utilisais les méthodes emptyResult, mais au fil de l’eau, c’est devenu obsolète.
Cependant, je les ai gardé au cas où. Et puis ce n’est pas comme si elles étaient gênantes ! ^^

Par contre, les méthodes fillMethod sont les plus utiles : ce sont elles qui vont permettre du mapping de données (j’y reviens dans un autre billet, faut même pas imaginer que je l’ai fait à la main sur les autres classes).

Il existe aussi un Boolean desactivateLog.
C’est un peut étrange à partir du moment où on a ajouté une méthode qui permet de tracer toutes les requêtes, non ?
Eh bien non, parce que pour mes tests, j’ai deux modes de sortie : fichier plat ou…base de données.
Donc desactivateLog est présent pour éviter de loguer en base les requêtes qui permettent de loguer en base (et donc de tourner en rond).

Pour ce qui est de l’usage, il faudra déjà le faire au sein d’un bloc de ce type :

using (ConnectionHandler connexion = ConnectionHandler.CreateConnexion(connectionString))
{
    // Requêtes ici
}

Pour ce qui est des requêtes :

// Permet de retourner un SqlDataReader, donc un lot de données (SELECT)
// ExecuteReader renvoit également un Boolean pour savoir s'il y a des données ou non
// Exemple plus poussé dans la méthode GenerateTableTypes dans le pavé de code précédent
String query = "SELECT TOP 10 * FROM MaTable";
connexion.ExecuteReader(query, reader =>
{
    // Actions à réaliser dans le cas où des données sont remontées
    while (reader.Read())
    {
        // Actions à réaliser avec le reader, comme du mapping de données
    }
},
() =>
{
    // Actions à réaliser dans le cas où aucune donnée n'est remontée, facultatif
});

/*****************************************************************/
// Permet de retourner un seul et unique champ (SELECT)
String query = "SELECT MaDate FROM MaTable WHERE MaColonne = 'toto'";
Object date = connexion.ExecuteScalar(query);

/*****************************************************************/
// Permet de retourner un nombre de lignes impactées par une commande (UPDATE/DELETE)
String query "DELETE FROM MaTable WHERE MaColonne IN ('toto', 'tata', 'titi')";
Int32 nbLignesImpactees = connexion.ExecuteNonQuery(query);

/*****************************************************************/
// Permet d'exécuter une procédure stockée sans retour, mais des paramètres OUTPUT
Int32 retourParam2 = 0;
connexion.ExecuteStoreProcedure("MaProcedureAvecOutput",
    parameters =>
    {
        // Paramètres en entrée, facultatif
        parameters.AddWithValue("Param1", "Valeur1");
        parameters.Add(
                new SqlParameter("Param2", SqlDbType.Int)
                        { Direction = ParameterDirection.Output });
    },
    parameters =>
    {
        // Paramètres en sortie, facultatif
        retourParam2 = parameters["Param2"].Value as Int32?;
    });

/*****************************************************************/
// Permet d'exécuter une procédure stockée avec retour (SELECT)
connexion.ExecuteStoreProcedure("MaProcedureAvecRetour",
    parameters =>
    {
        // Paramètres en entrée, facultatif
        parameters.AddWithValue("Param1", "valeur1");
    },
    reader =>
    {
        // Actions à réaliser dans le cas où des données sont remontées, facultatif
        while (reader.Read())
        {
            // Actions à réaliser avec le reader, comme du mapping de données
        }
    },
    () => 
    {
        // Actions à réaliser dans le cas où aucune donnée n'est remontée, facultatif
    });

Tout ce qui concerne la connexion reste au sein de la connexion.
Elle est ouverte dès que requis et fermée au plus tôt.
De plus, tous les manipulation de données doivent se faire à l’extérieur de cette classe, via les différentes Action.

Pour ce qui est de la gestion d’erreur…aucune.
Pour moi, c’est une couche qui ne doit pas gérer les erreurs et exception.
Elle ne doit avoir aucune intelligence concernant ces deux points puisque ce n’est pas son rôle.

Cette classe gère aussi les transactions et là, le mécanisme change.
La connexion reste ouverte dans tout le scope du using (qui est, de préférence, le plus petit possible).
Toutes les requêtes exécutée dans le scope seront automatiquement sous l’effet de la transaction.
En cas de plantage, elle va subir un rollback de façon automatique, du coup, il ne faut pas oublier d’appeler la méthode CommitTransaction(), qui ferme la connexion en cours (et doit donc être la dernière instruction).

Et pour ce qui est des exceptions ?
Il n’y a strictement aucune gestion d’exception ici, ce n’est pas le but ni le rôle de cette classe.
Donc, toutes les exceptions remontent directement !

Voilà, beaucoup de code, du bien brut, mais ça peut toujours être utile ^^

Catégories :.Net, SGBDR, Sql Server
  1. Aucun commentaire pour l’instant.
  1. 01/12/2013 à 00:23

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

%d blogueurs aiment cette page :