How to: Update, Insert, and Delete Data with the LinqDataSource Control

Artikkeli
10/22/2014

With the LinqDataSource control, you can create Web pages that enable users to update, insert, and delete data. You do not have to specify SQL commands, because the LinqDataSource control uses dynamically created commands for those operations. To let users modify data, you can enable update, insert, or delete operations on the LinqDataSource control. You can then connect the control to a data-bound control that lets users update data, such as the DetailsView or GridView control. If you want to customize the values to be updated, you can add parameters or create an event handler and change values dynamically.

This topic describes how to enable the LinqDataSource control to automatically handle update, insert, and delete operations. It also describes how to customize the values that are being updated. Finally, the topic shows how to create an event handler to programmatically set a property before it inserts or updates a database record.

The steps for enabling automatic update, insert, and delete operations are similar to one other, and the procedures in this topic show how to enable all three functions. However, you do not have to enable all three. You can enable only the functions you need.

For more information, see the following topics:

Automatically Updating, Inserting, and Deleting Data

To enable a data-bound control to automatically handle data updates by using the LinqDataSource control, you must do the following:

Connect the LinqDataSource control to a database.
Specify the table that you want to update.
Configure the LinqDataSource control to enable data updates.
Configure the data-bound control to use the LinqDataSource control.
Configure the data-bound control to enable data updates.

To enable updating, inserting, and deleting data

Add a LinqDataSource control to an ASP.NET Web page.
Set the ContextTypeName property to the name of an object that derives from the DataContext class.

When you use the Object Relationship Designer in Visual Studio 2008 to create classes that represent SQL database tables, the generated classes automatically derive from DataContext. For information about how to connect the LinqDataSource control to a database, see LinqDataSource Web Server Control Overview.

Note

If you connect a LinqDataSource control to a database by using the Entity Framework instead of LINQ to SQL, you cannot enable updating, inserting, or deleting.
Set the TableName property to the name of the entity class that represents the associated database table that you want to update.
Set one or more of the EnableUpdate, EnableDelete, and EnableInsert properties of the LinqDataSource control to true.

Note

Make sure that you do not set the Select property or the GroupBy property. If either of these properties is set, it must be cleared in order to enable automatic data updates.
Add a data bound control, such as a DetailsView control, and set its DataSourceID property to the ID of the LinqDataSource control.
Set the DataKeyNames property of the data control to the name of the table's primary key column or columns.

Provide a way in the data-bound control for the user to switch to update, delete, or edit mode.

For example, you can add buttons to the data-bound control or enable the control to automatically create those buttons.

The following example shows the markup for part of an ASP.NET Web page that contains a LinqDataSource control and a DetailsView control. The controls are configured to enable the user to display, update, insert, and delete data in a table named Products.

<asp:LinqDataSource
  ContextTypeName="ExampleDataContext"
  TableName="Products"
  EnableUpdate="true"
  EnableInsert="true"
  EnableDelete="true"
  ID="LinqDataSource1"
  runat="server">
</asp:LinqDataSource>
<asp:DetailsView
  DataSourceID="LinqDataSource1"
  DataKeyNames="ProductID"
  AutoGenerateEditButton="true"
  AutoGenerateDeleteButton="true"
  AutoGenerateInsertButton="true"
  AllowPaging="true"
  ID="DetailsView1"
  runat="server">
</asp:DetailsView>

Adding Default Values for Inserting Data

By default, you do not have to add parameters to insert data. A data-bound control passes its values to the LinqDataSource control, and the LinqDataSource control uses the values to set the corresponding property. LINQ to SQL creates the commands to modify the data source. For more information, see LINQ to SQL.

You can provide default values for inserting data in the InsertParameters collection. These values are used only for fields that are defined in the data source but that are not bound in the data control. For example, suppose a database table has columns that are named Name, Address, and PostalCode, but a ListView control that is bound to the table is bound only to the Name and Address fields. The values in the InsertParameters collection will be used only for the PostalCode field. They will not be used for the Name and Address field. This is true even if no value is entered for those fields. If the data-bound control autogenerates fields for all fields in the data source, no values from the InsertParameters collection will be used.

To add default values for inserting data

Add an insert parameter definition for each field that you want to provide a default value for.

The following example shows how to add an insert parameter for a property named Category.

<asp:LinqDataSource
  ContextTypeName="ExampleDataContext"
  TableName="Products"
  EnableUpdate="true"
  EnableInsert="true"
  EnableDelete="true"
  ID="LinqDataSource1"
  runat="server">
  <InsertParameters>
    <asp:Parameter Name="Category" DefaultValue="Miscellaneous" />
  </InsertParameters>
</asp:LinqDataSource>

Setting Values Programmatically

To set properties programmatically before a data operation is performed, you can create event handlers for the Inserting, Updating, and Deleting events of the LinqDataSource control. Any properties that you do not set in the event handler are set automatically by the LinqDataSource control.

To set values programmatically

Create an event handler for the Inserting, Updating, or Deleting event of the LinqDataSource control.

Add code to the event handler that programmatically sets the value that you want to modify.

The data to update, insert, or delete is in the following properties:

Event	Property	Class
Inserting	NewObject	LinqDataSourceInsertEventArgs
Updating	NewObject OriginalObject	LinqDataSourceUpdateEventArgs
Deleting	OriginalObject	LinqDataSourceDeleteEventArgs

Inserting

NewObject

LinqDataSourceInsertEventArgs

Updating

NewObject

OriginalObject

LinqDataSourceUpdateEventArgs

Deleting

OriginalObject

LinqDataSourceDeleteEventArgs

All these properties return an object of type Object. You can cast the objects returned from these properties to the type that represents the table that you want to update. After you cast the object, you can set the properties for that type. For information about casting, see Casting and Type Conversions (C# Programming Guide) or Type Conversions in Visual Basic.

The following example shows an event handler for the Inserting event that sets a DateModified column to the current date and time. It casts the object in the NewObject property to the Product type.

The Product type in the example is generated by LINQ to SQL and corresponds to the Product table in the Northwind Traders database. For more information about how to connect the LinqDataSource control to a database by using classes that are generated by the LINQ-to-SQL Object Relational Designer, see LinqDataSource Web Server Control Overview.

Protected Sub LinqDataSource_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceInsertEventArgs)
    Dim product As Product
    product = CType(e.NewObject, Product)
    product.DateModified = DateTime.Now
End Sub

protected void LinqDataSource_Inserting(object sender, LinqDataSourceInsertEventArgs e)
{
    Product product = (Product)e.NewObject;
    product.DateModified = DateTime.Now;
}

The following example shows the markup for a LinqDataSource control that binds the LinqDataSource_Inserting method to the Inserting event.

<asp:LinqDataSource
  ContextTypeName="ExampleDataContext"
  TableName="Products"
  OnInserting="LinqDataSource_Inserting"
  EnableUpdate="true"
  EnableInsert="true"
  EnableDelete="true"
  ID="LinqDataSource1"
  runat="server">
</asp:LinqDataSource>

Security

This example includes a text box that accepts user input, which is a potential security threat. By default, ASP.NET Web pages validate that user input does not include script or HTML elements. For more information, see Script Exploits Overview.

For information about how to store a connection string, see How To: Secure Connection Strings when Using Data Source Controls.

For information about how to avoid displaying sensitive information in error messages, see How to: Display Safe Error Messages.

Change History

Date	History	Reason
February 2010	Removed references to Update and Delete parameters because the control does not use those collections.	Customer feedback.
April 2009	Clarified step instructions and prerequisites and added links to API reference documentation.	Customer feedback.

February 2010

Removed references to Update and Delete parameters because the control does not use those collections.

Customer feedback.

April 2009

Clarified step instructions and prerequisites and added links to API reference documentation.