SqlDataReader déjà ouvert sur mon SqlCommand : où ça ?

Version imprimable

Bonjour,

J'ai un problème que je ne m'explique pas...

Voici une méthode que j'ai écris qui permet de supprimer une ligne dans une table de type "clé/valeur", en vérifiant que la ligne n'est pas référencée dans une autre table par une clé étrangère.

Code:

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
 
        public bool DeleteSimpleValue(string table, int id)
        {
            using (SqlCommand cmd1 = Cnx.CreateCommand(), cmd2 = Cnx.CreateCommand())
            {
                SqlParameter pId = cmd2.Parameters.Add("id", SqlDbType.Int);
                pId.Value = id;
                cmd1.CommandText = "SELECT FK.TABLE_NAME  [table], concat('select count(*) from [', FK.TABLE_NAME, '] fk where fk.[', CU.COLUMN_NAME, '] in (select p.[', PT.COLUMN_NAME, '] from [', PK.TABLE_NAME, '] p where p.id = @id)') query FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME where PK.TABLE_NAME = @name";
                SqlParameter pName = cmd1.Parameters.Add("name", SqlDbType.VarChar, 50);
                pName.Value = table;
                cmd1.Prepare();
                SqlDataReader dr = cmd1.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult);
                while (dr.Read())
                {
                    cmd2.CommandText = dr.GetString(1);
                    cmd2.Prepare();
                    int nb = (int)cmd2.ExecuteScalar();
                    if (nb > 0)
                    {
                        MessageBox.Show(string.Format("This {0} is referenced in another table : {1}", table, dr.GetString(0)));
                        dr.Close();
                        return false;
                    }
                }
                dr.Close();
 
                cmd2.CommandText = string.Format("delete {0} where id = @id", table);
                cmd2.Prepare();
                cmd2.ExecuteNonQuery();
                return true;
            }
        }

Le programme plante au premier appel de la ligne "int nb = (int)cmd2.ExecuteScalar();" avec une erreur comme quoi j'ai déjà un DataReader ouvert sur ma Command, et que je devrais le fermer avant d'en réouvrir un autre...

Sauf que... c'est le premier appel à cmd2 !
Oui, cmd1, qui est sur la même connexion, a bien un DataReader ouvert, mais ce n'est pas le même objet...

Que se passe-t-il ?

En l'absence d'une explication, j'ai résolu mon problème en n'imbriquant plus les connexion, mais j'aimerais comprendre pourquoi je suis obligé de charger tout mon jeu de données en mémoire avant de pouvoir exécuter une autre requête dans un objet command différent !

Code:

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
 
        public bool DeleteSimpleValue(string table, int id)
        {
            using (SqlCommand cmd1 = Cnx.CreateCommand(), cmd2 = Cnx.CreateCommand())
            {
                List<KeyValuePair<string, string>> list = new List<KeyValuePair<string, string>>();
                SqlParameter pId = cmd2.Parameters.Add("id", SqlDbType.Int);
                pId.Value = id;
                cmd1.CommandText = "SELECT FK.TABLE_NAME  [table], concat('select count(*) from [', FK.TABLE_NAME, '] where (', CU.COLUMN_NAME, ') in (select ', PT.COLUMN_NAME, ' from [', PK.TABLE_NAME, '] where id = @id)') query FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME where PK.TABLE_NAME = @name";
                SqlParameter pName = cmd1.Parameters.Add("name", SqlDbType.VarChar, 50);
                pName.Value = table;
                cmd1.Prepare();
                SqlDataReader dr = cmd1.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult);
                while (dr.Read())
                {
                    list.Add(new KeyValuePair<string, string>(dr.GetString(0), dr.GetString(1)));
                }
                dr.Close();
 
                foreach (KeyValuePair<string, string> kv in list)
                {
                    cmd2.CommandText = kv.Value;
                    cmd2.Prepare();
                    int nb = (int)cmd2.ExecuteScalar();
                    if (nb > 0)
                    {
                        MessageBox.Show(string.Format("This {0} is referenced in another table : {1}", table, kv.Key));
                        return false;
                    }
                }
 
                cmd2.CommandText = string.Format("delete {0} where id = @id", table);
                cmd2.Prepare();
                cmd2.ExecuteNonQuery();
                return true;
            }
        }

06/12/2013, 14h23
meziantou

Pour faire ce que tu veux il faut activer MARS (un mars et ça repart :))
06/12/2013, 15h08
StringBuilder

Merci pour la réponse.

Ceci dit, je dois dire que je suis absolument perplexe...

MARS ouvre autant de connexion que d'objets command, ça laisse rêveur quant aux performances et aux comportement transactionels (locks notamment) 8O

Mais surtout, il y a 15 ans, je faisais des sites web en ASP (VBScrpit) avec ADODB sur SQL Server 6.5, 7.0 puis 2000

Et avec ces technologies antédiluviennes j'ai toujours procédé de la sorte, sans aucun problème : il était parfaitement possible d'avoir plusieurs objet command travaillant en même temps sur la même connexion.

Et je suis, à pas grand chose près, absolument certain d'avoir déjà utilisé ce genre de méthode avec .NET 2.0 et SQL Server 2005 ou 2008.

Là, j'utilise Visual Studio 2013 avec SQL Server 2014.

C'est des nouvelles fonctions ?
09/12/2013, 10h23
DotNetMatt

La règle standard c'est une connexion = une session. Donc tu ne peux pas avoir le comportement que tu recherches, sauf à activer MARS comme indiqué par meziantou.

MARS permet à une seule connexion de pouvoir gérer plusieurs commandes, chacune ayant sa propre session.

Au niveau des performances, je ne vois pas où est le problème, le pool de connexions est là pour gérer les connexions et il sait très bien le faire.

Je ne sais pas ce qui est le mieux entre activer MARS ou laisser le pool de connexion faire simplement son travail, en tout cas je ne vois pas vraiment l'intérêt de MARS. Il vaudrait mieux revoir la structure de ton programme...
09/12/2013, 14h45
StringBuilder

Citation:

Envoyé par DotNetMatt

La règle standard c'est une connexion = une session. Donc tu ne peux pas avoir le comportement que tu recherches, sauf à activer MARS comme indiqué par meziantou.

MARS permet à une seule connexion de pouvoir gérer plusieurs commandes, chacune ayant sa propre session.

Au niveau des performances, je ne vois pas où est le problème, le pool de connexions est là pour gérer les connexions et il sait très bien le faire.

Je ne sais pas ce qui est le mieux entre activer MARS ou laisser le pool de connexion faire simplement son travail, en tout cas je ne vois pas vraiment l'intérêt de MARS. Il vaudrait mieux revoir la structure de ton programme...

Rien n'explique dans ce que tu dis pourquoi il faudrait que 1 command = 1 session

Dans un bloc PL, on peut tout à fait avoir des curseurs imbriqués, au sein d'une même transaction, d'une même session.

Ca n'a rien à voir avec MARS.

Moi je cherche exactement à faire la même chose : un premier curseur ouvert (un premier objet command) et effectuer des requêtes en fonction du résultat du premier, donc avec une imbrication.

MARS ne permet que de faire travailler parallèlement deux traitements, mais d'un point de vue transactions notamment, c'est totalement disjoint. Et c'est pas du tout ce que je souhaite.

Mais bon, dans mon cas particulier, j'ai pu m'en tirer sans faire d'imbrication donc le problème est résolu.

Croyez-moi ou non, jusqu'à récemment, cela fonctionnait parfaitement bien les command imbriqués, dans la même session, la même connexion, et la même transaction !