Sunday, August 23, 2015

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:

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.
Search records from Sql Server database using Linq to Sql

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:
Search records from Sql Server database using Linq to Sql

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

4 comments:

  1. not understand the query in C# please expalin.because i'm fresher in .net.please share Google+.

    ReplyDelete
    Replies
    1. var is anyomous type in linq.....
      query to bind the gridview:-
      var grid = from m in db.Movies
      select m;

      Query to search the record on button click:
      var search = from mn in db.Movies
      where mn.Name.Contains(txtname.Text)
      select mn;

      if you getting a problem study the linq tutorial....

      Delete
  2. Nice Articles i'm looking same content of code like searching

    Dotnet Project with Source Code Click on Link to get more project
    thanks in advances

    ReplyDelete
    Replies
    1. Keep visiting and reading for more articles.... my new post Link

      Delete