Insertion, mise à jour et suppression de données avec SqlDataSource (C#)

par Scott Mitchell

Télécharger le PDF

Dans les tutoriels précédents, nous avons appris comment le contrôle ObjectDataSource autorisait l’insertion, la mise à jour et la suppression de données. Le contrôle SqlDataSource prend en charge les mêmes opérations, mais l’approche est différente, et ce tutoriel montre comment configurer sqlDataSource pour insérer, mettre à jour et supprimer des données.

Introduction

Comme indiqué dans Vue d’ensemble de l’insertion, de la mise à jour et de la suppression, le contrôle GridView fournit des fonctionnalités intégrées de mise à jour et de suppression, tandis que les contrôles DetailsView et FormView incluent la prise en charge de l’insertion ainsi que des fonctionnalités de modification et de suppression. Ces fonctionnalités de modification des données peuvent être directement connectées à un contrôle de source de données sans qu’une ligne de code ait besoin d’être écrite. Vue d’ensemble de l’insertion, de la mise à jour et de la suppression examinée à l’aide de ObjectDataSource pour faciliter l’insertion, la mise à jour et la suppression avec les contrôles GridView, DetailsView et FormView. Vous pouvez également utiliser SqlDataSource à la place de ObjectDataSource.

Rappelez-vous que pour prendre en charge l’insertion, la mise à jour et la suppression, avec ObjectDataSource, nous avions besoin de spécifier les méthodes de couche objet à appeler pour effectuer l’action d’insertion, de mise à jour ou de suppression. Avec SqlDataSource, nous devons fournir des INSERTinstructions SQL , UPDATEet DELETE (ou procédures stockées) à exécuter. Comme nous le verrons dans ce tutoriel, ces instructions peuvent être créées manuellement ou générées automatiquement par l’Assistant Configuration de la source de données de SqlDataSource.

Étape 1 : spécification des instructions INSERT, UPDATE et DELETE

Comme nous l’avons vu dans les deux derniers tutoriels, pour récupérer des données à partir d’un contrôle SqlDataSource, nous devons définir deux propriétés :

1. ConnectionString, qui spécifie la base de données à laquelle envoyer la requête, et
2. SelectCommand, qui spécifie l’instruction SQL ad hoc ou le nom de procédure stockée à exécuter pour retourner les résultats.

Pour SelectCommand les valeurs avec des paramètres, les valeurs de paramètre sont spécifiées via la collection s SelectParameters SqlDataSource et peuvent inclure des valeurs codées en dur, des valeurs sources de paramètres courantes (champs de chaîne de requêtes, variables de session, valeurs de contrôle web, etc.) ou peuvent être attribuées par programmation. Lorsque la méthode s du Select() contrôle SqlDataSource est appelée par programmation ou automatiquement à partir d’un contrôle Web de données, une connexion à la base de données est établie, les valeurs de paramètre sont attribuées à la requête et la commande est supprimée vers la base de données. Les résultats sont ensuite retournés sous la forme d’un DataSet ou d’un DataReader, en fonction de la valeur de la propriété s du DataSourceMode contrôle.

En plus de sélectionner des données, le contrôle SqlDataSource peut être utilisé pour insérer, mettre à jour et supprimer des données en fournissant INSERTdes instructions SQL , UPDATEet DELETE de la même manière. Attribuez simplement aux InsertCommandpropriétés , UpdateCommandet DeleteCommand les INSERTinstructions SQL , UPDATEet DELETE à exécuter. Si les instructions ont des paramètres (comme c’est le cas le plus souvent), incluez-les dans les InsertParameterscollections , UpdateParameterset DeleteParameters .

Une fois qu’une InsertCommandvaleur , UpdateCommandou DeleteCommand a été spécifiée, l’option Activer l’insertion, Activer la modification ou Activer la suppression dans la balise active du contrôle Web de données correspondantes devient disponible. Pour illustrer cela, prenons un exemple à partir de la Querying.aspx page que nous avons créée dans le didacticiel Interrogation des données avec le contrôle SqlDataSource et augmentons-le pour inclure des fonctionnalités de suppression.

Commencez par ouvrir les InsertUpdateDelete.aspx pages et Querying.aspx à partir du SqlDataSource dossier. Dans le Designer de la Querying.aspx page, sélectionnez SqlDataSource et GridView dans le premier exemple (ProductsDataSourceles contrôles et GridView1 ). Après avoir sélectionné les deux contrôles, accédez au menu Modifier et choisissez Copier (ou appuyez simplement sur Ctrl+C). Ensuite, accédez au Designer de InsertUpdateDelete.aspx et collez les contrôles. Une fois que vous avez déplacé les deux contrôles vers InsertUpdateDelete.aspx, testez la page dans un navigateur. Vous devez voir les valeurs des ProductIDcolonnes , ProductNameet UnitPrice pour tous les enregistrements de la Products table de base de données.

Figure 1 : Tous les produits sont répertoriés, commandés par ProductID (cliquer pour afficher l’image en taille réelle)

Ajout des propriétés DeleteCommand et DeleteParameters de SqlDataSource

À ce stade, nous avons un SqlDataSource qui retourne simplement tous les enregistrements de la Products table et un GridView qui affiche ces données. Notre objectif est d’étendre cet exemple pour permettre à l’utilisateur de supprimer des produits via GridView. Pour ce faire, nous devons spécifier des valeurs pour les propriétés et DeleteParameters les propriétés du DeleteCommand contrôle SqlDataSource, puis configurer GridView pour prendre en charge la suppression.

Les DeleteCommand propriétés et DeleteParameters peuvent être spécifiées de plusieurs façons :

• Via la syntaxe déclarative
• À partir du Fenêtre Propriétés dans le Designer
• À partir de l’écran Spécifier une instruction SQL personnalisée ou une procédure stockée dans l’Assistant Configurer la source de données
• Via le bouton Avancé de l’écran Spécifier les colonnes d’une table d’affichage de l’Assistant Configurer la source de données, qui génère en fait automatiquement l’instruction SQL et la DELETE collection de paramètres utilisées dans les DeleteCommand propriétés et DeleteParameters

Nous allons examiner comment créer automatiquement l’instruction à l’étape DELETE 2. Pour l’instant, utilisons le Fenêtre Propriétés dans le Designer, bien que l’Assistant Configurer la source de données ou l’option de syntaxe déclarative fonctionne tout aussi bien.

À partir du Designer dans InsertUpdateDelete.aspx, cliquez sur SqlDataSourceProductsDataSource, puis affichez le Fenêtre Propriétés (dans le menu Affichage, choisissez Fenêtre Propriétés, ou appuyez simplement sur F4). Sélectionnez la propriété DeleteQuery, qui fera apparaître un ensemble de points de suspension.

Figure 2 : Sélectionnez la propriété DeleteQuery dans la fenêtre Propriétés

Cliquez sur les points de suspension de la propriété DeleteQuery pour afficher la boîte de dialogue Commande et paramètre Rédacteur (voir figure 3). Dans cette boîte de dialogue, vous pouvez spécifier l’instruction DELETE SQL et spécifier les paramètres. Entrez la requête suivante dans la DELETE zone de texte de commande (manuellement ou à l’aide du Générateur de requêtes, si vous préférez) :

DELETE FROM Products
WHERE ProductID = @ProductID

Ensuite, cliquez sur le bouton Actualiser les paramètres pour ajouter le @ProductID paramètre à la liste des paramètres ci-dessous.

@ProductID ajouté à la liste des paramètres de commande DELETE. » />

Figure 3 : Sélectionnez la propriété DeleteQuery dans la fenêtre Propriétés (cliquez pour afficher l’image en taille réelle)

Ne fournissez pas de valeur pour ce paramètre (laissez sa source de paramètre sur Aucun ). Une fois que nous avons ajouté la prise en charge de la suppression à GridView, GridView fournit automatiquement cette valeur de paramètre, en utilisant la valeur de sa DataKeys collection pour la ligne dont le bouton Supprimer a été cliqué.

Si le nom et DataKeyNames la valeur du paramètre ne correspondent pas, GridView ne peut pas affecter automatiquement au paramètre la valeur de la DataKeys collection.

Après avoir entré les informations relatives à la suppression dans la boîte de dialogue Commande et paramètre Rédacteur, cliquez sur OK et accédez à la vue Source pour examiner le balisage déclaratif résultant :

<asp:SqlDataSource ID="ProductsDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
    SelectCommand=
        "SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products]"
    DeleteCommand="DELETE FROM Products WHERE ProductID = @ProductID">
    <DeleteParameters>
        <asp:Parameter Name="ProductID" />
    </DeleteParameters>
</asp:SqlDataSource>

Notez l’ajout de la DeleteCommand propriété ainsi que de la <DeleteParameters> section et de l’objet Parameter nommé productID.

Configuration de GridView pour la suppression

Une fois la DeleteCommand propriété ajoutée, la balise active GridView contient désormais l’option Activer la suppression. Allez-y et case activée cette case à cocher. Comme indiqué dans Une vue d’ensemble de l’insertion, de la mise à jour et de la suppression, GridView ajoute un CommandField avec sa ShowDeleteButton propriété définie sur true. Comme le montre la figure 4, lorsque la page est visitée via un navigateur, un bouton Supprimer est inclus. Testez cette page en supprimant certains produits.

Figure 4 : Chaque ligne GridView inclut désormais un bouton Supprimer (cliquer pour afficher l’image en taille réelle)

Lorsque vous cliquez sur un bouton Supprimer, une publication se produit, gridView affecte au paramètre la ProductID valeur de la collection pour la DataKeys ligne dont le bouton Supprimer a été cliqué, et appelle la méthode s Delete() SqlDataSource. Le contrôle SqlDataSource se connecte ensuite à la base de données et exécute l’instruction DELETE . GridView se relie ensuite à SqlDataSource, récupérant et affichant l’ensemble actuel de produits (qui n’inclut plus l’enregistrement qui vient de supprimer).

La figure 5 illustre cette interaction sous forme graphique. Reportez-vous au didacticiel Examen des événements associés à l’insertion, à la mise à jour et à la suppression pour une discussion plus détaillée sur la chaîne d’événements associée à l’insertion, à la mise à jour et à la suppression d’un contrôle web de données.

Figure 5 : Cliquer sur le bouton Supprimer dans GridView appelle la méthode s Delete() SqlDataSource

Étape 2 : Génération automatique des instructions INSERT, UPDATE et DELETE

À l’étape 1, INSERTUPDATEles instructions SQL , et DELETE peuvent être spécifiées par le biais de la syntaxe déclarative Fenêtre Propriétés ou du contrôle. Toutefois, cette approche exige que nous écrivions manuellement les instructions SQL à la main, ce qui peut être monotone et sujet aux erreurs. Heureusement, l’Assistant Configurer la source de données offre une option permettant de générer automatiquement les INSERTinstructions , UPDATEet DELETE lors de l’utilisation de l’écran Spécifier des colonnes à partir d’une table d’affichage.

Explorons cette option de génération automatique. Ajoutez un DetailsView au Designer dans et définissez sa ID propriété sur .InsertUpdateDelete.aspxManageProducts Ensuite, à partir de la balise active DetailsView, choisissez de créer une source de données et de créer une source SqlDataSource nommée ManageProductsDataSource.

Figure 6 : Créer un nom SqlDataSource ManageProductsDataSource (cliquer pour afficher l’image en taille réelle)

Dans l’Assistant Configuration de la source de données, choisissez d’utiliser le NORTHWINDConnectionString chaîne de connexion et cliquez sur Suivant. Dans l’écran Configurer l’instruction Select, laissez la case d’option Spécifier les colonnes d’une table ou d’une vue sélectionnée et sélectionnez la Products table dans la liste déroulante. Sélectionnez les ProductIDcolonnes , ProductName, UnitPriceet Discontinued dans la liste à cocher.

Figure 7 : À l’aide de la Products table, renvoyer les ProductIDcolonnes , ProductName, UnitPriceet Discontinued (cliquez pour afficher l’image en taille réelle)

Pour générer INSERTautomatiquement des instructions , UPDATEet DELETE en fonction de la table et des colonnes sélectionnées, cliquez sur le bouton Avancé et case activée la case à cocher Générer INSERT, UPDATEet DELETE les instructions .

Figure 8 : Cochez la case à cocher Générer INSERT, UPDATEet DELETE

La case à cocher Générer INSERTles instructions , UPDATEet DELETE ne peut être cochée que si la table sélectionnée a une clé primaire et si la colonne de clé primaire (ou les colonnes) est incluse dans la liste des colonnes retournées. La case à cocher Utiliser l’accès concurrentiel optimiste, qui devient sélectionnable une fois la case à cocher Générer INSERT, UPDATEet DELETE les instructions a été cochée, augmente les WHERE clauses dans les instructions et DELETE résultantes UPDATE pour fournir un contrôle d’accès concurrentiel optimiste. Pour l’instant, laissez cette case à cocher désactivée . Nous allons examiner l’accès concurrentiel optimiste avec le contrôle SqlDataSource dans le tutoriel suivant.

Après avoir coché la case Générer INSERTles instructions , UPDATEet DELETE , cliquez sur OK pour revenir à l’écran Configurer l’instruction Select, puis sur Suivant, puis sur Terminer, pour terminer l’Assistant Configuration de la source de données. À la fin de l’Assistant, Visual Studio ajoute BoundFields au DetailsView pour les ProductIDcolonnes , ProductNameet UnitPrice et un Champ CheckBoxField pour la Discontinued colonne. À partir de la balise active DetailsView, case activée l’option Activer la pagination afin que l’utilisateur qui visite cette page puisse parcourir les produits. Effacez également les propriétés DetailsView Width et Height .

Notez que la balise active a les options Activer l’insertion, Activer la modification et Activer la suppression disponibles. Cela est dû au fait que SqlDataSource contient des valeurs pour ses InsertCommand, UpdateCommandet DeleteCommand, comme le montre la syntaxe déclarative suivante :

<asp:DetailsView ID="ManageProducts" runat="server" AllowPaging="True"
    AutoGenerateRows="False" DataKeyNames="ProductID"
    DataSourceID="ManageProductsDataSource" EnableViewState="False">
    <Fields>
        <asp:BoundField DataField="ProductID" HeaderText="ProductID"
            InsertVisible="False" ReadOnly="True" SortExpression="ProductID" />
        <asp:BoundField DataField="ProductName" HeaderText="ProductName"
            SortExpression="ProductName" />
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"
            SortExpression="UnitPrice" />
        <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued"
            SortExpression="Discontinued" />
    </Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="ManageProductsDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
    DeleteCommand=
        "DELETE FROM [Products] WHERE [ProductID] = @ProductID"
    InsertCommand=
        "INSERT INTO [Products] ([ProductName], [UnitPrice], [Discontinued])
         VALUES (@ProductName, @UnitPrice, @Discontinued)"
    SelectCommand=
        "SELECT [ProductID], [ProductName], [UnitPrice], [Discontinued]
         FROM [Products]"
    UpdateCommand=
        "UPDATE [Products] SET [ProductName] = @ProductName,
         [UnitPrice] = @UnitPrice, [Discontinued] = @Discontinued
         WHERE [ProductID] = @ProductID">
    <DeleteParameters>
        <asp:Parameter Name="ProductID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="ProductName" Type="String" />
        <asp:Parameter Name="UnitPrice" Type="Decimal" />
        <asp:Parameter Name="Discontinued" Type="Boolean" />
        <asp:Parameter Name="ProductID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="ProductName" Type="String" />
        <asp:Parameter Name="UnitPrice" Type="Decimal" />
        <asp:Parameter Name="Discontinued" Type="Boolean" />
    </InsertParameters>
</asp:SqlDataSource>

Notez que les valeurs du contrôle SqlDataSource ont été automatiquement définies pour ses InsertCommandpropriétés , UpdateCommandet DeleteCommand . L’ensemble de colonnes référencées dans les InsertCommand propriétés et UpdateCommand est basé sur celles de l’instruction SELECT . Autrement dit, au lieu d’avoir chaque colonne Products dans et InsertCommandUpdateCommand, il n’y a que les colonnes spécifiées dans ( SelectCommand moins , qui est omis ProductIDcar il s’agit d’une IDENTITY colonne, dont la valeur ne peut pas être modifiée lors de la modification et qui est automatiquement affectée lors de l’insertion). En outre, pour chaque paramètre des InsertCommandpropriétés , UpdateCommandet DeleteCommand , il existe des paramètres correspondants dans les InsertParameterscollections , UpdateParameterset DeleteParameters .

Pour activer les fonctionnalités de modification de données de DetailsView, case activée les options Activer l’insertion, Activer la modification et Activer la suppression dans sa balise active. Cela ajoute un Champ de commande avec ses ShowInsertButtonpropriétés , ShowEditButtonet ShowDeleteButton définies sur true.

Visitez la page dans un navigateur et notez les boutons Modifier, Supprimer et Nouveau inclus dans detailsView. En cliquant sur le bouton Modifier, le DetailsView passe en mode édition, qui affiche chaque objet BoundField dont ReadOnly la propriété est définie false sur (valeur par défaut) en tant que TextBox, et checkBoxField en tant que case à cocher.

Figure 9 : Interface d’édition par défaut de DetailsView (cliquer pour afficher l’image en taille réelle)

De même, vous pouvez supprimer le produit actuellement sélectionné ou ajouter un nouveau produit au système. Étant donné que l’instruction InsertCommand fonctionne uniquement avec les ProductNamecolonnes , UnitPriceet Discontinued , les autres colonnes ont NULL leur valeur par défaut affectée par la base de données lors de l’insertion. Comme avec ObjectDataSource, s’il manque des InsertCommand colonnes de table de base de données qui n’autorisent NULL pas s et n’ont pas de valeur par défaut, une erreur SQL se produit lors de la tentative d’exécution de l’instruction INSERT .

En outre, gardez à l’esprit que pour la mise à jour et la suppression, DetailsView utilise la valeur du produit actuel, qui n’est DataKey présente que si la DataKeyNames propriété est configurée. Si la modification ou la suppression semble n’avoir aucun effet, vérifiez que la DataKeyNames propriété est définie.

Limitations de la génération automatique d’instructions SQL

Étant donné que l’option Générer INSERTdes instructions , UPDATEet DELETE est disponible uniquement lors de la sélection de colonnes à partir d’une table, pour les requêtes plus complexes, vous devez écrire vos propres INSERTinstructions , UPDATEet DELETE , comme nous l’avons fait à l’étape 1. En règle générale, les instructions SQL SELECT utilisent JOIN s pour ramener des données d’une ou plusieurs tables de choix à des fins d’affichage (par exemple, pour ramener le champ s de CategoryName la table lors de l’affichage Categories d’informations sur le produit). Dans le même temps, nous pouvons autoriser l’utilisateur à modifier, mettre à jour ou insérer des données dans la table principale (Productsdans ce cas).

Bien que les INSERTinstructions , UPDATEet DELETE puissent être entrées manuellement, tenez compte du conseil de gain de temps suivant. Configurez initialement SqlDataSource pour qu’il extrait les données uniquement de la Products table. Utilisez l’assistant Configurer la source de données de l’écran Spécifier des colonnes à partir d’une table ou d’une vue afin de pouvoir générer automatiquement les INSERTinstructions , UPDATEet DELETE . Ensuite, une fois l’Assistant terminé, choisissez de configurer SelectQuery à partir de l’Fenêtre Propriétés (ou, sinon, revenez à l’Assistant Configuration de la source de données, mais utilisez l’option Spécifier une instruction SQL personnalisée ou une procédure stockée). Mettez ensuite à jour l’instruction SELECT pour inclure la JOIN syntaxe. Cette technique offre les avantages de gain de temps des instructions SQL générées automatiquement et permet une instruction plus personnalisée SELECT .

Une autre limitation de la INSERTgénération automatique des instructions , UPDATEet DELETE est que les colonnes des INSERT instructions et UPDATE sont basées sur les colonnes retournées par l’instruction SELECT . Toutefois, nous devrons peut-être mettre à jour ou insérer plus ou moins de champs. Par exemple, dans l’exemple de l’étape 2, nous voulons peut-être que le UnitPrice champ BoundField soit en lecture seule. Dans ce cas, il ne doit pas apparaître dans le UpdateCommand. Vous pouvez également définir la valeur d’un champ de table qui n’apparaît pas dans GridView. Par exemple, lors de l’ajout d’un nouvel enregistrement, il se peut que la QuantityPerUnit valeur soit définie sur TODO .

Si de telles personnalisations sont requises, vous devez les effectuer manuellement, soit via la Fenêtre Propriétés, l’option Spécifier une instruction SQL personnalisée ou une procédure stockée dans l’Assistant, soit via la syntaxe déclarative.

Résumé

Pour que les contrôles Web de données utilisent leurs fonctionnalités intégrées d’insertion, de modification et de suppression, le contrôle de source de données auquel ils sont liés doit offrir ces fonctionnalités. Pour sqlDataSource, cela signifie que INSERTles instructions SQL , UPDATEet DELETE doivent être affectées aux InsertCommandpropriétés , UpdateCommandet DeleteCommand . Ces propriétés et les collections de paramètres correspondantes peuvent être ajoutées manuellement ou générées automatiquement via l’Assistant Configuration de la source de données. Dans ce tutoriel, nous avons examiné les deux techniques.

Nous avons examiné l’utilisation de l’accès concurrentiel optimiste avec ObjectDataSource dans le didacticiel Implémentation de l’accès concurrentiel optimiste . Le contrôle SqlDataSource fournit également une prise en charge de l’accès concurrentiel optimiste. Comme indiqué à l’étape 2, lors de la INSERTgénération automatique des instructions , UPDATEet DELETE , l’Assistant offre une option Utiliser l’accès concurrentiel optimiste. Comme nous le verrons dans le tutoriel suivant, l’utilisation de l’accès concurrentiel optimiste avec SqlDataSource modifie les WHERE clauses des UPDATE instructions et DELETE pour garantir que les valeurs des autres colonnes n’ont pas changé depuis la dernière fois que les données ont été affichées sur la page.

Bonne programmation !

À propos de l’auteur

Scott Mitchell, auteur de sept livres ASP/ASP.NET et fondateur de 4GuysFromRolla.com, travaille avec les technologies Web Microsoft depuis 1998. Scott travaille comme consultant indépendant, formateur et écrivain. Son dernier livre est Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Il est accessible à l’adressemitchell@4GuysFromRolla.com . ou via son blog, qui se trouve à l’adresse http://ScottOnWriting.NET.