Search records from Sql Server database using Linq to Sql

In this article I am going to explain how to search the records from Sql server database using Linq to Sql.

Description:

In the previous articles I have explained WCF tutorial to Insert, Bind, Edit,update and Delete the record from sql server database in Asp.net, Displayrecords in Gridview from sql server database using WCF service and WCFtutorial: Create and Consume WCF Service using visual studio 2013.

I have a table Movie. I want to filter/search the records on the basis of movie name and display the record in Gridview data control.

Implementation:

Add a webform to project/website. Drag and drop the Gridview control from toolbox to

webform.

HTML Markup of webform:

<fieldset style="width:45px">

            <legend>Linq Tutorial</legend>

      

        <table><tr><td>

            <asp:TextBox ID="txtname" runat="server"></asp:TextBox></td><td>

                <asp:Button ID="btnserach" runat="server" Text="Search" /></td></tr>

            <tr>

                <td colspan="2">

    <asp:GridView ID="grdmoviedetail" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None">

                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />

                <Columns>

                    <asp:BoundField DataField="Name" HeaderText="Movie Name" >

                     <ItemStyle HorizontalAlign="Center" />

                    </asp:BoundField>

                     <asp:BoundField DataField="Genre" HeaderText="Genre"> <ItemStyle HorizontalAlign="Center" />

                    </asp:BoundField>

                     <asp:BoundField DataField="Cost" HeaderText="Budget (In Crore)"> <ItemStyle HorizontalAlign="Center" />

                    </asp:BoundField>

                     <asp:ImageField DataImageUrlField="Poster" HeaderText="Poster" ControlStyle-Width="200px">

<ControlStyle Width="200px"></ControlStyle>

                    </asp:ImageField>

                </Columns>

                <EditRowStyle BackColor="#999999" />

                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />

                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />

                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />

                <SortedAscendingCellStyle BackColor="#E9E7E2" />

                <SortedAscendingHeaderStyle BackColor="#506C8C" />

                <SortedDescendingCellStyle BackColor="#FFFDF8" />

                <SortedDescendingHeaderStyle BackColor="#6F8DAE" />

            </asp:GridView>

                    </td>

            </tr></table> </fieldset>

Now move to code file. First of all create the object of DBML file. Write the code to bind the Gridview:

C#:

ProjectDataClassesDataContext db = new ProjectDataClassesDataContext();

    protected void Page_Load(object sender, EventArgs e)

    {

        if(!IsPostBack)

        {

            BindGridview();

        }

    }

    public void BindGridview()

    {

        var grid = from m in db.Movies

                   select m;

        grdmoviedetail.DataSource = grid;

        grdmoviedetail.DataBind();

    }

VB:

Private db As New ProjectDataClassesDataContext()

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        If Not IsPostBack Then

            BindGridview()

        End If

    End Sub

    Public Sub BindGridview()

        Dim grid = From m In db.Movies

        grdmoviedetail.DataSource = grid

        grdmoviedetail.DataBind()

    End Sub

Now add a textbox and button control above the Gridview and write the below code on button click.

C#:

protected void btnserach_Click(object sender, EventArgs e)

    {

        var search = from mn in db.Movies

                     where mn.Name.Contains(txtname.Text)

                     select mn;

        grdmoviedetail.DataSource = search;

        grdmoviedetail.DataBind();

    }

VB:

Protected Sub btnserach_Click(sender As Object, e As EventArgs) Handles btnserach.Click

        Dim search = From mn In db.Movies Where mn.Name.Contains(txtname.Text)

        grdmoviedetail.DataSource = search

        grdmoviedetail.DataBind()

    End Sub

Build the project and run. Check out the result.

 Result:

  In this article we have learn to how to Insert, Select, Edit, Update and Delete record in Gridview using WCF service in asp.net. I hope you enjoyed this article.