un. Avant-propos :
Une procédure stockée est un ensemble d'instructions SQL conçues pour exécuter des fonctions spécifiques, qui sont compilées et stockées dans la base de données. L'utilisateur exécute une procédure stockée en spécifiant son nom et en donnant des paramètres (si la procédure stockée a des paramètres). Les procédures stockées constituent un objet important dans la base de données et toute application de base de données bien conçue doit utiliser des procédures stockées. En général, les procédures stockées présentent les avantages suivants :
◆ Les procédures stockées permettent une programmation de composants standard
◆ Les procédures stockées peuvent atteindre une vitesse d'exécution plus rapide
◆ Les procédures stockées peuvent réduire le trafic réseau
◆ Les procédures stockées peuvent être pleinement utilisées comme mécanisme de sécurité
L'auteur de cet article présentera vous explique l'application de procédures stockées dans les applications de base de données .NET et comment les utiliser conjointement avec l'objet SqlDataAdapter, l'objet DataSet, etc. dans ADO.NET pour améliorer les performances globales des applications de base de données .NET.
deux. Configuration système requise :
Outils de développement : Visual Studio.NET
Système de gestion de base de données : SQL Server 2000 (y compris la base de données Pubs utilisée dans l'exemple de programme)
3. Créer une procédure stockée simple :
je vais vous présenter ici comment utiliser Visual Studio.NET IDE pour créer une procédure stockée. Il est très simple et intuitif de créer des procédures stockées à l'aide de Visual Studio.NET IDE. Tant que vous accédez à la base de données Pubs dans l'explorateur de serveur et développez le nœud, vous trouverez divers objets de base de données, notamment des procédures stockées, comme le montre la figure 1. Montrer.
Cliquez avec le bouton droit sur le nœud de procédure stockée pour faire apparaître un menu contenant la commande "Nouvelle procédure stockée". Après avoir créé une nouvelle procédure stockée, le modèle de code ci-dessous apparaîtra dans la fenêtre d'édition de code de l'EDI :
CRÉER UNE PROCÉDURE dbo.StoredProcedure1
/*
(
@parameter1 type de données = valeur par défaut,
@paramètre2, type de données OUTPUT )
*/
COMME
/* METTRE NOCOUNT SUR */
RETOUR
Le modèle de code ci-dessus est conforme aux règles de syntaxe simplifiées pour la création de procédures stockées. Les règles de syntaxe complètes sont les suivantes :
CREATE PROC [ EDURE ] nom_procédure [ ;
[ { @parameter data_type }
[ VARIABLE ] [ = par défaut ] [ SORTIE ]
] [ ,...n ]
[ AVEC
{ RECOMPILE | CHIFFREMENT | RECOMPILE , CHIFFREMENT } ]
[ POUR LA RÉPLICATION ]
AS instruction_sql [ ...n ]
En raison de contraintes d'espace, la signification de chaque paramètre ne sera pas présentée ici. Les lecteurs intéressés peuvent se référer aux informations sur le système de gestion de base de données SQL Server 2000.
Ci-dessous, je présenterai brièvement chaque composant grammatical de ce modèle de code. L'instruction CREATE PROCEDURE crée une procédure stockée, suivie du nom de la procédure stockée. Les composants entre "/*...*/" sont les paramètres de la procédure stockée, qui peuvent inclure des paramètres d'entrée et des paramètres de sortie. Le contenu qui suit le mot clé AS constitue le corps principal de la procédure stockée, c'est-à-dire n'importe quel nombre et type d'instructions SQL contenues dans la procédure stockée. Le mot clé RETURN indique la fin de la procédure stockée et peut renvoyer une valeur d'état entière à l'appelant. Créons une procédure stockée simple sans paramètres et utilisons-la :
CREATE PROCEDURE dbo.up_GetPublisherInfo
COMME
SELECT pub_id, pub_name, ville, état, pays
DES éditeurs
RETOUR
Après avoir créé la procédure stockée ci-dessus, enregistrez-la. Une fois enregistré, le nœud correspondant à la procédure stockée apparaîtra dans l'Explorateur de serveur. Veuillez également noter que le mot-clé CREATE dans la fenêtre d'édition de code a été remplacé par le mot-clé ALTER, qui est utilisé pour modifier toutes les procédures stockées existantes. Pour exécuter la procédure stockée ci-dessus, cliquez simplement sur son nœud et sélectionnez « Exécuter la procédure stockée » dans le menu contextuel du clic droit. Le résultat de l'opération est le suivant :
Quatre. Créer une procédure stockée avec des paramètres :
ci-dessus, nous avons créé une procédure stockée simple sans paramètres, mais dans les applications réelles, de nombreuses procédures stockées avec des paramètres sont souvent utilisées. Les procédures stockées avec des paramètres sont généralement utilisées pour mettre à jour des données ou insérer des données. Ci-dessous, nous pouvons utiliser la même méthode d'opération pour créer une procédure stockée avec des paramètres :
CRÉER UNE PROCÉDURE dbo.up_UpdatePublisherInfo
(
@pub_id char (4),
@pub_namevarchar (40),
@cityvarchar(20),
@state char (2),
@country varchar (30)
)
COMME
MISE À JOUR des éditeurs
SET nom_pub = @nom_pub, ville = @ville, état = @état,
pays = @pays
OÙ ( pub_id = @pub_id )
RETOUR
Dans le code ci-dessus pour créer une procédure stockée, nous déclarons les variables-paramètres locaux de la procédure stockée en ajoutant un signe "@" avant le nom. Nous déclarons également le type de chaque paramètre et déterminons la valeur de direction de chaque paramètre, cela. est, indique si le paramètre est de type entrée ou type de sortie ou type d'entrée-sortie ou type de valeur de retour. Les utilisateurs peuvent appeler la procédure stockée via le nom de la procédure stockée correspondante et les paramètres corrects et valides. Vous pouvez également ajouter des paramètres de sortie aux paramètres à l'aide du mot-clé OUTPUT. Veuillez vous référer aux règles de syntaxe ci-dessus pour les méthodes spécifiques. Les paramètres de sortie peuvent renvoyer des informations pertinentes à l'appelant.
La procédure stockée ci-dessus peut mettre à jour les informations sur l'éditeur correspondant dans la table des éditeurs. Vous pouvez l'exécuter en cliquant sur le nœud de la procédure stockée et en sélectionnant "Exécuter la procédure stockée" dans le menu contextuel du clic droit. Une fois exécuté, une boîte de dialogue permettant de saisir les informations sur l'éditeur apparaîtra dans l'EDI (comme le montre la figure 3). Remplissez les informations de mise à jour correctes et valides dans cette boîte de dialogue. Notez que la valeur de pub_id doit exister dans la table d'origine, puis cliquez sur le bouton "OK" pour mettre à jour les données.
cinq. Créez une application de base de données avec une procédure stockée simple :
nous utiliserons ensuite la procédure stockée ci-dessus sans paramètres pour créer une application de base de données, qui utilise également l'objet SqlDataAdapter et l'objet DataSet dans ADO.NET. L'objet SqlDataAdapter sert de pont entre la base de données SQL Server et l'objet DataSet pour connecter les deux. L'objet SqlDataAdapter contient deux méthodes couramment utilisées : la méthode Fill() et la méthode Update(). La méthode Fill() peut obtenir les données correspondantes de la base de données et les remplir dans l'objet DataSet, et la méthode Update(), comme son nom l'indique, met à jour l'ensemble de données. Avant d'appeler la méthode Fill(), nous devons définir la propriété SelectCommand de l'objet SqlDataAdapter, qui est en fait un objet SqlCommand. La propriété SelectCommand contient des instructions SQL valides et peut obtenir les données correspondantes de la base de données et les remplir dans l'objet DataSet.
Tout d’abord, nous créons une application Windows Forms, le langage de programmation est C#. Après avoir créé un nouveau projet dans Visual Studio.NET, ajoutez une nouvelle classe au projet : la classe Publishers, qui encapsule la logique métier de connexion à la base de données principale et d'obtention de l'objet ensemble de données. Les étapes sont les suivantes :
1. Ajoutez les références d’espace de noms nécessaires : à l’aide de System.Data.SqlClient
2. Ajoutez les variables nécessaires suivantes à cette classe :
private SqlConnection cnPubs ;
cmdPubs SqlCommand privés ;
daPubs SqlDataAdapter privés ;
DataSet dsPubs privé ;
3. Dans le constructeur de cette classe, effectuez la connexion à la base de données backend et obtenez l'objet SqlDataAdapter et une autre logique métier :
public Publishers()
{
essayer
{
//Créer un objet de connexion à la base de données
cnPubs = new SqlConnection( "server=localhost;integrated security=true;database=pubs" );
//Créez un objet SqlCommand et spécifiez son type de commande en tant que procédure stockée
cmdPubs = new SqlCommand();
cmdPubs.Connection = cnPubs;
cmdPubs.CommandType = CommandType.StoredProcedure ;
cmdPubs.CommandText = "up_GetPublisherInfo";
//Créez un objet SqlDataAdapter et définissez sa propriété SelectCommand sur l'objet SqlCommand ci-dessus
daPubs = new SqlDataAdapter();
daPubs.SelectCommand = cmdPubs;
//Créer un objet DataSet
dsPubs = nouveau DataSet();
}
capture (Exception) {}
}
4. Enfin, une méthode GetPublisherInfo() est fournie pour cette classe, qui remplit l'objet DataSet avec l'objet SqlDataAdapter et renvoie l'objet DataSet rempli. La méthode est la suivante (il est à noter que l'objet SqlDataAdapter ouvrira implicitement la connexion à la base de données et). ouvrez implicitement la connexion à la base de données après avoir obtenu les données. Fermez formellement la connexion, ce qui signifie que l'objet DataSet fonctionne en mode non-connexion et lorsque vous ouvrez explicitement la connexion à la base de données et obtenez les données, l'objet SqlDataAdapter ne fermera pas la connexion) :
DataSet public GetPublisherInfo().
{
// Appel de la méthode Fill() de l'objet SqlDataAdapter et renvoie l'objet ensemble de données
daPubs.Fill(dsPubs);
renvoyer dsPubs ;
}
Après avoir terminé la conception de la classe Publishers, nous ajoutons un contrôle DataGrid au formulaire principal et l'utilisons pour afficher les données dans l'objet DataSet. Ajoutez d’abord les variables membres suivantes à la classe de formulaire principale :
pubs d’éditeurs privés ;
DataSet privé ds ;
Après cela, modifiez le constructeur de la classe de formulaire principale comme suit :
formulaire public1()
{
//
// Requis pour la prise en charge de Windows Forms Designer
//
InitializeComponent();
//
// TODO : Ajouter n'importe quel code constructeur après l'appel InitializeComponent
// pubs = nouveaux éditeurs();
ds = pubs.GetPublisherInfo();
dataGrid1.DataSource = ds.Tables[0];
}
De cette façon, dès le démarrage de l'application, les données correspondantes obtenues de la base de données Pubs à l'aide de la procédure stockée ci-dessus sans paramètres seront affichées dans le contrôle DataGrid du formulaire principal. Le schéma d'exécution du programme est le suivant :
6. Créer une application de base de données avec une procédure stockée avec paramètres :
Ci-dessus, nous avons créé une application avec une procédure stockée sans paramètres, et nous allons maintenant créer une application de base de données plus complexe. Dans les applications de base de données réelles, nous avons souvent besoin d'obtenir des données et de mettre à jour, d'insérer ou de supprimer des données. À ce stade, nous devons utiliser des procédures stockées avec des paramètres. En même temps, lors de l'utilisation de l'objet SqlDataAdapter, nous l'appellerons Update(). méthode. La méthode Update() terminera automatiquement l'opération correspondante en fonction des modifications apportées à chaque enregistrement de l'objet DataTable dans l'objet DataSet. L'objet SqlDataAdapter contient également des propriétés telles que UpdateCommand, InsertCommand, DeleteCommand, etc. Ces propriétés sont en réalité des objets SqlCommand. La méthode Update() sélectionne les attributs appropriés en fonction du type d'opération.
Lorsque nous utilisons des procédures stockées avec des paramètres pour créer des applications de base de données, nous utilisons généralement la classe SqlParameter, qui encapsule diverses propriétés et méthodes liées aux paramètres SQL. Les propriétés incluent ParameterName, SqlDBType, Direction, Size, Value, SourceColumn et SourceVersion, etc. Parmi eux, ParameterName, SqlDBType, Direction, Size et d'autres attributs sont utilisés pour correspondre aux paramètres définis dans la procédure stockée. Par exemple, l'objet SqlParameter défini ci-dessous est utilisé pour faire correspondre le paramètre "@pub_id " dans la procédure stockée up_UpdatePublisherInfo précédemment définie.
SqlParameter updParam = new SqlParameter( "@pub_id", SqlDbType.Char, 4 );
Dans la définition ci-dessus, bien que l'attribut Direction ne soit pas explicitement donné, sa valeur par défaut est Input, il répond donc à nos besoins. Et si la propriété Direction d'un objet SqlParameter est InputOutput ou Output ou ReturnValue, sa propriété Direction doit être clairement indiquée. Par exemple, le code suivant déclare clairement que la propriété Direction d'un objet SqlParameter est Output.
oParam.Direction = ParameterDirection.Output ;
La propriété SourceColumn est utilisée pour faire correspondre l'objet DataColumn dans un objet DataTable. Cette correspondance peut implicitement importer l'objet SqlParameter requis lorsque la méthode Update() est appelée pour mettre à jour l'objet DataTable. Si cette propriété n'est pas déclarée lors de la définition, vous devez alors indiquer explicitement la propriété SourceColumn de l'objet SqlParameter dans votre code.
La valeur par défaut de la propriété SourceVersion est la valeur actuelle dans le champ correspondant de l'objet DataRow, qui est la valeur à mettre à jour dans la base de données. Bien entendu, la propriété SourceVersion peut également pointer vers la valeur d'origine dans le champ correspondant de l'objet DataRow, c'est-à-dire la valeur initiale obtenue de la base de données. Dans un système de traitement des transactions de base de données, la question de la synchronisation des données est très importante. Créons une procédure stockée capable de détecter la synchronisation des données.
CRÉER UNE PROCÉDURE dbo.up_UpdatePublisherName
(
@pub_id char(4),
@pub_namevarchar(40),
@Original_pub_namevarchar(40)
)
COMME
s'il existe (sélectionnez pub_id
des éditeurs
où (pub_id = @pub_id) ET (pub_name = @Original_pub_name))
Commencer
MISE À JOUR des éditeurs SET pub_name = @pub_name
OÙ (pub_id = @pub_id)
Fin
RETOUR
Ensuite, nous appelons la procédure stockée dans l'application ci-dessus pour mettre à jour le nom de l'éditeur. Tout d’abord, améliorez sa classe de logique métier-classe d’éditeurs basée sur l’application d’origine :
1. Ajoutez un nouvel objet SqlCommand qui peut être utilisé comme propriété UpdateCommand de l'objet SqlDataAdapter :
SqlCommand privé cmdUpdPubs ;
2. Mettez à jour la fonction constructeur Publishers() de cette classe pour ajouter ce qui suit :
// Créez un autre objet SqlCommand qui fait référence à la procédure stockée qui met à jour le nom de l'éditeur
cmdUpdPubs = new SqlCommand();
cmdUpdPubs.Connection = cnPubs;
cmdUpdPubs.CommandType = CommandType.StoredProcedure ;
cmdUpdPubs.CommandText = "up_UpdatePublisherName";
//Ajouter les paramètres nécessaires à l'objet SqlCommand ci-dessus
cmdUpdPubs.Parameters.Add( "@pub_id", SqlDbType.Char, 4, "pub_id" );
cmdUpdPubs.Parameters.Add( "@pub_name", SqlDbType.VarChar, 40, "pub_name" );
SqlParameter updParam = nouveau SqlParameter
( "@Original_pub_name", SqlDbType.VarChar, 40, "pub_name" );
updParam.SourceVersion = DataRowVersion.Original ;
cmdUpdPubs.Parameters.Add( updParam );
3. Spécifiez la propriété UpdateCommand de l'objet SqlDataAdapter en tant qu'objet SqlCommand défini ci-dessus :
daPubs.UpdateCommand = cmdUpdPubs;
4. Ajouter la méthode UpdatePublisherName() :
public void UpdatePublisherName (DataSet dsChanges)
{
// Mettre à jour toutes les modifications
daPubs.Update(dsChanges);
}
Une fois la classe de logique métier de l'application terminée, ajoutez un bouton nommé « Mettre à jour l'ensemble de données » sur le formulaire principal et ajoutez la fonction de réponse d'événement du bouton comme suit :
private void button1_Click(object sender, System.EventArgs e) { if ( ds.HasChanges() ) { pubs.UpdatePublisherName( ds.GetChanges() ); ds.Clear(); ds = pubs.GetPublisherInfo( } }
Jusqu'à présent, la classe de logique métier et la classe de formulaire principale de l'application ont été mises à jour. L'application peut désormais mettre à jour le contenu pertinent dans la base de données en fonction des modifications apportées par l'utilisateur.
Sept. Résumé :
Cet article vous présente les connaissances de base des procédures stockées et comment combiner des objets SqlDataAdapter, des objets DataSet, etc. pour créer des applications basées sur les données dans des applications de base de données .NET. Dans cet article, nous avons utilisé deux types de procédures stockées : l'une est une procédure stockée simple sans paramètres, relativement simple à utiliser ; l'autre est une procédure stockée avec paramètres, et vous devez appeler ce type de procédure stockée Apply. à l'objet SqlParameter. Dans le même temps, il n'est pas difficile de constater que l'encapsulation de la logique métier de mise à jour des données dans une procédure stockée est une bonne méthode de conception, qui peut améliorer la gérabilité, l'évolutivité et la sécurité de la base de données de l'application. De même, la logique métier d'insertion et de suppression de données peut être encapsulée dans des procédures stockées et utilisée dans les applications de la même manière. Enfin, j’espère que cet article sera d’une grande aide pour tout le monde.