Page 1 of 1

Advanced Control of LinqDataSource & binding with Search

#1 coultertech  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 31
  • Joined: 18-May 09

Posted 14 September 2010 - 12:47 AM

I have always been more of a Windows programmer but over the last few weeks I have had to dive head first into a full rewrite of an asp.net application. Luckily I wasn't a complete beginner, but the asyncrinicity of web always throws me. I had to change a lot about the way I thought about things. Anyway I got to really get heavy into Linq and thought I'd share a few cool and powerful features. I have always loved to be able to bind things and save myself a TON of programming. In this post I am going to show you how to override the linqdatasource select at run time to return just about anything your datacontext has to offer, then bind it to a gridview (or anything really). Lets get started:

First thing first, you will need to create a dbml file (linq to sql). To do this, right click on your project and add a new item. Choose linq to sql in the data section. Give it a name and click add. This will open up the dbml designer. Use the server explorer to connect to your database, and drag your tables onto the design surface. Hopefully your database is well defined with primary keys, foriegn keys, identity fields, etc. If not, then i'd suggest revisiting your DB before going on.

Once you have your tables on the designer, click save and lets move on.

On your aspx page, add a GridView and a LinqDataSource. Choose to configure your linqdatasource. This should open up the wizard. Choose your datacontext from the dropdown list. Then click next.

Next choose your table and click finish. Since we are going to override the select statement, it doesn't matter what table your choose. Then set the datasource on your gridview to use your linqdatasource. All of this so far has been pretty easy. Now lets get into some code. Be sure to set your gridview's AutoGenerateColumns="False". This will make sure only the columns we specify will show. Here is the code for my GridView and LinqDataSource:

<asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="Tracker.ETDataContext" EntityTypeName="" onselecting="LinqDataSource1_Selecting">
</asp:LinqDataSource>



                       <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
                            Font-Size="12px" AlternatingRowStyle-ForeColor="#333333" CellPadding="2" ForeColor="Black"
                            GridLines="None" Height="100%" Width="100%" BackColor="LightGoldenrodYellow"
                            BorderColor="Tan" BorderWidth="1px" DataSourceID="LinqDataSource1" PageSize="25">
                            <AlternatingRowStyle BackColor="PaleGoldenrod" Height="14px" HorizontalAlign="Center"
                                VerticalAlign="Middle" />
                            <Columns>
                                <asp:TemplateField>
                                    <ItemTemplate>
                                        <asp:CheckBox ID="CheckBox1" runat="server" />
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="Account #" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left"
                                    ItemStyle-VerticalAlign="Top">
                                    <ItemTemplate>
                                        <asp:HyperLink ID="HyperLink2" runat="server" NavigateUrl='<%# Eval("Account", "EditAccount.aspx?id={0}") %>'
                                            Text="(Details)"></asp:HyperLink>
                                        <asp:Label ID="AcctLabel" runat="server" Text='<%# Eval("Account") %>'></asp:Label>
                                    </ItemTemplate>
                                    <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top"></ItemStyle>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="Trust Name" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left"
                                    ItemStyle-VerticalAlign="Top">
                                    <ItemTemplate>
                                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("Name") %>'></asp:Label>
                                    </ItemTemplate>
                                    <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top"></ItemStyle>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="Trust Type" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left"
                                    ItemStyle-VerticalAlign="Top">
                                    <ItemTemplate>
                                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("aType") %>'></asp:Label><br />
                                    </ItemTemplate>
                                    <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top"></ItemStyle>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="Execution Date" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left"
                                    ItemStyle-VerticalAlign="Top">
                                    <ItemTemplate>
                                        <asp:Label ID="Label4" runat="server" Text='<%# Bind("sdate", "{0:d}") %>'></asp:Label><br />
                                    </ItemTemplate>
                                    <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top"></ItemStyle>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="Last Amended" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left"
                                    ItemStyle-VerticalAlign="Top">
                                    <ItemTemplate>
                                        <asp:Label ID="AmendLabel" runat="server" Text='<%# Eval("Amended", "{0:d}") %>'></asp:Label>
                                    </ItemTemplate>
                                    <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
                                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top"></ItemStyle>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="Clients" SortExpression="first_name" ItemStyle-HorizontalAlign="Center"
                                    ItemStyle-VerticalAlign="Top">
                                    <ItemTemplate>
                                        <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="false" DataSource='<%# Bind("OtherAccountHolders") %>'
                                            Font-Size="12px" AlternatingRowStyle-ForeColor="#333333" CellPadding="2" ForeColor="Black"
                                            GridLines="None" Height="100%" Width="100%" BackColor="LightGoldenrodYellow"
                                            BorderColor="Tan" BorderWidth="1px">
                                            <AlternatingRowStyle BackColor="PaleGoldenrod" Height="14px" HorizontalAlign="Center"
                                                VerticalAlign="Middle" />
                                            <Columns>
                                                <asp:TemplateField>
                                                    <ItemTemplate>
                                                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("relation") %>'></asp:Label>
                                                    </ItemTemplate>
                                                </asp:TemplateField>
                                                <asp:TemplateField HeaderText="Name">
                                                    <ItemTemplate>
                                                        <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "EditPeople.aspx?pid=" + Eval("pid") + "&aid=" + Eval("aid") %>'>
                                                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("oName") %>'></asp:Label>
                                                        </asp:HyperLink>
                                                    </ItemTemplate>
                                                </asp:TemplateField>
                                                <asp:TemplateField HeaderText="Address">
                                                    <ItemTemplate>
                                                        <table>
                                                            <tr>
                                                                <td>
                                                                    <asp:Label ID="HomeAddress" runat="server" Text='<%# Bind("home_add") %>' /><br />
                                                                    <asp:Label ID="HomeCity" runat="server" Text='<%# Bind("home_city") %>' />, <asp:Label
                                                                        ID="HomeState" runat="server" Text='<%# Bind("home_state") %>' /> <asp:Label ID="HomeZip"
                                                                            runat="server" Text='<%# Bind("home_zip") %>' />
                                                                </td>
                                                            </tr>
                                                        </table>
                                                    </ItemTemplate>
                                                </asp:TemplateField>
                                                                                            </Columns>
                                            <FooterStyle BackColor="Tan" />
                                            <HeaderStyle BackColor="Tan" Font-Bold="True" Font-Underline="True" />
                                            <PagerSettings NextPageImageUrl="~/Images/arrow_right_16.png" PreviousPageImageUrl="~/Images/arrow_left_16.png" />
                                            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
                                            <RowStyle Height="20px" HorizontalAlign="Center" VerticalAlign="Middle" />
                                            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
                                            <SortedAscendingCellStyle BackColor="#FAFAE7" />
                                            <SortedAscendingHeaderStyle BackColor="#DAC09E" />
                                            <SortedDescendingCellStyle BackColor="#E1DB9C" />
                                            <SortedDescendingHeaderStyle BackColor="#C2A47B" />
                                        </asp:GridView>
                                    </ItemTemplate>
                                    <ItemStyle HorizontalAlign="Center" VerticalAlign="Top"></ItemStyle>
                                </asp:TemplateField>
                            </Columns>
                            <FooterStyle BackColor="Tan" />
                            <HeaderStyle BackColor="Tan" Font-Bold="True" Font-Underline="True" />
                            <PagerSettings NextPageImageUrl="~/Images/arrow_right_16.png" PreviousPageImageUrl="~/Images/arrow_left_16.png"
                                Mode="NumericFirstLast" />
                            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
                            <RowStyle Height="20px" HorizontalAlign="Center" VerticalAlign="Middle" />
                            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
                            <SortedAscendingCellStyle BackColor="#FAFAE7" />
                            <SortedAscendingHeaderStyle BackColor="#DAC09E" />
                            <SortedDescendingCellStyle BackColor="#E1DB9C" />
                            <SortedDescendingHeaderStyle BackColor="#C2A47B" />
                        </asp:GridView>



I know that looks like a lot of code but really its just a handful of columns that I created. You could create all this and write very little code if any. I created a few template columns and bound the controls to datafield I'm going to create with my linq statement. Notice that the LinqDataSource has an event handler:

onselecting="LinqDataSource1_Selecting"



Lets go ahead and create our function to handle this event.

protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
        {
            ETDataContext etdc = new ETDataContext();
            var accts = from a in etdc.ACCOUNTs
                        orderby a.account_id
                        select new
                        {
                            Account = a.account_id,
                            aType = a.SERVICEs.FirstOrDefault().SERVICE_TYPE.service_type_desc,
                            Name = a.SERVICEs.FirstOrDefault().service_name,
                            letter_dt = a.create_dt,
                            sdate = a.SERVICEs.FirstOrDefault().service_date,
                            Amended = a.SERVICEs.FirstOrDefault().service_amended,
                            PrimAccthldr = a.PEOPLE.first_name + " " + a.PEOPLE.middle_name + " " + a.PEOPLE.last_name + a.PEOPLE.PEOPLE_TAG.tag_desc,
                            OtherAccountHolders = from oah in etdc.ACCOUNT_PEOPLE_RELATIONSHIPs
                                                  where oah.account_id == a.account_id
                                                  select new
                                                  {
                                                      relation = oah.relationship_to_primary,
                                                      oName = oah.PEOPLE.first_name + " " + oah.PEOPLE.middle_name + " " + oah.PEOPLE.last_name + oah.PEOPLE.PEOPLE_TAG.tag_desc,
                                                      pid = oah.people_id,
                                                      aid = a.account_id,
                                                      home_add = oah.PEOPLE.home_address,
                                                      home_phone = oah.PEOPLE.home_phone,
                                                      home_email = oah.PEOPLE.home_email,
                                                      home_city = oah.PEOPLE.home_city,
                                                      home_state = oah.PEOPLE.home_state,
                                                      home_zip = oah.PEOPLE.home_zip
                                                  },
                            people_id = a.primary_account_holder,
                            PrimAddress = a.PEOPLE.home_address,
                            PrimCity = a.PEOPLE.home_city,
                            PrimState = a.PEOPLE.home_state,
                            PrimZip = a.PEOPLE.home_zip
                            


                        };
            e.Result = accts;
        }



This query really shows the power of linq. My main query is on the accounts table of my database, but I want to pull data from many tables. So in this case I'm creating a new anonymous type where I'm pulling values from the Accounts table, Services Table, and Peoples Table. But In my GridView I Nested another Gridview in one of the column templates. Did you notice? Well guess what, in our anonymous type we can create an object that is a collection of yet another anonymous type. So otheraccountholders in each record will be a set of results. And better yet, its bindable to a control like the nested GridView.

So in one query, I am able to pull values from a bunch of table and even bind two different gridviews. Now that's what you call power.

The results of your override query will replace the results that the linqdatasource would have returned originally. Now how about we add some searching to all this fun. One may think that this may really complicate things, but you would be wrong. I'm only going to add a very simple sample of this to this post, but you should be able to take it and expand it to search through any field in your query.

First lets add some search fields to the aspx page. How about:

<asp:Label ID="Label4" runat="server" Text="Account ID"></asp:Label>
<br />
<asp:TextBox ID="txtAIDSearch" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label5" runat="server" Text="Trust Type:"></asp:Label><br />
<asp:TextBox ID="txtTypeSearch" runat="server"></asp:TextBox>
<br />
<asp:LinkButton ID="LinkButton5" runat="server" onclick="LinkButton5_Click">Search</asp:LinkButton>



Now the search code. Lets handle the LinkButton5_Click event.

 protected void LinkButton5_Click(object sender, EventArgs e)
{
            StringBuilder sb = new StringBuilder();
            if (txtAIDSearch.Text.Trim() != string.Empty)
                sb.Append("Account.Contains(\"" + txtAIDSearch.Text + "\")");
            if (txtTypeSearch.Text.Trim() != string.Empty)
            {
                if (sb.ToString().Trim() != string.Empty)
                    sb.Append(" && aType.Contains(\"" + txtTypeSearch.Text + "\")");
                else
                    sb.Append("aType.Contains(\"" + txtTypeSearch.Text + "\")");
            }
            LinqDataSource1.Where = sb.ToString();
            GridView1.DataBind();
}



It really is that simple. Create your where clause, in this case to search for account number and/or account type. I used Contains to basically do a '%something%' type query but you could change that to seach for 'something%' or '%something'. Once you add the where clause and bind the data, your gridview will update to only show the results. Another powerful thing here is that the linqdatasource does not have to requery the database to apply your where clause. so if you just loaded a lot of data, you won't have slow searching. Make it even nicer with an updatepanel and some nice graphic. I'm not going to show you that on this post though. I hope this is helpful to someone.

Enjoy!

Is This A Good Question/Topic? 0
  • +

Page 1 of 1