Convertir chaine sql en stringbuilder

**olibara** · 05/08/2010, 11h34

Bonjour

Il m'arrive souvent de transposer une requete SQL en StringBuilder

Trop souvent a mon gout !
Et c'est chaque fois asser pénible de redecouper la chaine honteusement déstructuée par SQL Management Studio pour une remise en forme humainement lisible a coup des Append dans un stringbuilder

Donc j'ai décidé s'écrire un petit outil qui fait ca automatiquement.

Mais avant de me lancer, avez-vous déja vu qq chose dans ce genre ?

Merci

**Mehdi_ab** · 05/08/2010, 13h11

Bonjour,

Tu peux stp illustrer ton propos avec un exemple concret ?

Merci

**Pol63** · 05/08/2010, 13h28

je ne vois pas du tout ce que tu fais ou alors je ne vois pas l'utilité ...

**Pol63** · 05/08/2010, 13h31

si finalement je crois avoir compris
tu fais une requete dans sql server et tu la colles dans ton code et tu pleures voyant l'étendu des dégats ^^

alors le mieux c'est d'ajouter un nouveau fichier au projet, fichier de type texte (.txt)
tu colles ta requetes dedans
l'ajouter ensuite dans les ressources (drag drop sur les ressources du projet)
et ensuite

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

moncommand.commandtext = my.Resources.nom_du_fichier_texte

un fichier texte en ressources étant transformé en string à l'utilisation

(en espérant que ca existe en c#, enfin en vb.net my.resources existe)

**olibara** · 05/08/2010, 13h33

Merci

Voici un exemple concret

Ceci

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT     COUNT(*) AS cnt,
 PhysicalBag.ID, PhysicalBag.Number, PhysicalBag.FillingLevel AS FLev, PhysicalBag.OpenDate, PhysicalBag.CloseRequestDate AS CloseNeedDt, 
      PhysicalBag.CloseRequiredDate AS CloseAskedDt, PhysicalBag.CloseDate AS CloseDt, PhysicalBag.ParcelCollectDefer AS ColectDay, 
  PhysicalBag.ReceivedDate AS ReceivedDt, 
           PhysicalBag.DispatchedDate, Affiliate.HRK, Affiliate.Name, Affiliate.PhoneNumber, Affiliate.FaxNumber, 
              Affiliate.GSMNumber, Affiliate.Email
FROM PhysicalBag INNER JOIN
      Affiliate ON PhysicalBag.AffiliateID = Affiliate.ID INNER JOIN
  VirtualBag ON PhysicalBag.ID = VirtualBag.PhysicalBagID INNER JOIN
                      TradeIn ON VirtualBag.ID = TradeIn.VirtualBagID
WHERE     (PhysicalBag.OpenDate < CONVERT(DATETIME, '2010-07-30 00:00:00', 102)) AND (PhysicalBag.CloseDate IS NULL) AND (PhysicalBag.CloseRequestDate IS NULL)
GROUP BY PhysicalBag.ID,
 PhysicalBag.Number, PhysicalBag.FillingLevel
 , PhysicalBag.OpenDate, PhysicalBag.CloseRequestDate, PhysicalBag.CloseRequiredDate, 
     PhysicalBag.CloseDate, PhysicalBag.ParcelCollectDefer, PhysicalBag.ReceivedDate, PhysicalBag.DispatchedDate, Affiliate.HRK, Affiliate.Name, 
 Affiliate.PhoneNumber,
 Affiliate.FaxNumber, Affiliate.GSMNumber, Affiliate.Email

Peut devenir cela...
(Mais entretemps j'ai déja ecris la procédure qui permet en plus de passer par le clipboard

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
      StringBuilder sb = new StringBuilder();
      sb.Append(" SELECT ");
      sb.Append(" COUNT(*) AS cnt,");
      sb.Append(" PhysicalBag.ID,");
      sb.Append(" PhysicalBag.Number,");
      sb.Append(" PhysicalBag.FillingLevel AS FLev,");
      sb.Append(" PhysicalBag.OpenDate,");
      sb.Append(" PhysicalBag.CloseRequestDate AS CloseNeedDt,");
      sb.Append(" PhysicalBag.CloseRequiredDate AS CloseAskedDt,");
      sb.Append(" PhysicalBag.CloseDate AS CloseDt,");
      sb.Append(" PhysicalBag.ParcelCollectDefer AS ColectDay,");
      sb.Append(" PhysicalBag.ReceivedDate AS ReceivedDt,");
      sb.Append(" PhysicalBag.DispatchedDate,");
      sb.Append(" Affiliate.HRK,");
      sb.Append(" Affiliate.Name,");
      sb.Append(" Affiliate.PhoneNumber,");
      sb.Append(" Affiliate.FaxNumber,");
      sb.Append(" Affiliate.GSMNumber,");
      sb.Append(" Affiliate.Email");
      sb.Append(" FROM");
      sb.Append(" PhysicalBag ");
      sb.Append("  INNER JOIN Affiliate ON PhysicalBag.AffiliateID = Affiliate.ID ");
      sb.Append("  INNER JOIN VirtualBag ON PhysicalBag.ID = VirtualBag.PhysicalBagID ");
      sb.Append("  INNER JOIN TradeIn ON VirtualBag.ID = TradeIn.VirtualBagID");
      sb.Append(" where (PhysicalBag.OpenDate < CONVERT(DATETIME, '2010-07-30 00:00:00', 102)) ");
      sb.Append("   AND (PhysicalBag.CloseDate IS NULL) ");
      sb.Append("   AND (PhysicalBag.CloseRequestDate IS NULL) ");
      sb.Append(" GROUP BY PhysicalBag.ID,");
      sb.Append(" PhysicalBag.Number,");
      sb.Append(" PhysicalBag.FillingLevel,");
      sb.Append(" PhysicalBag.OpenDate,");
      sb.Append(" PhysicalBag.CloseRequestDate,");
      sb.Append(" PhysicalBag.CloseRequiredDate, ");
      sb.Append(" PhysicalBag.CloseDate,");
      sb.Append(" PhysicalBag.ParcelCollectDefer,");
      sb.Append(" PhysicalBag.ReceivedDate,");
      sb.Append(" PhysicalBag.DispatchedDate,");
      sb.Append(" Affiliate.HRK, Affiliate.Name, ");
      sb.Append(" Affiliate.PhoneNumber,");
      sb.Append(" Affiliate.FaxNumber,");
      sb.Append(" Affiliate.GSMNumber,");
      sb.Append(" Affiliate.Email");
      sb.Append("   having (COUNT(*)>5)");

**Bluedeep** · 05/08/2010, 13h58

Oui, et pourquoi tu utilises un StringBuilder ici?
J'avoue ne pas du tout comprendre : tu ne rajoutes pas d'élements conditionel nécessitant l'AppendFormat.

**Pol63** · 05/08/2010, 13h59

on a du se croiser au niveau des posts
ma réponse devrait te convenir, vu que dans le fichier texte tu fais la mise en forme que tu veux comme dans l'ide sql

et le fichier txt va etre compilé dans l'exe

**SetaSensei** · 05/08/2010, 14h00

Bonjour,

Dans un soucis de lisibilité, n'avez-vous jamais pensé passer par une vue, voire une procédure stockée sur votre base ?

**Pol63** · 05/08/2010, 14h00

Envoyé par Bluedeep

Oui, et pourquoi tu utilises un StringBuilder ici?
J'avoue ne pas du tout comprendre : tu ne rajoutes pas d'élements conditionel nécessitant l'AppendFormat.

un stringbuilder est des centaines (des millions ?) de fois plus performant qu'un string pour de la concaténation

Envoyé par SetaSensei

Bonjour,

Dans un soucis de lisibilité, n'avez-vous jamais pensé passer par une vue, voire une procédure stockée sur votre base ?

les vues c'est pas mal sauf que pour une modif simple du code il faut ouvrir le sgbdr pour aller voir ce que fait la vue au lieu d'avoir le code sous la main
et puis une vue c'est trop simple, on peut modifier la requete sans avoir à recompiler l'exe ... c'est pas drole

**Guulh** · 05/08/2010, 14h13

Ce que tu dis est vrai quand le compilo ne sait pas ce que tu concatènes ; mais il est assez malin pour traiter statiquement string bidule = "a + "b" (qui devient string bidule = "ab" dans le bytecode). Donc l'argument de la perte de perfs n'est pas valide

Et même, plutôt que de préfixer chaque ligne par " et la suffixer par " + , il est bien plus simple d'utilser ce cher @, de cette façon :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
  string request = 
@"
SELECT trucmuche
FROM TABLE_MACHIN
";

C'est tout de même plus simple.

**tomlev** · 05/08/2010, 14h22

Envoyé par Pol63

un stringbuilder est des centaines (des millions ?) de fois plus performant qu'un string pour de la concaténation

Oui et non... si c'est pour un nombre limité de concaténations (a priori quelques dizaines grand max dans un cas comme celui là), ça ne fait pas une grosse différence.

Par contre, si tu fais des concaténations en boucle, avec potentiellement un grand nombre d'itérations, là il faut absolument utiliser StringBuilder pour éviter de créer inutilement des tonnes d'instances de String de plus en plus longues...

**Bluedeep** · 05/08/2010, 14h27

Envoyé par Pol63

un stringbuilder est des centaines (des millions ?) de fois plus performant qu'un string pour de la concaténation

Je le sais, mais ici il n'a pas de concaténation à faire justement.

**olibara** · 05/08/2010, 14h32

Pour repondre a l'avalanche des derniers post

1- Oui les vues et les procédures stocquées c'est bien, mais ca n'exclus pas de pouvoir aussi dans certains cas utiliser des requetes dans le code

2- Ce que j'ai montré est un exemple pour Mehdi_ab qui ne comprenait pas, mais il est evident que le but etait de pouvoir generer un code facilement manipulable et ne pas cammoufler un texte sql dans les ressources

2- Pas besoin de faire des AppendFormat pour utiliser un stringbuilder, l'intéret d'un stringbuilder, ormis la performance, c'est precisemént la facilité le cas echéant de pouvoir ajouter un AppendFormat sans devoir faire d'horribles manipulations sur le string.
Que ce soit sous la forme
""+""+""+""
ou sous la forme
@"
sdf
sdf
sdf
sdf";

Croyez moi, un stringbuilder rends les choses beaucoup plus simple et lisible

Et comme je l'ai dis, j'ai maitenant fait un petit outil a qui je peux donner une chaine SQL et qui me genere un code comme illusté

**olibara** · 05/08/2010, 14h44

pour BlueDeep

Je le sais, mais ici il n'a pas de concaténation à faire justement.

A bon ?

Je t'informe que chaque Append est une concaténation en vue de produire la chaine SQL résultante

Comme je l'ai dis l'intéret du stringbuilder et de pouvoir faicilemnt introduire le cas échéant un AppendFormat on manipuler des eléments

Moi quand je vois du code style

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

string machaine="abc"+a.Tostring()+" - "+"un bazar "+"+"+Mafonction().Tostring()+"\"quote\""+" "+DateTime.Now.Year.Tostring()+"/"+DateTime.Now.Month.Tostring();

J'attrappe la migraine et je cours chercher une aspirine ou j'envoie une paire de claque selon l'humeur

**ctxnop** · 05/08/2010, 14h54

Personnellement j'ai ajouter cette fonction dans un AddIn que j'ai codé pour la boite qui m'emploie. En l'occurence je construit une simple string, pas un stringbuilder, mais tu pourrais l'adapter.
Tu peux taper ca en macro plutot qu'en addin. Le principe étant simplement de prendre le texte du presse papier et de le modifier en ajoutant les "sb.AppendLine(", ....
Il faut également prendre en compte que la chaine collée peut contenir des caractères qu'il faut échaper (typiquement les guillemets).
Ma fonction est faite pour coller n'importe quel texte dans une variable string, ca n'est pas dédié au SQL, par contre après tu peux jouer avec les snippets pour qu'en 3 clique tu colle une requete et ca te sort un datatable prêt a l'emploie.
Et fait des AppendLine, pas seulement des Append. Ca parait con, mais au debug ca change tout

Avec un Append tu perd les retours a la ligne et la requete devient très vite illisible.

Mon code a adapter pour toi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
    /// <summary>
    /// Colle du texte en tant que variable string.
    /// </summary>
    private void PasteToString()
    {
        string ToPaste = string.Empty;
        if (Clipboard.ContainsText() == false)
            return;
 
        string VariableDeclaration = "string strCode = ";
        string ConcatenationOperator = " + Environment.NewLine +";
        string EndLine = Environment.NewLine;
        string EndDeclaration = ";";
        string QuoteProtection = "\\\"";
 
        // Si on est en VB :
        if (String.Compare(_DTE.ActiveWindow.Document.Language, "Basic", true) == 0)
        {
            VariableDeclaration = "dim strCode as String = ";
            ConcatenationOperator = " & Environment.NewLine & ";
            EndLine = "_" + Environment.NewLine;
            EndDeclaration = "";
            QuoteProtection = "\"\"";
        }
 
        ToPaste = Clipboard.GetText();
        string[] lines = Regex.Split(ToPaste, "$", System.Text.RegularExpressions.RegexOptions.Multiline);
 
        // Supprime la dernière ligne qui est vide
        Collection<string> text = new Collection<string>();
        foreach (string l in lines)
            text.Add(l);
        text.RemoveAt(text.Count - 1);
 
        // Génère le code
        ToPaste = string.Empty;
        for (int i = 0; i < text.Count; i++)
        {
            string modified = "\"" + text[i].Trim("\r\n".ToCharArray()).Replace("\\", "\\\\").Replace("\"", QuoteProtection) + "\"";
            if (i == 0)
                modified = VariableDeclaration + modified;
            if (i == text.Count - 1)
                modified += EndDeclaration;
            else
                modified += ConcatenationOperator + EndLine;
            ToPaste += modified;
        }
        TextDocument td = (TextDocument)_DTE.ActiveDocument.Object("");
        td.Selection.Insert(ToPaste, (int)vsInsertFlags.vsInsertFlagsContainNewText);
        _DTE.ExecuteCommand("Edit.FormatSelection", "");
    }

C'est pas parfait évidemment, mais ca dépanne bien

EDIT: Ah oui, comme je disais plus haut, c'est dans un addin visual studio ca, donc a toi d'adapter, soit tu créer un addin aussi, soit tu transforme en macro. Le code d'une macro est quasi identique a celui d'un addin. Si tu as besoin de précisions sur mon code, je suis pas loin.

**olibara** · 05/08/2010, 14h57

Pour répondre a toutes les bonnes volonté ici présente voici le petit bout de code
Ce n'est pas de la haute sophistication mais j'ai pondu ca en 10 minutes et c'est tres utile

N.B. : J'ai emballé cela dans une petite form qui gere les IO et perment de passer par le clipboard

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace SqlFormatter
{
  class C_SqlFormat
  {
    // ********************************************************************
    public StringBuilder ParseSql(string src)
    {
      StringBuilder sb = new StringBuilder();
      sb.AppendFormat("StringBuilder sb=new StringBuilder();");
      sb.Append(Environment.NewLine);
 
      src = PrepareSplitRight(src, "SELECT");
      src = PrepareSplitRight(src, "FROM");
      src = PrepareSplitRight(src, "WHERE");
 
      src = PrepareSplitLeft(src, "AND");
      src = PrepareSplitLeft(src, "INNER");
      src = PrepareSplitLeft(src, "RIGHT");
      src = PrepareSplitLeft(src, "LEFT");
      src = PrepareSplitLeft(src, "FULL");
 
      string x = src.Replace(Environment.NewLine, "|").Replace(",", ",|");
 
      string[] item = x.Split('|');
      int i;
      for (i = 0; i < item.Length; i++)
      {
        string st = item[i].Trim();
        if (st.Length <= 0)
        {
          continue;
        }
        sb.AppendFormat("sb.Append(\" {0}\");{1}", st, Environment.NewLine);
      }
      return sb;
    }
    // ********************************************************************
    private string PrepareSplitRight(string src, string key)
    {
      int pos = 0;
      while (true)
      {
        pos = src.IndexOf(key,pos, StringComparison.InvariantCultureIgnoreCase);
        if (pos < 0)
        {
          return src;
        }
        pos += key.Length;
        src=src.Insert(pos, "|");
        pos++;
      }
    }
    // ********************************************************************
    private string PrepareSplitLeft(string src, string key)
    {
      int pos = 0;
      while (true)
      {
        pos = src.IndexOf(key, pos, StringComparison.InvariantCultureIgnoreCase);
        if (pos < 0)
        {
          return src;
        }
        src = src.Insert(pos, "|");
        pos++;
        pos += key.Length;
      }
    }
  }
}

**olibara** · 05/08/2010, 15h01

Merci CtxNop

Nos codes se sont croisés je pense

**ctxnop** · 05/08/2010, 15h07

Oui, pile poil quand j'ai cliqué sur "Envoyer la réponse" j'ai reçut le mail m'indiquant que tu avais posté ton code

**Pol63** · 05/08/2010, 15h21

on ne peut pas faire de string multiligne en vb.net

enfin on peut avec _ en bout de ligne mais c'est vraiment pas pratique et puis il faut aussi mettre les environment.newline partout

donc nous partout dans notre code c'est

dim rq as string = "" & VbCrlf & _
"SELECT ch ... " & VbCrLf & _
" FROM table" & VbCrLf & _
" WHERE"

donc pas super
j'ai entendu dire qu'avec vb2010 on aurait pas besoin de mettre de _ m'enfin de là à pouvoir écrire un string sur plusieurs lignes comme le montre guulh, je pense pas

d'aileurs à quoi sert le @que vous mettez devant les string chez vous ?

(tiens, une des rares fois où j'envie les c#iens ^^)

**ctxnop** · 05/08/2010, 15h24

En vérité, même si le C# permet les \n pour faire des sauts de lignes, je ne les conseils pas.
Il ne faut pas oublier que via mono notemment le .Net est portable et que le retour à la ligne ne se fait pas partout pareil. Environment.NewLine est une string contenant la séquence propre au système sur lequel il s'exécute.

EDIt:
Je testerai ce que tu dis sur le VB2010.
Sinon le @ devant une string empêche la résolution des caractères d'échapement.
C'est pratique pour un chemin par exemple ou une expression rationnelle, on a pas besoin de faire \\ pour ajouter un simple \.
De même écrire @"\t" ca ne donnera pas une tabulation mais bien \t, comme si on avait écris "\\t"

Convertir chaine sql en stringbuilder

C#

Discussions similaires

Partager

Partager