Reader Level:
Article
ASP.NET

AngularJS AutoComplete In MVC With Web API

By Sibeesh Venu on Mar 23 2016
In this article we will learn how we can create AngularJS AutoComplete text box with the data from SQL Server database.
    • Like
    • Love It
    • Awesome
    • Interesting
    • It's Okay
    • Thumbs Down
  • 24.9k
  • 0

In this article we will learn how we can create AngularJS Autocomplete text box with the data from SQL Server database. We use MVC architecture with Web API and AngularJS to fetch the data and do all the manipulations. I am creating this application in Visual Studio 2015. 

Now we will go and create our application. I hope you will like this.

Download the source code

You can always download the source code here.

Background

For the past few days I have been experimenting with a few things in AngularJS. Here we are going to see a demo of how to use AngulaJS Autocomplete in MVC with Web API to fetch the data from database. Once we are done, this is how our applications output will be.

Angular_JS_Autocomplete_In_MVC_With_Web_API_Output_

Figure: Angular_JS_Autocomplete_In_MVC_With_Web_API_Output_

Angular_JS_Autocomplete_In_MVC_With_Web_API_Output_With_Filter_
Figure: Angular_JS_Autocomplete_In_MVC_With_Web_API_Output_With_Filter_

Create a MVC application

Click File, New, Project then select MVC application. From the following pop up we will select the template as empty and select the core references and folders for MVC.

Empty Template With MVC And Web API Folders

Figure: Empty Template With MVC And Web API Folders

Once you click OK, a project with MVC like folder structure with core references will be created for you.

Folder Structure And References For Empty MVC Project

Figure: Folder Structure And References For Empty MVC Project

Before going to start the coding part, make sure that all the required extensions/references are installed. Below are the required things to start with.

  • AngularJS
  • jQuery

You can all the items mentioned above from NuGet. Right click on your project name and select Manage NuGet packages.

Manage NuGet Package Window

Figure: Manage NuGet Package Window

Once you have installed those items, please make sure that all the items (jQuery, Angular JS files) are loaded in your scripts folder.

Using the code

As I have said before, we are going to use Angular JS for our client side operations, so it is better to create the AngularJS script files first right? Just to make sure that we have got all the required things :). For that I am going to create a script file called Home.js in which we will write our scripts. Sounds good? Yes, we have set everything to get started our coding. Now we will create a Web API controller and get the data from database in JSON format. Let’s start then.

We will set up our database first so that we can create Entity Model for our application later.

Create a database

The following query can be used to create a database in your SQL Server.

  1. USE [master]  
  2. GO  
  3. /****** Object: Database [TrialsDB]  
  4. CREATE DATABASE [TrialsDB]  
  5. CONTAINMENT = NONE  
  6. ON PRIMARY  
  7. NAME = N'TrialsDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TrialsDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  
  8. LOG ON  
  9. NAME = N'TrialsDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TrialsDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)  
  10. GO  
  11. ALTER DATABASE [TrialsDB] SET COMPATIBILITY_LEVEL = 110  
  12. GO  
  13. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
  14. begin  
  15. EXEC [TrialsDB].[dbo].[sp_fulltext_database] @action = 'enable'  
  16. end  
  17. GO  
  18. ALTER DATABASE [TrialsDB] SET ANSI_NULL_DEFAULT OFF  
  19. GO  
  20. ALTER DATABASE [TrialsDB] SET ANSI_NULLS OFF  
  21. GO  
  22. ALTER DATABASE [TrialsDB] SET ANSI_PADDING OFF  
  23. GO  
  24. ALTER DATABASE [TrialsDB] SET ANSI_WARNINGS OFF  
  25. GO  
  26. ALTER DATABASE [TrialsDB] SET ARITHABORT OFF  
  27. GO  
  28. ALTER DATABASE [TrialsDB] SET AUTO_CLOSE OFF  
  29. GO  
  30. ALTER DATABASE [TrialsDB] SET AUTO_CREATE_STATISTICS ON  
  31. GO  
  32. ALTER DATABASE [TrialsDB] SET AUTO_SHRINK OFF  
  33. GO  
  34. ALTER DATABASE [TrialsDB] SET AUTO_UPDATE_STATISTICS ON  
  35. GO  
  36. ALTER DATABASE [TrialsDB] SET CURSOR_CLOSE_ON_COMMIT OFF  
  37. GO  
  38. ALTER DATABASE [TrialsDB] SET CURSOR_DEFAULT GLOBAL  
  39. GO  
  40. ALTER DATABASE [TrialsDB] SET CONCAT_NULL_YIELDS_NULL OFF  
  41. GO  
  42. ALTER DATABASE [TrialsDB] SET NUMERIC_ROUNDABORT OFF  
  43. GO  
  44. ALTER DATABASE [TrialsDB] SET QUOTED_IDENTIFIER OFF  
  45. GO  
  46. ALTER DATABASE [TrialsDB] SET RECURSIVE_TRIGGERS OFF  
  47. GO  
  48. ALTER DATABASE [TrialsDB] SET DISABLE_BROKER  
  49. GO  
  50. ALTER DATABASE [TrialsDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF  
  51. GO  
  52. ALTER DATABASE [TrialsDB] SET DATE_CORRELATION_OPTIMIZATION OFF  
  53. GO  
  54. ALTER DATABASE [TrialsDB] SET TRUSTWORTHY OFF  
  55. GO  
  56. ALTER DATABASE [TrialsDB] SET ALLOW_SNAPSHOT_ISOLATION OFF  
  57. GO  
  58. ALTER DATABASE [TrialsDB] SET PARAMETERIZATION SIMPLE  
  59. GO  
  60. ALTER DATABASE [TrialsDB] SET READ_COMMITTED_SNAPSHOT OFF  
  61. GO  
  62. ALTER DATABASE [TrialsDB] SET HONOR_BROKER_PRIORITY OFF  
  63. GO  
  64. ALTER DATABASE [TrialsDB] SET RECOVERY FULL  
  65. GO  
  66. ALTER DATABASE [TrialsDB] SET MULTI_USER  
  67. GO  
  68. ALTER DATABASE [TrialsDB] SET PAGE_VERIFY CHECKSUM  
  69. GO  
  70. ALTER DATABASE [TrialsDB] SET DB_CHAINING OFF  
  71. GO  
  72. ALTER DATABASE [TrialsDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )  
  73. GO  
  74. ALTER DATABASE [TrialsDB] SET TARGET_RECOVERY_TIME = 0 SECONDS  
  75. GO  
  76. ALTER DATABASE [TrialsDB] SET READ_WRITE  
  77. GO   

Now we will create the table we needed. As of now I am going to create the table Products

Create tables in database

The following is the query to create the table Product.

  1. USE [TrialsDB]  
  2. GO  
  3. /****** Object: Table [dbo].[Product]  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. CREATE TABLE [dbo].[Product](  
  9. [ProductID] [intNOT NULL,  
  10. [Name] [nvarchar](maxNOT NULL,  
  11. [ProductNumber] [nvarchar](25) NOT NULL,  
  12. [MakeFlag] [bitNOT NULL,  
  13. [FinishedGoodsFlag] [bitNOT NULL,  
  14. [Color] [nvarchar](15) NULL,  
  15. [SafetyStockLevel] [smallintNOT NULL,  
  16. [ReorderPoint] [smallintNOT NULL,  
  17. [StandardCost] [money] NOT NULL,  
  18. [ListPrice] [money] NOT NULL,  
  19. [Size] [nvarchar](5) NULL,  
  20. [SizeUnitMeasureCode] [nchar](3) NULL,  
  21. [WeightUnitMeasureCode] [nchar](3) NULL,  
  22. [Weight] [decimal](8, 2) NULL,  
  23. [DaysToManufacture] [intNOT NULL,  
  24. [ProductLine] [nchar](2) NULL,  
  25. [Class] [nchar](2) NULL,  
  26. [Style] [nchar](2) NULL,  
  27. [ProductSubcategoryID] [intNULL,  
  28. [ProductModelID] [intNULL,  
  29. [SellStartDate] [datetime] NOT NULL,  
  30. [SellEndDate] [datetime] NULL,  
  31. [DiscontinuedDate] [datetime] NULL,  
  32. [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,  
  33. [ModifiedDate] [datetime] NOT NULL  
  34. ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  35. GO   

Can we insert some data to the tables now?

Insert data to table

You can use the below query to insert the data to the table Product,

  1. USE [TrialsDB]  
  2. GO  
  3. INSERT INTO [dbo].[Product]  
  4. ([ProductID]  
  5. ,[Name]  
  6. ,[ProductNumber]  
  7. ,[MakeFlag]  
  8. ,[FinishedGoodsFlag]  
  9. ,[Color]  
  10. ,[SafetyStockLevel]  
  11. ,[ReorderPoint]  
  12. ,[StandardCost]  
  13. ,[ListPrice]  
  14. ,[Size]  
  15. ,[SizeUnitMeasureCode]  
  16. ,[WeightUnitMeasureCode]  
  17. ,[Weight]  
  18. ,[DaysToManufacture]  
  19. ,[ProductLine]  
  20. ,[Class]  
  21. ,[Style]  
  22. ,[ProductSubcategoryID]  
  23. ,[ProductModelID]  
  24. ,[SellStartDate]  
  25. ,[SellEndDate]  
  26. ,[DiscontinuedDate]  
  27. ,[rowguid]  
  28. ,[ModifiedDate])  
  29. VALUES  
  30. (<ProductID, int,>  
  31. ,<Name, nvarchar(max),>  
  32. ,<ProductNumber, nvarchar(25),>  
  33. ,<MakeFlag, bit,>  
  34. ,<FinishedGoodsFlag, bit,>  
  35. ,<Color, nvarchar(15),>  
  36. ,<SafetyStockLevel, smallint,>  
  37. ,<ReorderPoint, smallint,>  
  38. ,<StandardCost, money,>  
  39. ,<ListPrice, money,>  
  40. ,<Size, nvarchar(5),>  
  41. ,<SizeUnitMeasureCode, nchar(3),>  
  42. ,<WeightUnitMeasureCode, nchar(3),>  
  43. ,<Weight, decimal(8,2),>  
  44. ,<DaysToManufacture, int,>  
  45. ,<ProductLine, nchar(2),>  
  46. ,<Class, nchar(2),>  
  47. ,<Style, nchar(2),>  
  48. ,<ProductSubcategoryID, int,>  
  49. ,<ProductModelID, int,>  
  50. ,<SellStartDate, datetime,>  
  51. ,<SellEndDate, datetime,>  
  52. ,<DiscontinuedDate, datetime,>  
  53. ,<rowguid, uniqueidentifier,>  
  54. ,<ModifiedDate, datetime,>)  
  55. GO   

So let us say we have inserted the data as follows. If you feel bored of inserting data manually, you can always run the SQL script file attached which has the insertion queries. Just run that, you will be all OK. If you don’t know how to generate SQL scripts with data, I strongly recommend you to have a read here.

Next thing we are going to do is create a ADO.NET Entity Data Model.

Create Entity Data Model

Right click on your model folder and click new, select ADO.NET Entity Data Model. Follow the steps given. Once you have done the processes, you can see the edmx file and other files in your model folder. Here I gave Dashboard for our Entity data model name. Now you can see a file with edmx extension have been created. If you open that file, you can see as below.

Entity Data Model Product Table

Figure: Entity Data Model Product Table

Now will create our Web API controller.

Create Web API Controller

To create a Web API controller, just right click on your controller folder and click Add, Controller, then select Web API 2 controller with actions, using Entity Framework.

Web API 2 Controller With Actions Using Entity Framework

Figure: Web API 2 Controller With Actions Using Entity Framework

Now select Product (AngularJSAutocompleteInMVCWithWebAPI.Models) as our Model class and TrialsDBEntities (AngularJSAutocompleteInMVCWithWebAPI.Models) as data context class.

Model Class And Data Context Class

Figure: Model Class And Data Context Class

As you can see It has been given the name of our controller as Products. Here I am not going to change that, if you wish to change it, you can do that.

Now you will be given the following codes in our new Web API controller.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.Entity;  
  5. using System.Data.Entity.Infrastructure;  
  6. using System.Linq;  
  7. using System.Net;  
  8. using System.Net.Http;  
  9. using System.Web.Http;  
  10. using System.Web.Http.Description;  
  11. using AngularJSAutocompleteInMVCWithWebAPI.Models;  
  12. namespace AngularJSAutocompleteInMVCWithWebAPI.Controllers  
  13. {  
  14.     public class ProductsController: ApiController  
  15.     {  
  16.         private TrialsDBEntities db = new TrialsDBEntities();  
  17.         // GET: api/Products  
  18.         public IQueryable < Product > GetProducts()  
  19.         {  
  20.             return db.Products;  
  21.         }  
  22.         // GET: api/Products/5  
  23.         [ResponseType(typeof(Product))]  
  24.         public IHttpActionResult GetProduct(int id)  
  25.         {  
  26.             Product product = db.Products.Find(id);  
  27.             if (product == null)  
  28.             {  
  29.                 return NotFound();  
  30.             }  
  31.             return Ok(product);  
  32.         }  
  33.         // PUT: api/Products/5  
  34.         [ResponseType(typeof(void))]  
  35.         public IHttpActionResult PutProduct(int id, Product product)  
  36.         {  
  37.             if (!ModelState.IsValid)  
  38.             {  
  39.                 return BadRequest(ModelState);  
  40.             }  
  41.             if (id != product.ProductID)  
  42.             {  
  43.                 return BadRequest();  
  44.             }  
  45.             db.Entry(product).State = EntityState.Modified;  
  46.             try  
  47.             {  
  48.                 db.SaveChanges();  
  49.             }  
  50.             catch (DbUpdateConcurrencyException)  
  51.             {  
  52.                 if (!ProductExists(id))  
  53.                 {  
  54.                     return NotFound();  
  55.                 }  
  56.                 else  
  57.                 {  
  58.                     throw;  
  59.                 }  
  60.             }  
  61.             return StatusCode(HttpStatusCode.NoContent);  
  62.         }  
  63.         // POST: api/Products  
  64.         [ResponseType(typeof(Product))]  
  65.         public IHttpActionResult PostProduct(Product product)  
  66.         {  
  67.             if (!ModelState.IsValid)  
  68.             {  
  69.                 return BadRequest(ModelState);  
  70.             }  
  71.             db.Products.Add(product);  
  72.             try  
  73.             {  
  74.                 db.SaveChanges();  
  75.             }  
  76.             catch (DbUpdateException)  
  77.             {  
  78.                 if (ProductExists(product.ProductID))  
  79.                 {  
  80.                     return Conflict();  
  81.                 }  
  82.                 else  
  83.                 {  
  84.                     throw;  
  85.                 }  
  86.             }  
  87.             return CreatedAtRoute("DefaultApi"new  
  88.             {  
  89.                 id = product.ProductID  
  90.             }, product);  
  91.         }  
  92.         // DELETE: api/Products/5  
  93.         [ResponseType(typeof(Product))]  
  94.         public IHttpActionResult DeleteProduct(int id)  
  95.         {  
  96.             Product product = db.Products.Find(id);  
  97.             if (product == null)  
  98.             {  
  99.                 return NotFound();  
  100.             }  
  101.             db.Products.Remove(product);  
  102.             db.SaveChanges();  
  103.             return Ok(product);  
  104.         }  
  105.         protected override void Dispose(bool disposing)  
  106.         {  
  107.             if (disposing)  
  108.             {  
  109.                 db.Dispose();  
  110.             }  
  111.             base.Dispose(disposing);  
  112.         }  
  113.         private bool ProductExists(int id)  
  114.         {  
  115.             return db.Products.Count(e => e.ProductID == id) > 0;  
  116.         }  
  117.     }  
  118. }   

As we are not going to use only read operation, you can remove other functionalities and keep only Getmethods.

  1. // GET: api/Products  
  2. public IQueryable<Product> GetProducts()  
  3. {  
  4.    return db.Products;  
  5. }   

So the coding part to fetch the data from database is ready, now we need to check whether our Web API is ready for action!. To check that, you just need to run the URL http://localhost:9038/api/products. Hereproducts is our Web API controller name. I hope you get the data as a result.

Web API Result

Figure: Web API Result

Now we will go back to our AngularJS file and consume this Web API. You need to change the scripts in the Home.js as follows.

  1. (function()  
  2. {  
  3.     'use strict';  
  4.     angular.module('MyApp', ['ngMaterial''ngMessages''material.svgAssetsCache']).controller('AutoCompleteCtrl', AutoCompleteCtrl);  
  5.   
  6.     function AutoCompleteCtrl($http, $timeout, $q, $log)  
  7.     {  
  8.         var self = this;  
  9.         self.simulateQuery = true;  
  10.         self.products = loadAllProducts($http);  
  11.         self.querySearch = querySearch;  
  12.   
  13.         function querySearch(query)  
  14.         {  
  15.             var results = query ? self.products.filter(createFilterFor(query)) : self.products,  
  16.                 deferred;  
  17.             if (self.simulateQuery)  
  18.             {  
  19.                 deferred = $q.defer();  
  20.                 $timeout(function()  
  21.                 {  
  22.                     deferred.resolve(results);  
  23.                 }, Math.random() * 1000, false);  
  24.                 return deferred.promise;  
  25.             }  
  26.             else  
  27.             {  
  28.                 return results;  
  29.             }  
  30.         }  
  31.   
  32.         function loadAllProducts($http)  
  33.         {  
  34.             var allProducts = [];  
  35.             var url = '';  
  36.             var result = [];  
  37.             url = 'api/products';  
  38.             $http(  
  39.             {  
  40.                 method: 'GET',  
  41.                 url: url,  
  42.             }).then(function successCallback(response)  
  43.             {  
  44.                 allProducts = response.data;  
  45.                 angular.forEach(allProducts, function(product, key)  
  46.                 {  
  47.                     result.push(  
  48.                     {  
  49.                         value: product.Name.toLowerCase(),  
  50.                         display: product.Name  
  51.                     });  
  52.                 });  
  53.             }, function errorCallback(response)  
  54.             {  
  55.                 console.log('Oops! Something went wrong while fetching the data. Status Code: ' + response.status + ' Status statusText: ' + response.statusText);  
  56.             });  
  57.             return result;  
  58.         }  
  59.   
  60.         function createFilterFor(query)  
  61.         {  
  62.             var lowercaseQuery = angular.lowercase(query);  
  63.             return function filterFn(product)  
  64.             {  
  65.                 return (product.value.indexOf(lowercaseQuery) === 0);  
  66.             };  
  67.         }  
  68.     }  
  69. })();   

Here, MyApp is our module name and AutoCompleteCtrl is our controller name. The function loadAllProducts is for loading the products from database using $http in Angular JS.

Once our service is called, we will get the data in return. We will parse the same and store it in a variable for future use. We will loop through the same using angular.forEach and format as needed.

  1. angular.forEach(allProducts, function(product, key)  
  2. {  
  3.     result.push(  
  4.     {  
  5.         value: product.Name.toLowerCase(),  
  6.         display: product.Name  
  7.     });  
  8. });   

The function querySearch will be called when ever user search for any particular products. This we will call from the view as follows.

  1. md-items="item in ctrl.querySearch(ctrl.searchText)"    

Now we need a view to show our data right? Yes, we need a controller too!.

Create a MVC controller

To create a controller, we need to right click on the controller folder, Add – Controller. I hope you will be given a controller as follows.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. namespace AngularJSAutocompleteInMVCWithWebAPI.Controllers  
  7. {  
  8.     public class HomeController: Controller  
  9.     {  
  10.         // GET: Home  
  11.         public ActionResult Index()  
  12.         {  
  13.             return View();  
  14.         }  
  15.     }  
  16. }   

Here, Home is our controller name.

Now we need a view, right?

Creating a view

To create a view, just right click on your controller name, Add View, then click Add.

Creating a view

Figure: Creating a view

Now in your view add the needed references.

  1. <script src="~/scripts/angular.min.js"></script>  
  2. <script src="~/scripts/angular-route.min.js"></script>  
  3. <script src="~/scripts/angular-aria.min.js"></script>  
  4. <script src="~/scripts/angular-animate.min.js"></script>  
  5. <script src="~/scripts/angular-messages.min.js"></script>  
  6. <script src="~/scripts/angular-material.js"></script>  
  7. <script src="~/scripts/svg-assets-cache.js"></script>  
  8. <script src="~/scripts/Home.js"></script>   

Once we add the references, we can call our AngularJS controller and change the view code as follows.

  1. <div ng-controller="AutoCompleteCtrl as ctrl" layout="column" ng-cloak="" class="autocompletedemoBasicUsage" ng-app="MyApp" style="width: 34%;">  
  2.     <md-content class="md-padding">  
  3.         <form ng-submit="$event.preventDefault()">  
  4.             <md-autocomplete md-no-cache="false" md-selected-item="ctrl.selectedItem" md-search-text="ctrl.searchText" md-items="item in ctrl.querySearch(ctrl.searchText)" md-item-text="item.display" md-min-length="0" placeholder="Search for products here!.">  
  5.                 <md-item-template> <span md-highlight-text="ctrl.searchText" md-highlight-flags="^i">{{item.display}}</span> </md-item-template>  
  6.                 <md-not-found> No matching "{{ctrl.searchText}}" were found. </md-not-found>  
  7.             </md-autocomplete>  
  8.         </form>  
  9.     </md-content>  
  10. </div>   

Here md-autocomplete will cache the result we gets from database to avoid the unwanted hits to the database. This we can disable/enable by the help of md-no-cache. Now if you run your application, you can see our Web API call works fine and successfully get the data. But the page looks clumsy, right? For this you must add a style sheet angular-material.css. 

  1. <link href="~/Content/angular-material.css" rel="stylesheet" />   

Now run your page again, I am sure you will get the output as follows.

Output

Angular_JS_Autocomplete_In_MVC_With_Web_API_Output_
Figure: Angular_JS_Autocomplete_In_MVC_With_Web_API_Output_

Angular_JS_Autocomplete_In_MVC_With_Web_API_Output_With_Filter_
Figure: Angular_JS_Autocomplete_In_MVC_With_Web_API_Output_With_Filter_

We have done everything!. That’s fantastic, right? Have a happy coding.

Reference

Conclusion

Did I miss anything that you may think is needed? Did you try Web API yet? Have you ever wanted to do this requirement? Did you find this post useful? I hope you liked this article. Please share with me your valuable suggestions and feedback.

Your turn. What do you think?

A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, Asp.Net Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can.

Please read this article in my blog here.

Read more articles on ASP.NET: