Dans mon précédent billet, nous avions donc 4 procédures stockées.
Les procédures stockées managées ne contenaient aucune erreur (c'est le code "classique" de création de procédures de ce type !), ni meme les procédures stockées T-SQL.


CREATE PROCEDURE [dbo].[AddUsers]
@FirstName varchar(50),
@LastName varchar(50)
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO dbo.Users (FirstName, LastName) VALUES (@FirstName, @LastName);
END


CREATE PROCEDURE [dbo].[GetUsers]
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM dbo.Users;
END


Ces dernières cependant avaient l'instruction SET NOCOUNT ON (généralement présente par défaut lors de la création d'une procédure stockée via les wizards de VS. SET NOCOUNT permet de désactiver ou pas le message permettant d'indiquer le nombre de lignes affectées(retournées) par une requête.

Concernant le programme en lui même, voici le premier bloc.

Console.WriteLine("Working with T-SQL Stored Procedures");

SqlParameter p1 = new SqlParameter("@FirstName", SqlDbType.VarChar);
p1.Value = "David";

SqlParameter p2 = new SqlParameter("@LastName", SqlDbType.VarChar);
p2.Value = "POULIN";

oComm.CommandText = "dbo.AddUsers";
oComm.Parameters.AddRange(new SqlParameter[]{p1, p2});
oComm.Connection = oConn;
oComm.CommandType = CommandType.StoredProcedure;

oConn.Open();

int count = 0;
count = oComm.ExecuteNonQuery();

Console.WriteLine("{0} rows inserted.", count);

using(SqlCommand oComm2 = new SqlCommand()) {
  oComm2.CommandText = "dbo.GetUsers";
  oComm2.Connection = oConn;

  using(SqlDataReader reader = oComm2.ExecuteReader())
  {
    while (reader.Read()) {
    Console.WriteLine("User : {0} {1}", reader["FirstName"], reader["LastName"]);
    }
  }
}


Il nous affiche :
Working with T-SQL Stored Procedures
-1 rows inserted
David POULIN

En effet, l'instruction SET NOCOUNT empêche d'afficher le nombre de lignes insérées dans notre table par notre procédure stockée !

Maintenant, le deuxième bloc dans lequel nous faisions appel à nos procédures stockées managées...
Les procédures stockées managées s'exécutent dans leur propre contexte par défaut. L'assembly contenant ces procédures stockées par défaut est définie sur des permissions de type SAFE. le type de connection autorisé uniquement est de type "Context Connection = True". En changeant le type de permissions, il est possible de pouvoir externaliser l'accès à d'autres ressources (définir sa propre chaine de connection), tout en utilisant Code Access Security .

Enfin, pour revenir à notre bloc de code, une exception de type InvalidOperationException sera levée à l'éxecution de la commande. Pourquoi ?
Il n'est pas possible tout simplement de pouvoir faire appel à des procédures stockées managées depuis C# (en tout cas, "par défaut" ...).

Rappelons le, l'idée est de pouvoir utiliser C# (VB aussi), d'utiliser ses propriétés, ses capacités et de créer des assemblies au sein de SQL Server 2005 !  Elles (les CLR SP) permettent de répondre à des problèmatiques qu'il n'est pas possible forcément (ou pas du tout) de résoudre avec du Transact SQL tout simplement.

Je n'en sais pas plus parce que, je n'ai pas cherché à en savoir plus pour le moment (étant donné les utilisations que j'en faisais) mais je vous redirige par ici ou il y a un excellent book d'initiation ou par la pour un overview de l'intégration CLR à SQL Server !

A +!