Récupérer les infos de base -SQL ServerCe – ce qui marche

 On peut facilement récupérer la liste des tables,des colonnes,pour les clés étrangères c’est un peu plus compliqué .  

 J’utilise les mêmes vues qu’avec Sql Server mais toutes ne sont pas disponibles .  

 http://msdn.microsoft.com/fr-fr/library/ms186778.aspx  

Clé étrangères  

Et voilà on récupère toutes les informations de clé étrangères en une seule requête  

Autrement il est possible de passer par DbConnection .GetSchema()

(Très efficace pour récupérer les informations des bases Microsoft Acces , cela liste même les vues avec le corps .Beaucoup moins avec SqlServerCe qui reste assez compliqué à utiliser )

4 – Procédures stockées /Vues (Sql Server)

A-  SELECT

CREATE PROCEDURE SELECTContacts

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[ContactAge],[CountryId]

FROM [Contact]

GO

CREATE PROCEDURE SELECTContact(@ContactId int)

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[ContactAge],[CountryId]

FROM [Contact]

 WHERE [ContactId]=@ContactId

GO

CREATE PROCEDURESELECTContactsOfCountry(@CountryId int)

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[ContactAge],[CountryId]

FROM [Contact] WHERE [CountryId]=@CountryId

GO

B – INSERT / UPDATE / DELETE

CREATE PROCEDURE INSERTContact(@ContactName char(100),@ContactFirstName char(100),@ContactAge int,@CountryId int)

AS

INSERT INTO [Contact]([ContactName] ,[ContactFirstName] ,[ContactAge] ,[CountryId] )

VALUES(@ContactName,@ContactFirstName,@ContactAge,@CountryId)

SELECT SCOPE_IDENTITY();

GO

CREATE PROCEDURE UPDATEContact(@ContactId int,@ContactName char(100),@ContactFirstName char(100),@ContactAge int,@CountryId int)

AS

UPDATE [Contact] SET[ContactName]=@ContactName,[ContactFirstName]=@ContactFirstName,[ContactAge]=@ContactAge,[CountryId]=@CountryId

WHERE [ContactId]=@ContactId

GO

CREATE PROCEDURE DELETEContact(@ContactId int)

AS

DELETE FROM [Contact]

WHERE [ContactId]=@ContactId

GO

C - mêmes exemples pour MS ACCESS

CREATE PROCEDURE SELECTContacts

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[CountryId]

FROM [Contact]

GO

CREATE PROCEDURESELECTContactsOfCountry(parCountryId int)

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[CountryId]

FROM [Contact] WHERE [CountryId]=parCountryId

GO

CREATE PROCEDURE INSERTContact(parContactName VarChar(100),parContactFirstName VarChar(100),parCountryId int)

AS

INSERT INTO [Contact]([ContactName] ,[ContactFirstName] ,[CountryId] )

VALUES(parContactName,parContactFirstName,parCountryId)

GO

CREATE PROCEDURE SELECTContact(parContactId int)

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[CountryId]

FROM [Contact]

 WHERE [ContactId]=parContactId

GO

CREATE PROCEDURE UPDATEContact(parContactId int,parContactName VarChar(100),parContactFirstName VarChar(100),parCountryId int)

AS

UPDATE [Contact] SET[ContactName]=parContactName,[ContactFirstName]=parContactFirstName,[CountryId]=parCountryId

WHERE [ContactId]=parContactId

GO

CREATE PROCEDURE DELETEContact(parContactId int)

AS

DELETE FROM [Contact]

WHERE [ContactId]=parContactId

GO

D – EXECUTE

EXECUTE INSERTCountry 'France'

EXECUTE INSERTContact 'Dupond','paul',20,1

EXECUTEINSERTContact'Durand','luc',40,1

EXECUTE SELECTCountrys

Astuces :

-    en transact-sql les vues sont précédées de v_ et les procédures stockées de sp_

E- Variable

CREATE PROCEDURE sp_contactcount

AS

DECLARE @result Int

SELECT @result=count(*)

FROM Contact

RETURN @result

GO

5 – Astuce /ordre des requêtes

Pour éviter les conflits avec les contraintes lorsque l’on doit ajouter des données en même temps que l’on crée des tables

a-  création des tables

b-  ajout des données

c-  ajout des contraintes(clé primaires,secondaires)

exemples : ajout de données sous sql server, le problème ici étant la clé auto incrémentée

SET IDENTITY_INSERT [Country] ON INSERT INTO [Country]([CountryId],[CountryName])

VALUES(1,'France');

Sous access une clé auto incrément n’empèche pas de définir la valeur voulue

INSERT INTO [Country]([CountryId],[CountryName])

VALUES(1,'France') ;                                                                           

6 – TRANSACTIONS –

A-  BEGIN / COMMIT /ROLLBACK

Exemple : on supprime tous les contacts mais rollback annule cette suprression

BEGIN TRANSACTION

DELETE FROM [Contact];

ROLLBACK;

COMMIT TRANSACTION

Attention à la ponctuation , ne pas mettre de virgules

Astuce : pour garder l’intégrité de la base

B - nommer la transaction/@@ERROR <>0

BEGIN TRANSACTION T

DELETE FROM [Contact]

if @@error <>0 ROLLBACK

COMMIT TRANSACTION T

Autre exemple

BEGIN TRANSACTION T

INSERT INTO [Country]([CountryName])VALUES('Suisse')

IF @@ERROR <>0 ROLLBACK

INSERT INTO [Country]([CountryName])VALUES('Bulgarie')

IF @@ERROR <>0 ROLLBACK

COMMIT TRANSACTION T

B-  Point de sauvegarde

SAVE TRANSACTION MySave

ROLLBACK TRANSACTION MySave

Exemple

BEGIN TRANSACTION T

INSERT INTO [Country]([CountryName])VALUES('Belgique')

SAVE TRANSACTION MySave

INSERT INTO [Country]([CountryName])VALUES('Espagne')

IF @@ERROR <>0 ROLLBACK TRANSACTION MySave

COMMIT TRANSACTION T

Ordre des clauses

1-      EXTRAIRE

A – une colonne

SELECT [Nom]

FROM [Amis];

b- Plusieurs colonnes (les colonnes apparaissent dans le même ordre que dans la requête)

SELECT [ID],[Prenom],[Nom]

FROM [Amis];

c – toutes les colonnes

SELECT *

FROM [Amis];

Astuces :

-    il est préférable d’énumérer toutes les colonnes plutôt que d’utiliser *

-    Pour un accès plus précis aux tables [NomBase].[NomTable] et pour les colonnes [NomTable].[NomColonne]

-    Le SQL  est insensible à la casse(SELECT ou select c’est idem) par contre il n’en est pas de même pour les noms des tables,colonnes,…

2-  TRIER

A-  Sur une colonne

SELECT *

FROM [Amis]

ORDER BY [Nom]

B-  Sur plusieurs colonnes

SELECT *

FROM [Amis]

ORDER BY [Nom],[Prenom]

       c- Spécifier ordre de tri5(DESC de z à a,ASC ordre de tri par défaut donc inutile à spécifier)

SELECT *

FROM [Amis]

ORDER BY [Nom] DESC

3-  FILTRER

A-  Filtrage sur une condition

Opérateurs  de la clause where :

<> différent de

!= différent de

< 

<=

> 

>=

!> pas supérieur à

!< pas inférieur à

BETWEEN de l’une à l’autre valeurs spécifiées

IS NULL est de valeur nulle

SELECT [ProductId],[CategoryId],[Title],[NumberOfPages]

FROM [Products]

WHERE[NumberOfPages]> 100

SELECT [ProductId],[CategoryId],[Title],[NumberOfPages]

FROM [Products]

WHERE [NumberOfPages] BETWEEN 20 AND 200

SELECT [ProductId],[CategoryId],[Title],[NumberOfPages]

FROM [Products]

WHERE [Title]='La bonne cuisine'

SELECT *

FROM [Products]

WHERE [Title] IS NULL

B-  Filtrer sur plusieurs conditions Opérateur AND et OR

SELECT *

FROM [Products]

WHERE[NumberOfPages]<100 AND [Title]='La bonne cuisine'

SELECT *

FROM [Products]

WHERE[NumberOfPages]<100 OR [NumberOfPages]>300

Astuce : utiliser des parenthèses si plusieurs conditions en cas de probème d’évaluation

c- Opérateur IN

(exemple sélection de tous les produits de catégorie 2 et 5)

SELECT *

FROM [Products]

WHERE [CategoryId] IN (2,5);

Attention si les conditions sont des textes, les placer entre ‘’

exemple WHERE [Category] IN (‘Littérature’,’jeunesse’);

C-  Opérateur NOT (utile pour les clauses complexes)

(exemple sélectionne tous les produits sauf ceux dont la category est 5)

SELECT *

FROM [Products]

WHERE NOT[CategoryId]=5

D-  LIKE et Caractères de substitution

_ remplace un caractère

% Remplace plusieurs carcatères

[] les crochets servent à psécifier pour un emplcement donné un ensemble de caractères possibles

^ caractère d’exclusion

Exemple : sélectionne tous les produits dont le titre commence par un L

SELECT *

FROM [Products]

WHERE [Title] LIKE 'L%'

Exemple sélectionne tous les produits commençant par C ou L

SELECT *

FROM [Products]

WHERE [Title] LIKE '[CL]%'

4-  CHAMPS CALCULES

A-  Concaténation

Exemple on place les titres entre parenthèses

SELECT '(' + [Title] + ')'

FROM [Products];

Astuce : il existe des fonctions comme RTRIM()

B-  Alias

Exemple on donne un nom à la colonne créée par concaténation et on reutilise cet alias(ici pour trier)

SELECT '(' + [Title] + ')' AS My_Title

FROM [Products]

ORDER BY My_Title;

C – Calculs arithmétiques

Exemple pour chaque article on calcule le prix par la quantité

SELECT [SalesDetailId],[Amount]*[Quantity] As Result

FROM [SalesDetails]

5-  LES FONCTIONS

Fonctions de texte ,de date et heure,numériques

http://msdn.microsoft.com/fr-fr/library/ms181984.aspx

Fonctions d’agregation

AVG(),COUNT(),MAX(),MIN(),SUM()

SELECT COUNT(*) As Result

FROM [Categories];

Astuce ALL par défaut et DISTINCT

6-  GROUPER

affiche le nombre de produits par categorie

SELECT COUNT(*) As Result

FROM [Products]

GROUP BY CategoryId

  + HAVING : filtrer

SELECT COUNT(*) AS Result

FROM [Products]

GROUP BY CategoryId

HAVING COUNT(*)>1

7-  SOUS REQUETES

// TODO

    8 – JOINTURES

Ici on affiche chaque roduit avec le nom de la catégorie au lieu de la clé

SELECT[Products].[ProductId],[Products].[Title],[Products].[NumberOfPages],[Categories].[Name]

FROM [Products],[Categories]

WHERE [Products].[CategoryId]=[Categories].[CategoryId]

Astuce si plusieurs jointures, utiliser AND

Jointure interne >>

SELECT[Products].[ProductId],[Products].[Title],[Products].[NumberOfPages],[Categories].[Name]

FROM [Products] INNER JOIN [Categories]

ON [Products].[CategoryId]=[Categories].[CategoryId]

9 – UNION et UNION ALL

SELECT [CustomerId],[CountryId],[FirstName],[LastName],[CompanyName],[Address]

FROM [Customers]

WHERE [CountryId]=3

UNION

SELECT [CustomerId],[CountryId],[FirstName],[LastName],[CompanyName],[Address]

FROM [Customers]

WHERE [CountryId]=4

Astuce : union all n’élimine pas les doublons