Modifying Data using Data Source Controls
Data source controls greatly expand the capabilities of data-bound controls such as the ListView, GridView, FormView, and DetailsView controls to modify data at the data source without requiring extensive additional code. The data source control performs the data modification, so data-bound controls can support updates without requiring specific information about the database or other data source. You can use different data source controls with any combination of controls on the page. In addition, you can change the database or data source that a data-bound control works with by changing its DataSourceID to point to a different data source control.
Data Modification Commands
You can configure the ObjectDataSource, SqlDataSource, and AccessDataSource controls with data commands to insert, update, and delete data in their associated data store.
When you use the LinqDataSource control for data modification, you do not have to provide commands to insert, update, and delete data. Those commands are automatically generated for you.
Modifying Data with the LinqDataSource Control
You enable insert, update, and delete operations with the LinqDataSource control by setting the EnableInsert, EnableUpdate, and EnableDelete properties to true. When these properties are true, the LinqDataSource control uses LINQ to SQL to automatically create the commands to modify the data.
The following example shows a LinqDataSource control that has EnableInsert, EnableUpdate and EnableDelete set to true. A DetailsView control displays the data and creates a button for each command that lets users modify the data.
<asp:LinqDataSource
ContextTypeName="ExampleDataContext"
TableName="Products"
EnableUpdate="true"
EnableInsert="true"
EnableDelete="true"
ID="LinqDataSource1"
runat="server">
</asp:LinqDataSource>
<asp:DetailsView
DataKeyNames="ProductID"
AutoGenerateEditButton="true"
AutoGenerateDeleteButton="true"
AutoGenerateInsertButton="true"
AllowPaging="true"
DataSourceID="LinqDataSource1"
ID="GridView1"
runat="server">
</asp:DetailsView>
<asp:LinqDataSource
ContextTypeName="ExampleDataContext"
TableName="Products"
EnableUpdate="true"
EnableInsert="true"
EnableDelete="true"
ID="LinqDataSource1"
runat="server">
</asp:LinqDataSource>
<asp:DetailsView
DataKeyNames="ProductID"
AutoGenerateEditButton="true"
AutoGenerateDeleteButton="true"
AutoGenerateInsertButton="true"
AllowPaging="true"
DataSourceID="LinqDataSource1"
ID="GridView1"
runat="server">
</asp:DetailsView>
Modifying Data with the ObjectDataSource Control
The ObjectDataSource control enables you to specify a data object method for performing a specific type of update. The InsertMethod property specifies the data object method that is called when the ObjectDataSource control inserts an item into the data source. Similarly, the UpdateMethod property specifies the data object method used for item updates, and the DeleteMethod property specifies the method used to delete an item from the data source.
The following code example shows an ObjectDataSource control with its InsertMethod, UpdateMethod, and DeleteMethod properties configured with the names of methods from the underlying data object.
<asp:ObjectDataSource
ID="EmployeeDetailsObjectDataSource"
runat="server"
TypeName="Samples.AspNet.Controls.NorthwindEmployee"
SelectMethod="GetEmployee"
UpdateMethod="UpdateEmployee"
DeleteMethod="DeleteEmployee"
InsertMethod="InsertEmployee"
OnInserted="EmployeeDetailsObjectDataSource_OnInserted" >
<SelectParameters>
<asp:Parameter Name="EmployeeID" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="NewEmployeeID" Direction="Output"
Type="Int32" DefaultValue="0" />
</InsertParameters>
</asp:ObjectDataSource>
<asp:ObjectDataSource
ID="EmployeeDetailsObjectDataSource"
runat="server"
TypeName="Samples.AspNet.Controls.NorthwindEmployee"
SelectMethod="GetEmployee"
UpdateMethod="UpdateEmployee"
DeleteMethod="DeleteEmployee"
InsertMethod="InsertEmployee"
OnInserted="EmployeeDetailsObjectDataSource_OnInserted" >
<SelectParameters>
<asp:Parameter Name="EmployeeID" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="NewEmployeeID" Direction="Output"
Type="Int32" DefaultValue="0" />
</InsertParameters>
</asp:ObjectDataSource>
For more information, see Creating an ObjectDataSource Control Source Object.
Modifying Data with SQL Commands
The SqlDataSource and AccessDataSource controls enable you to supply SQL commands for modifying data at the data source. The InsertCommand property specifies a SQL command to insert a record into the data source. Similarly, the UpdateCommand property specifies a command used for record updates, and the DeleteCommand property specifies a command used to delete a record from the data source.
The following code example shows a SqlDataSource control with its InsertCommand, UpdateCommand, and DeleteCommand properties configured with SQL commands that perform update tasks.
<asp:sqlDataSource ID="EmployeeDetailsSqlDataSource"
SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"
InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName);
SELECT @EmpID = SCOPE_IDENTITY()"
UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName
WHERE EmployeeID=@EmployeeID"
DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"
ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
RunAt="server">
<SelectParameters>
<asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
</InsertParameters>
</asp:sqlDataSource>
<asp:sqlDataSource ID="EmployeeDetailsSqlDataSource"
SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"
InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName);
SELECT @EmpID = SCOPE_IDENTITY()"
UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName
WHERE EmployeeID=@EmployeeID"
DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"
ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
RunAt="server">
<SelectParameters>
<asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
</InsertParameters>
</asp:sqlDataSource>
For more information, see Modifying Data using the SqlDataSource Control.
Parameters
You can make your data-update scenarios more flexible secure by using parameters to pass values to be inserted, updated, or deleted in a data store. Parameter values can include the values of controls on the page, ASP.NET application variables, session values, and so on.
Parameter values typically come from data-bound controls when those controls invoke an update, insert, or delete operation. Additionally, you can create explicit parameter objects for the data source control for a given operation, which allows you to customize the parameters. For example, you can use an explicit parameter object to specify the type or direction of a parameter, or to define a default value for a parameter in case a null value is passed.
For the ObjectDataSource control, parameter values are passed as arguments when calling the appropriate method of the underlying data object. For the SqlDataSource or AccessDataSource control, parameters are passed to the SQL command used for the update. For more information and examples, see Using Parameters with Data Source Controls.
Events
Data source controls raise events both before and after data is modified. You can use these events to execute code before the data operation occurs, including canceling the operation, and after the data operation has taken place. For example, you can use the Deleting event of the SqlDataSource control to log information about a record about to be deleted. You can also use the Inserted event of the SqlDataSource control to retrieve an auto-generated identity value for the newly inserted record.