utilisation des procédures stockées dans un développement client avec ado

8/6/2019 Utilisation Des procédures stockées Dans un développement Client avec ADO

http://slidepdf.com/reader/full/utilisation-des-procedures-stockees-dans-un-developpement-client-avec-ado 1/14

Utilisation Des procédures stockées Dans un développement Client avec ADO.Net

Utilisation

Des procédures stockées

Dans un développement Client

Avec

ADO.Net

Ce document a été fabriqué par PDFmail (Copyright RTE Multimedia)

http://www.pdfmail.com

http://www.pdfmail.com/








Pré requis.

- Connaître et savoir mettre en oeuvre l’objet DataReader du modèleADO.net.

- Avoir développer des applications événementielles avec ADO.net

1 Comment appeler une procédure stockée

Les procédures stockées sont un ensemble d’ordres compilés sur le moteur SGBD.Grâce aux procédures stockées, il est possible de centraliser l’ensemble du codeSQL de votre application à un seul endroit, ce qui facilite la maintenance. D’autrepart, une procédure stockée est plus performante qu’une requête dynamique dontle plan d’exécution et la compilation doivent être effectués par le moteur SGBDavant toute exécution.

1.1-Appel à une procédure sans paramètre et avec un jeu d'enregistrements enretour

Une procédure stockée peut être appelée avec les langages événementiels qui utilise lemodèle objet ADO.net

Pour ce faire, il faut générer une requête d’appel à la procédure stockée à partir d’unobjet de type Command du modèle objet ADO .net

Exemples:

Les exemples de ce support utilisent la base de données PUBS de Sql*Servermise en place lors de l’installation du SGBD.

Exemple 1 : Dans cet exemple, la procédure est cataloguée au niveau SQL Server

dans la base de données Pubs qui devra être la base courante.

Reptp1 : Liste des titres par éditeur avec le prix et la date de publication

CREATE PROCEDURE reptq1 ASSELECT id_éditeur, id_titre, prix, datepubFROM titresWHERE prix is NOT NULLORDER BY id_éditeurCOMPUTE avg(prix) BY id_éditeur // à mettre en commentaire pour exécution COMPUTE avg(prix) // avec ADO .net











Application Evénementielle CSharp

Programmation :

Base de données : PUBS de Sql*ServerProcédure Stockée : reqtp1 (sans paramètre en entrée), liste des titres et de leurs prix.

Note : il faut mettre en commentaires les instructions compute sur l’order by pourrecevoir après exécution uniquement les occurrences et non le récapitulatif.

Utilisation des classes du modèle ADO.net

SqlCommandSqlDataReader

Code en CSharp

Connexion :

string strConn = "Data Source=localhost; Integrated Security= SSPI; DataBase = pubs";try{

oConn = new SqlConnection(strConn);

oConn.Open();}catch (Exception error){

MessageBox.Show (error.Message,"Erreur",MessageBoxButtons.OK,MessageBoxIcon.Error);

}











Affichage

try{ // Préparation de l’appel à la procédure reptp1 de la base Pubs string strProcStock = "reptq1";

SqlCommand oCmd = new SqlCommand( strProcStock, oConn);

oCmd.CommandType = CommandType.StoredProcedure;

// Exécution de la procédure, récupération dans un DataReader le résultat // de la requête SqlDataReader oReader = oCmd.ExecuteReader();

// Affichage dans la liste string affligne;affligne = oReader.GetName(0)+" ";

affligne += oReader.GetName(1)+" ";affligne += oReader.GetName(2);

listBox1.Items.Add (affligne);while (oReader.Read())

{affligne = oReader.GetString(0);affligne += " ";affligne += oReader.GetString(1);affligne += " ";affligne += Convert.ToString(oReader.GetValue(2));listBox1.Items.Add(affligne);

}oReader.Close ();

}catch (Exception error){


}











1.2-Appel à une procédure avec paramètre(s) et avec un jeu d'enregistrements en

retour d’exécution.

Exemple 2:

Liste des titres en fonction d'une fourchette de prix et d'un code d'édition

On passe en paramètre ces trois informations à la procédure stockée reptq3

CREATE PROCEDURE reptq3 @lolimit money, @hilimit money,@type char(12)ASselect pub_id, type, title_id, pricefrom titleswhere price >@lolimit AND price <@hilimit AND type = @type /* OR type LIKE '%cook%'*/ en commentaire

order by pub_id, type /* COMPUTE count(title_id) BY pub_id, type */ en commentaire

Application CSharp











On passe les paramètres à l’objet SqlCommand par une collection Parameters

Lors de l’utilisation des paramètres avec l’objet SqlCommand, les noms des paramètresajoutés à SqlParameters doivent correspondre aux noms des paramètres de la

procédure stockée.

Exemple du code en CSharp

// Préparation de l’appel// à la procédure reptp2 de la base Pubsstring strProcStock = "reptq3";SqlCommand oCmd = new SqlCommand( strProcStock, oConn);oCmd.CommandType = CommandType.StoredProcedure;

// paramétres en entréeSqlParameter oParam1 = oCmd.Parameters.Add("@lolimit",SqlDbType.Money);oParam1.Value = Convert.ToDecimal(txtmini.Text);SqlParameter oParam2 = oCmd.Parameters.Add("@hilimit",SqlDbType.Money);

oParam2.Value = Convert.ToDecimal(txtmaxi.Text);SqlParameter oParam3 = oCmd.Parameters.Add("@type",SqlDbType.NChar,12);oParam3.Value = Convert.ToString(txtType.Text);

// Exécution de la procédure, récupération dans// un DataReader le résultat de la requêteSqlDataReader oReader = oCmd.ExecuteReader();











1.3.-Appel à une procédure avec paramètre(s) en entrée et paramètre(s) en sortie

Paramètres : il faut décrire le type de paramètre via la propriété ParameterDirection

de l’objet Parameter (sauf pour les paramètres en entrée). Celle-ci peut prendre l’une

des valeurs suivantes : Input, InputOutput, Output ou ReturnValue.

Exemple 3:

Recherche du plus grand numéro d'employé d'un service

On passe en paramètre l'information code job d’un employé à la procédure stockéereptq32 qui nous renverra la date du plus jeune employé (embauche) dans cettecatégorie de job.

CREATE PROCEDURE reptq32

@id_job int@date datetime OUTPUTASBegin

If exists(select * from employes where job_id = @id_job)BeginSelect @date= (select max(hire_date) from employes where job_id = @id_job)End

ElseBeginSelect @ date = 0End

end











Application CSharp

Entrer le code job, afficher la date du plus jeune employé

Dans l'application CSharp, il faut utiliser l'objet Parameter .

Les valeurs ParameterDirection sont utilisées par les propriétés de direction de

paramètre de OleDbParameter et SqlParameter.

Membres

Nom de membre Description

Input

Pris en charge par le .NET CompactFramework.

Le paramètre est un paramètre d'entrée.

InputOutput


Le paramètre est à la fois un paramètred'entrée et de sortie.

Output


Le paramètre est un paramètre de sortie.

ReturnValue


Le paramètre représente une valeur deretour d'une opération telle qu'uneprocédure stockée, une fonction intégrée ouune fonction définie par l'utilisateur.

Configuration requise

Espace de noms : System.Data











try{// Préparation de l’appel

// à la procédure reptp2 de la base Pubsstring strProcStock = "reptq32";

SqlCommand oCmd = new SqlCommand( strProcStock, oConn);oCmd.CommandType = CommandType.StoredProcedure;

// paramètres en entréeSqlParameter oParam1 = oCmd.Parameters.Add("@idjob",SqlDbType.Int);oParam1.Value = Convert.ToInt16(txtJob.Text);

// paramètre en sortieSqlParameter oParam2 = oCmd.Parameters.Add("@date",SqlDbType.VarChar,20);oParam2.Direction = ParameterDirection.Output;

// Exécution de la procédure, récupération dans

// un DataReader le résultat de la requêteoCmd.ExecuteReader();

txtDate.Text = oParam2.Value.ToString ();



}











1.4-Appel à une procédure avec ou sans paramètre pour une action sur la base de

données avec gestion des erreurs.

La procédure stockée est une « requête_action » demandant une mise à jour sur la

base. En retour on teste dans l'application le retour de bonne exécution de l'action(Update, Delete, Insert)

Exemple 4:

Dans cet exemple, la procédure effectue une mise à jour sur la base de donnéesPUBS table AUTEURS

Mise à jour du numéro de téléphone

CREATE PROCEDURE reptq4@id_auteur char(11),@telephone char(12)

ASbegin

declare @ermsg char(45)

if(select @id_auteur)=""

beginselect @ermsg='Numéro d auteur obligatoire'goto erreur

endif(select @telephone)=""

beginselect @ermsg='Numéro de téléphone obligatoire'goto erreur

end

if exists(select 1 from auteurswhere id_auteur=@id_auteur)

begin

UPDATE auteurs

Set téléphone = @telephonewhere id_auteur = @id_auteur

if @@error<>0begin

select @ermsg='Impossible de modifier l Auteur'goto erreur

endend











elsebegin

select @ermsg='Auteur inconnu'goto erreur

endreturn

erreur:raiserror 50001 @ermsg

endGO

Application CSharp

try

{ // Préparation de l’appel // à la procédure reptp2 de la base Pubsstring strProcStock = "reptq4";SqlCommand oCmd = new SqlCommand( strProcStock, oConn);oCmd.CommandType = CommandType.StoredProcedure; // paramètres en entréeSqlParameter oParam1 = oCmd.Parameters.Add("@id_auteur ",SqlDbType.Char,11);oParam1.Value = Convert.ToString(txtCode.Text);SqlParameter oParam2 = oCmd.Parameters.Add("@telephone",SqlDbType.Char,12);oParam2.Value = Convert.ToString(txtNumTel.Text);

// paramètre code retourSqlParameter oParam3 = oCmd.Parameters.Add("@ermsg",SqlDbType.VarChar,45);oParam3.Direction = ParameterDirection.ReturnValue;

// Exécution de la procédureoCmd.ExecuteReader();string mess = oParam3.Value.ToString();

MessageBox.Show (mess,

Retour normal

Retour en erreur











"Message",MessageBoxButtons.OK,MessageBoxIcon.Error);


MessageBox.Show (error.Message,"Erreur",MessageBoxButtons.OK,MessageBoxIcon.Error);}











Capture des erreurs

La procédure cataloguée renvoie par le raiserror un numéro de message et un libellé demessage d'erreurDans l'application CSharp on capture les erreurs (numéro et messageregarder l'exempleprécédent, les deux méthode sont utilisées.











Exercices d’application :

Reprendre les quatre procédures développés dans le supports de la séquenceprocédures stockées sur la base de données GOELAND.

- Développer une application CSharp par procédure.- Tester les cas d'erreur dans CSharp








utilisation des procédures stockées dans un développement client avec ado

Documents