Sql server, .net and c# video tutorial: Angular anchorscroll with database data

Suggested Videos
Part 19 - AngularJS Services
Part 20 - Create custom service in AngularJS
Part 21 - AngularJS anchorscroll example

(追記) (追記ここまで)

In Part 21 of AngularJS tutorial we discussed using anchorscroll service with hardcoded data. In this video we will discuss using angular anchorscroll service with database data.

(追記) (追記ここまで)

So here is what we want to do. Retrieve the countries and cities data from respective tables in the SQL server database and display it on the web page. When we click on a country button, the page should automatically scroll to the respective country and it's cities.

Angular anchorscroll with database data

Step 1 : Create SQL Server tables and insert data

Create Table tblCountry

(

Id int primary key identity,

Name nvarchar(50)

)

Insert into tblCountry values ('India')

Insert into tblCountry values ('USA')

Insert into tblCountry values ('UK')

Create Table tblCity

(

Id int primary key identity,

Name nvarchar(50),

CountryId int foreign key references tblCountry(Id)

)

Insert into tblCity values ('Mumbai', 1)

Insert into tblCity values ('Delhi', 1)

Insert into tblCity values ('Bangalore', 1)

Insert into tblCity values ('Chennai', 1)

Insert into tblCity values ('Hyderabad', 1)

Insert into tblCity values ('New York', 2)

Insert into tblCity values ('Los Angeles', 2)

Insert into tblCity values ('Chicago', 2)

Insert into tblCity values ('Houston', 2)

Insert into tblCity values ('Philadelphia', 2)

Insert into tblCity values ('London', 3)

Insert into tblCity values ('Birmingham', 3)

Insert into tblCity values ('Coventry', 3)

Insert into tblCity values ('Liverpool', 3)

Insert into tblCity values ('Manchester', 3)

Step 2 : Create new empty asp.net web application project. Name it Demo.

Step 3 : Include the following settings in web.config file.

<?xmlversion="1.0"encoding="utf-8"?>

<addname="DBCS"

connectionString="server=.;database=SampleDB; integrated security=SSPI"/>

</connectionStrings>

<system.web>

<addname="HttpGet"/>

</protocols>

</webServices>

</system.web>

</configuration>

Step 4 : Add a class file to the project. Name it City.cs. Copy and paste the following code.

namespace Demo

{

publicclassCity

{

publicint Id { get; set; }

publicstring Name { get; set; }

publicint CountryId { get; set; }

}

Step 5 : Add a class file to the project. Name it Country.cs. Copy and paste the following code.

using System.Collections.Generic;

namespace Demo

{

publicclassCountry

{

publicint Id { get; set; }

publicstring Name { get; set; }

publicList<City> Cities { get; set; }

}

Step 6 : Add a new WebService (ASMX). Name it CountryService.asmx. Copy and paste the following code.

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Web.Script.Serialization;

using System.Web.Services;

namespace Demo

{

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.ComponentModel.ToolboxItem(false)]

[System.Web.Script.Services.ScriptService]

publicclassCountryService : System.Web.Services.WebService

{

[WebMethod]

publicvoid GetData()

{

List<Country> listCountries = newList<Country>();

string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

using (SqlConnection con = newSqlConnection(cs))

{

SqlCommand cmd = newSqlCommand("Select * from tblCountry;Select * from tblCity", con);

SqlDataAdapter da = newSqlDataAdapter(cmd);

DataSet ds = newDataSet();

da.Fill(ds);

DataView dataView = newDataView(ds.Tables[1]);

foreach (DataRow countryDataRow in ds.Tables[0].Rows)

{

Country country = newCountry();

country.Id = Convert.ToInt32(countryDataRow["Id"]);

country.Name = countryDataRow["Name"].ToString();

dataView.RowFilter = "CountryId = '" + country.Id + "'";

List<City> listCities = newList<City>();

foreach (DataRowView cityDataRowView in dataView)

{

DataRow cityDataRow = cityDataRowView.Row;

City city = newCity();

city.Id = Convert.ToInt32(cityDataRow["Id"]);

city.Name = cityDataRow["Name"].ToString();

city.CountryId = Convert.ToInt32(cityDataRow["CountryId"]);

listCities.Add(city);

}

country.Cities = listCities;

listCountries.Add(country);

}

JavaScriptSerializer js = newJavaScriptSerializer();

Context.Response.Write(js.Serialize(listCountries));

}

Step 7 : Add a new folder to the project. Name it Scripts. Add a new JavaScript file to the Scripts folder. Name it Script.js. Copy and paste the following code.

/// <reference path="angular.js" />

var demoApp = angular.module("demoApp", [])

.controller("countryController",

function ($scope, $location, $anchorScroll, $http) {

$http.get("CountryService.asmx/GetData")

.then(function (response) {

$scope.countries = response.data;

});

$scope.scrollTo = function (countryName) {

$location.hash(countryName);

$anchorScroll();

}

});

Step 8 : Add a new stylesheet to the project. Name it Styles.css. Copy and paste the following styles in it.

body {

font-family: Arial;

}

div {

display: block;

font-size: xx-large;

height: 350px;

width: 400px;

border: 1pxsolidblack;

padding: 10px;

overflow-y: scroll;

}

Step 9 : Add an HTML page to the ASP.NET project. Copy and paste the following HTML and Angular code

<!DOCTYPEhtml>

<htmlxmlns="http://www.w3.org/1999/xhtml"ng-app="demoApp">

<head>

<scriptsrc="Scripts/angular.js"></script>

<scriptsrc="Scripts/Script.js"></script>

<linkhref="Styles.css"rel="stylesheet"/>

</head>

<bodyng-controller="countryController">

<spanng-repeat="country in countries">

<buttonng-click="scrollTo(country.Name)">{{country.Name}}</button>

</span>

<divclass="containerDiv">

<fieldsetng-repeat="country in countries"id="{{country.Name}}">

<legend>{{country.Name}}</legend>

<ul>

<ling-repeat="city in country.Cities">

</li>

</ul>

</fieldset>

</div>

</body>

</html>

AngularJS tutorial for beginners

Angular anchorscroll with database data

2 comments: