Filter HTML Table using Multiselect DropDownList using C# and VB.Net in AngularJS

Last Reply 6 months ago By dharmendr

Posted 6 months ago

How to filter table using multiselect dropdownlist in angularjs

Posted 6 months ago

Hi rani,

Check this example. Now please take its reference and correct your code.

Here i have used SplitString function in the query. So to know more about it refer below article.

Split and convert Comma Separated (Delimited) String to Table in SQL Server

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.4.3/angular.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.5/css/bootstrap.min.css" />
<script type="text/javascript">
    var app = angular.module('MyApp', []);
    app.directive('multiselectDropdown', function () {
        return {
            restrict: 'E',
            scope: {
                model: '=',
                options: '='
            },
            template:
        "<div class='btn-group' data-ng-class='{open: open}' style='width: 200px;'>" +
        "<button type='button' class='btn btn-small' style='width: 160px;height: 30px;'>---Select---</button>" +
        "<button type='button' class='btn btn-small dropdown-toggle' data-ng-click='openDropdown()' style='width: 40px;height: 30px;' ><span class='caret'></span></button>" +
        "<ul class='dropdown-menu' aria-labelledby='dropdownMenu' style='position: relative;'>" +
        "<li style='cursor:pointer;' data-ng-repeat='option in options'><a data-ng-click='toggleSelectItem(option)'><span data-ng-class='getClassName(option)' aria-hidden='true'></span> {{option.name}}</a></li>" +
        "</ul></div>",
            controller: function ($scope) {
                $scope.openDropdown = function () {
                    $scope.open = !$scope.open;
                };

                $scope.selectAll = function () {
                    $scope.model = [];
                    angular.forEach($scope.options, function (item, index) {
                        $scope.model.push(item);
                    });
                };

                $scope.deselectAll = function () {
                    $scope.model = [];
                };

                $scope.toggleSelectItem = function (option) {
                    var intIndex = -1;
                    angular.forEach($scope.model, function (item, index) {
                        if (item.id == option.id) {
                            intIndex = index;
                        }
                    });
                    if (intIndex >= 0) {
                        $scope.model.splice(intIndex, 1);
                    } else {
                        $scope.model.push(option);
                    }
                };

                $scope.getClassName = function (option) {
                    var varClassName = 'glyphicon glyphicon-remove-circle';
                    angular.forEach($scope.model, function (item, index) {
                        if (item.id == option.id) {
                            varClassName = 'glyphicon glyphicon-ok-circle';
                        }
                    });
                    return (varClassName);
                };
            }
        }
    });

    app.controller("MyController", function ($scope, $http, $window) {
        $scope.SelectedCities = [];
        $scope.Cities = [
                { id: 1, name: 'Seattle' },
                { id: 2, name: 'Tacoma' },
                { id: 3, name: 'Kirkland' },
                { id: 4, name: 'Redmond' },
                { id: 5, name: 'London' }
            ];
        var data = { cities: "" };
        GetEmployees(data);

        $scope.Search = function () {
            var cities = "";
            for (var i = 0; i < $scope.SelectedCities.length; i++) {
                if ($scope.SelectedCities[i].id) {
                    cities += $scope.SelectedCities[i].name + ",";
                }
            }
            var data = { cities: cities };
            GetEmployees(data);
        }

        function GetEmployees(data) {
            $http.post("Default.aspx/GetEmployees", JSON.stringify(data), { headers: { 'Content-Type': 'application/json'} })
            .then(function (response) {
                $scope.Employees = eval(response.data.d);
            }, function error(response) {
                alert(response.responseText);
            });
        }
    });
</script>
<div ng-app="MyApp" ng-controller="MyController">
    <multiselect-dropdown model="SelectedCities" options="Cities "></multiselect-dropdown>
    <input type="button" value="Search" ng-click="Search()" class="btn btn-default" />
    <hr />
    <table class="table table-table-responsive">
        <thead>
            <tr>
                <th>Id</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>City</th>
                <th>Country</th>
            </tr>
        </thead>
        <tbody>
            <tr ng-repeat="employee in Employees">
                <td>{{ employee.EmployeeId }}</td>
                <td>{{ employee.FirstName }}</td>
                <td>{{ employee.LastName }}</td>
                <td>{{ employee.City }}</td>
                <td>{{ employee.Country }}</td>
            </tr>
        </tbody>
    </table>
</div>

Namespaces

C#

using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;

VB.Net

Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization
Imports System.Web.Services

Code

C#

[WebMethod]
public static string GetEmployees(string cities)
{
    List<object> employees = new List<object>();
    string sql = "SELECT * FROM Employees WHERE City IN(SELECT Item FROM dbo.SplitString(@Cities, ',')) OR @Cities IS NULL";
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlCommand cmd = new SqlCommand(sql))
        {
            cmd.Parameters.AddWithValue("@Cities", !string.IsNullOrEmpty(cities) ? cities : (object)DBNull.Value);
            cmd.Connection = conn;
            conn.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    employees.Add(new
                    {
                        EmployeeId = sdr["EmployeeID"],
                        FirstName = sdr["FirstName"],
                        LastName = sdr["LastName"],
                        City = sdr["City"],
                        Country = sdr["Country"]
                    });
                }
            }
            conn.Close();
        }
    }
 
    return (new JavaScriptSerializer().Serialize(employees));
}

VB.Net

<WebMethod()>
Public Shared Function GetEmployees(ByVal cities As String) As String
    Dim employees As List(Of Object) = New List(Of Object)()
    Dim sql As String = "SELECT * FROM Employees WHERE City IN(SELECT Item FROM dbo.SplitString(@Cities, ',')) OR @Cities IS NULL"
    Using conn As SqlConnection = New SqlConnection()
        conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using cmd As SqlCommand = New SqlCommand(sql)
            cmd.Parameters.AddWithValue("@Cities", If(Not String.IsNullOrEmpty(cities), cities, CObj(DBNull.Value)))
            cmd.Connection = conn
            conn.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    employees.Add(New With {
                        .EmployeeId = sdr("EmployeeID"),
                        .FirstName = sdr("FirstName"),
                        .LastName = sdr("LastName"),
                        .City = sdr("City"),
                        .Country = sdr("Country")
                    })
                End While
            End Using
 
            conn.Close()
        End Using
    End Using
 
    Return (New JavaScriptSerializer().Serialize(employees))
End Function

Screenshot