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.
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
