Aujourd'hui, petite présentation de quelques fonctionnalités intéressantes SQL : sp_prepare, sp_prepexec, sp_execute et sp_unprepare.
L'idée générale est d'utiliser ces api pour :
- Préparer une commande (avec sp_prepare, voir sp_prepexec)
- Exécuter cette commande avec différents paramètres (avec sp_execute)
- Décharger cette commande (avec sp_unprepare)
Le but de ces quelques procédures stockées systèmes étant d'optimiser un lot de requêtes exécutées sur SQL, dont la commande est identique.
Pour informations c'est ce qui se passe lorsque vous utilisez SSIS, dans certains cas.
La seule difficulté (qui n'en est pas une) consiste à récupérer le handle généré par la commande sp_prepare et l'utiliser avec sp_execute, ainsi qu'avec sp_unprepare
Mais voyons ce que cela donne, en pratique (sur une base de données AdventureWorks) :
Tout d'abord, préparer la commande :
Declare @handle int
exec sp_prepare @handle output,
N'@Name nvarchar(50), @RowGuid uniqueidentifier, @ModifiedDate Datetime',
N'Insert into Production.ProductCategory (Name, rowguid, ModifiedDate)
Values (@Name, @RowGuid, @ModifiedDate)';
La commande est prète, on peut effectuer une insertion en masse :
exec sp_execute @handle, @Name=N'Catégorie153',
@RowGuid='34661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie154',
@RowGuid='54661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie155',
@RowGuid='A8661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie156',
@RowGuid='B8661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie157',
@RowGuid='C8661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie158',
@RowGuid='D8661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie159',
@RowGuid='E8661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie160',
@RowGuid='FF661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
Et libérer le handle (sp_unprepare)
exec sp_unprepare @handle;
Note :
On peut aussi utiliser la commande sp_prepexec qui va cumuler dans la première instruction sp_prepare et sp_execute. On peut donc aussi écrire :
declare @handle int;
exec sp_prepexec @p1 output,N'@Name nvarchar(50),@RowGuid uniqueidentifier,
@ModifiedDate datetime',
N'Insert into Production.ProductCategory (Name, rowguid, ModifiedDate)
Values (@Name, @RowGuid, @ModifiedDate)',
@Name=N'Catégorie1',@RowGuid='D8F1139C-DC4E-4E3E-91DB-C2945C9DBB65',
@ModifiedDate='2008-01-16 10:35:00:113'
Vous poursuivez ensuite en utilisant sp_execute normalement.
Attention, l'utilisation de sp_prepare ne prend tout son intéret que si vous effectuez une série de requête identique assez conséquente. Inutile de passer par là pour ne faire qu'une ou deux insertions !
Et les procédures stockées !!!
Bien, au vu de ce que nous venons de voir, je vois poindre au loin, une remarque que vous allez certainement vous faire ... "Mais ... pourquoi ne pas utiliser les procédures stockées !!!???"
Alors oui, effectivement, les procédures stockées, c'est mieux, plus élégant, et suremment beaucoup plus répandu et simple. Il n'empèche que vous pouvez trés bien être amenés à faire une insertion en masse en base de données, sans avoir pour autant une proc. stock. à disposition (oui ça arrive aussi !) :)
Intégration dans .NET avec tout ça ?
Et bien vous allez voir que c'est "on ne peut plus simple". En effet, vous possédez sur l'objet SqlCommand une méthode qui s'appelle SqlCommand.Prepare() qui va avoir le bon gout de préparer votre commande, en utilisant sp_prepexec.
Voici un morceau de code qui reprend ce que nous venons de faire, mais en .NET :
using (SqlConnection myConn =
new SqlConnection(
@"Data Source=.\sql2005;Integrated Security=true;Initial Catalog=AdventureWorks"))
{
SqlCommand myComm =
new SqlCommand("Insert into Production.ProductCategory (Name, rowguid, ModifiedDate) " +
"Values (@Name, @RowGuid, @ModifiedDate)", myConn);
myComm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 50);
myComm.Parameters.Add("@RowGuid", System.Data.SqlDbType.UniqueIdentifier);
myComm.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime);
myComm.Parameters["@Name"].Value = "Catégorie1" ;
myComm.Parameters["@RowGuid"].Value = Guid.NewGuid();
myComm.Parameters["@ModifiedDate"].Value = DateTime.Now;
myConn.Open();
myComm.Prepare();
myComm.ExecuteNonQuery();
for (int i = 2; i <= 10; i++)
{
myComm.Parameters["@Name"].Value = "Catégorie" + i.ToString();
myComm.Parameters["@RowGuid"].Value = Guid.NewGuid();
myComm.Parameters["@ModifiedDate"].Value = DateTime.Now;
myComm.ExecuteNonQuery();
}
myConn.Close();
}
On ne peut plus simple !
Happy coding !
Nouvel article !
Je vous propose de retrouver un nouvel article sur un sujet que j'ai commencé à étudier sérieusement :) Microsoft Synchronization Framework.
Voici un deuxième article qui aborde un peu plus en profondeur le sujet : Nous allons essayer de construire, sans passer par l'assistant VS 2008, un projet de synchronisation.
Vous verrez que nous passerons d'abord par l'utilisation d'un objet SqlSyncAdapterBuilder, puis nous essaierons de nous en passer dans un deuxième temps.
Pour terminer, il sera question aussi de personnalisation de synchronisation, et la façon de la mettre en oeuvre rapidement et efficacement !
Retrouver l'article complet sur le site TechHeadBrothers :
Chapitre II : Synchronisation manuelle et personnalisation
Vous pouvez aussi retrouver cet article au format Word sur le site Bewise :
Chapitre II : Synchronisation manuelle et personnalisation (Word)
Bonne lecture !
Une petite nouveauté apportée par Sql Serveur 2008 en ce jour de l'an !
Et là j'avoue que celle là, elle m'a franchement fait plasir :)
Vous connaissez peut être déjà le générateur de scripts intégré à l'outil Management Sql Server, qui permet de générer le script de création d'une table par exemple.
Là n'est pas la nouveauté, mais l'adjonction à ce générateur de script d'une option permettant de générer un script de données, oui.
Alors oui je sais, on peut faire ce genre de choses à la main, ou depuis Visual Studio Team Database, ou autre... mais je trouve que l'avoir rajouter dans Sql Serveur 2008, c'est vraiment un plus.... qu'ils auraient dû implémenter depuis des lustes !
Démonstration en images :
Voilà une première image du générateur de scripts, façon Sql Serveur 2005 :
Et le même, version Sql Serveur 2008. Notez l'option Generate Datas :
Et le résultat, en image :
Voilà, bon script !
Juste un petit post pour vous souhaiter à tous une bonne année !
Et rendez vous de suite pour un post en rapport avec ce qui vous intéresse plus que mes voeux, Sql Serveur 2008 :)