Overview

In this article, we will see how to use $anchorScroll with database. In our database, there are two tables, countries and cities. We will disable countries and their respective cities on a webpage with the help of AngularJS. In previous articles, we have seen how to use anchorScroll part .

scroll

For more articles on AngularJS, refer here.

Introduction

First, we create tables and insert some data into those. So, let's create a table called Country.
  1. Create Table Country
  2. ( Id int primary key identity,
  3. name varchar(50)
  4. )
  1. insert into country values('India')
  2. insert into country values('USA')
  3. insert into country values('UK')
Now, create another table,City and in that, pass the CountryId as foreign key reference.
  1. Create table City
  2. (id int primary key identity,
  3. name varchar(50),
  4. CountryId int foreign key references country(Id))
Now, we insert the data.
  1. insert into City values('Mumbai', 1)
  2. insert into City values('Chennai', 1)
  3. insert into City values('Bangalore', 1)
  4. insert into City values('Delhi', 1)
  5. insert into City values('New York', 2)
  6. insert into City values('LA', 2)
  7. insert into City values('Chicago', 2)
  8. insert into City values('London', 3)
Now, let's see the output.
output

Now, let's add connectionString in our web.config file.
  1. <connectionStrings>
  2. <add name="Test" connectionString="Data Source=IBALL-PC;Initial Catalog=TEST;Persist Security Info=True;User ID=sa;Password=p@ssw0rd" providerName="System.Data.SqlClient" /> </connectionStrings>
As we are going to use webService HTTP protocol, so add these in your web.config file.
  1. <system.web>
  2. <compilation debug="true" targetFramework="4.0" />
  3. <webServices>
  4. <protocols>
  5. <add name="HttpGet" /> </protocols>
  6. </webServices>
  7. </system.web>
Now, we will add two class files, Country and City. Here, I have added a class file called City.cs and have assigned the get and set properties.
  1. public int Id { get; set; }
  2. public string Name { get; set; }
  3. public string CountryId { get; set; }
Now, add another class file as Country.cs.
  1. public class Country {
  2. public int Id {
  3. get;
  4. set;
  5. }
  6. public string Name {
  7. get;
  8. set;
  9. }
  10. public List < City > Cities {
  11. get;
  12. set;
  13. }
  14. }
As we are going to display the list of cities that belong to class city. Let's add a webService to our project as we are fetching the data from our database.

Now, let's create a webService. Right click on the Web and create a webService.

webservice

Name this service as CountryService.asmx.

In that, add these imports as we are using some of the ADO.NET code for our web service.
  1. using System.Data;
  2. using System.Data.SqlClient;
  3. using System.Configuration;
  4. using System.Web.Script.Serialization;
As the last import says, we are using JSON to process our web service as script.serialization. Now, just copy this code in your web service.
  1. public void GetData() {
  2. List < Country > listCountries = new List < Country > ();
  3. string cs = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
  4. using(SqlConnection con = new SqlConnection(cs)) {
  5. SqlCommand cmd = new SqlCommand("select * from Country;Select * from City ", con);
  6. SqlDataAdapter da = new SqlDataAdapter(cmd);
  7. DataSet ds = new DataSet();
  8. da.Fill(ds);
  9. DataView dataview = new DataView(ds.Tables[1]);
  10. foreach(DataRow countryDataRow in ds.Tables[0].Rows) {
  11. Country country = new Country();
  12. country.Id = Convert.ToInt32(countryDataRow["Id"]);
  13. country.Name = countryDataRow["Name"].ToString();
  14. dataview.RowFilter = "CountryID='" + country.Id + "'";
  15. List < City > listcities = new List < City > ();
  16. foreach(DataRowView cityDataRowView in dataview) {
  17. DataRow cityDataRow = cityDataRowView.Row;
  18. City city = new City();
  19. city.Id = Convert.ToInt32(cityDataRow["Id"]);
  20. city.Name = cityDataRow["Name"].ToString();
  21. city.CountryId = cityDataRow["CountryId"].ToString();
  22. listcities.Add(city);
  23. }
  24. country.Cities = listcities;
  25. listCountries.Add(country);
  26. }
  27. }
  28. JavaScriptSerializer js = new JavaScriptSerializer();
  29. Context.Response.Write(js.Serialize(listCountries));
  30. }
highlighted section

The highlighted section in the above picture contains an ADO.NET code that shows, we are displaying the list of countries as India, USA, and UK respectively. Similarly, we are using a Connection String called Test which we passed in our web.config file.
Now, we are just trying to establish our connection with our SQL Server by using sqlconnection statement and passing those two queries.

Now, loop those records as,

code

Here, we are using foreach statement to loop those records. In the first foreach, we are looping those as list of countries, in which we are passing those corresponding names and cities too.

In the next foreach statement, we are looping the list of cities. If India is the country, then the list of cities displays the cities as Bangalore,Chennai, and so on.

Now, let's test our service. Run the service.

service

Click on GetData.

GetData

Now, invoke the service and see the output.

output

As you can see, we got the desired JSON output.

Now, we will add some code to our Controller. Let's include a JavaScript file and name it as Script.js.

controller

In this file, just add AngularJS reference.
  1. /// <reference path="angular.js" />
Drag and drop the AngularJS file.
Now, we add a Module named as demoApp and assign a Controller named as CountryController. In that, we will add a function where we add the following:
  1. var demoApp = angular.module("demoApp", [])
  2. .controller("countryController", function ($scope, $http, $location, $anchorScroll)
Now, we reference the service which we have created.
  1. $http.get("CountryService.asmx/GetData")
  2. .then(function (response) {
  3. $scope.countries = response.data;
  5. });
We have referenced our service by using $http.get and attached it to our $scope object by response the data .

Now, just add this code.
  1. $scope.scrollTo = function(countryName) {
  2. $location.hash(countryName);
  3. $anchorScroll();
  4. }
As the location uses hash, so when we will click on the button, a hash gets implemented. Similarly, when India button is clicked, it scrolls to Indian cities as we are using .scrollto and in that function, we are passing country name.

So, our final code is.
  1. /// <reference path="angular.js" />
  2. var demoApp = angular.module("demoApp", []).controller("countryController", function($scope, $http, $location, $anchorScroll) {
  3. $http.get("CountryService.asmx/GetData").then(function(response) {
  4. $scope.countries = response.data;
  5. });
  6. $scope.scrollTo = function(countryName) {
  7. $location.hash(countryName);
  8. $anchorScroll();
  9. }
  10. });
Now, just add a simple HTML Page to your solution.

HTML Page

First, we add the reference of our script file and Angular file too.
  1. <script src="scripts/angular.js"></script>
  3. <script src="scripts/Script.js"></script>
  4. <link href="Style.css" rel="stylesheet" />
In that, we add a Module and Controller name.
  1. <html ng-app="demoApp">
  2. <body ng-controller="countryController">
Now, add this code.
  1. <span ng-repeat="country in countries">
  2. <button ng-click="scrollTo(country.Name)">{{country.Name}}</button>
  4. </span>
  5. <br/><br/>
  6. <div>
  7. <fieldset ng-repeat="country in countries" id="{{country.Name}}">
  8. <legend>{{country.Name}}</legend>
  9. <ul>
  10. <li ng-repeat="city in country.Cities">
  11. {{city.Name }}
  12. </li>
  13. </ul>
  15. </fieldset>
  17. </div>
We have added a button to its respective country, as we are using ng-click directive .

As we are looping those records, we are using ng-repeat directive and displaying its country name and cities name respectively .

So, our final HTML page code is.
  1. <!DOCTYPE html>
  2. <html ng-app="demoApp">
  3. <head>
  4. <title></title>
  5. <meta charset="utf-8" />
  6. <script src="scripts/angular.js"></script>
  8. <script src="scripts/Script.js"></script>
  9. <link href="Style.css" rel="stylesheet" />
  11. </head>
  12. <body ng-controller="countryController">
  13. <span ng-repeat="country in countries">
  14. <button ng-click="scrollTo(country.Name)">{{country.Name}}</button>
  16. </span>
  17. <br/><br/>
  18. <div>
  19. <fieldset ng-repeat="country in countries" id="{{country.Name}}">
  20. <legend>{{country.Name}}</legend>
  21. <ul>
  22. <li ng-repeat="city in country.Cities">
  23. {{city.Name }}
  24. </li>
  25. </ul>
  27. </fieldset>
  29. </div>
  31. </body>
  32. </html>
Just add some styles to the page.
  1. body {
  3. font-family:Arial;
  4. }
  5. div {
  6. display:block;
  7. font-size:xx-large;
  8. height:350px;
  9. width:400px;
  10. border:1px solid black;
  11. padding:10px;
  12. overflow-y:scroll;
  13. }
Now, run the code.

output
We have got the desired output.

When click on India, see the URL. A # has appeared.

output

Similarly, for USA and UK also, we are able to scroll through.

Conclusion

This was all about $anchorScroll service with database. Hope this article was helpful.