SQL and Database: June 2013

This demo we explain how LINQ use IN Keyword to get result set as we get result through SQL query. Here we use student list to apply search in list and bind selected result to repeater.

Student.cs

using System;

/// <summary>
/// Summary description for Student
/// </summary>
public class Student
{
    public int StudentID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public Student()
    {
        //
        // TODO: Add constructor logic here
        //
    }
}

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="LinqInDemo.aspx.cs" Inherits="LinqInDemo" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <strong>Choose Student ID</strong>
        <asp:CheckBoxList ID="chkStudentList" runat="server" RepeatDirection="Horizontal"
            RepeatColumns="3" Width="300px">
        </asp:CheckBoxList>
    </div>
    <div>
        <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" />
        <table>
            <asp:Repeater ID="rptStudent" runat="server">
                <ItemTemplate>
                    <tr>
                        <td>
                            <strong>
                                <%# Eval("StudentID") %>
                            </strong>
                        </td>
                        <td>
                            <strong>
                                <%# Eval("FirstName") %>
                            </strong>
                        </td>
                        <td>
                            <strong>
                                <%# Eval("LastName") %>
                            </strong>
                        </td>
                    </tr>
                </ItemTemplate>
            </asp:Repeater>
        </table>
    </div>
    </form>
</body>
</html>

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class LinqInDemo : System.Web.UI.Page
{
    List<Student> lstStudent = null;
    List<int> lstStudenttIds = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindStudentID();
        }
    }

    private List<Student> GetStudentList()
    {
        lstStudent = new List<Student>();

        Student objStudent1 = new Student();
        objStudent1.StudentID = 100;
        objStudent1.FirstName="Mahesh";
        objStudent1.LastName = "kumar";

        Student objStudent2 = new Student();
        objStudent2.StudentID = 101;
        objStudent2.FirstName = "Narendra";
        objStudent2.LastName = "kumar";

        Student objStudent3 = new Student();
        objStudent3.StudentID = 103;
        objStudent3.FirstName = "Ashok";
        objStudent3.LastName = "Mehta";

        Student objStudent4 = new Student();
        objStudent4.StudentID = 104;
        objStudent4.FirstName = "Rahul";
        objStudent4.LastName = "Porwal";

        Student objStudent5 = new Student();
        objStudent5.StudentID = 105;
        objStudent5.FirstName = "Rajni";
        objStudent5.LastName = "Bist";

        Student objStudent6 = new Student();
        objStudent6.StudentID = 106;
        objStudent6.FirstName = "Alok";
        objStudent6.LastName = "Pandey";

        lstStudent.Add(objStudent1);
        lstStudent.Add(objStudent2);
        lstStudent.Add(objStudent3);
        lstStudent.Add(objStudent4);
        lstStudent.Add(objStudent5);
        lstStudent.Add(objStudent6);

        return lstStudent;
    }

    private void BindStudentID()
    {
        chkStudentList.DataSource = GetStudentList();
        chkStudentList.DataTextField = "StudentID";
        chkStudentList.DataValueField = "StudentID";
        chkStudentList.DataBind();
    }

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        lstStudenttIds = new List<int>();
        foreach (ListItem item in chkStudentList.Items)
        {
            if (item.Selected)
            {
                lstStudenttIds.Add(Convert.ToInt16(item.Value));
            }
        }

        lstStudent = new List<Student>();
        lstStudent = GetStudentList();

        var students=(from s in lstStudent
                     where lstStudenttIds.Contains(s.StudentID)
                          select s
                          ).ToList();

        if (students != null)
        {
            rptStudent.DataSource = students;
            rptStudent.DataBind();
        }
    }
}

SQL and Database

Tuesday, 11 June 2013

IN query in LINQ

Blog Archive