Using LinqDataSource for Inserts,Updates and Deletes
In this small walk through I will demonstrate how to use linq datasource to do insert,update,delete and select using form view,gridview and details view control. I will use Formview control to do inserts and use gridview to do updates and deletes. When you select a row in the grid, the detail record will be displayed in a details view control. The screen shot of the page looks like this.
Code for aspx looks like this.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="LinqDataSource.aspx.cs" Inherits="LinqDataSource" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Insert/Update/Delete Using LinqDataSource</title>
<style type="text/css">
.label
{
text-align:right;
width:100px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FormView ID="FormView1" runat="server" CellPadding="4"
DefaultMode="Insert" DataKeyNames="CustomerID"
DataSourceID="customersource" ForeColor="#333333"
oniteminserted="FormView1_ItemInserted">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<InsertItemTemplate>
<table>
<tr>
<td class="label">CustomerID:</td>
<td>
<asp:TextBox ID="TextBox1" runat="server"
Text='<%# Bind("CustomerID") %>' />
</td>
</tr>
<tr>
<td class="label">CompanyName:</td>
<td>
<asp:TextBox ID="CompanyNameTextBox" runat="server"
Text='<%# Bind("CompanyName") %>' />
</td>
</tr>
<tr>
<td class="label">ContactName:</td>
<td>
<asp:TextBox ID="ContactNameTextBox" runat="server"
Text='<%# Bind("ContactName") %>' />
</td>
</tr>
<tr>
<td class="label">ContactTitle:</td>
<td>
<asp:TextBox ID="ContactTitleTextBox" runat="server"
Text='<%# Bind("ContactTitle") %>' />
</td>
</tr>
<tr>
<td class="label">Phone:</td>
<td>
<asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />
</td>
</tr>
<tr>
<td style="text-align:center" colspan="2">
<asp:Button ID="insert" runat="server" CommandName="Insert"
Text="Insert" />
</td>
</tr>
</table>
</InsertItemTemplate>
</asp:FormView>
<br />
<asp:GridView ID="customergrid" runat="server"
AllowPaging="True"
DataSourceID="customersource"
DataKeyNames="CustomerID"
AutoGenerateColumns="False"
AutoGenerateSelectButton="true"
AutoGenerateEditButton="True"
AutoGenerateDeleteButton="True" AllowSorting="True" CellPadding="4"
ForeColor="#333333" GridLines="None"
>
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<Columns>
<asp:BoundField DataField="CompanyName" HeaderText="Company"
SortExpression="CompanyName" />
<asp:BoundField DataField="ContactName" HeaderText="Name"
SortExpression="ContactName" />
<asp:BoundField DataField="ContactTitle" HeaderText="Title"
SortExpression="ContactTitle" />
<asp:BoundField DataField="Phone" HeaderText="Phone"
SortExpression="Phone" />
</Columns>
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:LinqDataSource id="customersource" runat="server"
ContextTypeName="NorthWindDataContext"
TableName="Customers"
OrderBy="ContactName"
EnableInsert="true"
EnableUpdate="true"
EnableDelete="true"
AutoSort="true"
AutoPage="true" />
<br />
<asp:DetailsView ID="DetailsView1" runat="server"
DataSourceID="singlecustomer" DefaultMode="ReadOnly" />
<asp:LinqDataSource id="singlecustomer" runat="server"
ContextTypeName="NorthWindDataContext"
TableName="Customers"
Where="CustomerID=@customerid"
>
<WhereParameters>
<asp:ControlParameter Name="customerid" ControlID="customergrid" />
</WhereParameters>
</asp:LinqDataSource>
</div>
</form>
</body>
</html>
I start with creating NorthWind dbml file and drag he Customer table onto OR designer as shown below.
By dragging the Customer entity onto the designer, I will have the ability to program against the entity in my aspx file.
In order to Insert Customer using NorthWindDataContext, I make use of Formview and linq datasource control. The aspx code is shown below.
If you have been using the Formview control earlier, the code would look very similar. I am setting the DataSourceID to linq datasource which makes it possible to insert customer into the database. Since I am using Formview control to only insert customer record, I am setting DefaultMode property to Insert. I am also making use of two way Bind Method to send the updated property information to linq datasource control. I also have a button whose command Name property is set to Insert to trigger the Insert process. It is important that you set the DataKeyNames property to the primarykey column in the customer entity which is CustomerID. If you do not specify the primary key, insert will fail. I am also registering with the Inserted event of the form view control. I am doing so that once the insert gets written to the database, I want to my grid of customers to reflect the new record that we just added. This is the code that I put in my inserted event.
The code for LinqDataSource looks like this
In the linq datasource, I am setting the ContextTypeName to the NorthWind DataContext that I generated. The table I am inserting,deleting,updating and selecting is Customers which is set using the TableName property. Since I want the my customer grid to be sorted by ContactName I am setting the OrderBy clause to ContactName. I also want my customer grid to be sortable and pagable and for that I set the my datasource to AutoSort and AutoPage. To support inserting, updating and deleting, I simply turn on EnableInsert, EnableUpdate, EnableDelete on my linq datasource control. One thing you must be wondering is, how come I don't have insert,update and delete parameters. The reason is, by default all bind values themselves create their own parameters. The only time I create my specific bind parameters for insert, update or delete, is when I need to specify a conversion from string to other type like aftertime or integer. Since all my parameter are string, I am okay in this case.
My customer grid supports paging, sorting, updating,deleting and selecting by making use of linq datasource. The code for grid is shown below.
In customer grid, I set my DataSourceID to my linq datasource control. In order for my grid to support updating,deleting and selecting, I set my AutoGenerateSelectButton, AutoGenerateEditButton and AutoGenerateDeleteButton to true. I also have to set my DataKeyNames to customerid for all this to work properly. When you select a row in the grid, I am binding the selected value of the grid to DetailsView which shows the detail for the record selected in the grid. The code for DetailsView and its linq datasource is shown below.
The DetailsView displays a single record based on the LinqDataSource which retrieves a single customer selected on the gridview control. The where parameter for the linq datasource gets its value from the customer grid's SelectedValue property. I am setting the DefaultMode for detail's view to ReadOnly to display read only view of the data.