none
Optimisation de SqlConnexion RRS feed

  • Question

  • Bonjour tout le monde,

    J'ai récupéré dans un forum une application (WinForm) de gestion de commandes écrite en VB.Net, et à force de tout refaire j'ai fini par la faire fonctionner.

    Il reste un point, c'est l'optimisation de la connexion. Les données sont dans un fichier SQL Server dans le répertoire d'exécution, et l'application y accède à l'aide d'un objet SqlConnexion qui est ouvert en même temps qu'un formulaire MDI qui ouvre les autres formulaires de l'application, et qui est fermé en même temps que lui.

    On pourrait se dire que pour une fois, le concepteur de l'application a fait quelque chose de juste, puisque cette architecture lui évite d'ouvrir et fermer la connexion à tout bout de champ (il y aurait sûrement plus à redire si c'était une connexion vers une base partagée). La contrepartie est de devoir tout faire via l'objet SqlConnexion, donc en DAO (DataReader ...). Il y a des objets d'accès aux données, mais guère utilisés.

    Je suis quand même pris d'un doute : si je vais jeter un coup d'œil aux tables avant de fermer l'application, je trouve la trace de mes transactions dans SSMS, mais pas dans l'explorateur de serveurs dans Visual Studio. Est-ce que logiquement on n'aurait pas plutôt pu s'attendre à l'inverse ?

    Le plus gênant, c'est que si je ferme et que je rouvre l'application, je ne retrouve pas mes nouvelles données, un peu comme si la session précédente de l'application n'avait pas eu lieu.

    Ces derniers jours j'ai plusieurs fois réinstallé SQL Server car je ne réussissais pas à le lancer (un coup ça ne marchait que depuis l'application, un coup que depuis SSMS, un coup pas du tout), et pour finir il me semble qu'Avast a dû fermer une vanne en serrant un peu trop fort. En le désactivant je finis par faire fonctionner l'application, mais dans les conditions que je viens de décrire. Je ne risquais pas d'oublier de désactiver Avast pendant la réinstallation car je me faisais jeter message d'erreur à l'appui (en gros, fichier verrouillé), pour finir il s'avère qu'il est souhaitable de le désactiver aussi pendant l'exécution. J'ai posé dans un autre forum (en Anglais) la question de son paramétrage.

    Il semblerait qu'un peu plus d'expérience de ma part serait bienvenue pour savoir par où prendre le problème. Que me conseillez-vous ?




    • Modifié Gloops lundi 29 juin 2015 12:01
    lundi 29 juin 2015 11:39

Réponses

  • Bonjour,

    Avant d'aller plus loin dans votre problématique, il est TRES important de fermer la connexion à SQL Server une fois que vous n'en avez plus besoin.

    Donc ouvrir la connexion au lancement de la fenêtre et la fermer quand la fenêtre se ferme est une TRES mauvaise idée. (et ce n'est en rien une optimisation)


    Richard Clark
    Consultant - Formateur .NET
    http://www.c2i.fr
    Depuis 1996: le 1er site .NET francophone


    lundi 29 juin 2015 12:11
  • Bonjour,

    Est-ce que par hasard votre base de données est un fichier qui fait partie de votre projet ? Et ce fichier ne serait-il pas en mode "Toujours copier" dans sa propriété "Copier dans le répertoire de sortie" ?

    Car ca ressemble fortement à un écrasement de votre base à chaque lancement de votre application depuis Visual Studio.

    Cordialement,


    Yan Grenier

    Merci de bien vouloir "Marquer comme réponse", les réponses qui ont répondues à votre question, et de noter les réponses que vous avez trouvé utiles.

    • Marqué comme réponse Gloops jeudi 2 juillet 2015 06:31
    jeudi 2 juillet 2015 06:10

Toutes les réponses

  • C'est pas très claire. Pourriez-vous poster votre chaine de connexion (connectionString)?

    Please click "Mark As Answer" if my post helped.

    lundi 29 juin 2015 12:03
  • Bonjour,

    Avant d'aller plus loin dans votre problématique, il est TRES important de fermer la connexion à SQL Server une fois que vous n'en avez plus besoin.

    Donc ouvrir la connexion au lancement de la fenêtre et la fermer quand la fenêtre se ferme est une TRES mauvaise idée. (et ce n'est en rien une optimisation)


    Richard Clark
    Consultant - Formateur .NET
    http://www.c2i.fr
    Depuis 1996: le 1er site .NET francophone


    lundi 29 juin 2015 12:11
  • Bonjour,

    Avant d'aller plus loin dans votre problématique, il est TRES important de fermer la connexion à SQL Server une fois que vous n'en avez plus besoin.

    Donc ouvrir la connexion au lancement de la fenêtre et la fermer quand la fenêtre se ferme est une TRES mauvaise idée. (et ce n'est en rien une optimisation)


    Richard Clark
    Consultant - Formateur .NET
    http://www.c2i.fr
    Depuis 1996: le 1er site .NET francophone


    Bonjour,

    C'est effectivement ce que j'ai appris de manière générale. Je voulais laisser à l'auteur de l'application une chance d'avoir fait quelque chose de bon :)

    Allez c'est même au point que je ne vais pas attendre d'avoir fait les tests avant de marquer la réponse ...

    Oh je suis un peu sévère : il y a l'interface MDI pour appeler les différents formulaires avec des menus déroulants, qui fonctionne correctement.

    • Modifié Gloops lundi 29 juin 2015 13:13
    lundi 29 juin 2015 12:59
  • C'est pas très claire. Pourriez-vous poster votre chaine de connexion (connectionString)?

    Please click "Mark As Answer" if my post helped.

    Oui c'est vite fait, même si la réponse de Richard rejoint ce que j'avais déjà appris, et ... finalement noyer le poisson n'était probablement pas une bonne idée.

        Public strcon As String = "Data Source=.\SQLEXPRESS;Initial Catalog=Gestion_Commandes;Integrated Security=True"
    

    lundi 29 juin 2015 13:00
  • Hum ... Finalement peut-être va-t-il falloir décortiquer un peu quand même.

    Voici un extrait de code lié à un bouton :

       Sub Bouton_Click(obj, e)
        connecter()
        // lecture de la quantité en stock, retirée pour simplifier
        
        // création de la ligne de commande :
        req = "INSERT INTO detail_commande VALUES('" & Me.txt_codCom.Text & "', '" & Art.cod_Art & "', '" & Me.txt_qte.Text & "')"
        'MsgBox(req)
        cmd = New SqlCommand(req, con)
        cmd.ExecuteNonQuery()
        deconnecter()
        End Sub
    
        Public Sub connecter()
            Try
                If con.State = ConnectionState.Closed Then
                    MsgBox("Connexion")
                    con.Open()
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub
        Public Sub deconnecter()
            MsgBox("Déconnexion")
            con.Close()
        End Sub

    Grâce à un point d'arrêt, j'ai récupéré le code de la requête et je l'ai exécuté dans l'interface (nouvelle requête) de l'explorateur de serveurs. J'ai vu la nouvelle ligne de commande dans la table.

    En revanche si je laisse l'application (le code ci-dessus) se débrouiller pour créer la ligne de commande, je la vois dans SSMS, mais pas dans l'explorateur de serveurs, et comme je l'ai dit ce matin si je relance l'application la saisie est perdue. D'ailleurs l'information dans SSMS est écrasée. Fonctionnellement on peut rêver mieux.

    Est-ce que ça parle à quelqu'un ?



    • Modifié Gloops lundi 29 juin 2015 14:53
    lundi 29 juin 2015 14:51
  • Tout d'abord, pourquoi se déconnecter après la commande INSERT?

    Comme l'a souligné Richard, c'est très mauvais d'ouvrir une connexion juste pour la fermer après exécution de la requête. Essayez de supprimer l'appel à la procédure "deconnecter()". Faites en appel après avoir être sure de ne plus ouvrir la connexion de nouveau.

    Avez-vous essayé de rafraichir l'explorateur de serveurs après exécution de l'application?

    Autre chose, votre code n'est pas sécurisé. Il y a une faille de "SQL Injection".


    Please click "Mark As Answer" if my post helped.

    lundi 29 juin 2015 15:08
  • Parmi les modifications que j'ai effectuées dans l'application, changer le type de clefs des tables, pour mettre du numérique plutôt que du varchar, afin de pouvoir trier dans l'ordre numérique.

    Curieusement, malgré ça ça passe bien avec des apostrophes autour de la clef. Sans aussi d'ailleurs, c'est plutôt la façon de passer la requête (par le code ou par l'interface de l'explorateur) qui influe sur le succès ou non.

    Moi qui avais l'habitude sur d'autres plateformes de me faire insulter si je mettais des apostrophes autour d'un nombre ou que je les oubliais autour d'une chaîne de caractères ...

    lundi 29 juin 2015 15:09

  • Tout d'abord, pourquoi se déconnecter après la commande INSERT?

    Comme l'a souligné Richard, c'est très mauvais d'ouvrir une connexion juste pour la fermer après exécution de la requête. Essayez de supprimer l'appel à la procédure "deconnecter()". Faites en appel après avoir être sure de ne plus ouvrir la connexion de nouveau.

    Avez-vous essayé de rafraichir l'explorateur de serveurs après exécution de l'application?

    Autre chose, votre code n'est pas sécurisé. Il y a une faille de "SQL Injection".


    Please click "Mark As Answer" if my post helped.

    Ce qu'a dit Richard est "il est TRES important de fermer la connexion à SQL Server une fois que vous n'en avez plus besoin." Ne comprenons-nous pas cette phrase de la même manière ?

    Par ailleurs, l'application comporte effectivement une procédure Ajout, avec juste le point faible qu'elle n'est appelée que trois fois sur quatre. Là j'ai mis le code directement en me disant que ça risquait d'être plus lisible dans un forum. Une fois ce fonctionnement asynchrone expliqué je remettrai effectivement l'appel à la procédure ajout. Finalement, avec l'interface MDI, ça faisait deux points valables dans cette application.

    Il y a quelque chose de curieux dans cette procédure d'ailleurs, c'est qu'il faut convertir la clef en varchar parce que le contrôle (de non pré-existence de la clef) est conçu comme ça (j'ai reçu des clefs en varchar et les ai changées car ça donnait un tri inadapté), et que c'était aussi rapide de faire la conversion que de réécrire le contrôle.

    Oui j'ai cliqué sur le point d'exclamation au-dessus de la requête ouverte depuis l'explorateur de serveurs, afin de rafraîchir les résultats. Ma nouvelle ligne de commande n'était pas là. Dans SSMS, si.


    • Modifié Gloops lundi 29 juin 2015 15:38
    lundi 29 juin 2015 15:20
  • La bonne méthode pour faire un insert (et éviter aussi l'injection SQL) :

        Private Const ConnectionString As String = "..."
        Private Const SqlInsert As String = "INSERT INTO detail_commande (CodeCommande, CodeArticle, Quantite) VALUES (@CodeCommande, @CodeArticle, @Quantite)"
        Public Function InsertCommande(codeCommande As String, codeArticle As String, quantite As Integer) As Integer
            Using connexion As New SqlConnection(connectionString)
                Using command As New SqlCommand(sqlInsert, connexion)
                    command.Parameters.AddWithValue("@CodeCommande", codeCommande)
                    command.Parameters.AddWithValue("@CodeArticle", codeArticle)
                    command.Parameters.AddWithValue("@Quantite", quantite)
                    connexion.Open()
                    Return command.ExecuteNonQuery()
                End Using
            End Using
        End Function

    L'utilisation de Using fait que les méthodes Dispose de l'objet Connexion et de l'objet Command sont appelées à la fin de l'exécution de la fonction.

    Et vous remarquerez que l'on a pas besion de guillemets ;-)


    Richard Clark
    Consultant - Formateur .NET
    http://www.c2i.fr
    Depuis 1996: le 1er site .NET francophone


    mardi 30 juin 2015 07:17
  • Bonjour,

    Cette syntaxe est effectivement meilleure à différents points de vue :

    • par l'utilisation de using, elle libère la ressource mentionnée dès qu'elle n'est plus utilisée, obtenant ainsi une meilleure utilisation de la mémoire
    • par l'utilisation d'une requête paramétrée, elle améliore la sécurité
    • aussi par l'utilisation des paramètres de la requête, elle libère de la contrainte d'avoir à convertir les données

    C'est bien pour cette raison que c'est l'équivalent en C# que j'utilise dans mes applications. Je viens de la tester dans celle que je suis en train de déboguer (d'où l'intérêt de le faire : ça me permet de prendre conscience que les bénéfices de la syntaxe que j'utilisais vont au-delà de ce que j'imaginais).

    J'ai laissé le code article et la quantité pour la ligne de commande, et parce que c'est plus simple à tester en fonction d'où ça se trouve dans l'application j'ai mis en œuvre la création de commande.

        Public Function InsertCommande(ByVal codeCommande As Integer, ByVal DateCommande As Date, ByVal cod_cli As Integer) As Integer
            Using connexion As New SqlConnection(strcon)
                Dim sqlInsert As String
                sqlInsert = "INSERT INTO Commande(cod_com, dat_com, cod_cli) VALUES(@CodeCommande,  @DateCommande, @CodeClient)"
                Using command As New SqlCommand(sqlInsert, connexion)
                    command.Parameters.AddWithValue("@CodeCommande", codeCommande)
                    command.Parameters.AddWithValue("@DateCommande", DateCommande)
                    command.Parameters.AddWithValue("@CodeClient", cod_cli)
                    connexion.Open()
                    Return command.ExecuteNonQuery()
                End Using
            End Using
        End Function
    

    et pour l'appel :

            Module1.InsertCommande(txt_codCom.Text, Me.txt_dateCom.Value, C.Id)
    ce qui effectivement m'a évité de ramer pour le formatage de la date (c'est la partie qui se voit), et d'autre part améliore le reste.

    Maintenant, ça serait bien aussi de solutionner l'autre problème, celui que je posais : celui de la permanence des données.

    Nous sommes en progrès, la commande créée apparaît dans l'explorateur de serveurs dans Visual Studio.

    Maintenant, il me reste à m'assurer qu'après avoir créé les commandes 23 et 24, l'application évite à la session suivante de me proposer de créer la commande 22. Mais là aussi, la réponse pourrait s'apparenter aux inconvénients de la "programmation spaghetti", et il faut bien reconnaître que faire ça dans un langage modulaire, c'est dommage.

    Je regarde ça et je vous dis ...


    • Modifié Gloops mercredi 1 juillet 2015 13:30
    mercredi 1 juillet 2015 13:20
  • C'est clair qu'en lisant le numéro de commande au bon moment ça aide :)

    Au passage je le fais aussi comme ça :

        Public Function GetNumProchaineCommande()
            Using connexion As New SqlConnection(strcon)
                Dim i As Int64
                Dim sqlNumComd As String
                sqlNumComd = "SELECT MAX(cod_com) FROM commande"
                Using cmd As New SqlCommand(sqlNumComd, connexion)
                    connexion.Open()
                    Dim dr As SqlDataReader
                    Dim result As Int64
                    dr = cmd.ExecuteReader
                    dr.Read()
                    If (dr.HasRows) Then
                        result = dr.GetInt64(0) + 1
                    Else
                        result = 1
                    End If
                    dr.Close()
                    Return result
                End Using
            End Using
        End Function
    

    Je me suis retrouvé avec une difficulté inhabituelle (l'antivirus qui verrouille la base de données), ce qui fait que dans une situation inhabituelle de programmation (design patterns non respectés) j'ai cherché dans la mauvaise direction.

    Merci pour le coup de main, maintenant ça devrait aller.

    mercredi 1 juillet 2015 14:05
  • Bonjour, euh ... c'est encore moi.

    J'ai donc noté le progrès que les données apparaissaient dans l'explorateur de solutions, dans Visual Studio, alors qu'avant elles n'apparaissaient que dans SSMS.

    Le hic, c'est que ça ne dure pas.

    Je viens de faire une session où j'ai créé les commandes 22 à 25, tout s'est bien passé, je ferme l'application, j'affiche la table des commandes à partir de l'explorateur de serveurs et je vois bien les commandes jusqu'à la 25.

    Je relance l'application, GetNumProchaineCommande me retourne 22.

    Je ferme l'application, je rafraîchis la requête obtenue à partir de l'explorateur de serveurs, elle m'affiche les commandes jusqu'à la 22 : pour limiter la casse j'ai recréé la commande correspondant au numéro en cours en observant qu'elle n'existe pas (ce qui, en toute logique, est une ineptie, donc normalement je me retrouve avec les commandes jusqu'à la 21, comme à la session précédente).

    Mettons que pour les lignes de commandes ce soit un peu plus sophistiqué car j'ai utilisé une transaction, mais pour les commandes c'est la requête ci-dessus (InsertCommande, avec la syntaxe d'appel indiquée).

    Accessoirement, la boucle de développement est un peu fluidifiée en ajoutant le répertoire obj dans les exceptions de l'antivirus, après il faudra que je me rappelle comment autoriser plusieurs connexions sur la base pour ne pas être obligé de fermer la connexion de l'explorateur de serveurs.

    Mais surtout, le point clef serait que l'application retrouve les commandes qu'elle avait à la session précédente.

    J'ai modifié les fonctions de lecture de la liste des clients et de la liste des articles pour utiliser le mot-clef using, il ne reste que la fonction de suppression de commande qui n'utilise pas ça, mais je n'ai pas effectué de suppression de commande.

    Bon allez on n'est plus à une fonction près :

        Public Function InsertLigneCommande(ByVal codeCommande As Integer, ByVal codeArticle As Int64, ByVal qteComm As Integer)
            Dim booExit As Boolean
            Using scope As TransactionScope = New TransactionScope()
    
                Using connexion As New SqlConnection(strcon)
                    Dim sqlInsert As String
                    Dim qtestock As Integer
                    sqlInsert = "SELECT q_stock FROM article WHERE cod_art = @CodeArticle"
                    Using command As New SqlCommand(sqlInsert, connexion)
                        command.Parameters.AddWithValue("@CodeArticle", codeArticle)
                        connexion.Open()
                        Dim dr As SqlDataReader
                        dr = command.ExecuteReader
                        dr.Read()
                        If (dr.HasRows) Then
                            qtestock = dr.GetInt32(0)
                        Else
                            MsgBox(String.Format("article inconnu : @0", codeArticle))
                            booExit = True
                        End If
                        dr.Close()
                    End Using
                    If Not booExit Then
                        If qtestock < qteComm Then
                            MsgBox(String.Format("Quantité commandée {0} supérieure à la quantité en stock {1}", qteComm, qtestock))
                            booExit = True
                        End If
                        If Not booExit Then
                            Dim nb As Integer
                            nb = qtestock - qteComm
                            sqlInsert = "UPDATE article SET q_stock = @QteStock WHERE cod_art = @CodeArticle"
                            Using command As New SqlCommand(sqlInsert, connexion)
                                command.Parameters.AddWithValue("@QteStock", nb)
                                command.Parameters.AddWithValue("@CodeArticle", codeArticle)
                                command.ExecuteNonQuery()
                            End Using
                            sqlInsert = "INSERT INTO detail_commande(cod_com, cod_art, qte_com) VALUES(@CodeCommande, @CodeArticle, @QteCommandee)"
                            Using command As New SqlCommand(sqlInsert, connexion)
                                command.Parameters.AddWithValue("@CodeCommande", codeCommande)
                                command.Parameters.AddWithValue("@CodeArticle", codeArticle)
                                command.Parameters.AddWithValue("@QteCommandee", qteComm)
                                Return command.ExecuteNonQuery
                            End Using
                        Else
                            Return ""
                        End If
                    Else
                        Return ""
                    End If
                End Using
            End Using
        End Function
    

    jeudi 2 juillet 2015 06:02
  • Bonjour,

    Est-ce que par hasard votre base de données est un fichier qui fait partie de votre projet ? Et ce fichier ne serait-il pas en mode "Toujours copier" dans sa propriété "Copier dans le répertoire de sortie" ?

    Car ca ressemble fortement à un écrasement de votre base à chaque lancement de votre application depuis Visual Studio.

    Cordialement,


    Yan Grenier

    Merci de bien vouloir "Marquer comme réponse", les réponses qui ont répondues à votre question, et de noter les réponses que vous avez trouvé utiles.

    • Marqué comme réponse Gloops jeudi 2 juillet 2015 06:31
    jeudi 2 juillet 2015 06:10
  • Bingo, c'était bien ça. Merci.

    Bon, j'espère que maintenant je vais pouvoir faire une pause avec les questions de débutant.

    jeudi 2 juillet 2015 06:28