IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
logo
Sommaire > Schéma
        Comment récupérer les informations de clé primaire pour le fournisseur SQL-SERVER ?
        Comment récupérer le schéma complet d'une base de données ?
        J'ai une erreur MissingPrimaryKeyException alors que ma table possède bien une clé primaire ?
        Peut-on créer une base Access en partant d'un Dataset ?



Comment récupérer les informations de clé primaire pour le fournisseur SQL-SERVER ?
auteur : Jean-Marc Rabilloud
La méthode GetSchema ne permet pas de récupérer ce type d'information. Il existe deux méthodes pour atteindre ce résultat.

1) En utilisant la bibliothèque COM SQLDMO.

Par exemple, pour extraire des informations de schéma dont les contraintes de clé primaire, on peut utiliser le code suivant, en ayant ajouté au préalable la référence COM "Microsoft SQLDMO 8.5 Object Library"

Dim SoonNode1 As TreeNode, SoonNode2 As TreeNode, RootNode As TreeNode
Using MaConn As New SqlClient.SqlConnection("Data Source=ISPCF261025\SQLEXPRESS;" & _
"Initial Catalog=pubs;Integrated Security=True")
    MaConn.Open()
    Dim Catalogue As String = MaConn.Database
    Dim srvSQLDMO As New SQLDMO.SQLServer
    srvSQLDMO.LoginTimeout = -1
    srvSQLDMO.LoginSecure = True
    srvSQLDMO.Connect("ISPCF261025\SQLEXPRESS")
    Dim dmoDB As SQLDMO._Database = srvSQLDMO.Databases.Item(Catalogue)
    RootNode = Me.TreeView1.Nodes.Add(Catalogue)
    For Each Table As SQLDMO.Table In dmoDB.Tables
SoonNode1 = RootNode.Nodes.Add(Table.Name)
For Each Champ As SQLDMO.Column In Table.Columns
    SoonNode2 = SoonNode1.Nodes.Add(Champ.Name)
    With SoonNode2.Nodes.Add(Champ.Datatype)
Dim SystemDataType As SQLDMO.SystemDatatype = dmoDB.SystemDatatypes.Item(Champ.PhysicalDatatype)
For Each Prop As SQLDMO.Property In SystemDataType.Properties
    If Prop.Value IsNot Nothing Then .Nodes.Add(Prop.Name + " => " + Prop.Value.ToString)
Next
    End With
    SoonNode2.Nodes.Add("Valeur par défaut => " + Champ.Default)
    SoonNode2.Nodes.Add("Nullable => " + Champ.AllowNulls.ToString)
    SoonNode2.Nodes.Add("Clé primaire => " + Champ.InPrimaryKey.ToString)
Next
    Next
    dmoDB = Nothing
    srvSQLDMO.DisConnect()
    srvSQLDMO = Nothing
    MaConn.Close()
End Using
2) En passant des commandes SQL

Si votre connexion a les privilèges suffisants pour accéder aux objets système, il est possible d'utiliser une requête pour récupérer les informations. Reprenons l'exemple donné dans la question ??

Dim SoonNode1 As TreeNode, SoonNode2 As TreeNode, RootNode As TreeNode
Dim tmpTable1 As DataTable, tmpTable2 As DataTable, MaTableBase As DataTable, MaVue As DataView

Using MaConn As New SqlClient.SqlConnection("Data Source=ISPCF261025\SQLEXPRESS;" & _
"Initial Catalog=pubs;Integrated Security=True")
    MaConn.Open()
    Dim Catalogue As String = MaConn.Database
    Dim MaCommande As New SqlClient.SqlCommand("SELECT @NomColonne=c.name" & _
" FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id " & _
"AND i.index_id = ic.index_id INNER JOIN sys.columns AS c ON ic.object_id = c.object_id " & _
"AND c.column_id = ic.column_id " & _
"WHERE i.is_primary_key = 1 AND i.object_id = OBJECT_ID(@NomTable)")
    MaCommande.Connection = MaConn
    RootNode = Me.TreeView1.Nodes.Add(Catalogue)
    Dim Restrictions() As String
    ReDim Restrictions(3)
    'remplissage des tables
    Restrictions(0) = Catalogue
    Restrictions(3) = "BASE TABLE"
    MaTableBase = MaConn.GetSchema("Tables", Restrictions)
    tmpTable2 = MaConn.GetSchema("DataTypes")
    MaVue = tmpTable2.DefaultView
    MaVue.Sort = "typename"
    For Each LigneParent As DataRow In MaTableBase.Rows
SoonNode1 = RootNode.Nodes.Add(LigneParent.Item("Table_Name").ToString)
With MaCommande.Parameters.Add("@NomTable", SqlDbType.NVarChar, 50)
    .Direction = ParameterDirection.Input
    .Value = SoonNode1.Text
End With
MaCommande.Parameters.Add("@NomColonne", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output
MaCommande.ExecuteNonQuery()
SoonNode1.Nodes.Add("PK => " + MaCommande.Parameters.Item("@NomColonne").Value.ToString)
MaCommande.Parameters.Clear()
Restrictions(3) = Nothing
Restrictions(2) = SoonNode1.Text
tmpTable1 = MaConn.GetSchema("Columns", Restrictions)
For Each tmpLigne1 As DataRow In tmpTable1.Rows
    SoonNode2 = SoonNode1.Nodes.Add(tmpLigne1.Item("Column_Name").ToString)
    With SoonNode2.Nodes.Add(tmpLigne1.Item("data_type").ToString)
Dim Pos As Integer = MaVue.Find(tmpLigne1.Item("data_type").ToString)
If Pos > -1 Then
    For Each MaCol As DataColumn In MaVue.Table.Columns
.Nodes.Add(MaCol.ColumnName + " => " + MaVue(Pos).Item(MaCol.Ordinal).ToString)
    Next
End If
    End With
    SoonNode2.Nodes.Add("Nullable => " + tmpLigne1.Item("is_nullable").ToString)
    SoonNode2.Nodes.Add("Valeur par défaut => " + tmpLigne1.Item("column_default").ToString)
Next
    Next
    MaConn.Close()
End Using

    End Sub
Dans ce code je vais remonter la clé primaire de la table à l'aide d'une commande définie telle que :

Dim MaCommande As New SqlClient.SqlCommand("SELECT @NomColonne=c.name  " & _
"FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id " & _
"AND i.index_id = ic.index_id INNER JOIN sys.columns AS c ON ic.object_id = c.object_id " & _
"AND c.column_id = ic.column_id " & _
"WHERE i.is_primary_key = 1 AND i.object_id = OBJECT_ID(@NomTable)")
MaCommande.Connection = MaConn
Puis pour chaque table, j'exécuterais la commande avec les bons paramètres :

With MaCommande.Parameters.Add("@NomTable", SqlDbType.NVarChar, 50)
   .Direction = ParameterDirection.Input
   .Value = SoonNode1.Text
End With
MaCommande.Parameters.Add("@NomColonne", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output
MaCommande.ExecuteNonQuery()
SoonNode1.Nodes.Add("PK => " + MaCommande.Parameters.Item("@NomColonne").Value.ToString)
MaCommande.Parameters.Clear()
Notez que cette approche est sensiblement équivalente à l'écriture d'une requête SQL-SERVER qui serait de la forme :

USE pubs; 
GO 
DECLARE @NomTable nvarchar(max);
DECLARE @NomColonne nvarchar(max);
SET @NomTable = 'publishers'
SELECT @NomColonne = c.name
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic 
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c 
    ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1 
    AND i.object_id = OBJECT_ID(@NomTable);
PRINT @NomColonne

Comment récupérer le schéma complet d'une base de données ?
auteur : Jean-Marc Rabilloud
Il n'existe pas de méthodes universelles pour lire le schéma complet d'une base de données de façon générique. Généralement il y a trois approches possibles :

  • Utiliser la méthode GetSchema de l'objet connection. Cette technique peut donner de bon résultats avec une approche assez générique, cela va dépendre des collections mises à disposition par le fournisseur managé.
  • Utiliser le SQL DDL. Bien que très efficace, cela va dépendre de comment le SQL est implémenté dans le SGBD et le code sera généralement spécifique à chaque SGBD.
  • Utiliser une bibliothèque spécifique. Dans ce cas, le code sera propre à la bibliothèque qui elle-même est liée à un type de SGBD. Ces bibliothèques sont généralement des bibliothèques COM (ADOX, SQL-DMO, etc?)
Le code suivant permet d'extraire très simplement les informations de schéma disponibles de manière "générique" :

Dim SoonNode As TreeNode, RootNode As TreeNode
RootNode = Me.TreeView1.Nodes.Add("0", "Schema")
Dim MaConn As New SqlClient.SqlConnection("Data Source=ISPCF261025\SQLEXPRESS;" & _
"Initial Catalog=pubs;Integrated Security=True")
MaConn.Open()
Dim MaTableFille As DataTable
Dim MaTableBase As DataTable = MaConn.GetSchema()
For Each LigneParent As DataRow In MaTableBase.Rows
    SoonNode = RootNode.Nodes.Add(LigneParent.Item(0).ToString)
    MaTableFille = MaConn.GetSchema(LigneParent.Item(0).ToString)
    For Each LigneFille As DataRow In MaTableFille.Rows
For Each ColonneFille As DataColumn In MaTableFille.Columns
    SoonNode.Nodes.Add(ColonneFille.ColumnName + " : " + & _
LigneFille.Item(ColonneFille.Ordinal).ToString)
Next
    Next
Next
MaConn.Close()
Dans l'exemple suivant nous allons extraire quelques valeurs de schéma sur une base SQL Server :

Dim SoonNode1 As TreeNode, SoonNode2 As TreeNode, RootNode As TreeNode
Dim tmpTable1 As DataTable, tmpTable2 As DataTable, MaTableBase As DataTable, MaVue As DataView
Using MaConn As New SqlClient.SqlConnection("Data Source=ISPCF261025\SQLEXPRESS;" & _
"Initial Catalog=pubs;Integrated Security=True")
    MaConn.Open()
    Dim Catalogue As String = MaConn.Database
    RootNode = Me.TreeView1.Nodes.Add(Catalogue)
    Dim Restrictions() As String
    ReDim Restrictions(3)
    'remplissage des tables
    Restrictions(0) = Catalogue
    Restrictions(3) = "BASE TABLE"
    MaTableBase = MaConn.GetSchema("Tables", Restrictions)
    tmpTable2 = MaConn.GetSchema("DataTypes")
    MaVue = tmpTable2.DefaultView
    MaVue.Sort = "typename"
    For Each LigneParent As DataRow In MaTableBase.Rows
SoonNode1 = RootNode.Nodes.Add(LigneParent.Item("Table_Name").ToString)
Restrictions(3) = Nothing
Restrictions(2) = SoonNode1.Text
tmpTable1 = MaConn.GetSchema("Columns", Restrictions)
For Each tmpLigne1 As DataRow In tmpTable1.Rows
    SoonNode2 = SoonNode1.Nodes.Add(tmpLigne1.Item("Column_Name").ToString)
    With SoonNode2.Nodes.Add(tmpLigne1.Item("data_type").ToString)
Dim Pos As Integer = MaVue.Find(tmpLigne1.Item("data_type").ToString)
If Pos > -1 Then
    For Each MaCol As DataColumn In MaVue.Table.Columns
.Nodes.Add(MaCol.ColumnName + " => " + MaVue(Pos).Item(MaCol.Ordinal).ToString)
    Next
End If
    End With
    SoonNode2.Nodes.Add("Nullable => " + tmpLigne1.Item("is_nullable").ToString)
    SoonNode2.Nodes.Add("Valeur par défaut => " + tmpLigne1.Item("column_default").ToString)
Next
    Next
    MaConn.Close()
End Using

J'ai une erreur MissingPrimaryKeyException alors que ma table possède bien une clé primaire ?
auteur : Jean-Marc Rabilloud
Il ne faut pas confondre le schéma entrant (celui du SGBD) avec le schéma local (celui du dataset). De manière générale, le schéma entrant n'est jamais mappé sur le schéma local sauf appel explicite. On a tendance à perdre cela de vue avec les Datasets fortement typés où le schéma est mappé par le code généré, mais lorsqu'on gère ses objets, on doit gérer aussi le mappage du schéma.

Le code suivant va lever l'exception MissingPrimaryKeyException

Using MaConn As New SqlClient.SqlConnection("Data Source=FIXE;Initial Catalog=pubs;Integrated Security=True")
    MaConn.Open()
    Dim MaCommande As New SqlClient.SqlCommand(" SELECT * FROM Publishers", MaConn)
    tblEditeur = New DataTable("Editeurs")
    Dim Mondta As New SqlClient.SqlDataAdapter(MaCommande)
    Mondta.Fill(tblEditeur)
    Dim LigneSelect As DataRow = tblEditeur.Rows.Find("1622")
    MessageBox.Show(LigneSelect.Item(1).ToString)
    MaConn.Close()
End Using
En effet, l'objet DataAdapter ne va pas mapper le schéma par défaut. Je peux dans ce cas forcer le mappage en appelant la méthode FillSchema de l'objet DataAdapter

    Dim MaCommande As New SqlClient.SqlCommand(" SELECT * FROM Publishers", MaConn)
    tblEditeur = New DataTable("Editeurs")
    Dim Mondta As New SqlClient.SqlDataAdapter(MaCommande)
    Mondta.FillSchema(tblEditeur, SchemaType.Mapped)
    Mondta.Fill(tblEditeur)
    Dim LigneSelect As DataRow = tblEditeur.Rows.Find("1622")
On rencontre d'ailleurs un problème identique lorsqu'on charge l'objet DataTable avec un DataReader qui lui ne lit jamais le schéma, comme dans le cas :

Using MaConn As New SqlClient.SqlConnection("Data Source=FIXE;Initial Catalog=pubs;Integrated Security=True")
    MaConn.Open()
    Dim MaCommande As New SqlClient.SqlCommand(" SELECT * FROM Publishers", MaConn)
    tblEditeur = New DataTable("Editeurs")
    tblEditeur.Load(MaCommande.ExecuteReader)
    Dim LigneSelect As DataRow = tblEditeur.Rows.Find("1622")
    MessageBox.Show(LigneSelect.Item(1).ToString)
    MaConn.Close()
End Using
Dans ce cas, comme il n'existe pas de méthode simple pour mapper le schéma, on le fait généralement à l'aide du code en recréant la clé primaire, dans notre exemple :

Using MaConn As New SqlClient.SqlConnection("Data Source=FIXE;Initial Catalog=pubs;Integrated Security=True")
    MaConn.Open()
    Dim MaCommande As New SqlClient.SqlCommand(" SELECT * FROM Publishers", MaConn)
    tblEditeur = New DataTable("Editeurs")
    tblEditeur.Load(MaCommande.ExecuteReader)
    tblEditeur.Constraints.Add("PKediteur", tblEditeur.Columns("pub_id"), True)
    Dim LigneSelect As DataRow = tblEditeur.Rows.Find("1622")
    MessageBox.Show(LigneSelect.Item(1).ToString)
    MaConn.Close()
End Using

Peut-on créer une base Access en partant d'un Dataset ?
auteur : Jean-Marc Rabilloud
Oui, c'est même assez simple en utilisant la bibliothèque COM ADOX comme dans l'exemple ci-dessous. Cela peut éventuellement se faire uniquement avec du SQL DDL mais c'est nettement plus lourd à écrire.

Le code suivant est un exemple qui permet de transformer le Dataset issu de la base de données exemple AdventureWorks pour SQL Server en base Access.

Dim Catalogue As ADOX.Catalog, NewTable As ADOX.Table, NewKey As ADOX.Key
Dim strConn As String
Catalogue = New ADOX.Catalog
Dim GestionTable As New List(Of DataTable)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\AdvWorks.mdb;" + " & _
"Jet OLEDB:Engine Type=5"
Catalogue.Create(strConn)
For Each maTable As DataTable In Me.AdventureWorksDataSet.Tables
    'Try
    NewTable = New ADOX.Table
    NewTable.Name = maTable.TableName
    For Each maColonne As DataColumn In maTable.Columns
Dim NewCol As New ADOX.Column
With NewCol
    .Name = maColonne.ColumnName
    .ParentCatalog = Catalogue
    Select Case True
Case maColonne.DataType Is GetType(Byte)
    .Type = ADOX.DataTypeEnum.adVarBinary

Case maColonne.DataType Is GetType(Decimal)
    .Type = ADOX.DataTypeEnum.adNumeric
    .NumericScale = 2
    .Precision = 8

Case maColonne.DataType Is GetType(Guid)
    .Type = ADOX.DataTypeEnum.adGUID

Case maColonne.DataType Is GetType(Integer)
    .Type = ADOX.DataTypeEnum.adInteger
    .Attributes = 0

Case maColonne.DataType Is GetType(Boolean)
    .Type = ADOX.DataTypeEnum.adBoolean

Case maColonne.DataType Is GetType(Short)
    .Type = ADOX.DataTypeEnum.adSmallInt

Case maColonne.DataType Is GetType(Date)
    .Type = ADOX.DataTypeEnum.adDate

Case maColonne.DataType Is GetType(Single)
    .Type = ADOX.DataTypeEnum.adSingle

Case maColonne.DataType Is GetType(String)
    If maColonne.MaxLength > 255 Then
 .Type = ADOX.DataTypeEnum.adLongVarWChar
    Else
 .Type = ADOX.DataTypeEnum.adVarWChar
 .DefinedSize = maColonne.MaxLength
    End If

Case maColonne.DataType Is GetType(Byte())
    .Type = ADOX.DataTypeEnum.adVarBinary

Case Else
    MsgBox("attention " + maColonne.DataType.ToString)

  End Select
  If maColonne.AutoIncrement Then
       .Properties("Description").Value = "Autoincrement - Seed " + & _
maColonne.AutoIncrementSeed.ToString(My.Application.Culture.NumberFormat)
       '.Properties("AutoIncrement").Value = True
       '.Properties("Seed").Value = maColonne.AutoIncrementSeed
       '.Properties("Increment").Value = maColonne.AutoIncrementStep
       '.Properties("Nullable").Value = False
       '.Properties("Fixed Length").Value = False
  End If
  If maColonne.AllowDBNull AndAlso maColonne.DataType IsNot GetType(Boolean) Then 
       .Properties("Nullable").Value = True
  Else
       .Properties("Nullable").Value = False
       .Properties("Default").Value = 0
  End If
  If Not IsDBNull(maColonne.DefaultValue) Then
       .Properties("Default").Value = maColonne.DefaultValue
  End If
      End With
      NewTable.Columns.Append(NewCol)
      NewCol = Nothing
      If maColonne.Unique Then
   NewKey = New ADOX.Key
   With NewKey
.Type = ADOX.KeyTypeEnum.adKeyUnique
.Columns.Append(maColonne.ColumnName)
.Name = "Unique_" + maColonne.ColumnName
   End With
   NewTable.Keys.Append(NewKey)
   NewKey = Nothing
      End If
    Next
      If Not maTable.PrimaryKey Is Nothing Then
  NewKey = New ADOX.Key
  NewKey.Type = ADOX.KeyTypeEnum.adKeyPrimary
  NewKey.Name = "PK_" + maTable.TableName
  For Each MaCol As DataColumn In maTable.PrimaryKey
      NewKey.Columns.Append(MaCol.ColumnName)
  Next
  NewTable.Keys.Append(NewKey)
  NewKey = Nothing
      End If
      Catalogue.Tables.Append(NewTable)
      NewTable = Nothing
Next
For Each matable As DataTable In Me.AdventureWorksDataSet.Tables
    If matable.ParentRelations.Count > 0 Then
GestionTable.Add(matable)
    Else
GestionTable.Insert(0, matable)
    End If
    For Each MaRelation As Data.DataRelation In matable.ParentRelations
NewKey = New ADOX.Key
With NewKey
    .Type = ADOX.KeyTypeEnum.adKeyForeign
    .Name = "FK_" + matable.TableName + "_" + MaRelation.ParentTable.TableName
    .RelatedTable = MaRelation.ParentTable.TableName
    .UpdateRule = ADOX.RuleEnum.adRINone
    .DeleteRule = ADOX.RuleEnum.adRINone
    Dim tmpCol As String
    For Each LaCol As DataColumn In MaRelation.ChildColumns
tmpCol = LaCol.ColumnName
.Columns.Append(tmpCol)
.Columns(tmpCol).RelatedColumn = MaRelation.ParentColumns(0).ColumnName
    Next
End With
Try
    Catalogue.Tables(matable.TableName).Keys.Append(NewKey)
Catch ex As Runtime.InteropServices.COMException
    If ex.ErrorCode = -2147467259 Then 'relation existe déjà
NewKey = Nothing
    End If
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try
NewKey = Nothing
    Next
Next
Catalogue.ActiveConnection = Nothing
Catalogue = Nothing
Dim MaConn As New ADODB.Connection()
MaConn.ConnectionString = strConn
MaConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
MaConn.Open()
Dim MonRs As ADODB.Recordset
Dim MonChamp As ADODB.Field
For cmpt As Int32 = 0 To GestionTable.Count - 1
    MonRs = New ADODB.Recordset
    MonRs.Open("SELECT * FROM " + GestionTable(cmpt).TableName, MaConn, & _
ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, & _
 ADODB.CommandTypeEnum.adCmdText)
    For Each Ligne As DataRow In GestionTable(cmpt).Rows
MonRs.AddNew()
For Each MonChamp In MonRs.Fields
    If TypeOf Ligne.Item(MonChamp.Name) Is Byte Then
MonChamp.Value = CInt(Ligne.Item(MonChamp.Name))
    Else
MonChamp.Value = Ligne.Item(MonChamp.Name)
    End If
Next
    Next
    Try
MonRs.UpdateBatch()
    Catch ex As Runtime.InteropServices.COMException

    End Try
    MonRs.Close()
    MonRs = Nothing
Next
MaConn.Close()
MaConn = Nothing
MsgBox("done")
N.B : Notez que j'ai désactivé la récupération des informations d'auto-incrément puisque je souhaite récupérer les données du dataset pour les injecter dans la base Access, ce qui ne saurait pas possible avec des champs auto-incrémentés.



Consultez les autres F.A.Q's


Valid XHTML 1.1!Valid CSS!

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2009 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.