a "netTiers like" LINQ to SQL application

Some of us maybe familiar with netTiers when building an application, it's really helping us to cut the development time, LINQ does cut the time also... No, i don't want to compare between netTiers and LINQ to SQL, although in some cases they're comparable... What i want to share here is how to make a simple data visualization using gridview, really simple coz we will use LINQ to accomodate the simpleness... What "simple" i'm talking about? commonly when we build a CRUD application, we use ADO.NET or DAAB to access the database, but in this case, ADO.NET was handled by LINQ.

Familiar with the search control above? if you are new to that control, that is GridViewSearchPanel of netTiers. But what i'll show you is we'll create our own search to GridView exactly the same function as netTiers have.

    <form id="form1" runat="server">
    <div>
        Look for :
        <asp:DropDownList ID="DropDownList1" runat="server">
            <asp:ListItem>Code</asp:ListItem>
            <asp:ListItem>Description</asp:ListItem>
        </asp:DropDownList>
        Which :
        <asp:DropDownList ID="DropDownList2" runat="server">
            <asp:ListItem Value="0">Contains</asp:ListItem>
            <asp:ListItem Value="1">Ends With</asp:ListItem>
            <asp:ListItem Value="2">Starts With</asp:ListItem>
        </asp:DropDownList>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Button ID="Button1"
            runat="server" Text="Search" onclick="Button1_Click" />
        <asp:Button ID="Button2"
            runat="server" Text="Reset" onclick="Button2_Click" />
        <p></p>   
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true">
        </asp:GridView></div>
    </form>

1. Add an aspx page right exactly the same as above. 

2. Add LINQ to SQL (.dbml), disobey what behind the dbml right now, we talk about it later, here is my dbml:  

3. Go to your code behind, add this code and play it:

    protected void Page_Load(object sender, EventArgs e)
    {
        GridView1.DataSource = GetJobPrefix(0);
        GridView1.DataBind();
    }

    public IList<JobPrefix> GetJobPrefix(int rowStatus)
    {
        using (JobPrefixDataContext context = new JobPrefixDataContext())
        {
            return (from c in context.JobPrefixes
                    where c.RowStatus == rowStatus
                    select c).ToList();
        }
    }

    protected void Button1_Click(object sender, EventArgs e){}

    protected void Button2_Click(object sender, EventArgs e){}

In my application, the aspx will render like this, at this point the search facility didn't worked yet.

4. Next we'll create a method for searching and filtering.

    public IList<JobPrefix> GetJobPrefix(int rowStatus, string desc, string code, SearchOperator searchOperator)
    {
        using (JobPrefixDataContext context = new JobPrefixDataContext())
        {
            switch (searchOperator)
            {
                case SearchOperator.contains:
                    return (from c in context.JobPrefixes
                            where c.RowStatus == rowStatus && c.JobPrefixDesc.Contains(desc) && c.JobPrefixCode.Contains(code)
                            select c).ToList();
                    break;
                case SearchOperator.endswith:
                    return (from c in context.JobPrefixes
                            where c.RowStatus == rowStatus && c.JobPrefixDesc.EndsWith(desc) && c.JobPrefixCode.EndsWith(code)
                            select c).ToList();
                    break;
                case SearchOperator.startswith:
                    return (from c in context.JobPrefixes
                            where c.RowStatus == rowStatus && c.JobPrefixDesc.StartsWith(desc) && c.JobPrefixCode.StartsWith(code)
                            select c).ToList();
                    break;
                default:
                    return (from c in context.JobPrefixes
                            where c.RowStatus == rowStatus && c.JobPrefixDesc.Contains(desc) && c.JobPrefixCode.Contains(code)
                            select c).ToList();
            }
        }
    }

    public enum SearchOperator
    {
        contains = 0,
        endswith = 1,
        startswith = 2,
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        switch (DropDownList1.SelectedValue)
        {
            case "Code":
                GridView1.DataSource = GetJobPrefix(0, string.Empty,TextBox1.Text,(SearchOperator)Convert.ToInt32(DropDownList2.SelectedValue));
                GridView1.DataBind();
                break;
            case "Description":
                GridView1.DataSource = GetJobPrefix(0, TextBox1.Text, string.Empty, (SearchOperator)Convert.ToInt32(DropDownList2.SelectedValue));
                GridView1.DataBind();
                break;
        }
       
    }

    protected void Button2_Click(object sender, EventArgs e)
    {
        GridView1.DataSource = GetJobPrefix(0);
        GridView1.DataBind();
    }

Ok, that's it. When i try to filter the data using keyword "in" in the Description(JobPrefixDesc) field with the "Contains" method, the aspx will render like this (you could try the "Ends With" and "Starts With" by yourself) 

  Really simple isn't it? build our own "netTiers like" GridViewSearchPanel using LINQ to SQL. Please enjoy...