Dans mes précédents posts, j’ai décrit ce qu’était un repository de manière générale et comment implémenter un système dans un environnement web afin de choisir de manière simple son repository.
Je vais aujourd’hui détailler une méthode afin de faire rimer les repository avec procédures stockées. Pour ce faire rien de tel qu’un exemple, et celui ci sera la gestion de cours de danse, et de salles de cours y afférent. Nous aurons donc besoin de connaitre la listes des endroits où se passent les cours, de pouvoir récupérer le détail d’un endroit, de sauver un endroit ou de l’effacer. le processus sera le même pour les cours.
l’interface de notre repository
Tout ceci va nous amener à développer l’interface suivante pour discuter avec notre base de données :
public interface IRepositoryCourses
{
List<Place> GetPlaces(FilterPlace filter);
Place GetPlace(FilterPlace filter);
void SavePlace(Place p);
void DeletePlace(Place p);
List<Course> GetCourses(FilterCourse filter);
Course GetCourse(FilterCourse filter);
void SaveCourse(Course c);
void DeleteCourse(Course c);
}
Avant de continuer le code c#, allons faire un petit tour du coté de notre base. Pour cet exemple je prendrais un script de transact-sql pour sql server 2005 mais , je crois que n’importe quelle base pourrait faire l’affaire .
L’idée va être de regrouper toutes nos commande en une seule procédure stockées, qui va ainsi, elle aussi, posséder les fonctions suivantes
– —————————————–
– | @id_op | opération |
– —————————————–
– | 1 | Get Places |
– —————————————–
– | 2 | Get Place |
– —————————————–
– | 3 | Save Place |
– —————————————–
– | 4 | Delete Place |
– —————————————–
– | 5 | Get Courses |
– —————————————–
– | 6 | Get Course |
– —————————————–
– | 7 | Save Course |
– —————————————–
– | 8 | Delete Course |
– —————————————–
– | | |
– —————————————–
Comme vous le pouvez le constater chaque fonction est associée à un id opération. C’est qui va nous permettre de différencier la commande à effectuer lors de l’appel de notre procédure.
Quel est l’intérêt de pratiquer de la sorte?
Il est très simple :
- Cela groupe les fonctionnalités par bloc de code dans votre base de données plus simple à migrer si besoin était.
- Pour plusieurs fonctions, on nécessite parfois les mêmes informations. De la sorte, on ne les déclare qu’une seule fois.
- cela va nous permettre de factoriser notre code c# et par la même
La procédure stockée
Dans la pratique comment ca fonctione? Voice le code complet de la procedure stockée
SP_COURSES
Le parametre @id_op va nous permettre de naviguer dans la procédure en jouant sur des bloc if comme suit :
– Get Places
if @id_op=1
begin
Select * from V_places where
PlaceIsDeleted=isnull(@filter_place_IsDeleted,PlaceIsDeleted)
and PlaceIsReleased=isnull(@filter_place_IsReleased,PlaceIsReleased)
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR RETURN
END
end
la définition des paramétres hors cet id_op obéit toujours à la même regle :
–getting, saving and deleting places
@place_id int=null, – id of the place
@place_name varchar(50)=null, – name of the place
@place_description varchar(255)=null, – description of the place
@place_Released bit=null, – true of false, indicates if the place has been released
@place_Deleted bit=null, – true of false, indicates if the place has been deleted
@place_startdate datetime=null, – start date of the place
@place_enddate datetime=null, – end date of the place
à savoir que chaque parametre est nullable. Avoir des parametres nullable ne résoudra pas tous les soucis et ne permettra pas toutes les factorisations possibles mais dans le cas du select Get Places détaillé plus haut, travailer de la sorte nous permettra d’avoir suffisemment de latitudes pour combler notre besoin.
Par ailleurs, on peut remarquer que la procédure stockées s’articule à l’interieur d’une transaction :
SET NOCOUNT ON
– starting transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION TRANSACTION_COURSES
…
COMMIT TRANSACTION TRANSACTION_COURSES
RETURN
LBL_ERROR:
ROLLBACK TRANSACTION TRANSACTION_COURSES
RETURN
L’implementation du repository coté c#
Maintenant que les règles pour écrire notre procédure stockée ont été détaillé, on va pouvoir passer au code c# qui va se charger de communiquer avec elle.
L’idée serait d’avoir une seule classe capable de cette communication. Cette classe devra donc implémenter l’interface décrite ci dessus. Par ailleurs, il faudrait aussi que cette classe soit indépendante de la base de donnée auquelle elle accede. Et donc que seul l’interface de la base de données ( son nom et ses parametres d’entrée et de sortie) soit le parametre déterminant qui compose à sa création.
Les contraintes étantes fixées, je vais m’appuyer sur une DAL comme décrite dans cet article de developpez.com écrit par SaumonAgile (erf j ai paumé le lien…) afin de rendre mon code indépendant de ma base.
public class SqlServerCoursesRepository : SQLRepository, IRepositoryCourses
{
}
Ma classe va aussi vous le voyer hériter de SQLRepository. En effet, toute une partie du traitement sera commune á ce repository comme à d’autres.
/// <summary>
/// Description résumée de ProckStock
/// </summary>
public abstract class SQLRepository : ISQLRepository
{
public class CastValeur : ExceptionLibrary
{
public CastValeur(string parametername, DbType casttype, object parametervalue)
:
base(string.Format(« The parameter {0} has not been casted in {1}. The parameter value was {2} », parametername,
casttype.ToString(), parametervalue.ToString()))
{
}
}
protected IDataParameterCollection ParamCollection;
protected IDbConnection ApplicationConnection;
public abstract Object Execute();
public void HttpTrace(string storedProcedure, string operation)
{
System.Web.HttpContext.Current.Trace.Warn(string.Format(« SP name={0}, Operation ={1} », storedProcedure, operation));
foreach (IDataParameter p in ParamCollection)
{
System.Web.HttpContext.Current.Trace.Warn(string.Format(« Pname={0};Value={1} », p.ParameterName, p.Value));
}
}
public SQLRepository(IDbConnection connection)
{
if (connection == null)
throw new Exception(« The connection is null »);
ApplicationConnection = connection;
ParamCollection = DataProviderFactory.GetInstance().GetParameterCollection();
}
Comme vous pouvez le voir, le comportement géneral est de
- fournir un méthode pour tracer nos appels
- Définir une exception pour des problemes de cast, ceci provenant de souci avec ma DAL.
- définit un constructeur qui accepte une connection ouverte afin de pouvoir executer la procédure stockée
- oblige les classe héritantes à définir une fonction execute qui retournera un objet.
Cela étant dit, nous allons pouvoir nous immerger dans ce qui nous tient plus á coeur, à savoir l’implémentation de notre repository DBCoursesRepository.cs, dont vous trovuerez le code ici.
Cette classe fonctionne de la sorte : On l’instancie et on appelle une des fonctions qui implémente son interface, et cette fonction appelle une fonction nommée Execute qui elle fera le contact avec la base de données.
illustrons ceci par un exemple avec la selection des endroits pour les cours de danse, getPlaces:
public List<Place> GetPlaces(FilterPlace filter)
{
List<Place> l = new List<Place>();
Place p;
IDataReader dr;
this.Id_op = E_Operation.GetPlaces;
this._FilterPlace = filter;
dr = (IDataReader)this.Execute();
while (dr.Read())
{
p = createPlace(dr);
l.Add(p);
}
dr.Dispose();
return l;
}
Comme défini dans l’interface, elle nous renvoie une List<Place> . En l’inspectant un peu plus, on se rend compte qu’elle va associé à une propriété Id_op l’enuméré GetPlaces.
En regardant cette énuméré, on se rend compte d’une coincidence troublante :
public enum E_Operation
{
GetPlaces = 1,
GetPlace = 2,
SavePlace = 3,
DeletePlace = 4,
GetCourses = 5,
GetCourse = 6,
SaveCourse = 7,
DeleteCourse = 8
}
GetPlaces est associée à la valeur 1 tout comme dans notre procédure stockée. Par ailleurs, l’obujet filter qui étati passé en paramétre vient se loger au creux d’une propriété de cette classe.
La fonction execute est appelée et renvoie un datareader. Tout le mystere va donc avoir lieu dans cette fonction execute :
public override object Execute()
{
//déclaration des variables
Object o = null;
string course_dates=string.Empty;
string storedProcedure = « SP_COURSES »;
Tout d’abord dans cette fonction, on définit à quelle procédure stockée cette fonction va devoir s’adresser, ici SP_COURSES.
Ensuite, elle va converser avec la procédure stockée afin d’attribuer à chaque paramétre, une valeur:
ParamCollection.Clear();
//Insertion of enter parameters
using (dp = DataProviderFactory.GetInstance())
{
ParamCollection.Add(dp.GetParameterInput(« @Id_op », (int)this.Id_op));
//Place
ParamCollection.Add(dp.GetParameterInput(« @place_id », this._Place.ID));
ParamCollection.Add(dp.GetParameterInput(« @place_name », this._Place.Name));
ParamCollection.Add(dp.GetParameterInput(« @place_description », this._Place.Description));
ParamCollection.Add(dp.GetParameterInput(« @place_Released », this._Place.IsReleased));
ParamCollection.Add(dp.GetParameterInput(« @place_Deleted », this._Place.IsDeleted));
ParamCollection.Add(dp.GetParameterInput(« @place_startdate », this._Place.StartDate));
ParamCollection.Add(dp.GetParameterInput(« @place_enddate », this._Place.EndDate));
//Place filter
ParamCollection.Add(dp.GetParameterInput(« @filter_place_id », this._FilterPlace.ID));
ParamCollection.Add(dp.GetParameterInput(« @filter_place_IsReleased », this._FilterPlace.IsReleased));
ParamCollection.Add(dp.GetParameterInput(« @filter_place_IsDeleted », this._FilterPlace.IsDeleted));
ParamCollection.Add(dp.GetParameterInput(« @filter_place_StartDate », this._FilterPlace.StartDate, DbType.DateTime));
ParamCollection.Add(dp.GetParameterInput(« @filter_place_EndDate », this._FilterPlace.EndDate, DbType.DateTime));
…ect…
Le premier parametre défini et l’id_op que l’on avait stockée avant et on remarquera que le filtre stockée dans notre fonction appelante est retransmis dans cette collection de parametres. Il ne reste plus alors qu à appeler notre procédure stockée de la facon qui nous interesse :
//gives an output in the trace of the execution of this stored procedure
HttpTrace(storedProcedure, this.Id_op.ToString());
//execution of the code
switch (Id_op)
{
case E_Operation.SaveCourse:
case E_Operation.SavePlace:
case E_Operation.DeleteCourse:
case E_Operation.DeletePlace:
o = dp.ExecuteNonQuery(ApplicationConnection, storedProcedure, ParamCollection);
break;
case E_Operation.GetCourse:
case E_Operation.GetCourses:
case E_Operation.GetPlace:
case E_Operation.GetPlaces:
o = dp.ExecuteReader(ApplicationConnection, storedProcedure, ParamCollection);
break;
default:
break;
}
return o;
}
Notre DAL nous permettant au choix : ExecuteNonQuery,ExecuteReader,ExecuteDataset,ExecuteQueryParamsOut,ExecuteScalar.
Dans le cas de notre exmple, ce qui est retourné est un Idatareader. Sur lequel nous allons tourner, afin de créer notre liste d’endroits, la fonction create Place permettant de décortiquer ce datareader est de créer un objet Place directement à partir de celui ci :
private Place createPlace(IDataReader dr)
{
Place p = new Place();
p.ID = int.Parse(dr["PlaceId"].ToString());
p.Name = dr["PlaceName"].ToString();
p.Description = dr["PlaceDescription"].ToString();
p.IsDeleted = bool.Parse(dr["PlaceIsDeleted"].ToString());
p.IsReleased = bool.Parse(dr["PlaceIsReleased"].ToString());
p.StartDate = DateTime.Parse(dr["PlaceStartDate"].ToString());
p.EndDate = DateTime.Parse(dr["PlaceEndDate"].ToString());
return p;
}
Si des traitements devaient se faire sur les données provenant de notre base de données c’est ici qu’ils devraient avoir lieu.
Conclusion
On a aborder ici de maniére trés générale la construction d’un repository pour une procédure stockée. Il y a evidemment beaucoup de variantes possibles en fonction des besoins et de l’architecture. L’intérêt de cette méthode est je trouve de ne plus avoir qu’une seule procédure stockée en entrée de base de données, ce qui permet d’unifier aussi l’appel à la base et ainsi de restreindre le nombre de fois ou on aura à réutiliser le même interfacage pour les le même pool de données. Tout ceci ne vient que de mon experience personnelle et des discussions que j’ai pu avoi ici et lá. Je serais donc ravi de connaitre votre avis sur cette méthode ( si vous en avez un 
)…
Dans de prochains articles j’essaierais de détailler un peu plus le fonctionnement de ma DAL, cela permettra en autres d’expliquer la présence de cete exception CastValeur dans mon code.
public class SqlServerCoursesRepository : SQLRepository, IRepositoryCourses
{
}
