Probleme C# et SQL SERVER procédure stockée

**myriss** · 10/01/2014, 17h10

Bonjour à tous,

Je sollicite votre aide car après des semaines intenses de recherches, je ne trouve pas pourquoi mes données ne s'enregistrent pas alors que tout me semble correcte. C'est surement une connerie mais elle ne me saute pas aux yeux.
D'avance, merci de votre aide.

J'ai donc le code C# suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SqlDataAdapter daEnf = new SqlDataAdapter();
SqlCommand cmdSelectEnf = new SqlCommand("spCreateUpdateEnfantPersonnel", connectionBM.BmSqlConnection);
cmdSelectEnf.CommandType = CommandType.StoredProcedure;
cmdSelectEnf.Parameters.Add("@IdPersonnel", SqlDbType.UniqueIdentifier, 100).Value = IdPersonnel;
cmdSelectEnf.Parameters.Add("@IdActiviteEnfant", SqlDbType.UniqueIdentifier, 100).Value = idActiviteEnfant;
cmdSelectEnf.Parameters.Add("@Nom", SqlDbType.VarChar, 100).Value = Nom;
cmdSelectEnf.Parameters.Add("@Prenom", SqlDbType.VarChar, 100).Value = Prenom;
cmdSelectEnf.Parameters.Add("@DateAnneeNaissance", SqlDbType.DateTime, 8).Value = DateNaissance;
cmdSelectEnf.Parameters.Add("@DateNaissanceComplete", SqlDbType.Bit, 1).Value = true;
cmdSelectEnf.Parameters.Add("@Sexe", SqlDbType.Bit, 1).Value = Sexe;
cmdSelectEnf.Parameters.Add("@AppartientFoyer", SqlDbType.Bit, 1).Value = true;
cmdSelectEnf.Parameters.Add("@Commentaire", SqlDbType.VarChar, 100).Value = "MAJ";
cmdSelectEnf.Parameters.Add("@ErrorNumber", SqlDbType.Int, 1).Value = 0;
cmdSelectEnf.Parameters.Add("@ErrorDescription", SqlDbType.VarChar, 1).Value = "";

Voici le code de la procédure stockée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
ALTER PROCEDURE [dbo].[spCreateUpdateEnfantPersonnel]
/*-----------------------------------------------------------*/
/* Création/mise à jour d'un enfant d'un personnel (crypté). */
/*-----------------------------------------------------------*/
	@IdEnfant              UNIQUEIDENTIFIER,
	@IdPersonnel           UNIQUEIDENTIFIER,
	@IdActiviteEnfant      UNIQUEIDENTIFIER,
	@Nom                   NVARCHAR(60),
	@Prenom                NVARCHAR(60),
	@DateAnneeNaissance    DATETIME,
	@DateNaissanceComplete BIT,
	@Sexe                  BIT,
	@AppartientFoyer       BIT,
	@Commentaire           NVARCHAR(MAX),
	@ErrorNumber           INTEGER          OUTPUT,
	@ErrorDescription      NVARCHAR(2060)   OUTPUT
AS
	SET NOCOUNT ON
	-- NVARCHAR(MAX) a NULL si pas de commentaire.
	IF LEN(RTRIM(LTRIM(@Commentaire))) = 0
		SET @Commentaire = NULL
	-- GUID de la clé symétrique.
	DECLARE @KeyGUID UNIQUEIDENTIFIER
	SET @KeyGUID = Key_GUID('BureauMobileSymmetricKey')
	-- Début de la capture d'erreur.
	BEGIN TRY
		-- Transaction : 
		-- 1) En cas d'erreur, annule toute la transaction (SET XACT_ABORT ON).
		-- 2) Niveau d'isolation SERIALIZABLE pour éviter l'apparition d'un item entre
		--    a) la vérification de présence de fiche enfant,
		--    b) la mise à jour de la fiche enfant.
		SET XACT_ABORT ON
		SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
		BEGIN TRANSACTION
		-- Fiches enfants du personnel.
		SELECT
			IdEnfant,
			IdActiviteEnfant,
			Nom,
			Prenom,
			DateAnneeNaissance,
			DateNaissanceComplete,
			Sexe,
			AppartientFoyer,
			Commentaire
		INTO #tmpEnfant
		FROM tbEnfant
		WHERE IdPersonnel = @IdPersonnel
		-- Vérifie l'existence d'une fiche identique.
		DECLARE @FicheIdentiqueExiste INTEGER
		SET @FicheIdentiqueExiste = (
			SELECT COUNT(*)
			FROM #tmpEnfant
			WHERE 
				IdEnfant                                                                 = @IdEnfant               AND
				IdActiviteEnfant                                                         = @IdActiviteEnfant       AND
				DecryptByKey(Nom)                                                        = @Nom                    AND
				DecryptByKey(Prenom)                                                     = @Prenom                 AND 
				CONVERT(DATETIME,CONVERT(NVARCHAR(10),DecryptByKey(DateAnneeNaissance))) = @DateAnneeNaissance     AND
				DateNaissanceComplete                                                    = @DateNaissanceComplete  AND
				Sexe                                                                     = @Sexe                   AND
				AppartientFoyer                                                          = @AppartientFoyer        AND
				ISNULL(dbo.fnDecrypteNVarCharMax(Commentaire),'')                        = ISNULL(@Commentaire,''))
		IF @FicheIdentiqueExiste = 0 
		BEGIN
			-- Pas de fiche identique. Une fiche existe-t-elle avec des données différentes ?
			DECLARE @FicheExiste INTEGER
			SET @FicheExiste = (
				SELECT COUNT(*)
				FROM #tmpEnfant
				WHERE IdEnfant = @IdEnfant )
			IF @FicheExiste = 1
			BEGIN
				-- Oui, dans ce cas on met à jour les données de cette fiche.
				UPDATE tbEnfant
				SET
					IdActiviteEnfant      = @IdActiviteEnfant,
					Nom                   = EncryptByKey(@KeyGUID,@Nom),
					Prenom                = EncryptByKey(@KeyGUID,@Prenom),
					DateAnneeNaissance    = EncryptByKey(@KeyGUID,dbo.fnDateToDmyString(@DateAnneeNaissance)),
					DateNaissanceComplete = @DateNaissanceComplete,
					Sexe                  = @Sexe,
					AppartientFoyer       = @AppartientFoyer,
					Commentaire           = dbo.fnCrypteNVarCharMax(@Commentaire) 
				WHERE IdEnfant = @IdEnfant
			END
			ELSE
			BEGIN
				-- Non, dans ce cas, on créé la fiche.
				INSERT INTO tbEnfant (
					IdEnfant,
					IdPersonnel,
					IdActiviteEnfant,
					Nom,
					Prenom,
					DateAnneeNaissance,
					DateNaissanceComplete,
					Sexe,
					AppartientFoyer,
					Commentaire)
				VALUES (
					@IdEnfant,
					@IdPersonnel,
					@IdActiviteEnfant,
					EncryptByKey(@KeyGUID,@Nom),
					EncryptByKey(@KeyGUID,@Prenom),
					EncryptByKey(@KeyGUID,dbo.fnDateToDmyString(@DateAnneeNaissance)),
					@DateNaissanceComplete,
					@Sexe,
					@AppartientFoyer,
					dbo.fnCrypteNVarCharMax(@Commentaire) )
			END
		END	
		-- Supprime la table temporaire.
		DROP TABLE #tmpEnfant
		-- Fin de la transaction.
		COMMIT TRANSACTION
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED
		SET XACT_ABORT OFF
		SET @ErrorNumber = NULL
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED
		SET XACT_ABORT OFF
		SET @ErrorNumber      = ERROR_NUMBER()
		SET @ErrorDescription = ERROR_MESSAGE()
	END CATCH

**Pol63** · 11/01/2014, 08h49

il manque un morceau de code (la partie avec executenonquery)
il manque l'info de ce qui se passe (rien ? une erreur ?)
il manque un retour de l'erreur dans le catch côté sql, s'il y une erreur il faut avertir l'appelant, sinon tu ne sauras rien côté c#

après cette procédure stockée ne sert à rien, enfin si elle sert à plomber ton serveurs en lui donnant du travail inutile
on ne copie pas une table dans une table temporaire pour rien !
et ta transaction ne sert à rien non plus, une transaction c'est utile quand on fait au moins 2 {insert/update/delete} à la suite, ici soit tu fais un seul update soit un seul insert

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
"if not exists (select 1 from table where condition d'existance)
     insert into table ...
  else
     update table set ... where condition"

là c'est beaucoup plus performant, beaucoup plus simple (pas 100 lignes de code) et tu auras un retour direct (catch de c#) si ca plante

**myriss** · 11/01/2014, 09h08

Bonjour Pol63 et merci pour ta réponse.

Avant de te répondre, je tiens à préciser que ce script est utilisé dans le but de mettre à jour une table SQL Server. Je pense que tu l'avais compris.

A présent je répond.

Il manque un morceau de code (la partie avec executenonquery)

Ah effectivement cette ligne n'est pas passée dans le copié-collé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SqlDataAdapter daEnf = new SqlDataAdapter();
SqlCommand cmdSelectEnf = new SqlCommand("spCreateUpdateEnfantPersonnel", connectionBM.BmSqlConnection);
cmdSelectEnf.CommandType = CommandType.StoredProcedure;
cmdSelectEnf.Parameters.Add("@IdPersonnel", SqlDbType.UniqueIdentifier, 100).Value = IdPersonnel;
cmdSelectEnf.Parameters.Add("@IdActiviteEnfant", SqlDbType.UniqueIdentifier, 100).Value = idActiviteEnfant;
cmdSelectEnf.Parameters.Add("@Nom", SqlDbType.VarChar, 100).Value = Nom;
cmdSelectEnf.Parameters.Add("@Prenom", SqlDbType.VarChar, 100).Value = Prenom;
cmdSelectEnf.Parameters.Add("@DateAnneeNaissance", SqlDbType.DateTime, 8).Value = DateNaissance;
cmdSelectEnf.Parameters.Add("@DateNaissanceComplete", SqlDbType.Bit, 1).Value = true;
cmdSelectEnf.Parameters.Add("@Sexe", SqlDbType.Bit, 1).Value = Sexe;
cmdSelectEnf.Parameters.Add("@AppartientFoyer", SqlDbType.Bit, 1).Value = true;
cmdSelectEnf.Parameters.Add("@Commentaire", SqlDbType.VarChar, 100).Value = "MAJ";
cmdSelectEnf.Parameters.Add("@ErrorNumber", SqlDbType.Int, 1).Value = 0;
cmdSelectEnf.Parameters.Add("@ErrorDescription", SqlDbType.VarChar, 1).Value = "";
cmdSelectEnf.ExecuteNonQuery();

il manque l'info de ce qui se passe (rien ? une erreur ?)

Alors effectivement je ne l'ai pas marqué. Mon script s'exécute et continu jusqu'à la fin.
Pas d'erreur que ce soit dans mon log ou dans le log SQL Server.

il manque un retour de l'erreur dans le catch côté sql, s'il y une erreur il faut avertir l'appelant, sinon tu ne sauras rien côté c#

Alors là, je comprend ce que tu veux dire mais je ne sais pas trop comment on fait cela côté SQL Server. TU peux m'en dire plus ?

après cette procédure stockée ne sert à rien, enfin si elle sert à plomber ton serveurs en lui donnant du travail inutile
on ne copie pas une table dans une table temporaire pour rien !

Historiquement, cette procédure stockée a été développé il y a quelques temps par une personne employée par une amie que je dépanne.
Il avait l'air de se compliquer énormément la vie. Ta réponse me le confirme.
Je débute en C# mais je développe dans pas mal d'autres langages et j'ai bien vu que quelque chose n'allait pas dans ses scripts.

"if not exists (select 1 from table where condition d'existance)
insert into table ...
else
update table set ... where condition"
là c'est beaucoup plus performant, beaucoup plus simple (pas 100 lignes de code) et tu auras un retour direct (catch de c#) si ca plante

Ah ben c'est clair que si je l'avais faite moi-même j'aurais bâti la procédure en ce sens.

Merci pour ton aide, je vais réécrire la procédure stockée et cela m'intéresse de savoir comment avertir m'appelant pour avoir l'erreur dans le catch côté SQL. Encore merci.

**myriss** · 11/01/2014, 09h17

J'ai trouvé ! Merci à toi Pol63

En fait le paramètre IdEnfant était renseigné en champ obligatoire et je ne l'avais pas renseigné.
De plus la ligne SET XACT_ABORT ON m'empêchait de voir cette erreur.
Après mise en commentaire, j'ai pu voir ce qui n'allait pas et tout fonctionne à présent.

Encore merci pour ton aide.

**Pol63** · 11/01/2014, 14h41

il n'est pas obligatoire de faire une procédure stockée, on peut mettre tout ca dans le CommandText

Envoyé par myriss

cela m'intéresse de savoir comment avertir m'appelant pour avoir l'erreur dans le catch côté SQL. Encore merci.

raiserror est une instruction d'sql server qui génère une erreur, avec sévérité=16 ca arrête l'exécution et l'excutenonquery plante avec le message fournit
en mettant ca dans le catch ca peut aller

sinon on peut terminer le code par un select de quelque chose, et après avec executereader on peut lire le résultat (par exemple un booléen de réussite et un string de message)

**myriss** · 11/01/2014, 15h02

Merci pour toutes ces infos.

Par contre j'utilise une procédure stockée parce que les données sont cryptées.

Probleme C# et SQL SERVER procédure stockée

C#

Discussions similaires

Partager

Partager