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.

Published Sunday, May 04, 2008 2:53 PM by zhirani

Comments

# re: Using LinqDataSource for Inserts,Updates and Deletes

Tuesday, May 13, 2008 4:27 PM by LoftenPierce

Can I use my own form instead of the formsview/detailsview, or is that too much work to assign the databindings manually?

# re: Using LinqDataSource for Inserts,Updates and Deletes

Wednesday, July 30, 2008 6:10 PM by Kenneth Courtney

Thanks for helping me make the connection on the FormView ... I couldn't figure out until your excellent article that asp.net is inferring the controls to bind to from the Bind expression in the text property... or at least that is my best guess and it worked in my scenario.

# re: Using LinqDataSource for Inserts,Updates and Deletes

Friday, August 08, 2008 3:31 AM by My Comment

Can you send the source code

# re: Using LinqDataSource for Inserts,Updates and Deletes

Sunday, December 20, 2009 1:55 PM by asim suvedi

Hi,

I have a grid view with dropdownlists in the edittemplate. The data in the gridview comes from tblUsersInRoles of AsimDB.

In one edittemplate, i have a ddl that is bind with the aspnet_users table of the database AspnetDB which shows the UserID.

The edittemplate in the other column is bind to the table tbl_Roles which is a table I created in my database - AsimDB which shows the RoleID.

Both of these ddls are bind with diffrent linqdatasource ( also different linq to sql classes)

Now when i click edit, the ddls show in the edit mode. But the problem is it doesnot update the table tblUsers_In_Roles.

I dont know why it is not updating. The UserID and RoleID are GUID. Is it happening because of GUUID??

What else can be the reason??

Title (required)
Name (required)
Your URL (optional)
Comments (required)
Remember Me?

Zeeshan Hirani

Search

Tags

Sponsors

News

Navigation

Archives