SMO

Dimanche 22 mars 2009

SMO (SQL Management Object) Sample #1 : Balade dans les API / Connexion et récupérer la liste des bases

Je vais vous proposer une série de sample afin d'illuster l'utilisation de SMO (SQL Server Management Object) dans le cadre d'un système de sauvegarde de données de base à base, en suivant l'arbre des dépendances (techniques ou fonctionelles) du modèle physique.

J'aurais pu faire un simple package SSIS à cet effet, mais dans la pratique les modèles de donnée changent régulièrement, donc SSIS s'adapate mal car cela supposera de maintenir les métadonnées :(
Il s'agit donc de créer dynamiquement ces packages, tout un programme...
Le code source du projet est sur www.codeplex.com/bilab sous $/POC/BILABHistory/
Dans ce projet, je m'appuie sur une classe clef "TablePath". Elle embarquera toute la logique.

SMO est une API qui permet de se balader dans un SQL Server. Pour se connecter, il faudra passer par la classe Server :

sqlserver =

new Server(sc);

Commençons par du basique : afin de récupérer l'arbre des dépendances, je vous propose déjà de se connecter et de récupérer la liste des bases existantes dans l'instance concernée. C'est que fait la méthode GetDatabaseList :

           27         public List<string> GetDatabaseList(string server, ServerAuthMode mode, string user, string password)
      
           28         {
      
           29             List<string> dblist = new List<string>();   
              
           30             ServerConnection sc = null;
      
           31             Server sqlserver;
      
           32 
      
           33             log.Info("connecting");
      
           34 
      
           35 
      
           36             // Sets appropriate connections proerties accordign db
        context
      
           37             if (mode == ServerAuthMode.SqlServer)
      
           38             {
      
           39                 sc = new ServerConnection(server, user, password);
      
           40             }
      
           41             else if (mode ==
        ServerAuthMode.Integrated)
      
           42             {
      
           43                 sc = new ServerConnection(server);
      
           44             }
      
           45 
      
           46 
      
           47             try
      
           48             {
      
           49                 sqlserver = new Server(sc);
      
           50                 log.Info("Creating db list");
      
           51                 foreach (Database db in sqlserver.Databases)
      
           52                 {
      
           53                     log.Info("adding " +
        db.Name + " to the list ");
      
           54                     dblist.Add(db.Name);
      
           55                 }
      
           56             }
      
           57             catch (Exception e)
      
           58             {
      
           59                 log.Debug("Error obtaining database list
        connecting : " + e.Message);
      
           60             }
      
           61             finally
      
           62             {
      
           63                 sc = null;
      
           64                 _server = null;
      
           65             }           
      
           66 
      
           67             return dblist;
      
           68         }

Par Renaud Harduin
Ecrire un commentaire - Voir les 0 commentaires - Recommander

Lundi 23 mars 2009

SMO Sample # 2 : Balade dans les API / Récupérer pour une table, les tables dépendantes

Toujours dans la série SMO, je souhaite maintenant créer un arbre objet qui me donnera pour une table, sa clef (primaire ou composée), les tables qui dépendent de cette pk directement .

j'ai donc rajouté à mon objet TablePath une méthode AnalyseDatabaseBy :

        132 public TablePath AnalyseDatabaseBy(string tableName,string schema)
    
        133 {  
    
        134     /* Sets main information */
    
        135     this.TableName = tableName;
    
        136     this.Schema = schema;
    
        137 
    
        138     /* Build a representation of any PrimaryKey */
    
        139     ColumnCollection cols = _database.Tables[tableName, schema].Columns;

je fixe en passant le schema et le nom de la table en cours. (Mon idée derrière est de travailler par la suite récursif).

Deuxième étape : j'ai besoin de garder la ou les clefs de la tables via InPrimaryKey
Cette information ne se retrouve pas directement, il faut itérer dans les colonnes et "noter" les colonnes contributives, en testant InPrimaryKey :

            138 /* Build a representation of any PrimaryKey */
        
            139             ColumnCollection cols = _database.Tables[tableName,
          schema].Columns;
        
            140             StringBuilder sb = new StringBuilder();
        
            141             foreach (Column
          col in cols)
        
            142             {
        
            143                 if (col.InPrimaryKey)
        
            144                 {
        
            145                     sb.Append("[");
        
            146                     sb.Append(col.Name);
        
            147                     sb.Append("]");
        
            148                 }               
        
            149             }
        
            150             this._primaryKey = sb.ToString();

Troisième étape : trouver la liste des tables dépendantes via EnumForeignKeys

J'ai caché toute cette logique sous la forme d'une fonction qui exploite la méthode EnumForeignKeys() d'objet
Table. Ci dessous la fin de la méthode AnalyseDatabaseBy () et l'appel à LookForChilds():

          153     this._childs = this.lookForChilds(this);
      
          154 
      
          155     return this;
      
          156 }
      
Examinons le corps de la méthode LookForChilds :

        158 private List<TablePath> lookForChilds(  TablePath parent)
    
        159 {
    
        160     List<TablePath> ltp =  new List<TablePath>();
    
        161 
    
        162     DataTable dt = _database.Tables[parent.TableName,parent.Schema].EnumForeignKeys();

La méthode EnumForeignKeys() nous renvoie un DataTable contenant la liste des tables liées, leur schema et la foreignkey concernée. Ci dessous un screenshot de l'enum en mode debug :

Nous rajoutons donc les enfants sous la forme d'une liste :

        163 foreach (DataRow dr in
      dt.Rows)
    
        164            {
    
        165                TablePath tp = new TablePath();
    
        166                tp._database = parent._database;
    
        167                tp._server = parent._server;
    
        168                tp.TableName = dr[1].ToString();
    
        169                tp.Schema = dr[0].ToString();
    
        170                ltp.Add(tp);
    
        171            }         
    
        172            return ltp;
    
        173        }

AnalyseDatabaseBy in action !
Appliqué dans notre IHM à AdventureWorks et à la table SalesOrderDetails :

         54 state = _tp.ConnectToDatabase(@".\BILAB2008","AdventureWorks",ServerAuthMode.Integrated,null,null);
    
         55 _tp = _tp.AnalyseDatabaseBy("SalesOrderHeader","Sales");

Quickwatch du TablePath _tp :

Nous avons 2 enfants , "SalesOrderDetails" tout d'abord :

et ensuite "SalesOrderHeaderSalesReason" :

Code source sur : www.codeplex.com/BILab sous $/POC/BILabHistory

Par Renaud Harduin
Ecrire un commentaire - Voir les 0 commentaires - Recommander

Mardi 24 mars 2009

SMO Sample #2bis : Recherche des dépendances et restitution

Toujour sur le périmètre de la recherche des dépendances de tables, je fait un petit point intermédiaire de manière à les restituer dans mon IHM. Le plus simple est de les afficher dans un treeview.

Pour résumer, lorsque du clic sur le bouton "Analyse" de l'UI,

Je force pour l'analyse sur une base Sample de MS comme suit :

___

        private void analyseToolStripMenuItem_Click(object sender,
        EventArgs e)
      
                {
      
                    bool state = false;
      
                    #region comments
      
                    //if (UserMenuItem.Text == "" && PasswordMenuItem.Text == "")
      
                    //{
      
                    //    state = _tp.ConnectToDatabase(ServerMenuItem.Text,
      
                    //                   
                      dbListCombo.SelectedText,
      
                    //                   
                      ServerAuthMode.Integrated,
      
                    //                   
                      null, null);
      
                    //}
      
                    //else
      
                    //{
      
                    //    state = _tp.ConnectToDatabase(ServerMenuItem.Text,
      
                    //                   
                      dbListCombo.SelectedText,
      
                    //                   
                      ServerAuthMode.Integrated,
      
                    //                   
                      UserMenuItem.Text, PasswordMenuItem.Text);               
      
                    //}    
      
                    #endregion
      
                    state = _tp.ConnectToDatabase(@".\BILAB2008","AdventureWorks",ServerAuthMode.Integrated,null,null);
      
                    _tp.TableName = "SalesOrderHeader";
      
                    _tp.Schema = "Sales";
      
                    _tp = _tp.AnalyseDatabaseBy("SalesOrderHeader","Sales");
      
                    TreeNode treeNode = new TreeNode();
      
                    /* convert TreePath Tree into a TreeNode for display */
      
                    this.AdaptTablePath(ref treeNode, _tp);
      
                    DatabaseTreeView.Nodes.Add(treeNode);
      
                }
      
    ___

    L'élément clef pour alimenter notre Trevview est l'objet treeNode. Nous l'alimentons en récurisif :

    ____

      private void AdaptTablePath(ref TreeNode node,
      TablePath tablePath)
    
      {
    
          node.Text = tablePath.TableName;
    
          if (tablePath.Childs != null)
    
          {
    
              foreach (TablePath tp in tablePath.Childs)
    
              {
    
                  TreeNode childNode = new TreeNode(tp.TableName);
    
                  AdaptTablePath(ref childNode, tp);
    
                  node.Nodes.Add(childNode);
    
              }
    
          }
    
      }

____

Par Renaud Harduin
Ecrire un commentaire - Voir les 0 commentaires - Recommander

Lundi 30 mars 2009

SMO#3 : Analyse récursive des dépendances

Sur la base de la proposition, l'ajout de récursivité va permettre d'analyser dynamiquement l'arbre :
____

private

List<TablePath> lookForChilds( TablePath root , TablePath parent)

{

List<TablePath> ltp = new List<TablePath>();

DataTable dt = _database.Tables[parent.TableName,parent.Schema].EnumForeignKeys();

foreach (DataRow dr in dt.Rows)

{

string tableName = dr[1].ToString();

string schemaName = dr[0].ToString();

string fkname = dr[2].ToString();

if (!(tableName.Equals(parent.TableName) &&

schemaName.Equals(parent.Schema))

)

{

____

Je teste le parent afin de ne pas partir en récursion infinie liée à un éventuel Parent-Chlid ou association :

____

log.Info(

"scanning now " + tableName);

TablePath tp = new TablePath();

tp._database = parent._database;

tp._server = parent._server;

tp.TableName = dr[1].ToString();

tp.Schema = schemaName;

tp.ForeignKey = fkname;

tp._childs =

this.lookForChilds(root, tp);

ltp.Add(tp);

}

else

{

log.Info(

"preventing infiniteRecursion on " + tableName);

}

return ltp;

}

____

En passant, nous remplissons la ForeignKey impliquée. L'ensemble se restitue toujours avec la méthode AdaptTablePath() :

____

private void AdaptTablePath(ref TreeNode node, TablePath tablePath)

{

node.Text = tablePath.TableName +

" via < " +tablePath.ForeignKey +" >";

if (tablePath.Childs != null)

{

foreach (TablePath tp in tablePath.Childs)

{

TreeNode childNode = new TreeNode(tp.TableName );

AdaptTablePath(

ref childNode, tp);

node.Nodes.Add(childNode);

}

____

En mode restitué :
____

____

Par Renaud Harduin
Ecrire un commentaire - Voir les 0 commentaires - Recommander

The Business Intelligence Blog

Business Intelligence Blog

Calendrier

MVP Blog

Recommander

Liens

W3C

SMO (SQL Management Object) Sample #1 : Balade dans les API / Connexion et récupérer la liste des bases

SMO Sample # 2 : Balade dans les API / Récupérer pour une table, les tables dépendantes

SMO Sample #2bis : Recherche des dépendances et restitution

SMO#3 : Analyse récursive des dépendances

Recherche

Articles récents

Catégories