LinqDataSource Control in ASP.NET 3.5


INTRODUCTION

In this article I will show you how to Select, Insert, Update, Delete data using LinqDataSource control. Language-Integrated Query (LINQ) is a query syntax that defines a set of query operators that enable traversal, filter, and projection operations to be expressed in a declarative way in any .NET-based programming language. The data object can be an in-memory data collection or an object that represents data from a database. You can retrieve or modify the data without having to write SQL commands for each operation.

The LinqDataSource control enables you to use LINQ in an ASP.NET Web page by setting properties in markup text. The LinqDataSource control uses LINQ to SQL to automatically generate the data commands.

<
asp:LinqDataSource
ID="LinqDataSource1" runat="server"
            ContextTypeName="DataClassesDataContext"
            Select="new (VendorId, VendorFName, VendorLName, VendorCity,
 VendorState, VendorCountry, PostedDate, VendorDescription)"

            TableName="Vendors"
>

        </asp:LinqDataSource>

First of all make a new website using ASP.NET and connect your database using Server Explorer.

Image1.jpg

Figure1.

Now drag and drop LinqDataSource and GridView control from toolbox.

Image2.jpg

Figure2.

Now add a LINQ to SQL class in solution explorer.

Image3.jpg

Figure3.

By default it saves in App_Data folder. You can save wherever you want.

Image4.jpg

Figure4.

Now drag and drop you table from Server Explorer to DataClass.

Image5.jpg

Figure5.

Now time to configure LinqDataSource.

Image6.jpg

Figure6.

Choose a context object.

Image7.jpg

Figure7.

Select tables, columns whatever you want show.

Image8.jpg

Figure8.

Put GridView DataSourceID = LinqDataSourceID

Image9.jpg

Figure9.

Here is my page code for showing data in grid view.
Select Data

<h2>Select Data Using LinqDataSource</h2>
        <asp:LinqDataSource ID="LinqDataSource1" runat="server"
            ContextTypeName="DataClassesDataContext"
            Select="new (VendorId, VendorFName, VendorLName, VendorCity, VendorState, VendorCountry, PostedDate, VendorDescription)"
            TableName="Vendors"
            EnableDelete="true"
            EnableInsert="true"
            EnableUpdate="true">  
        </asp:LinqDataSource>       

        <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
            AutoGenerateColumns="False" BackColor="LightGoldenrodYellow" BorderColor="Tan"
            BorderWidth="1px" CellPadding="2" DataSourceID="LinqDataSource1"
            ForeColor="Black" GridLines="None" AutoGenerateSelectButton="True"
            DataKeyNames="VendorId">
            <Columns>
                <asp:BoundField DataField="VendorId" HeaderText="VendorId" ReadOnly="True"
                    SortExpression="VendorId" />
                <asp:BoundField DataField="VendorFName" HeaderText="VendorFName"
                    ReadOnly="True" SortExpression="VendorFName" />
                <asp:BoundField DataField="VendorLName" HeaderText="VendorLName"
                    ReadOnly="True" SortExpression="VendorLName" />
                <asp:BoundField DataField="VendorCity" HeaderText="VendorCity" ReadOnly="True"
                    SortExpression="VendorCity" />
                <asp:BoundField DataField="VendorState" HeaderText="VendorState"
                    ReadOnly="True" SortExpression="VendorState" />
                <asp:BoundField DataField="VendorCountry" HeaderText="VendorCountry"
                    ReadOnly="True" SortExpression="VendorCountry" />
                <asp:BoundField DataField="PostedDate" HeaderText="PostedDate" ReadOnly="True"
                    SortExpression="PostedDate" />
                <asp:BoundField DataField="VendorDescription" HeaderText="VendorDescription"
                    ReadOnly="True" SortExpression="VendorDescription" />
            </Columns>
            <FooterStyle BackColor="Tan" />
            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
                HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" /
            <HeaderStyle BackColor="Tan" Font-Bold="True" />
            <AlternatingRowStyle BackColor="PaleGoldenrod" />
        </asp:GridView>

Output


Image10.jpg

Figure10.

Insert, Update, Delete.

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.

<asp:LinqDataSource ID="LinqDataSource2"
        runat="server"
        ContextTypeName="DataClassesDataContext"
        TableName="Vendors"
        EnableDelete="true"
        EnableInsert="true"
        EnableUpdate="true">
    </asp:LinqDataSource>
<asp:DetailsView AutoGenerateRows="false" DataSourceID="LinqDataSource2" DataKeyNames="VendorId"
        ID="DetailsView1" runat="server" CellPadding="4"
        ForeColor="#333333" GridLines="None" Height="50px" Width="125px"
        AllowPaging="True" AutoGenerateDeleteButton="True"
        AutoGenerateEditButton="True" AutoGenerateInsertButton="True">
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <Fields>
            <asp:BoundField DataField="VendorId" HeaderText="VendorId"
                InsertVisible="False" ReadOnly="True" SortExpression="VendorId" />\
            <asp:BoundField DataField="VendorFName" HeaderText="VendorFName"
                SortExpression="VendorFName" />
            <asp:BoundField DataField="VendorLName" HeaderText="VendorLName"
                SortExpression="VendorLName" />
            <asp:BoundField DataField="VendorCity" HeaderText="VendorCity"
                SortExpression="VendorCity" />
            <asp:BoundField DataField="VendorState" HeaderText="VendorState"
                SortExpression="VendorState" />
            <asp:BoundField DataField="VendorCountry" HeaderText="VendorCountry"
                SortExpression="VendorCountry" />
            <asp:BoundField DataField="PostedDate" HeaderText="PostedDate"
                SortExpression="PostedDate" />
            <asp:BoundField DataField="VendorDescription" HeaderText="VendorDescription"
                SortExpression="VendorDescription" />
        </Fields>
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:DetailsView>

Output:

Image11.jpg
Figure11.

When you click on Edit link button.

Image12.jpg

Figure12.

When you click on Insert link, looks like this

Image13.jpg
Figure13.

This is it for now