Upload
fmrassi
View
68
Download
0
Embed Size (px)
Citation preview
Plusieurs fichiers sont attachs ensemble dans ce lot PDF.
Adobe conseille dutiliser la version 8 (ou ultrieure) dAdobe Reader ou dAdobe Acrobat pour manipuler les documents contenus dans un lot PDF. En effectuant une mise niveau vers la dernire version, vous bnficierez des avantages suivants :
Un affichage PDF optimis et intgr
Une impression aise
Des recherches rapides
Vous ne disposez pas de la dernire version dAdobe Reader ?
Cliquez ici pour tlcharger la dernire version dAdobe Reader
Si vous disposez dj dAdobe Reader 8, cliquez sur un fichier dans le lot PDF pour lafficher.
Principedefonctionnementdunebasededonnes
Lesbasesdedonnessontdevenuesdeslmentsincontournablesdelamajoritdesapplications.Ellessesubstituentlutilisationdefichiersgrsparledveloppeurluimme.Cetapportpermetungaindeproductivitimportantlorsdudveloppementetuneamliorationsignificativedesperformancesdesapplications.Ellesfacilitentgalementlepartagedinformations entre utilisateurs. Pour pouvoir utiliser une base de donnes, vous devez connatre un minimum devocabulairelicettetechnologie.
1.Terminologie
Danslecontextedesbasesdedonnes,lestermessuivantssontfrquemmentutiliss:
Basededonnesrelationnelle
Une base de donne relationnelle est un type de base de donnes qui utilise des tables pour le stockage desinformations.Ellesutilisentdesvaleurs issuesdedeux tables,pourassocier lesdonnesdune tableauxdonnesdune autre table. En rgle gnrale, dans une base de donnes relationnelle, les informations ne sont stockesquuneseulefois.
Table
Unetableestuncomposantdunebasededonnesquistockelesinformationsdansdesenregistrements(lignes)etdansdeschamps(colonnes).Lesinformationssont,engnral,regroupesparcatgorieauniveaudunetable.Parexemple,nousauronslatabledesClients,desProduitsoudescommandes.
Enregistrement
Lenregistrement est lensemble des informations relativesunlmentdune table. Lesenregistrements sont lesquivalents,auniveaulogique,deslignesdunetable.Parexemple,unenregistrementdelatableClientscontientlescaractristiquesdunclientparticulier.
Champ
Un enregistrement est compos de plusieurs champs. Chaque champ dun enregistrement contient une seuleinformation sur lenregistrement.Parexemple,unenregistrementClientpeut contenir les champsCodeClient,Nom,Prnom...
Clprimaire
Uneclprimaireestutilisepouridentifier,demanireunique,chaquelignedunetable.Laclprimaireestunchampouunecombinaisondechampsdontlavaleurestuniquedanslatable.Parexemple,lechampCodeClientestlaclprimairedelatableClient.Ilnepeutpasyavoirdeuxclientsayantlemmecode.
Cltrangre
Une cltrangre reprsenteunouplusieurs champsdune table,qui font rfrenceauxchampsdelaclprimaireduneautretable.Lesclstrangresindiquentlamaniredontlestablessontlies.
Relation
Unerelationestuneassociationtablieentredeschampscommunsdansdeuxtables.Unerelationpeuttredeunun,deunplusieursoudeplusieursplusieurs.Grceauxrelations,lesrsultatsderequtespeuventcontenirdesdonnesissuesdeplusieurstables.UnerelationdeunplusieursentrelatableClientetlatableCommandepermetunerequtederenvoyertouteslescommandescorrespondantunclient.
2.LelangageSQL
AvantdepouvoircrireuneapplicationVisualBasicutilisantdesdonnes,vousdeveztrefamiliarisaveclelangageSQL (Structured Query Language). Ce langage permet de dialoguer avec la base de donnes. Il existe diffrentesversions du langage SQL, en fonction de la base de donnes utilise. Cependant, SQL disposegalement dunesyntaxelmentaire,normalise,indpendantedetoutesbasesdedonnes.
- 1 - ENI Editions - All rigths reserved
a.Recherchedinformations
Le langageSQLpermetde spcifierlesenregistrementsextraireainsiquelordredanslequelvoussouhaitezlesextraire.Vouspouvez creruneinstructionSQLquiextraitdesinformationsdeplusieurstablessimultanment,oucreruneinstructionquiextraituniquementunenregistrementspcifique.
LinstructionSELECT est utilise pour renvoyer des champs spcifiquesduneoudeplusieurs tablesde labasededonnes.
LinstructionsuivanterenvoielalistedesnomsetprnomsdetouslesenregistrementsdelatableClient:
SELECT Nom,Prenom FROM Client
Vouspouvezutiliserlesymbole*laplacedelalistedeschampspourlesquelsvoussouhaitezlavaleur:
SELECT * FROM Client
Vous pouvez limiter le nombre denregistrements slectionns, enutilisantunouplusieurs champspour filtrer lersultatdelarequte.Diffrentesclausessontdisponiblespourexcutercefiltrage.
Cetteclausepermetdespcifierlalistedesconditions,quedevrontremplirlesenregistrementspourfairepartiedesrsultatsretourns.LexemplesuivantpermetderetrouvertouslesclientshabitantNantes:
SELECT * FROM Client WHERE Ville=Nantes
Lasyntaxedecetteclausencessitelutilisationdesimplecotepourladlimitationdeschanesdecaractres.
Vous pouvez utiliser la clauseWHERE ... IN pour renvoyer tous lesenregistrementsqui rpondentunelistedecritres.Parexemple,vouspouvezrecherchertouslesclientshabitantenFranceouenEspagne:
SELECT * FROM Client WHERE Pays IN (France,Espagne)
Vous pouvezgalement renvoyer une slectiondenregistrementsqui se situententredeux critres spcifis.Larequtesuivantepermetdercuprerlalistedescommandespassesaumoisdenovembre2005:
SELECT * from Commandes WHERE DateCommande BETWEEN 01/11/05 AND 30/11/05
Vous pouvez utiliser la clauseWHERE ... LIKEpour renvoyer tous lesenregistrementspour lesquels il existeuneconditionparticulirepourunchampdonn.Parexemple,lasyntaxesuivanteslectionnetouslesclientsdontlenomcommenceparund:
SELECT * FROM Client WHERE Nom LIKE d%
Danscetteinstruction,lesymbole%estutilispourremplacerunesquencedecaractresquelconque.
Vous pouvez utiliser la clauseORDER BY pour renvoyer lesenregistrementsdansunordreparticulier. LoptionASCindiqueunordrecroissant,loptionDESCindiqueunordredcroissant.Plusieurschampspeuventtrespcifiscommecritredetri.Ilssontanalyssdelagaucheversladroite.Encasdgalitsurlavaleurdunchamp,lechampsuivantestutilis:
SELECT * FROM Client ORDER BY Nom DESC,Prenom ASC
ClauseWHERE
ClauseWHERE...IN
ClauseWHERE...BETWEEN
ClauseWHERE...LIKE
ClauseORDERBY...
- 2 - ENI Editions - All rigths reserved
Cette instruction retourne les clients tris par ordre dcroissant sur lenomet, en casdgalit, parordrecroissantsurleprnom.
b.Ajoutdinformations
LacrationdenregistrementsdansunetableseffectueparlacommandeINSERT INTO.Vousdevezindiquerlatabledanslaquellevoussouhaitezinsreruneligne,lalistedeschampspourlesquelsvousspcifiezunevaleuret,enfin,lalistedesvaleurscorrespondantes.Lasyntaxecomplteestdonclasuivante:
INSERT INTO client (codeClient,nom,prenom) VALUES (1000,Dupond,Pierre)
Lorsdelajoutdecenouveauclient,seulslenometleprnomserontrenseignsdanslatable.LesautreschampsprendrontlavaleurNULL.Silalistedeschampsnestpasindique,linstructioninsertexigequevousspcifiezunevaleurpourchaquechampdelatable.VoustesdoncobligsdutiliserlemotclNULLpourindiquerque,pourunchamp particulier, il ny a pas dinformation. Si la tableClient est compose de cinq champs(codeClient,nom,prenom,adresse,pays),linstructionprcdentepeuttrecriteaveclasyntaxesuivante:
INSERT INTO client VALUES (1000,Dupond,Pierre,NULL,NULL)
Danscecas,lesdeuxmotsclsNULLsontobligatoirespourleschampsadresseetpays.
c.Misejourdinformations
Lamodificationdeschampspourdesenregistrementsexistants,seffectueparlinstructionUPDATE.Cetteinstructionpeut mettrejourplusieurschampsdeplusieursenregistrementsdune table,partirdesexpressionsquiluisontfournies.Pourcela,vousdevezfournirlenomdelatablemettrejourainsiquelavaleuraffecterauxdiffrentschamps.LalisteestindiqueparlemotclSETsuividelaffectationdelanouvellevaleurauxdiffrentschamps.Sivousvoulezquelesmodificationsneportentquesurunensemblelimitdenregistrements,vousdevezspcifierlaclauseWHERE,afindelimiterlaportedelamisejour.SiaucuneclauseWHEREnestindique,lamodificationseferasurlensembledesenregistrementsdelatable.
Parexemple,pourmodifierladressedunclientparticulier,vouspouvezutiliserlinstructionsuivante:
UPDATE Client SET adresse= 4 rue de Paris 44000 Nantes WHERE codeClient=1000
Si lamodificationporte sur lensembledesenregistrementsde la table, la clauseWHEREestinutile.Parexemple,sivoussouhaitezaugmenterleprixunitairedetousvosarticles,vouspouvezutiliserlinstructionsuivante:
UPDATE CATALOGUE SET prixUnitaire=prixUnitaire*1.1
d.Suppressiondinformations
LinstructionDELETE FROMpermetdesupprimerunouplusieursenregistrementsdunetable.Vousdevez,auminimum,fournirlenomdelatablesurlaquellevaseffectuerlasuppression.Sivousnindiquezpasplusdeprcisions,toutesles lignes de la table sont supprimes. En gnral, une clauseWHERE est ajoute pour limiter ltendue de lasuppression.LacommandesuivanteeffacetouslesenregistrementsdelatableClient:
DELETE FROM Client
Lacommandesuivanteestmoinsradicaleetnesupprimequunenregistrementparticulier:
DELETE FROM Client WHERE codeClient=1000
Le langage SQL est, bien sr, beaucoup plus complet que cela et ne se rsume pas ces cinq instructions.Nanmoins, elles sont suffisantes pour la manipulation de donnes partir de Visual Basic. Si vous souhaitezapprofondirlapprentissagedulangageSQL,consultezundesouvragesdisponiblesdanslammecollectiontraitantdecesujetdemanirepluspousse.
- 3 - ENI Editions - All rigths reserved
PrsentationdADO.NET
ADO.NET est un ensemble de classes, dinterfaces, de structures et dnumrationspermettant lamanipulationdesdonnes. LesdiffrentscomposantsdADO.NETpermettentde sparer laccsauxdonnesdelamanipulationdesdonnes.ADO.NETfacilitegalementlutilisationdulangageXML,enpermettantlaconversiondedonnesrelationnellesauformatXMLoulimportationdedonnesauxformatsXMLdansunmodlerelationnel.DeuxmodesdefonctionnementsontdisponiblesdansADO.NET:
l lemodeconnect
l lemodenonconnect.
1.Modeconnect
Dans un environnement connect, lapplication ou lutilisateur est en permanence connect la sourcededonnes.Depuis lesdbutsdelinformatique,ctaitleseulmodedisponible.Cemodeprsentecertainsavantagesdanssonfonctionnement:
l Ilestfacilegrer:laconnexionestraliseaudbutdelapplicationpuisestcoupesafermeture.
l Laccs concurrentiel est plus facile contrler : comme tous les utilisateurs sont connects en permanence, il est plus facile decontrlerlequeltravaillesurlesdonnes.
l Lesdonnessontjour:toujoursgrcelaconnexionpermanenteauxdonnes,ilestfacilementenvisageabledeprvenirtouteslesapplicationsutilisantlesdonnesquedesmodificationsviennentdytreapportes.
Parcontre,certainsinconvnientsviennentunpeunoircirletableau:
l La connexion rseau doittre constammentmaintenue :en casdutilisationde lappli cation surunordinateurportable, laccsaurseaurisquedenepastredisponibleenpermanence.
l Il yaun risquedegaspillagede ressources sur le serveur :aumomentde ltablis sementduneconnexionentreuneapplicationcliente et un serveur, des ressources sont alloues sur le serveur pour la gestion de cette connexion. Ces ressources restentmonopolisesparlaconnexion,mmesiaucuneinformationnetransiteparcetteconnexion.
Cependant,danscertainessituations,lutilisationdunmodeconnectestincontournable.Cestlecas,parexemple,desapplicationseffectuantdestraitementsentempsrel.
2.Modenonconnect
Unmodenonconnectsignifiequuneapplicationouunutilisateurnestpasconstammentconnectunesourcededonnes.LesapplicationsInternetutilisentsouventcemodedefonctionnement.Laconnexionauxdonnesestouverte,lesdonnessontextraitespuislaconnexionestcoupe. Lutilisateur travailleavec lesdonnes,partirdesonnavigateur,et laconnexionestnouveauouvertepourlamisejourdelasourcededonnesou lobtentiondautresdonnes.Lesutilisateurs,travaillantsurdesordinateursportables,sontgalementlesprincipauxutilisateursdenvironnementsdconnects.Unmdecinpeut,parexemplelematin,chargerlesdossiersmdicauxdespatientsquilvavisiterdans la journe,puis le soir, fusionner lesmodificationsdans labasededonnes. Lesavantagesdunenvironnementdconnectsontlessuivants:
l Lesconnexionssontutilisespendantlapluscourtedurepossible.Decettefaon,unpetitnombredeconnexionsdisponiblessurunserveursuffisentpourdenombreuxutilisateurs.
l Un environnement dconnect amliore lvolutivit et les performances dune appli cation, en optimisant la disponibilit desconnexions.
Lenvironnementdconnectcomportecependantquelquesinconvnients:
l Lesdonnesdisponiblesdans lapplication ne sont pas toujours jour.Parexemple,danslecasdenotremdecin,sisasecrtaireajoutedesrsultatsdanalyseaprsquilaitrcuprlesdossiersmdicauxdecespatients,ilnepourrapasdisposerimmdiatementdesinformations.
l Desconflitspeuventparfoissurvenirlorsdelamisejourdesinformationsdanslabase.Cetypedeproblmesdoittreprisenchargelorsdelaconceptiondelapplication.Diffrentesapprochessontdisponiblespourlagestiondecesconflits:
l Autoriserlaprdominancedesmisesjour,lesplusrcentes,encrasantlesdonnesdjprsentesdanslabase.
l Autoriserlaprdominancedesmisesjour,lesplusanciennes,enabandonnantlesnouvellesmisesjour.
l Prvoirducodepermettantlutilisateurdechoisircequilsouhaitefaireencasdeconflitlorsdunemisejour.
3.ArchitecturedADO.NET
- 1 - ENI Editions - All rigths reserved
Le but dADO.NET est de fournir un ensemble de classes permettant laccs aux bases de donnes. Deux types de composants sontdisponibles:
l Lesfournisseursdedonnes,spcifiquesuntypedebasededonnes.Ilsassurentlacommunicationavecuntypespcifiquedebasededonnesetpermettentlamanipulationdesdonnesdirectementdanslabaseenmodeconnect.Lespossibilitssontcependantlimitespuisqueuniquementunaccsenlectureseuleestdisponible.
l Les classes de manipulation des donnes, indpendantes du type de base de donnes, voire utilisables sans base de donnes,permettentlamanipulationlocaledesdonnesdanslapplication.
4.Lesfournisseursdedonnes
Les fournisseurs de donnes servent de passerelle entre une application et une base de donnes. Ils sont utiliss pour rcuprer lesinformations, partir de la base de donnes, et transfrer les changements effectus sur les donnes par lapplication vers la base dedonnes.QuatrefournisseursdedonnessontdisponiblesdansleFramework.NET:
l lefournisseurpourSQLServer
l lefournisseurpourOLEDB
l lefournisseurpourODBC
l lefournisseurpourOracle.
Ilsproposenttouslimplmentationdequatreclasses,debase,ncessairespourledialogueaveclabasededonnes:
l LaclasseConnectionpermetdtabliruneconnexionavecleserveurdebasededonnes.
l LaclasseCommandpermetdedemanderlexcutionduneinstructionoudunensembledinstructionsSQLunserveur.
l La classeDataReader procureunaccsen lecture seuleetundfilement,enavantseulement,auxdonnes,(mmeprincipequunfichiersquenciel).
l LaclasseDataAdapterestutilisepourassurerletransfertdesdonnesversunsystmedecachelocallapplication(leDataSet)etmettrejourlabasededonnes,enfonctiondesmodificationseffectueslocalementdansleDataSet.
Quelquesautresclassessontdisponiblespour,parexemple,lagestiondestransactions,oulepassagedeparamtresuneinstructionSQL.
a.SQLServer
LefournisseurdedonnespourSQLServerutiliseunprotocolenatifpourdialogueravecleserveurdebasededonnes.Ilestgalementpeuconsommateurderessourcespuisquilaccdeauserveur,sansutiliserdecouchelogiciellesupplmentairetellequeOLEDBouODBC.Ilestutilisable avec SQL Serverpartirdelaversion7.TouteslesclassesdecefournisseurdedonnessontdisponiblesdanslespacedenomSystem.Data.SqlClient.Danscetespacedenom,lenomdechaqueclasseestprfixparSql.Ainsi,laclassepermettantdeseconnecterunserveurSQLServersappelleSqlConnection.
b.OLEDB
Le fournisseurOLEDButilise la couche logicielleOLEDBpour communiqueravec le serveurdebasededonnes.Vouspouvezutilisercefournisseurpourdialogueravecunebasededonnespourlaquelleilnexistepasdefournisseurspcifique,maispourlaquellelepiloteOLEDB est disponible. Avec cette solution, le fournisseur ne contacte pas le serveur directement mais passe par le pilote OLE DB pourcommuniquer.Pourquecettecommunicationsoitpossible,lepilotedoitimplmentercertainesinterfaces.Touteslesclassessontdisponiblesdans lespace de nomSystem.Data.OleDb. Lesnomsde classede cet espacedenomsontprfixs parOleDb.Pourpouvoir fonctionnercorrectement,cefournisseurexigelinstallationdeMDAC2.6surlamachine(MicrosoftDataAccessComponents).
c.ODBC
LefournisseurODBCutiliseunpiloteODBCnatifpourcommuniqueravecleserveurdebasededonnes.CefournisseurutiliseunpiloteODBCnatif pour la communication. Le principe est identique celuiutilispour le fournisseurOLEDB.Toutes les classes sontdisponiblesdanslespacedenomSystem.Data.Odbc.LesnomsdeclassessontprfixsparOdbc.Pourpouvoirfonctionnercorrectement,cefournisseurexigelinstallationsurlamachinedeMDAC2.6(MicrosoftDataAccessComponents).
d.ORACLE
LefournisseurpourOraclepermetlaconnexionunesourcededonnesOracle,traverslesoutilsclientOracle.CesoutilsclientdoiventtreinstallssurlesystmepourpouvoirseconnecterunebaseOracle.Laversion8.1.7ousuprieureestexige.Lesclassessontlocalisesdans lespace de nomSystem.Data.OracleClient et utilisent Oracle comme prfixedenom.Pourutiliser le fournisseurpourOracle,vousdevrezgalementajouterunerfrenceverslabibliothqueSystem.Data.OracleClient.dll.
- 2 - ENI Editions - All rigths reserved
5.Rechercherlesfournisseursdisponibles
Pourassurerlebonfonctionnementduneapplicationutilisantunaccsauxdonnes,lesfournisseursdedonnesdoiventtredisponiblessurle poste client. La classeDbProviderFactories propose lamthodepartageGetFactoryClasses,permettantdnumrerlesfournisseursdedonnesdisponiblessurleposte.Lexempledecodesuivantaffichelenom,ladescriptionetlespacedenomracinedechacundesfournisseursinstallssurlepostedetravail.
Imports System.Data Imports System.Data.Common Module ListeProviders Sub Main() Dim resultat As DataTable rcupration de la liste des fournisseurs dans une dataTable resultat = DbProviderFactories.GetFactoryClasses() Dim colonne As DataColumn Dim ligne As DataRow parcours des colonnes de la dataTable et affichage du nom For Each colonne In resultat.Columns Console.Write(colonne.ColumnName & vbTab) Next Console.WriteLine() parcours de la dataTable et affichage de chaque ligne For Each ligne In resultat.Rows parcours de chaque ligne et affichage de chaque champ For Each colonne In resultat.Columns Console.Write(ligne(colonne.ColumnName) & vbTab) Next Console.WriteLine() Next Console.ReadLine() Stop End Sub End Module
6.Compatibilitducode
En fonctiondu fournisseurutilis, vousdevez importer lespacedenomcorrespondantpouravoirunaccsfacileauxclassesdufournisseur.Cependant,commelesclassesdechacundesfournisseursneportentpaslemmenom,votrecodeseraspcifiqueuntypedefournisseur.Ilesttoutefoispossibledcrireducodepratiquementindpendantdutypedefournisseur.Pourcela,aulieudutiliserlesclassesspcifiqueschacundesfournisseurs,vouspouvezutilisercommetypededonneslesinterfacesquellesimplmentent.Lutilisationduneclassespcifiquenest indispensablequepour la crationde la connexion.Une foisque la connexionest cre,vouspouveztravailleruniquementavecdesinterfaces.LexempledecodesuivantlistelecontenudunetabledunebaseSQLServer,enutilisantuniquementdesinterfaces.
Module accesBdParInterfaces Dim ctn As IDbConnection Public Sub main() cest la seule ligne de code spcifique un fournisseur ctn = New System.Data.SqlClient.SqlConnection("Data Source=TG;Initial Catalog=Northwind;Integrated Security=True") Dim cmd As IDbCommand cmd = ctn.CreateCommand ctn.Open() cmd.CommandText = "select * from products Dim lecteur As IDataReader lecteur = cmd.ExecuteReader Console.WriteLine("Lecture des donnes dans une base SQL Server") Do While lecteur.Read Console.WriteLine("numro : {0} nom produit : {1}", lecteur.GetInt32(0), lecteur.GetString(1)) Loop End Sub End Module
Lexcutiondececodeaffichelersultatsuivant:
Lecture des donnes dans une base SQL Server numro : 56 nom produit : Gnocchi di nonna Alice numro : 57 nom produit : Ravioli Angelo numro : 58 nom produit : Escargots de Bourgogne numro : 59 nom produit : Raclette Courdavault numro : 60 nom produit : Camembert Pierrot numro : 61 nom produit : Sirop derable numro : 62 nom produit : Tarte au sucre
Sicetteapplicationdoitensuitemigrerversunautretypedebasededonnes,ilnyaquelaligneconcernantlaconnexionmodifier.SilesdonnessontmaintenantdisponiblesdansunebaseAccess,lacrationdelaconnexionprendalorslaformesuivante:
ctn = New System.Data.OleDb.OleDbConnection (Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\tgroussard\Mes documents\livre vb.net 2005\ accs aux bases de donnes\exemples\NWIND.MDB)
- 3 - ENI Editions - All rigths reserved
Lexcutionducodeainsimodifignrebienlemmersultat:
Lecture des donnes dans une base Access numro : 56 nom produit : Gnocchi di nonna Alice numro : 57 nom produit : Ravioli Angelo numro : 58 nom produit : Escargots de Bourgogne numro : 59 nom produit : Raclette Courdavault numro : 60 nom produit : Camembert Pierrot numro : 61 nom produit : Sirop derable numro : 62 nom produit : Tarte au sucre
Ilconvient,parcontre,dtreprudentetdenepasutiliserdinstructionsSQLspcifiquesuntypedebasededonnesparticulier.Pourfaciliterlarelectureducode,ilestprfrablederegroupertouteslesinstructionsSQLsousformedeconstantesdetypechanedecaractresaudbutde chaquemodule.Avec cette technique,vousnaurez pas chercherdesinstructionsSQLaumilieudecentainesdelignesdecodeVisualBasic.IlconvientdtregalementprudentlorsdelutilisationdeparamtresdansuneinstructionSQL.LefournisseurpourSQLServerutilisedesparamtresnommsdonclordredecrationdesparamtresnapasdimportance.
Le fournisseurpourOLEDButilise lapositiondesparamtresdans linstructionSQL,pourleremplacementlorsdelexcution.Lordredelacrationdesparamtresestdonc,danscecas,capitalpourlebonfonctionnementdelinstruction.
- 4 - ENI Editions - All rigths reserved
Utilisationdumodeconnect
Danscechapitre,nousallonsaborderlesoprationspouvanttreexcutessurunebasededonnes,enutilisantlemodeconnect. Certaines notionstudiesdanscechapitreserontgalementutilespourlefonctionnementenmodedconnect. Pour tester les diffrentes fonctionnalits tudies dans ce chapitre, nous utiliserons un serveur SQLServer2000. Labasededonnesutilisesera labaseNorthwindquiestcrepardfautlinstallationduserveur.Unepartiedelastructuredelabaseestdisponiblesurleschmacidessous.
1.Connexionunebase
Pourpouvoirtravailleravecunserveurdebasededonnes,uneapplicationdoittabliruneconnexionrseauavecleserveur. La classeSqlConnection est capable de grer une connexion vers un serveur SQLServer version 7.0 ouultrieure.Commepourtoutobjet,nousdevonsenpremierlieudclarerunevariable.
Dim ctn As System.Data.SqlClient.SqlConnection
Puis,nousdevons crer linstancede laclasseet linitialiserenappelantunconstructeur.Linitialisationvaconsisteressentiellement indiquer les paramtres utiliss pourtablir la connexion avec le serveur. Ces paramtressontdfinissousformedunechanedecaractres.IlspeuventtreindiquslorsdelappelduconstructeuroumodifisparlasuiteparlapropritConnectionString.
a.Chanedeconnexion
Leformatstandarddunechanedeconnexionestconstitudunesriedecouplesmotcl/valeursparspardespointsvirgules.Lesigne=estutilispourlaffectationdunevaleurunmotcl.Lanalysedelachaneesteffectuelors de laffectation de la chane lapropritConnectionString. Lesvaleursassociesauxmots clssontalorsextraites et affectesauxdiffrentespropritsde la connexion.Siuneerreurde syntaxeest trouvealorsuneexception est gnre immdiatement et aucune proprit nest modifie. Par contre, certaines proprits nepourront tre contrlesque lorsde louverturede la connexion.Cest alors cemomentquuneexceptionseradclenche si la chanede connexion contientuneerreur. La chane de connexion ne peuttremodifiequesilaconnexionestferme.Lesmotsclssuivantssontdisponiblespourunechanedeconnexion:
ConnectTimeout
Dure en secondes pendant laquelle lapplicationattendraune rponse du serveur sademandedeconnexion.Passcedlai,uneexceptionestdclenche.
- 1 - ENI Editions - All rigths reserved
DataSource
Nomouadresse rseau du serveur vers lequel esttablielaconnexion.Lenumroduportpeuttrespcifilasuitedunomoudeladresserseau.Silnestpasindiqu,lenumrodeportestgal1433.
InitialCatalog
Nomdelabasesurlaquelledoitseffectuerlaconnexion.
IntegratedSecurity
Si cettevaleurestpositionnesurfalsealorsunnomdutilisateuretunmotdepassedoiventtrefournisdanslachanedeconnexion.Sinon,lecompteWindowsdelutilisateurestutilispourlauthentification.
PersistSecurityInfo
Si cettevaleurestpositionne surtrue, alors lenomde lutilisateuretsonmotdepassesontaccessiblespar laconnexion.Pourdes raisonsde scurit, cette valeur doittrepositionnesurfalse.Cestdailleurslecassivousnindiquezriendansvotrechanedeconnexion.
Pwd
MotdepasseassociaucompteSQLServerutilispourlaconnexion.Silnyapasdemotdepasseassociuncompte,cetteinformationpeuttreomisedanslachanedeconnexion.
UserID
NomducompteSQLServerutilispourlaconnexion.
ConnectionLifeTime
Indiqueladuredevieduneconnexiondansunpooldeconnexions.Unevaleurgalezroindiqueuneduredevieinfinie.
ConnectionReset
Indiquesilaconnexionestrinitialiselorsdesaremisedanslepool.
MaxPoolSize
Nombremaximumdeconnexionsdanslepool.
MinPoolSize
Nombreminimumdeconnexionsdanslepool.
Pooling
Indiquesilaconnexionpeuttreextraitedunpooldeconnexion.
Unechanedeconnexionprenddonclaformeminimalesuivante:
ctn.ConnectionString = "Data Source=Minerve;Initial Catalog=Northwind;Integrated Security=true"
b.Pooldeconnexions
Les pools de connexions permettent damliorer les performances dune application, envitant la cration deconnexionssupplmentaires.Lorsquuneconnexionestouverte,unpooldeconnexionsestcrensebasantsurunalgorithme bas, luimmesur la chanede connexion.Chaquepool estdoncassoci une chanedeconnexionparticulire. Si une nouvelle connexion est ouverte et quil nexiste pas de pool correspondant exactement sachanedeconnexion,alorsunnouveaupoolestcr.Lespoolsdeconnexionsainsicrsexisterontjusqulafindelapplication.Lorsdelacrationdupool,dautresconnexionspeuventtrecresautomatiquementpoursatisfairelavaleurMin Pool Sizeindiquedanslachanedeconnexion.Dautresconnexionspourront,parlasuite,treajoutes
- 2 - ENI Editions - All rigths reserved
aupooljusquatteindrelavaleurMax Pool Sizedelachanedeconnexion.Lorsquuneconnexionestrequise,ellepeuttreobtenuepartirdunpooldeconnexion(silenexisteuncorrespondantexactementauxcaractristiquesdelaconnexiondemande).Ilfautbiensrquelepoolencontienneunedisponibleetactive.
Silenombremaximumdeconnexiondanslepoolestatteint,lademandeestmiseenfiledattentejusqucequuneconnexionsoitnouveaudisponible.Uneconnexionestremiseladispositiondupool,lorsdesafermetureoulorsdelappeldelamthodeDisposesurlaconnexion.Pourcetteraison,ilestrecommanddefermerexplicitementlesconnexionslorsquellesnesontplusutilisesdanslapplication.Lesconnexionssontretiresdupoollorsqueceluicidtectequelaconnexionnapastutilisedepuisuncertaintemps,indiquparlavaleurConnectionLifeTimedelachane de connexion. Elles sontgalement retires du pool, sil dtecte que la connexion avec le serveur atinterrompue.
c.vnementsdeconnexion
La classeSQLConnection propose deuxvnementsvouspermettantdtreprvenulorsqueltatdelaconnexionchangeouquunmessagedinformationestenvoyparleserveur.LvnementStateChangedestdclenchlorsdunchangementdtat de la connexion. Le gestionnaire de cetvnement reoit un paramtre de typeStateChangeEventArg permettantdobtenir, avec lapropritCurrentState, ltatactuelde laconnexionetavec lapropritOriginalState, ltatdelaconnexionavantledclenchementdelvnement.Pourtesterlavaleurdecesdeuxproprits,vouspouvezutiliserlnumrationConnectionState.
Lvnement InfoMessage est dclench lorsque le serveur vous informe dune situation, anormale, mais qui nejustifiepas ledclenchementduneexception(svritdumessageinfrieure10).LegestionnairedvnementsassocireoitunparamtredetypeInfoMessageEventArgs.ParlapropritErrorsdeceparamtre,vousavezaccs des objetsSqlErrors correspondant aux informations envoyes par le serveur. Le code suivant affiche, sur laconsole,lesmessagesdinformationsenprovenanceduserveur.
Private Sub ctn_InfoMessage(ByVal sender As Object, ByVal e As System.Data. SqlClient.SqlInfoMessageEventArgs) Handles ctn.InfoMessage Dim info As SqlClient.SqlError For Each info In e.Errors Console.WriteLine(info.Message) Next End Sub
2.Excutiondunecommande
Aprs avoirtabliuneconnexionversunserveurdebasededonnes,vouspouvezluitransmettredesinstructionsSQL. La classeSqlCommand est utilise pour demander au serveur lexcution dune commande SQL. Cette classecontientplusieursmthodespermettant lexcutiondediffrents typesde requtesSQL.LaclasseSqlCommandpeuttre instancie de faon classique, en utilisant un de ses constructeurs oune instance peuttreobtenuepar lamthodeCreateCommanddelaconnexion.
a.Crationdunecommande
La premire possibilit pour crer uneSqlCommand estdutiliserundes constructeursde la classe. Lutilisationduconstructeur par dfaut vous oblige par la suite utiliser diffrentes proprits, pour fournir les informationsconcernantlinstructionSQLexcuter.
LapropritCommandTextcontientletextedelinstructionSQLexcuter.LapropritConnectiondoitfairerfrenceuneconnexionvalideversleserveurdebasededonnes.Lecodesuivantrsumecesdiffrentesoprations:
Dim cmd As SqlCommand cmd = New SqlCommand cmd.Connection = ctn cmd.CommandText = SELECT * FROM Products
- 3 - ENI Editions - All rigths reserved
Ladeuximesolutionestdutiliserun constructeur surcharg, acceptantcommeparamtres,linstructionSQLsousforme dune chane de caractres et la connexion utilise par cetteSqlCommand. Le codeprcdentpeutdoncsersumerlalignesuivante:
Dim cmd As new SqlCommand( " SELECT * FROM Products",ctn)
La troisimesolutionestdutiliserlamthodeCreateCommanddelaconnexion.Danscecas,seulelinstructionSQLabesoindtrespcifieparlasuite.
Dim cmd as SqlCommand cmd = ctn.CreateCommand cmd.CommandText = " SELECT * FROM Products"
b.Lecturedinformations
Frquemment, linstruction SQL dune SqlCommand slectionne un ensemble denregistrements dans la base, ouventuellementunevaleuruniquetantlersultatduncalculeffectusurdesvaleurscontenuesdanslabase.UneinstructionSQL, renvoyantunensembledenregistrements,doittreexcuteparlamthodeExecuteReader.CettemthoderetourneunobjetDataReaderquivapermettre,parlasuite,lalecturedesinformationsenprovenancedelabase de donnes. Si linstruction SQL ne renvoie quune valeur unique, la mthodeExecuteScalar se charge delexcutionetretourneellemmelavaleurenprovenancedelabasededonnes.
Lecodesuivantpermetlarcuprationdunombredecommandespassesparunclient:
cmd.CommandText = " select count(orderid) from orders where customerid=FRANK" Console.WriteLine("le client FRANK a pass {0} commande(s)", cmd.ExecuteScalar())
Lecasdinstructionsrenvoyantplusieursenregistrementsestunpeupluscomplexe.Aprsavoirexcutlinstructionpar la mthode ExecuteReader et rcupr lobjet DataReader vous pouvez utiliser ce dernier pour parcourir lesrsultats renvoys. La mthode Read de la classeDataReader permet le dplacement dans lensemble desenregistrements renvoys. Cettemthode retourneunboolean indiquant sil resteunenregistrement suivant. Ledplacementnestpossiblequedupremieraudernierenregistrement.CetypededplacementestappelForward Only.LesinformationscontenuesdanslenregistrementcourantsontaccessiblesparunedesmthodesGet...delaclasseDataReader.Cesmthodespermettentdextrairelesdonnesdelenregistrementetdelesconvertirdansuntypededonnes.NET.IlenexisteuneversionpourchaquetypededonnesduFramework.NET.Ilfautbiensrqueles informations prsentes dans lenregistrement, puissenttre converties dans le type correspondant. Si laconversionestimpossible,ilyadclenchementduneexception.LesmthodesGet...attendent,commeparamtre,lenumroduchamppartirduquelellesrcuprentlinformation.Vouspouvezaussiutiliserlaproprit,pardfaut,Item duDataReader en indiquant lenomduchampconcern. Il nyapas,danscecas,deconversionet lavaleurrenvoyeestdetypeObject.
Lecodesuivantaffichelalistedetouteslescatgoriesdeproduitsdisponibles:
Imports System.Data.SqlClient Module TestExecuteReader Dim cmd As SqlCommand Dim ctn As SqlConnection Dim lecteur As SqlDataReader
Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind; Integrated Security=true" ctn.Open() cmd = New SqlCommand cmd.Connection = ctn cmd.CommandText = " select * from categories" lecteur = cmd.ExecuteReader Do While lecteur.Read Console.WriteLine("numero de la categorie:{0}" & vbTab & "Description:{1}", lecteur.GetInt32(0), lecteur("CategoryName")) Loop lecteur.Close() ctn.Close() End Sub End Module
LutilisationduneconnexionparunDataReaderseffectuedemanireexclusive.Pourquelaconnexionsoitnouveau
- 4 - ENI Editions - All rigths reserved
disponiblepouruneautrecommande,vousdevezobligatoirementfermerleDataReaderaprssonutilisation.
c.Modificationdesinformations
LamodificationdesinformationsdansunebasededonnesseffectueprincipalementparlesinstructionsSQL INSERT,UPDATE, DELETE.Ces instructionsne retournentpasdenregistrementsenprovenancedelabasededonnes.Pourutiliser ces instructions, vous devez crer uneSqlCommand, puis demander lexcution de cette commande par lamthodeExecuteNonQuery. Cette mthode retourne le nombre denregistrements affects par lexcution delinstructionSQLcontenuedanslaSqlCommand.SilapropritCommandTextcontientplusieursinstructionsSQL,alorslavaleur renvoye par la mthode ExecuteNonQuery correspond au nombre total de lignes affectespar toutes lesinstructionsSQLdelaSqlCommand.
LecodesuivantajouteunenouvelleentreprisedelivraisondanslatableShippers:
Imports System.Data.SqlClient Module TestExecuteNonQuery Dim cmd As SqlCommand Dim ctn As SqlConnection Public Sub main() ctn = New SqlClient.SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind; Integrated Security=true" ctn.Open() cmd = New SqlClient.SqlCommand cmd.Connection = ctn cmd.CommandText = "Insert into shippers (companyname,phone) values (DHL,02 40 41 42 43)" Console.WriteLine("{0} ligne(s) ajoute(s) dans la table", cmd.ExecuteNonQuery) ctn.Close() End Sub End Module
d.Utilisationdeparamtres
LamanipulationdinstructionsSQLpeuttrefaciliteparlacrationdeparamtres.IlspermettentdeconstruiredesinstructionsSQLgnriques, pouvant facilementtre rutilises.Leprincipede fonctionnementestsemblableauxprocdures et fonctions de Visual Basic. Une alternative lutilisationdeparamtres pourraittre la constructiondynamiquedinstructionSQLparconcatnationdechanesdecaractres.
Cidessous,unexempleutilisantcettetechniqueetpermettantlarecherchedunclientparsoncode(nousverronsensuitecommentamliorercecodeenutilisantdesparamtres):
Imports System.Data.SqlClient Module TestRequeteConcat Dim cmd As SqlCommand Dim ctn As SqlConnection Dim lecteur As SqlDataReader Dim codeClient As String Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind; Integrated Security=true" ctn.Open() cmd = New SqlCommand cmd.Connection = ctn Console.Write("saisir le code du client recherche :") codeClient = Console.ReadLine() cmd.CommandText = " SELECT * from Customers WHERE CustomerID = " & codeClient & "" lecteur = cmd.ExecuteReader Do While lecteur.Read Console.WriteLine("nom du client:{0}", lecteur("ContactName")) Loop lecteur.Close() ctn.Close() Console.ReadLine()
- 5 - ENI Editions - All rigths reserved
End Sub End Module
LapartieimportantedececodesesituelorsdelaffectationdunevaleurlapropritCommandText.UneinstructionSQL correcte doittre construite par concatnation de chanesde caractres.Dansnotre cas, cest relativementsimplepuisquilnyaquunevaleurvariabledanslinstructionSQL,maissiplusieursinformationsdoiventvarier,ilyaunemultitudedeconcatnationsraliser.Leserreursclassiquesdanscesconcatnationssont:
l loublidunespace
l loublidescaractrespourencadrerunevaleurdetypechanedecaractres
l unnombredecaractreimpair.
Toutesceserreursont,pourmmeeffet,lacrationduneinstructionSQLinvalidequiserarejetelexcutionparleserveur.
Lutilisationdesparamtressimplifieconsidrablementlcrituredecetypederequte.Lesparamtressontutilisspourmarquerunemplacementdansunerequteoseraplac,aumomentdelexcution,unevaleurlittralechanede caractres ou numrique. Les paramtres peuventtre nomms ou anonymes. Un paramtre anonyme estintroduitdansunerequteparlecaractre?.Lesparamtresnommssontspcifisparlecaractre@suividunomduparamtre.
Larequtedenotreexempleprcdentpeutprendrelesformessuivantes:
cmd.CommandText = " SELECT * from Customers WHERE CustomerID = ?"
ou
cmd.CommandText = " SELECT * from Customers WHERE CustomerID = @Code
LexcutiondelaSqlCommandchouemaintenantsiaucuneinformationnestfourniepourleoulesparamtres.
La SqlCommand doit avoir une liste de valeurs utilises pour le remplacement des paramtres, au moment delexcution. Cette liste est stocke dans la collectionParameters de laSqlCommand. Avant lexcution de laSqlCommand,ilfautdonccrerlesobjetsSqlParameteretlesajouterlacollection.PourchaqueSqlParameter,ilfautfournir:
l lenomduparamtre
l lavaleurduparamtre
l ladirectiondutilisationduparamtre.
Lesdeuxpremiresinformationssontindiqueslorsdelaconstructiondelobjet:
- 6 - ENI Editions - All rigths reserved
Dim paramCodeClient As SqlParameter paramCodeClient = New SqlParameter("@Code", codeClient)
La direction dutilisation indique si linformation contenue dans le paramtre, est passe au code SQL pour sonexcution (Input)ou si cest lexcutiondu codeSQLqui vamodifier lavaleurduparamtre (Output)ou lesdeux(InputOutput).LapropritDirectiondelaclasseSqlParameterindiquelemodedutilisationduparamtre.
LeparamtreestmaintenantprttreajoutlacollectionParameters.Ilconvientdtrevigilantceniveau,silarequteutiliselesparamtresanonymes.Lesparamtresdoiventobligatoirementtreajoutslacollection,danslordre de leur apparition dans la requte. Si les paramtresnommssontutiliss, il nestpas indispensablederespecter cette rgle,mais il estprudentde sy conformer, si un jour le codeSQLestmodifietnutilisepluslesparamtres nomms. Ceci pourratre le cas si vous devez changer de type fournisseur de donnes et que lenouveaunacceptepaslesparamtresnommsdansuneinstructionSQL.LaSqlCommandestmaintenantprtepourlexcution. noter quavec cette solution nous navons pas nous soucier du type de valeur attendue parlinstructionSQLpoursavoirsinousdevonslencadreravecdescaractres.SidesparamtressontutilissensortiedelinstructionSQL,ilsneserontdisponiblesquaprslafermetureduDataReader.Lexemplesuivantafficheenplusdunomduclient,lenombredecommandesquiladjpasses:
Imports System.Data.SqlClient Module TestRequeteConcat Dim cmd As SqlCommand Dim ctn As SqlConnection Dim lecteur As SqlDataReader Dim codeClient As String Dim paramCodeClient As SqlParameter Dim paramNbCommandes As SqlParameter
Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind; Integrated Security=true" ctn.Open() cmd = New SqlCommand cmd.Connection = ctn Console.Write("saisir le code du client recherche :") codeClient = Console.ReadLine() cmd.CommandText = " SELECT * from Customers WHERE CustomerID = @Code;select @nbCmd=count(orderid) from orders where customerid=@code" paramCodeClient = New SqlParameter("@Code", codeClient) paramCodeClient.Direction = ParameterDirection.Input cmd.Parameters.Add(paramCodeClient) paramNbCommandes = New SqlParameter("@nbCmd", Nothing) paramNbCommandes.Direction = ParameterDirection.Output cmd.Parameters.Add(paramNbCommandes) lecteur = cmd.ExecuteReader Do While lecteur.Read Console.WriteLine("nom du client:{0}, lecteur("ContactName")) Loop lecteur.Close() Console.WriteLine("ce client a passe {0} commande(s)", cmd.Parameters ("@nbCmd").Value) ctn.Close() Console.ReadLine() End Sub End Module
e.Excutiondeprocdurestocke
LesprocduresstockessontdeslmentsdunebasededonnescorrespondantunensembledinstructionsSQL,
- 7 - ENI Editions - All rigths reserved
pouvanttreexcutesparsimpleappeldeleurnom.CesontdesvritablesprogrammesSQLpouvantrecevoirdesparamtresetrenvoyerdesvaleurs.Deplus,lesprocduresstockessontenregistresdanslecachemmoireduserveur, sous formecompile lorsde leurpremireexcution, cequi accrot lesperformancespour lesexcutionssuivantes.UnautreavantagedesprocduresstockesestdecentralisersurleserveurdebasededonnestouslescodesSQLduneapplication.SidesmodificationsdoiventtreapportesdanslesinstructionsSQL,vousnaurezquedes modifications effectuer sur le serveur sans avoir reprendre le code de lapplication, donc sans avoirregnreretredployerlapplication.
Lappel uneprocdure stocke,partirdeVisualBasic,estpratiquementsimilairelexcutionduneinstructionSQL.LapropritCommandTextcontientlenomdelaprocdurestocke.VousdevezgalementmodifierlapropritCommandTypeaveclavaleurCommandType.StoredProcedurepourindiquerquelapropritCommandTextcontientlenomduneprocdure stocke.CommepouruneinstructionSQL,uneprocdurestockepeututiliserdesparamtresenentre ou en sortie. Il y a un troisime type de paramtre disponible pour les procdures stockes le typeReturnValue. Ce type de paramtre sert rcuprer la valeur renvoye par linstructionReturn de laprocdurestocke (mme principe quune fonction Visual Basic). Pour tester ces nouvelles notions, nous allons utiliser laprocdurestockesuivante,quiretournelemontanttotaldetouteslescommandespassesparunclient.
CREATE PROCEDURE TotalClient @code nchar(5) AS declare @total money select @total=sum(UnitPrice*Quantity*(1-Discount)) from Orders,[Order Details] where customerid=@code and Orders.orderid=[order details].orderid return @total GO
AuniveauducodeVisualBasic,nousdevonsindiquerquilsagitdelexcutionduneprocdurestockeetajouterunparamtrepourrcuprerlavaleurderetourdelaprocdurestocke.CeparamtredoitsappelerRETURN_VALUE.
Imports System.Data.SqlClient Module TestProcedureStockee Dim cmd As SqlCommand Dim ctn As SqlConnection Dim paramCodeClient As SqlParameter Dim paramMontant As SqlParameter Dim codeclient As String
Public Sub main() Console.Write("saisir le code du client recherche :") codeClient = Console.ReadLine() ctn = New SqlConnection() ctn.ConnectionString = Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true ctn.Open() cmd = New SqlCommand cmd.Connection = ctn cmd.CommandText = "TotalClient" cmd.CommandType = CommandType.StoredProcedure paramCodeClient = New SqlParameter("@Code", codeClient) paramCodeClient.Direction = ParameterDirection.Input cmd.Parameters.Add(paramCodeClient) paramMontant = New SqlParameter("RETURN_VALUE", SqlDbType.Decimal) paramMontant.Direction = ParameterDirection.ReturnValue cmd.Parameters.Add(paramMontant) cmd.ExecuteNonQuery() Console.WriteLine("Ce client a passe pour {0} Euros de commande", paramMontant.Value) Console.ReadLine() ctn.Close() End Sub End Module
- 8 - ENI Editions - All rigths reserved
Utilisationdumodenonconnect
Dansunmodenonconnect,laliaisonavecleserveurdebasededonnesnestpaspermanente.Ilfautdoncconserverlocalement lesdonnessur lesquellesonsouhaitetravailler.Lideestderecrer,laidedediffrentesclasses,uneorganisationsimilairecelledunebasededonnes.Lesprincipalesclassessontreprsentessurleschmasuivant:
DataSet
Cestleconteneurdeplushautniveau,iljouelemmerlequelabasededonnes.
DataTable
Commesonnomlindique,cestlquivalentdunetabledebasededonnes.
DataRow
Cetteclassejouelerledunenregistrement(ligne).
DataColumn
Cetteclasseremplaceunchamp(colonne)dunetable.
UniqueConstraint
Cestlquivalentdelaclprimairedunetable.
ForeignKeyConstraint
Cestlquivalentdelacltrangre.
DataRelation
Reprsenteunlienparent/enfantentredeuxDataTable.
Leschmacidessousreprsentecetteorganisation.
Nousallonsvoirmaintenantcommentcreretmanipulertoutescesclasses.
1.RemplirunDataSetpartirdunebasededonnes
Pourpouvoir travailler localementavec lesdonnes,nousdevons les rapatrierdepuis labasededonnesdansunDataSet. Chaque fournisseur de donnes fournit une classeDataAdapter, assurant le dialogue entre la base dedonnes et unDataSet. Tous leschanges se fontpar lintermdiairedecetteclasse,aussibiendelabaseversle
- 1 - ENI Editions - All rigths reserved
DataSet que duDataSet vers la base pour lamisejourdesdonnes.LeDataAdapterutiliserauneconnexionpourcontacterleserveuretuneouplusieurscommandespourletraitementdesdonnes.
a.UtilisationdunDataAdapter
LapremirechoseraliserestdecreruneinstancedelaclasseSQLDataAdapter.NousdevonsensuiteconfigurerleDataAdapter afinde lui indiquerquellesdonnes nous souhaitons rapatrierpartirde labasededonnes.Laproprit SelectCommand doit rfrencer un objetCommand, contenant linstructionSQLchargede slectionner lesdonnes. Lobjet Command utilis peut galement appeler une procdure stocke. La seule contrainte est quelinstructionSQLexcutepar lobjetCommandsoituneinstructionSELECT.LaclasseDataAdapter contientgalementlespropritsInsertCommand,DeleteCommandetUpdateCommandrfrenantlesobjetsCommand,utilisslorsdelamisejourdelabasededonnes.Tantquenousnesouhaitonspaseffectuerdemisejourdelabase,cespropritssont facultatives. Elles seronttudies plus en dtail dans le chapitre consacr la mise jourde labasededonnes.
LamthodeFilldelaclasseDataAdapterestensuiteutilisepourremplirleDataSetaveclersultatdelexcutiondelacommandeSelectCommand.Cettemthodeattend,commeparamtre, leDataSetquelledoitrempliretunobjetDataTableouunechanedecaractresutilisepournommerlaDataTabledansleDataSet.LeDataAdapterutilise,eninterne,unobjetDataReaderpourobtenirlenomdeschampsetletypedeschampspourcrerlaDataTabledansleDatasetetensuitelerempliraveclesdonnes.LaDataTableetlesDataColumnsontcrsuniquementsilsnexistentpas dj, sinon la mthode Fill utilise la structure existante. Si uneDataTable est cre,elleestajoute lacollectionTablesduDataSet.LetypededonnesdesDataColumnestdfinienfonctiondesmappagesprvusparlefournisseur de donnes, entre les types de la base de donneset les types .NET. Lexemplesuivant remplitunDataSetaveclescode,nom,adresseetvilledesclients.
Imports System.Data.SqlClient Module TestDataSet1 Dim cmd As SqlCommand Dim ctn As SqlConnection Dim ds As DataSet Dim da As SqlDataAdapter
Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true cmd = New SqlCommand cmd.Connection = ctn cmd.CommandText = " SELECT CustomerId,ContactName,Address,city from Customers" ds = New DataSet da = New SqlDataAdapter() da.SelectCommand = cmd da.Fill(ds, "Customers") End Sub End Module
Dans ce code, la connexion na past ouverte et ferme, explicitement. En effet, la mthode Fill ouvre laconnexionsiellenestpasdjouverteetdanscecas,larefermegalementlafindesonexcution.Toutefois,sivousavezbesoindutiliserplusieurs fois lamthodeFill,ilestplusefficacedegrervousmmelouvertureetlafermeturedeconnexion.Danstouslescas,lamthodeFilllaisselaconnexiondansltatoellelatrouve.
Un DataSet peut bien sr contenir plusieursDataTable cres partir deDataAdapter diffrents. Les donnespeuventmmeprovenirdebasesdedonnesdiffrentes,voiredetypesdeserveursdiffrents.
Lorsque leDataAdapter construit laDataTable, les noms des champs de la base sont utiliss pour nommer lesDataColumn.IlestpossibledepersonnalisercesnomsencrantdesobjetsDataTableMappingetenlesajoutantlacollection TableMappings duDataAdapter. Ces objetsDataTableMapping contiennent euxmmes des objetsDataColumnMappingutilissparlamthodeFill,commetraducteursentrelesnomsdeschampsdanslabaseetlesnoms desDatacolumn dans leDataSet.Danscecas, lorsdelappeldelamthodeFillnousdevonsluiindiquerlenomduDataTableMappingutiliser.
Si,pourunouplusieurschamps,ilnyapasdemappagedisponible,alorslenomduchampdanslabaseestutiliscomme nom pour laDataColumncorrespondante.Nouspouvons,parexemple,utilisercettetechniquepourtraduireleschampsdelabaseNorthwind.
LecodesuivanteffectuecettetraductionetaffichelenomdesDatacolumnduDataTablecr:
Imports System.Data.SqlClient Imports System.Data.Common
- 2 - ENI Editions - All rigths reserved
Module TestTableMapping Dim cmd As SqlCommand Dim ctn As SqlConnection Dim ds As DataSet Dim da As SqlDataAdapter Dim mappage As DataTableMapping Dim dc As DataColumn
Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true" cmd = New SqlCommand cmd.Connection = ctn cmd.CommandText = " SELECT CustomerId,ContactName,Address,city from Customers ds = New DataSet da = New SqlDataAdapter() da.SelectCommand = cmd mappage = New DataTableMapping("Customers", "Clients") mappage.ColumnMappings.Add("CustomerId", "CodeClient") mappage.ColumnMappings.Add("ContactName", "Nom") mappage.ColumnMappings.Add("Address", "Adresse") mappage.ColumnMappings.Add("city", "Ville") da.TableMappings.Add(mappage) da.Fill(ds, "Customers") For Each dc In ds.Tables("Clients").Columns Console.Write(dc.ColumnName & vbTab) Next Console.ReadLine() End Sub End Module
Nousobtenonslaffichage:
CodeClient Nom Adresse Ville
b.AjoutdecontraintesexistantesunDataSet
LamthodeFillnefaitquetransfrer,versleDataSet,lesdonnesenprovenancedelabase.Biensouvent,descontraintesdeclsprimairessontutilisesdanslabasededonneset,pardfaut,lamthodeFillnelesrapatriepasdansleDataSet.PourpouvoirrcuprercescontraintesdansleDataSet,ilyadeuxsolutionspossibles:
l ModifierlapropritMissingSchemaActionduDataAdapteraveclavaleurMissingSchemaAction.AddWithKey
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
l ProcderendeuxtapesenappelantdabordlamthodeFillSchemaduDataAdapterpourcrerlastructurecompltedelaDataTable,puisensuiteappelerlamthodeFillpourremplirlaDataTableaveclesdonnes.
da.FillSchema(ds, SchemaType.Mapped, "Customers") da.Fill(ds, "Customers")
Le deuxime paramtre de la mthode FillSchema indique si le mappage doittre pris en compte ou si lesinformationsissuesdelabasesontutilises.
IlestimportantdajouterlescontraintesdeclsprimairescarlamthodeFillvasecomporterdiffremmentsiellesexistentoupas.
SilescontraintesexistentauniveauduDataSet,lorsquelamthodeFillimporteunenregistrementdepuislabase,ellevrifiesilnexistepasdjuneligneaveclammevaleurdeclprimairedanslaDataTable.Sicestlecas,ellemet uniquement jourleschampsdelaligneexistante.Si,parcontre,ilnyapasdeligneavecunevaleurdeclprimaireidentique,alorslaligneestcredanslaDataTable.
Sil nyapasde contraintede clprimaire sur laDataTable, lamthodeFill ajoute tous lesenregistrementsenprovenancede la base. Il risquedans ce casdy avoir des doublons dans laDataTable.Ceci estparticulirement
- 3 - ENI Editions - All rigths reserved
importantlorsquelamthodeFilldoittreappeleplusieursfoispour,parexemple,obtenirlesdonnesmodifiesparuneautrepersonnedanslabasededonnes.
2.ConfigurerunDataSetsansbasededonnes
Il nest pas ncessaire de disposer dune base de donnes pour pouvoir utiliser desDataSet. Ils peuvent servirdalternativelutilisationdetableauxpourlagestioninternedesdonnesduneapplication.Danscecas,touteslesoprations effectues automatiquement par leDataAdapter devronttre ralisesmanuellement par le code.Ceciinclutnotamment la cration desDataTableavecleursDataColumn.LapremireoprationraliserestdecreruneinstancedelaclasseDataTable.Leconstructeurattend,commeparamtre,lenomdelaDataTable.Cenomestensuiteutilispour identifier laDataTable dans la collectionTables duDataSet.Aprs sa cration, laDataTablenecontientaucunestructure.Nousdevonsdonc crer une ou plusieursDataColumn et les ajouter lacollectionColumnsdelaDataTable.
LesDataColumnpeuventtrecres,enutilisantundesconstructeursdelaclasse,ouautomatiquementlorsdelajout la collectionColumns. La premire solution fournit plus de souplesse puisquelle permet la configuration denombreusesproprits de laDataColumn aumomentde sa cration.VousdevezauminimumindiquerunnometuntypededonnespourlaDataColumn.
col = New DataColumn(("Ht", Type.GetType("int")) table.Columns.Add(col) table.Columns.Add("Tva", Type.GetType("decimal"))
Une DataColumn peutgalement tre construite commetant une expression base sur une ou plusieurs autresDataColumn.Vousdevez,danscecas,indiquerlorsdelacrationdelaDataColumn,lexpressionservantaucalculdesavaleur. Le type de donnesgnr par lexpression doit bien sr tre compatible avec le type de donnesde laDataColumn. Vous devezgalement tre vigilant dans la conception de lexpression, en respectant la casse et enveillantnepascrerderfrencecirculaireentrelesDataColumn.
table.Columns.Add("Ttc", Type.GetType("System.Decimal"), "Ht * (1 + (Tva /100))")
PourassurerlunicitdesvaleursduneDataColumn,ilestpossibledutiliseruntypedeDataColumnautoincrment.LapropritAutoIncrement de cetteDataColumn doittrepositionne surtrue.Vouspouvezgalementmodifierlepasdincrmentationavec lapropritAutoIncrementStepet lavaleurdedpartaveclapropritAutoIncrementSeed.Lavaleur contenue dans cetteDataColumn est calculeautomatiquement lorsdelajoutdune ligneuneDataTableenfonctiondecespropritsetdeslignesexistantdjdanslaDataTable.
CetypedeDataColumnestgnralementutiliscommeclprimaireduneDataTable.Vousavezlapossibilitdedfinirla cl primaire dune DataTable en fournissant laproprit PrimaryKey un tableau contenant les diffrentesDataColumn devant composer la cl primaire. LesDataColumn concernes verront certaines de leurs propritsautomatiquementmodifies.LapropritUniqueserapositionnesurtrueetlapropritAllowDBNull surfalse.SilaclprimaireestconstituedeplusieursDataColumn,seulelapropritAllowDBNullseramodifiesurcesDataColumn.
col = New DataColumn("Numero", Type.GetType("System.Int32")) col.AutoIncrement = True col.AutoIncrementSeed = 1000 col.AutoIncrementStep = 1 table.Columns.Add(col) table.PrimaryKey = New DataColumn() {col}
3.ManipulerlesdonnesdansunDataSet
QuellequesoitlamthodeutilisepourremplirunDataSet,lebutdetouteapplicationestdemanipulerlesdonnesprsentes dans leDataSet. La classeDataTable contient de nombreuses proprits et mthodes facilitant lamanipulationdesdonnes.
a.Lecturedesdonnes
La lecturedesdonnesest lopration laplus frquente ralise sur unDataSet. Il faut toutdabordobtenirunerfrence sur laDataTablecontenantlesdonnes,puisnouspouvonsparcourirlacollectionRowsdelaDataTable.Cette collection est une instance de la classeDataRowCollection. Elle dispose de la proprit Item, par dfaut,permettantlaccsuneligneparticulireparunindex.Lapropritcountpermetdeconnatrelenombredelignesdisponibles.LeshabitusdeADOserontunpeuperdusaudbut,cardansuneDataTable,ilnyapasdenotiondepointeurdenregistrement,denregistrementcourant,demthodesdedplacementdanslejeudersultats.Sivous
- 4 - ENI Editions - All rigths reserved
voulezgrertoutescesnotions,vousdevezleprvoirexplicitementdansvotrecode.LamthodeGetEnumeratormetnotredispositionuneinstancedeclasseimplmentantlinterfaceIEnumerator.Parcetteinstancedeclasse,nousavons accs aux mthodes MoveNext etReset ainsi qua la proprit Current. Ces troislmentspermettentdeparcourirfacilementtoutesleslignesdelaDataTable.ChaquelignecorresponduneinstancedelaclasseDataRow.Cette classepossdegalementunepropritItem, pardfaut, fournissantunaccsauxdiffrentschampsdelaDataRow.Chaquechamppeuttreobtenuparsonnomouparsonindex.
Lecodesuivantillustrecesnotionsenaffichantlalistedesclients:
Imports System.Data.SqlClient Module TestLectureDataTable
Dim cmd As SqlCommand Dim ctn As SqlConnection Dim ds As DataSet Dim da As SqlDataAdapter Dim en As IEnumerator
Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true" cmd = New SqlCommand cmd.Connection = ctn cmd.CommandText = " SELECT ContactTitle,ContactName from Customers" ds = New DataSet da = New SqlDataAdapter() da.SelectCommand = cmd da.Fill(ds, "Customers") on recupere lenumerateur sur les lignes de la DataTable en = ds.Tables("Customers").Rows.GetEnumerator on se replace au debut de la table (par securite) en.Reset() on boucle tant que la mthode MoveNext nous indique quil reste des lignes Do While en.MoveNext on accede aux champs par le nom Console.Write(en.Current("ContactName") & vbTab) ou par le numero Console.WriteLine(en.Current(0)) Loop Console.ReadLine() End Sub End Module
b.CrationdecontraintessuruneDataTable
Vous pouvez utiliser des contraintes pourmettre enuvredes restrictions sur lesdonnesprsentesdansuneDataTable. Les contraintes constituentdes rglesquisontappliquesuneDataColumnousesDataColumn lies.Ellesdterminentlesactionseffectueslorsquelavaleurcontenuedansuneligneestmodifie.EllessontprisesencomptepourunDataSet,uniquement,sisapropritEnforceConstraintsestpositionnesurtrue.
Deuxtypesdecontraintessontutilisables:
CetypedecontraintevagarantirquelaoulesvaleursprsentesdansuneDataColumnouungroupedeDataColumnsont uniques. La mise en place dune contrainte unique seffectue en crant une instance de la classeUniqueConstraint avec la liste desDataColumn concernes par la contrainte. CetteUniqueConstraintdoit,ensuite,treajoutelacollectionConstraintsdelaDataTable.
table.Constraints.Add(New UniqueConstraint(New DataColumn() {col}))
SilacontrainteneportequesuruneDataColumn,ilestaussipossibledemodifiersimplementlapropritUniquedecetteDataColumn surtrue, pour crer une contrainte unique.notergalementque lacrationduneclprimairegnreautomatiquementunecontrainteunique,parlinversenestpasvrai.Laviolationdelacontrainte,lasuitedelamodificationduneligne,dclencheuneexception.
UniqueConstraint
- 5 - ENI Editions - All rigths reserved
LesForeignKeyConstraintcontrlentcommentvontsecomporterlesDataTable lieslorsdelamodificationoudelasuppression dune valeur dans laDataTable principale. Une action diffrente peuttre envisage pour unesuppression et unemodification. La classeForeignKeyConstraintdisposedespropritsDeleteRuleetUpdateRuleindiquantlecomportement,lorsdelasuppressionoudelamodification.Lesvaleurssuivantessontpossibles:
Cascade
Lasuppressionoumodificationestpropagelaouauxligneslies.
SetNull
LavaleurestmodifieDBNulldanslesligneslies.
SetDefault
Lavaleurpardfautestprisedanslesligneslies.
None
Aucuneactionnesteffectuesurlesligneslies.
Lajout dune ForeignkeyConstraint se fait par la cration dune instance de la classe en lui indiquant la ou lesDataColumndeDataTableparentetlaoulesDataColumndelatableenfant.SiplusieursDataColumnfontpartiedelacontrainte,ellessontfourniessousformedetableau.
Le code suivant ajoute une contrainte entre laDataTable Factures et laDataTable LignesFacture,pourque lasuppressiondunefactureentranelasuppresiondetoutesseslignes.
fkFact_LignesFact = New ForeignKeyConstraint("FK_FACT_LIGNESFACT", ds.Tables("Factures").Columns("Numero"), ds.Tables("LignesFacture").Columns("NumFact")) fkFact_LignesFact.AcceptRejectRule = AcceptRejectRule.Cascade fkFact_LignesFact.DeleteRule = Rule.Cascade ds.EnforceConstraints = True
c.AjoutderelationsentrelesDataTables
DansunDataSetcontenantplusieursDataTable,vouspouvezajouterdesrelationsentrelesDataTable.CesrelationspermettentlanavigationentreleslignesdesdiffrentesDataTable.UneinstancedelaclasseDataRelationdoittrecreetajoutelacollectionRelationsduDataSet.LacrationpeutsefairedirectementparlamthodeAdddelacollectionDataRelations.Lesinformationsfournirsont:
l LenomdelarelationpermettantderetrouverparlasuitelaDataRelationdanslacollection.
l LaoulesDataColumnparentessousformeduntableaudeDataColumnsilyenaplusieurs.
l LaoulesDataColumnenfantssousformeduntableau,silyenaplusieurs.
LecodesuivantajouteunerelationentrelatableCustomersetlatableOrders:
ds.Relations.Add("Client_Commandes", ds.Tables("Customers"). Columns("CustomerId"), ds.Tables("Orders").Columns("CustomerId"))
noterquelesDataRelationfonctionnentparalllementaveclesForeignKeyConstaintetlesUniqueConstraint.Pardfaut,lacrationdelarelationvaplaceruneUniqueConstraintsurlatableparentetuneForeignKeyConstraintsurla tableenfant.Sivousnesouhaitezpasquecescontraintessoientajoutesautomatiquementsiellesnexistentpas,vousdevezajouterunbooleanfalsecommequatrimeparamtre,lorsdelajoutdelaDataRelation.
d.Parcourirlesrelations
ForeignKeyConstraint
- 6 - ENI Editions - All rigths reserved
LebutprincipaldesrelationsestdepermettrelanavigationduneDataTableversuneautrelintrieurdunDataSet.Nous pouvons ainsi obtenir tous les objetsDataRow dune table lis une DataRow dune autreDataTable. Parexemple, aprs avoir charg les tablesCustomers etOrders dans leDataSet ettabli une relation entre cesdeux tables, nous pouvons,partirdunelignedelaDataTable ClientsobtenirdepuislaDataTable Orderstoutesles commandes de ce client. La mthodeGetChildRows retourne, sous formedun tableau deDataRow, toutes leslignescontenantlescommandesdececlient.
Cettemthodeprend,commeparamtre,lenomdelaDataRelationutilisepourlelien.Lexempleducodesuivantmetcelaenapplication,enaffichantpourchaqueclientlenumroetladatedesescommandes:
Imports System.Data.SqlClient Module TestRelations Dim cmdCustomers, cmdOrders As SqlCommand Dim ctn As SqlConnection Dim ds As DataSet Dim daCustomers, daOrders As SqlDataAdapter Dim ligneClient, ligneCommandes As DataRow
Public Sub main() ds = New DataSet ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true" cmdCustomers = New SqlCommand cmdCustomers.Connection = ctn cmdCustomers.CommandText = " SELECT * from Customers daCustomers = New SqlDataAdapter() daCustomers.SelectCommand = cmdCustomers daCustomers.Fill(ds, "Customers") cmdOrders = New SqlCommand cmdOrders.Connection = ctn cmdOrders.CommandText = " SELECT * from Orders" daOrders = New SqlDataAdapter() daOrders.SelectCommand = cmdOrders daOrders.Fill(ds, "Orders") ds.Relations.Add("Client_Commandes", ds.Tables("Customers").Columns ("CustomerId"), ds.Tables("Orders").Columns("CustomerId")) For Each ligneClient In ds.Tables("Customers").Rows Console.WriteLine(ligneClient("ContactName")) For Each ligneCommandes In ligneClient.GetChildRows("Client_Commandes") Console.WriteLine(vbTab & "commande N {0} du {1}", ligneCommandes("OrderId"), ligneCommandes("OrderDate")) Next Next End Sub End Module
Lanavigationduneligneenfantversuneligneparentestaussipossible,enutilisantlamthodeGetParentRowqui,elleaussi,attendcommeparamtrelenomdelarelationutilisecommelien.
Lapartieducodesuivantaffichepourchaquecommandelenomduclientlayantpasse:
For Each ligneCommandes In ds.Tables("Orders").Rows Console.WriteLine("la commande {0} a ete passee par {1}", ligneCommandes("OrderId"), ligneCommandes.GetParentRow("Client_Commandes") ("ContactName")) Next
e.tatetversionsduneDataRow
La classeDataRow est capable de suivre les diffrentesmodificationsapportesauxdonnesquelle contient.LapropritRowStatepermetdecontrlerlesmodificationsapporteslaligne.
Cinqvaleursdfiniesdansunenumrationsontpossiblespourcetteproprit:
Unchanged
LalignenapaschangdepuisleremplissageduDataSetparlamthodeFilloulavalidationdesmodificationsparlamthodeAcceptChanges.
- 7 - ENI Editions - All rigths reserved
Added
LaligneatajoutemaislesmodificationsnontpasencoretvalidesparlamthodeAcceptChanges.
Modified
Unouplusieurschampsdelaligneonttmodifis.
Deleted
LaligneateffacemaislesmodificationsnontpasencoretvalidesparlamthodeAcceptChanges.
Detached
LaligneatcremaisnefaitpasencorepartiedelacollectionRowsduneDataTable.
Lesdiffrentesversionsdunelignesontgalementdisponibles.Lorsquevousaccdezauxvaleurscontenuesdansuneligne,vouspouvezspcifierlaversionquivousintresse.
Pourcela,lnumrationDataRowVersionproposequatrevaleurs:
Current
Versionactuelledelaligne.CetteversionnexistepaspourunelignedontltatestDeleted.
Default
Versionpardfautdelaligne.PourunelignedontltatestAdded,Modified,Unchanged,cetteversionestquivalentelaversionCurrent.PourunelignedontltatestDeleted,cetteversionestquivalentelaversionOriginal.PourunelignedontltatestDetached,cetteversionestgalelaversionProposed.
Original
Versionoriginaledelaligne.PourunelignedontltatestAdded,cetteversionnexistepas.
Proposed
Version transitoiredisponiblependantuneoprationdemodificationde la ligneoupourune lignene faisantpaspartiedunecollectionRowsduneDataTable.
Lindicationdelaversiondsiredoittrespcifie,lorsdelaccsunchampparticulierduneDataRow.Pourcela,ilfaututiliser lunedes constantesprcdentes, la suitedunomoude lindexduchamp,lorsdelutilisationdelapropritItem,pardfaut,delaDataRow.
Cesdiffrentesversionsserontutilises,lorsdelamisejourdelabasededonnes,pourparexemplegrerlesaccsconcurrents.
f.Ajoutdedonnes
Lajout dune ligne une DataTable seffectue simplement en ajoutant uneDataRow la collectionRows duneDataTable.Ilfaut,aupralable,creruneinstancedelaclasseDataRow.Cestceniveauquenousrencontronsunproblme.
- 8 - ENI Editions - All rigths reserved
IlnyapasdeconstructeurdisponiblepourlaclasseDataRow.RassurezvouscenestpasuneerreurdeVisualBasic,mais cest bien volontairement quil nexiste pas de constructeur pour cette classe. En effet, lorsque nous avonsbesoin dune nouvelle instance dune DataRow, nous ne voulons pas uneDataRow quelconque mais uneDataRowspcifique au schma de notreDataTable. Cest pour cette raison que cestellequest confi le soinde crerlinstancedontnousavonsbesoinparlintermdiairedelamthodeNewRow.
Dim nouvelleLigne As DataRow nouvelleLigne = ds.Tables("Customers").NewRow()
Ltatde cette ligneest,pour linstant,Detached.Nouspouvonsensuiteajouterdesdonnesdanscettenouvelleligne.
nouvelleLigne("ContactName") = "Dupond"
Aprscela,ilnousresteajouterlalignelacollectionRowsdelaDataTable.
ds.Tables("Customers").Rows.Add(nouvelleLigne)
LtatdecettenouvelleligneestmaintenantAdded.
g.Modificationdedonnes
La modification des donnes contenues dans une ligne est ralise, simplement, en affectant aux champscorrespondants, lesvaleurs souhaites.CesvaleurssontstockesdanslaversionCurrentdelaligne.LtatdelaligneestalorsModified.Cettesolutionprsenteunpetitinconvnient.Siplusieurschampsdunelignedoiventtremodifis,ilpeutyavoirpendantlamodification,destatstransitoiresquiviolentuneouplusieurscontraintesplacessur laDataTable.Cest,parexemple,lecassilyasurlaDataTable,unecontraintedeclprimaireplacesurdeuxDataColumn. Ceci a pour effet de dclencher une exception. Pour pallier ce problme, nous pouvons demandertemporairement larrtde lavrificationdes contraintespour cette ligne. LamthodeBeginEditpasselaligneenmodeditionetsuspenddonc lavrificationdescontraintespourcetteligne.LesvaleursaffectesauxchampsnesontpasstockesdanslaversionCurrentdelalignemaisdanslaversionProposed.Lorsquetouteslesmodificationssonteffectuessurlaligne,vouspouvezlesvalideroulesannulerenappelantlamthodeEndEditoulamthodeCancelEdit.Vouspouvezgalementvrifierlesvaleurs,engrantlvnementColumnChangeddelaDataTable.Danslegestionnairedvnements, vous recevez un argument de typeDataColumnChangeEventArgpermettantdesavoirquelle DataColumn at modifie (args.Column.ColumnName), la valeur propose pour cetteDataColumn(args.ProposedValue)etpermettantdannuler lesmodifications(args.row.CancelEdit).EncasdevalidationaveclamthodeEndEdit, la versionProposed de la ligneest recopiedans laversionCurrentetltatdelalignedevientModified.Si, par contre, vousannulez lesmodificationsavec lamthodeCancelEdit, la versionCurrent nestpasmodifieetltatdelaligneestinchang.Danstouslescas,aprslappeldunedecesdeuxmthodes,lavrificationdescontraintesestractive.
Lexemplesuivantpermetlamodificationducodepostaldunclientenvrifiantqueceluiciestbiennumrique:
Imports System.Data.SqlClient Module TestModificationLigne Dim cmd As SqlCommand Dim ctn As SqlConnection Dim codeClient As String Dim codePostal As String Dim paramCodeClient As SqlParameter Dim ds As DataSet Dim da As SqlDataAdapter Dim WithEvents table As DataTable Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog= Northwind;Integrated Security=true" ctn.Open() cmd = New SqlCommand cmd.Connection = ctn Console.Write("saisir le code du client a modifier:") codeClient = Console.ReadLine() cmd.CommandText = " SELECT * from Customers WHERE CustomerID = @Code" paramCodeClient = New SqlParameter("@Code", codeClient) paramCodeClient.Direction = ParameterDirection.Input cmd.Parameters.Add(paramCodeClient) ds = New DataSet da = New SqlDataAdapter(cmd)
- 9 - ENI Editions - All rigths reserved
da.Fill(ds, "Clients") table = ds.Tables("Clients") table.Rows(0).BeginEdit() Console.Write("saisir le nouveau code postal du client:") codePostal = Console.ReadLine() table.Rows(0)("PostalCode") = codePostal table.Rows(0).EndEdit() Console.WriteLine("le nouveau code postal est : {0}", table.Rows(0)("PostalCode")) Console.ReadLine() End Sub
Private Sub table_ColumnChanged(ByVal sender As Object, ByVal e As System.Data.DataColumnChangeEventArgs) Handles table.ColumnChanged If e.Column.ColumnName = "PostalCode" Then If Not IsNumeric(e.ProposedValue) Then e.Row.CancelEdit() End If End If End Sub End Module
h.Suppressiondedonnes
Deuxsolutionsdiffrentessontdisponibles.Vouspouvezeffacerune ligneousupprimerune ligne.Lanuanceestsubtileentrecesdeuxsolutions:
LasuppressiondunedonnesefaitaveclamthodeRemovequiretiredfinitivementlaDataRowdelacollectionRowsdelaDataTable.Cettesuppressionestdfinitive.
LamthodeDeletednefaitquemarquerlalignepourlasupprimerultrieurement.LtatdelalignepasseDeletedet cenestquaumomentde lavalidationdesmodificationsque la ligneest rellement supprimedelacollectionRowsdelaDataTable.Silesmodificationssontannules,lalignerestedanslacollectionRows.
LamthodeRemoveestunemthodedelacollectionRows(elleagitdirectementsursoncontenu),lamthodeDeleteestunemthodedelaclasseDataRow(ellenefaitquechangerunepropritdelaligne).
efface la ligne table.Rows(1).Delete() supprime la ligne table.Rows.Remove(table.Rows(1))
i.Validerouannulerlesmodifications
Jusquprsent, lesmodificationseffectuessurune lignesont temporaires, ilestencorepossiblede revenirlaversionprcdente,ouaucontrairedevaliderdefaondfinitivelesmodificationsdansleslignes(maisencoredanslabase).LesmthodesAcceptChangesouRejectChangespermettentrespectivementlavalidationoulannulationdesmodifications.EllespeuventsappliquersuruneDataRow individuelle,uneDataTableouunDataSetentier.LorsquelamthodeAcceptChangesestexcute,lesactionssuivantessontralises:
l LamthodeEndEditestappeleimplicitementpourlaligne.
l Si ltat de la lignetaitAdded ouModified, ildevientUnchangedetlaversionCurrentestrecopiedanslaversionOriginale.
l SiltatdelalignetaitDeleted,alorslaligneestsupprime.
LamthodeRejectChangesexcutelesactionssuivantes:
l LamthodeCancelEditestappeleimplicitementpourlaligne.
l SiltatdelalignetaitDeletedouModified, ilrevientUnchangedetlaversionOriginalestrecopiedanslaversionCurrent.
- 10 - ENI Editions - All rigths reserved
l SiltatdelalignetaitAdded,alorselleestsupprime.
Silexistedescontraintesdecltrangre,lactiondelamthodeAcceptChangesouRejectChangesestpropageauxlignesenfantsenfonctiondelapropritAcceptRejectRuledelacontrainte.
j.Filtrerettrierdesdonnes
Il est frquentdavoirbesoinde limiter laquantitdedonnesvisiblesdansuneDataTableouencoredemodifierlordredeslignes.LapremiresolutionquivientlespritestderecrerunerequteSQLavecunerestrictionouuneclauseORDERBY.Cestoublierquenoussommesdansunmodedefonctionnementdconnectetquilestsouhaitablede limiter les accs la base, voire pire, que la base nest pas disponible. Nous devons donc nutiliserque lesdonnes disponibles, en faisant attention ne pas en perdre. La classeDataView va noustre trs utile poursolutionner nos problmes. Cette classe va nous servirmodifier lavisiondesdonnes dans laDataTable sansrisque pour les donnes ellesmmes. Il peut y avoir plusieursDataView pour une mme DataTable, ellescorrespondentdespointsdevuediffrentsdelaDataTable.PratiquementtouteslesoprationsralisablessuruneDataTablelesontaussiparlintermdiaireduneDataView.
DeuxsolutionssontdisponiblespourobteniruneDataView:
l Creruneinstanceparlundesconstructeurs.
l Utiliserlinstance,pardfaut,fournieparlapropritDefaultView.
Lepremierconstructeurutilisableattendsimplementcommeparamtre laDataTablepartirdelaquelleestgnrela DataView. Dans ce cas, il ny a aucun filtre ni aucun tri deffectu sur lesdonnes visibles par laDataView.UnrsultatquivalentestobtenuenutilisantlapropritDefaultViewduneDataTable.
Ledeuximeconstructeurpermetdespcifierunfiltre,unordredetrietlaversiondeslignesconcernes.Pourtrevisibles dans laDataView, les lignes devront correspondretouscescritres.Lesdiffrentscritrespeuventaussitremodifispartroisproprits.
Cettepropritaccepteunechanedecaractresreprsentantlaconditiondevanttrerempliepourquunelignesoitvisible.CetteconditionaunesyntaxetoutfaitsimilaireauxconditionsduneclauseWHERE.LesoprateursAndetOrpeuventgalementtreutilisspourassocierplusieursconditions.
LexemplesuivantaffichelenomdesclientscommerciauxoudirecteursdeventeenFrance:
Imports System.Data.SqlClient Module TestDataView Dim cmd As SqlCommand Dim ctn As SqlConnection Dim ds As DataSet Dim da As SqlDataAdapter Dim table As DataTable Dim ligne As DataRowView Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true" ctn.Open() cmd = New SqlCommand cmd.Connection = ctn cmd.CommandText = " SELECT * from Customers" ds = New DataSet da = New SqlDataAdapter(cmd) da.Fill(ds, "Clients") table = ds.Tables("Clients") table.DefaultView.RowFilter = "Country=France and (contactTitle= Sales Agent or contactTitle=Sales Manager) For Each ligne In table.DefaultView Console.WriteLine("nom : {0}", ligne("ContactName")) Next End Sub End Module
RowFilter
- 11 - ENI Editions - All rigths reserved
UnfiltrepeuttreannulenaffectantunechanevidelapropritRowFilter.
Cettepropritaccepte,elleaussi,une chanede caractres reprsentant leou lescritresutilisspourletri.LasyntaxeestquivalentecelledelaclauseORDER BY.
Lexemplesuivantaffichelesclientstrisparpayspuisparnom,pourunmmepays:
on annule le filtre precedent table.DefaultView.RowFilter = "" toutes les lignes sont maintenant visibles on ajoute un critere de tri table.DefaultView.Sort = "Country ASC,ContactName ASC" For Each ligne In table.DefaultView Console.WriteLine("Pays : {0}" & vbTab & vbTab & " nom : {1}, ligne("Country"), ligne("ContactName")) Next
CettepropritdtermineltatdeslignesetquelleversiondelaligneestvisibledanslaDataView.Huitpossibilitssontdisponibles:
CurrentRows
PrsentelaversionCurrentdetoutesleslignesajoutes,modifiesouinchanges.
Added
PrsentelaversionCurrentdetoutesleslignesajoutes.
Deleted
PrsentelaversionOriginaldetouteslesligneseffaces.
ModifiedCurrent
PrsentelaversionCurrentdetoutesleslignesmodifies.
ModifiedOriginal
PrsentelaversionOriginaldetoutesleslignesmodifies.
None
Aucuneligne.
OriginalRows
PrsentelaversionOriginaldetoutesleslignesmodifies,supprimesouinchanges.
Unchanged
PrsentelaversionCurrentdetoutesleslignesinchanges.
Lexemplesuivantsupprimedeuxlignesetlesafficheparlintermdiairedunfiltre:
on supprime deux lignes table.Rows(2).Delete() table.Rows(5).Delete() on annulele filtre table.DefaultView.RowFilter = "" on affiche la version original des lignes supprimees
Sort
RowStateFilter
- 12 - ENI Editions - All rigths reserved
table.DefaultView.RowStateFilter = DataViewRowState.Deleted For Each ligne In table.DefaultView Console.WriteLine("Pays : {0}" & vbTab & vbTab & " nom : {1}", ligne("Country"), ligne("ContactName")) Next
k.Rechercherdesdonnes
LarecherchepeutseffectueraveclesdeuxmthodesFindetFindRows.Pourquecesdeuxmthodesfonctionnent,ilestimpratifdavoiraupralabletrilesdonnesaveclapropritSort.
Cette mthode retourne lindexde lapremire ligne correspondantau critrede recherche.Si aucune lignenesttrouve,cettemthoderetourne1.Elleattendcommeparamtrelavaleurrecherche.Cettevaleurestrecherchedans le champutilis commecritrede tri.Si le critrede triestcomposdeplusieurschamps,ilfautpasserlamthodeFinduntableaudobjetscontenantlesvaleursrecherchespourchaquechampducritredetridanslordredapparitiondanslapropritSort.
Cettemthodeestsouventutilisepourrechercherunelignepartirdelaclprimaire.
Imports System.Data.SqlClient Module TestFind Dim cmd As SqlCommand Dim ctn As SqlConnection Dim ds As DataSet Dim da As SqlDataAdapter Dim table As DataTable Dim ligne As DataRowView Dim codeClient As String Dim index As Integer
Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true ctn.Open() cmd = New SqlCommand cmd.Connection = ctn cmd.CommandText = " SELECT * from Customers ds = New DataSet da = New SqlDataAdapter(cmd) da.Fill(ds, "Clients") table = ds.Tables("Clients") Console.Write("saisir le code client : ") codeClient = Console.ReadLine() table.DefaultView.Sort = "CustomerID ASC" index = table.DefaultView.Find(codeClient) If index = -1 Then Console.WriteLine("il ny a pas de client avec ce code") Else Console.WriteLine("le code {0} correspond au client {1}", codeClient, table.DefaultView(index)("ContactName")) End If Console.ReadLine() End Sub End Module
CettemthoderecherchetoutesleslignescorrespondantaucritrederechercheetretourneceslignessousformeduntableaudeDataRowView.
Lecodesuivantrecherchetouslesclientsdunpaysetdunevilledonns:
Imports System.Data.SqlClient Module TestFindRows Dim cmd As SqlCommand
Find
FindRows
- 13 - ENI Editions - All rigths reserved
Dim ctn As SqlConnection Dim ds As DataSet Dim da As SqlDataAdapter Dim table As DataTable Dim ligne As DataRowView Dim pays, ville As String Dim lignesTrouvees() As DataRowView Dim criteres() As Object
Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true" ctn.Open() cmd = New SqlCommand cmd.Connection = ctn cmd.CommandText = " SELECT * from Customers" ds = New DataSet da = New SqlDataAdapter(cmd) da.Fill(ds, "Clients") table = ds.Tables("Clients") Console.Write("saisir le Pays : ") pays = Console.ReadLine() Console.Write("saisir la ville: ") ville = Console.ReadLine() table.DefaultView.Sort = "Country ASC,City ASC" criteres = (New Object() {pays, ville}) lignesTrouvees = table.DefaultView.FindRows(criteres) If lignesTrouvees.Length = 0 Then Console.WriteLine("il ny a pas de client correspondant") Else Console.WriteLine("les clients suivants correspondent ") For Each ligne In lignesTrouvees Console.WriteLine("Nom :{0}", ligne("ContactName")) Next End If Console.ReadLine() End Sub End Module
4.Mettrejourlabasededonnes
Tout le travail effectu sur les donnes avec les mthodes vues prcdemment est irrmdiablement perdu lafermeturedelapplication,sinousneprenonspassoindesauvegarderlesdonnes.
Dans la majorit des cas, les donnes proviennent dunebasededonnes, il faut donc lamettre jouravec lesmodificationscontenuesdansunDataSet,uneDataTableouuneDataRow.LeDataAdapteratutilispourremplirleDataSet,cestgalementluiquelonvafaireappelpourmettrejourlabasededonnes.
CommelamthodeFill,lamthodeUpdatevautiliserdesinstructionsSQLpourledialogueaveclabasededonnes.En fonction des besoins, elle utilisera linstruction contenue dans la commandeInsertCommand, UpdateCommand ouDeleteCommand.SilamthodeUpdateabesoindunecommandeetquellenestpasdisponible,alorsuneexceptionestgnre.LamthodeFillparcourtleslignesdelaDataTablequelledoitmettrejouret,enfonctiondeltatdelaligne (Added, Deleted, Modified), appelle la commandeInsertCommand, DeleteCommand, UpdateCommand. Lordre danslequellesmisesjoursonteffectuesdanslabase,peutavoirdelimportance.Pourcontrlerlordredexcutiondesinsertions,modificationsetsuppressions,vouspouvezprocderentroistapes,enneproposantlamthodeUpdatequun jeu restreint de lignesmettre jour.Vouspouvez,parexemple,ne slectionnerquelesligneseffacesetdemanderlamisejourdelabaseaveccetensembledelignespuisprocderdemmeavecleslignesmodifiesetleslignesajoutes.
LamthodeSelectpermetdobteniruntableaudeDataRow correspondantuncritrespcifique.CestcetableaudeDataRowquiestpasscommeparamtrelamthodeUpdate.
Lexemplesuivantraliselessuppressions,lesmodificationspuislesajoutsdanslabasededonnes.
Dim lignes() As DataRow recupere les lignes supprimees et demande la mise a jour de la base lignes = table.Select(Nothing, Nothing, DataViewRowState.Deleted) da.Update(table)
- 14 - ENI Editions - All rigths reserved
recupere les lignes modifiees et demande la mise a jour de la base lignes = table.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent) da.Update(table) recupere les lignes ajoutees et demande la mise a jour de la base lignes = table.Select(Nothing, Nothing, DataViewRowState.Added) da.Update(table)
CetexemplesupposebiensrquelescommandesInsertCommand,DeleteCommand,UpdateCommandsoientdfiniesaupralable.
a.Gnrationautomatiquedecommandes
Les commandes charges de la mise jour de la base peuventtre gnres automatiquement par un objetSqlCommandBuilder.Pourfonctionnercorrectement,leSqlCommandBuilderaquelquesexigences:
l LapropritSelectCommanddoittredfiniepourleDataAdaptercarcestpartirdecetteinstructionSQLquilvagnrerlesinstructionsINSERT,UPDATE,DELETE.
l LaclprimairedoittredisponibledanslaDataTable.
l Lesdonnesnedoiventpasprovenirdunejointureentreplusieurstables.
Si une ou plusieurs de ces exigences ne sont pas respectes, il y a dclenchement dune exception lors de lagnrationdescommandes.
Lescommandessontgnresenrespectantlescritressuivants:
Insre une ligne dans la base pour toutes les lignes dont ltat estAdded. Tous les champs,hormis les champsidentit,expressionouTimeStampsontmisjour.
MetjourdanslabasetoutesleslignesdontltatestModified.Tousleschampssontmisjoursaufleschampsidentit, expressionouTimeStamp.Lalignemettrejourestrecherchedanslabase,parlaclprimaire,maisilfautgalementquelesvaleursdesautreschampsdanslabasecorrespondentlaversionOriginalduchampdanslaDataRow.
EffacedelabasetoutesleslignesdontltatestDeleted.IlfautgalementquelesvaleursprsentesdanslabasecorrespondentlaversionOriginaldeschampsdanslaDataRow.LescommandesgnressontdisponiblesvialesmthodesGetInsertCommand,GetUpdateCommand,GetDeleteCommand.
Lexemple suivant affiche les instructions SQL des trois commandes gnres automatiquement pour la tableCustomers:
Imports System.Data.SqlClient Module TestOrdreMAJBase Dim cmd As SqlCommand Dim ctn As SqlConnection Dim ds As DataSet Dim da As SqlDataAdapter Dim table As DataTable Dim ligne As DataRowView Dim codeClient As String Dim index As Integer Dim bldr As SqlCommandBuilder Public Sub main() ctn = New SqlConnection() ctn.ConnectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true" ctn.Open() cmd = New SqlCommand
InsertCommand
UpdateCommand
DeleteCommand
- 15 - ENI Editions - All rigths reserved
cmd.Connection = ctn cmd.CommandText = " SELECT * from Customers" ds = New DataSet da = New SqlDataAdapter(cmd) da.Fill(ds, "Clients") table = ds.Tables("Clients") bldr = New SqlCommandBuilder(da) Console.WriteLine("Instruction SQL de UpadteCommand : {0}", bldr.GetUpdateCommand.CommandText) Console.WriteLine("Instruction SQL de InsertCommand : {0}", bldr.GetInsertCommand.CommandText) Console.WriteLine("Instruction SQL de DeleteCommand : {0}", bldr.GetDeleteCommand.CommandText) End Sub End Module
Cecodeaffichelesinformationssuivantes:
Instruction SQL de UpadteCommand : UPDATE [Customers] SET [CustomerID] = @p1, [CompanyName] = @p2, [ContactName] = @p3, [ContactTitle] = @p4, [Address] = @p5, [City] = @p6, [Region] = @p7, [PostalCode] = @p8, [Country] = @p9, [Phone] = @p10, [Fax] = @p11 WHERE (([CustomerID] = @p12) AND ([CompanyName] = @p13) AND ((@p14 = 1 AND [ContactName] IS NULL) OR ([ContactName] = @p15)) AND ((@p16 = 1 AND [ContactTitle] IS NULL) OR ([ContactTitle] = @p17)) AND ((@p18 = 1 AND [Address] IS NULL) OR ([Address] = @p19)) AND ((@p20 = 1 AND [City] IS NULL) OR ([City] = @p21)) AND ((@p22 = 1 AND [Region] IS NULL) OR ([Region] = @p23)) AND ((@p24 = 1 AND [PostalCode] IS NULL) OR ([PostalCode] = @p25)) AND ((@p26 = 1 AND [Country] IS NULL) OR ([Country] = @p27)) AND ((@p28 = 1 AND [Phone] IS NULL) OR ([Phone] = @p29)) AND ((@p30 = 1 AND [Fax] IS NULL) OR ([Fax] = @p31))) Instruction SQL de InsertCommand : INSERT INTO [Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11) Instruction SQL de DeleteCommand : DELETE FROM [Customers] WHERE (([CustomerID] = @p1) AND ([CompanyName] = @p2) AND ((@p3 = 1 AND [ContactName] IS NULL) OR ([ContactName] = @p4)) AND ((@p5 = 1 AND [ContactTitle] IS NULL) OR ([ContactTitle] = @p6)) AND ((@p7 = 1 AND [Address] IS NULL) OR ([Address] = @p8)) AND ((@p9 = 1 AND [City] IS NULL) OR ([City] = @p10)) AND ((@p11 = 1 AND [Region] IS NULL) OR ([Region] = @p12)) AND ((@p13 = 1 AND [PostalCode] IS NULL)OR ([PostalCode] = @p14)) AND ((@p15 = 1 AND [Country] IS NULL) OR ([Country] = @p16)) AND ((@p17 = 1 AND [Phone] IS NULL) OR ([Phone] = @p18)) AND ((@p19 = 1 AND [Fax] IS NULL) OR ([Fax] = @p20)))
Lemoinsquelonpuissedire,cestquececodenestpastrsparlant!
Rassurezvousdans leparagraphesur lesaccsconcurrents,nousallonsclaircirlaprsencedecesinnombrablesparamtres dans ces trois instructions SQL. Limportant, pour le moment, est que ces instructions ralisentcorrectementlamisejourdelabasededonnes.
Nousallons levrifier en ralisantunajout,unemodificationetunesuppressiondanslatableCustomerssurdesclientsFranais.R