How to: Update, Insert, and Delete Data with the LinqDataSource Control
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:
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.
When you use the Object Relationship Designer in Visual Studio 2010 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.
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 class that represents the database table that you want to update.
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>
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>
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
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:
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 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.
<asp:LinqDataSource ContextTypeName="ExampleDataContext" TableName="Products" OnInserting="LinqDataSource_Inserting" EnableUpdate="true" EnableInsert="true" EnableDelete="true" ID="LinqDataSource1" runat="server"> </asp:LinqDataSource>
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.