C'est un peu long, mais c'est une belle expérience que j'ai pu faire et je la partage. Et puis ce sont aussi de petites révisions sur ADO .NET, et les Procédures Stockées Managées.
Voici le contexte. J'ai 4 procédures stockées dont 2 sont des procédures stockées managées . Voici le 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
38
39
40
41
42
43
44
45
 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public class StoredProcedures
{
    private static readonly string m_ContextConnection = @"Context Connection = True";

    [SqlProcedure]
    public static void GetUsersCLR()
    {
        using (SqlConnection oConn = new SqlConnection(m_ContextConnection))
        {
            using(SqlCommand oComm = new SqlCommand())
            {
                oComm.CommandText = "SELECT * FROM dbo.Users";
                oConn.Open();
                SqlContext.Pipe.ExecuteAndSend(oComm);
            }
        }
    }

    [SqlProcedure]
    public static void AddUsersCLR(SqlString FirstName, SqlString LastName)
    {
        using (SqlConnection oConn = new SqlConnection(m_ContextConnection))
        {
            using (SqlCommand oComm = new SqlCommand())
            {
                SqlParameter p1 = new SqlParameter("@FirstName", SqlDbType.VarChar);
                p1.Value = FirstName;
                SqlParameter p2 = new SqlParameter("@LastName", SqlDbType.VarChar);
                p2.Value = LastName;

                oComm.CommandText = "INSERT INTO dbo.Users VALUES (@FirstName, @LastName)";
                oComm.Parameters.AddRange(new SqlParameter[]{p1, p2});
                oConn.Open();
                SqlContext.Pipe.ExecuteAndSend(oComm);
            }
        }
    }
};

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
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

Que va t-il se passer lors de l'exécution de ce programme ?

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
 
using(SqlConnection oConn = new SqlConnection(m_ConnectionString)) {
   
   using (SqlCommand oComm = new SqlCommand()) {
      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"]);
            }
         }
      }
   }
}

            
using (SqlConnection oConn = new SqlConnection(m_ConnectionString)) {
   using (SqlCommand oComm = new SqlCommand()) {
      Console.WriteLine("Working with CLR Stored Procedures");
                    
      SqlParameter p1 = new SqlParameter("@FirstName", SqlDbType.VarChar);
      p1.Value = "Toto";
      SqlParameter p2 = new SqlParameter("@LastName", SqlDbType.VarChar);
      p2.Value = "TATA";

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

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

      Console.WriteLine("{0} rows inserted.", count);
      using (SqlCommand oComm2 = new SqlCommand())
      {
         oComm2.CommandText = "dbo.GetUsersCLR";
         oComm2.CommandType = CommandType.StoredProcedure;
         oComm2.Connection = oConn;
         using (SqlDataReader reader = oComm2.ExecuteReader())
         {
            while (reader.Read()) {
               Console.WriteLine("User : {0} {1}", reader["FirstName"], reader["LastName"]);
            }
         }
      }
   }
}

(Il n'y a pas d'erreur de parenthèses Big Smile !).