Asp.Net Web API Example – AutoComplete Textbox using jQuery Ajax and Web API – C# and Vb.Net

← PrevNext →

I have written few articles before on AutoComplete textbox using jQuery UI AutoComplete plug-in, with various web service technologies such as Asp.Net Web Service (ASMX) and WCF services. The two web services, which I have mentioned could not cope up with recent advancements and have lost steam in the process. However, we now have a better API to create web services and that is the Asp.Net Web API. Therefore, here in this article I’ll show you how to create a simple web service using Web API for an AutoComplete textbox using jQuery AutoComplete plug-in.

AutoComplete text using jQuery and Asp.Net Web API

The Asp.Net Web API framework uses pure http methods for building services, which could serve a wide range of clients or applications, which run either on browsers (any modern browser) or use small devices such as a mobile phone. Asp.Net Web API is simple and flexible.

Anyways, you may search the web for more details on Asp.Net Web API or wait until I add more resources (posts) on the technology. Meanwhile, let’s create a simple web service for our AutoComplete textbox.

Related: How to Implement AutoComplete Feature in AngularJS using SQL Server Database with Web API - C# and Vb.Net

Create an SQL Server table

The Web API will extract data from an SQL Server table called the Books. Yes, I am fond of books and I already have a books table created for examples such as this. Here you can find the table.

Create Asp.Net Web API

We will now create a Web API in Asp.net. Therefore, start Visual Studio and select New Project from the File menu. In the New Project window, under Installed Templates expand Visual C# and click Web. Choose Visual Basic if your preferred language is VB.

I have written a code in both VB and C#. Then choose the project called Asp.Net MVC 4 Web Application from the list of projects. Name the project as BooksApp and click OK.

New MVC4 Project in Asp.Net

The Studio will now open the New ASP.NET MVC 4 Project window, and it would ask you to select a template from a list of Project Template. Click Web API template and press the OK button.

Add a “Model” to the Application

The Model represents the data in the Web API. In the Solution Explorer, find a folder named Models. Right click the folder, choose Add and select the Class option. Name the class as Books.cs. (Book.vb for Visual Basic)

Create a Model in Web API Project

Inside the class, add the below code.

Model “Books.cs” (for C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace BooksApp.Models
{
    public class Books
    {
        public int BookId { get; set; }
        public string BookName { get; set; }
    }
}

The class has two public properties, in the form of BookId that holds an Integer value and next is the BookName that holds a string value. Both the properties will hold the data and will serve the data to its clients in the form of XML or JSON, upon request.

Model “Books.vb” (for Vb.Net)
Imports System.Web

Namespace BooksApp.Models
    Public Class Books
        Public Property BookId() As Integer
            Get
                Return m_BookId
            End Get
            Set(value As Integer)
                m_BookId = Value
            End Set
        End Property

        Private m_BookId As Integer

        Public Property BookName() As String
            Get
                Return m_BookName
            End Get
            Set(value As String)
                m_BookName = Value
            End Set
        End Property
        Private m_BookName As String
    End Class
End Namespace

Add a Controller to the Application

Now, here is an object, which will respond to the requests made by its clients. The Controller in this example, will return a list of values (in our case books), by accepting single of multiple values from the client. The client here is an Ajax call, which will request for list of data by sending a single input value (a single or multiple alphanumeric values).

Again, open the Solution Explorer and find the Controllers folder from the list. Right click the folder, choose Add and select the Controller… option.

Create a Controller in Web API Project

This will open the Add Controller window. Change the controller name with BooksController (you may add a name of your choice). Under the Scaffolding options, you will see a dropdown list with a list of “Templates”. Choose “Empty MVC controller” from dropdown list and click the “Add” button.

Empty MVC Controller in Web API

Add the below code inside the class.

Controller “BooksController.cs” (for C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.http;
using System.Web.http;

using BooksApp.Models;
using System.Data.SqlClient;

namespace BooksApp.Controllers
{
    public class BooksController : ApiController
    {
        // LIST OBJECT WILL HOLD AND RETURN A LIST OF BOOKS.
        List<Books> MyBooks = new List<Books>();

        // RETURN A LIST OF BOOKS MATCHING WITH THE REQUESTED ALPHANUMERIC VALUE(S).
        public IEnumerable<Books> Get(string sLookUpString)
        {
            GetTheBooks(sLookUpString);
            return MyBooks;
        }

        public void GetTheBooks(string sFind)
        {
            string sConnString = "Data Source=DNA;Persist Security Info=False;" +
                "Initial Catalog=DNA_Classified;User Id=sa;Password=;Connect Timeout=30;";

            SqlConnection myConn = new SqlConnection(sConnString);

            // SEARCH DATABASE TABLE MATCHING BOOKS WITH THE "LOOKUP" STRING.
            SqlCommand objComm = new SqlCommand("SELECT *FROM dbo.Books " +
                "WHERE BookName LIKE '%'+@LookUP+'%' ORDER BY BookName", myConn);
            myConn.Open();

            objComm.Parameters.AddWithValue("@LookUP", sFind);
            SqlDataReader reader = objComm.ExecuteReader();

            // ADD EACH BOOKNAME ALONG WITH ITS ID IN THE LIST.
            while (reader.Read())
            {
                MyBooks.Add(new Books { BookId = (int)reader["BookID"], 
                    BookName = reader["BookName"].ToString() });
            }
            myConn.Close();
        }
    }
}

Note: You must change the values inside the connection string, with values defining your database.

string sConnString = "Data Source=DNA;Persist Security Info=False;" +
     "Initial Catalog=DNA_Classified;User Id=sa;Password=;Connect Timeout=30;";

Along with default namespaces, I have defined two more namespaces.

1) Namespace “using BooksApp.Models”: It would inherit the properties declared in the Models class that we have named as “Books.cs” (Books.vb for Visual Basic).
2) Namespace “using “System.Data.SqlClient”: To connect with an SQL Server table.

Controller “BooksController.cs” (for Vb.Net)
Option Explicit On

Imports System.Net.http
Imports System.Web.http

Imports System.Data.SqlClient
Imports BooksApp.BooksApp.Models

Namespace BooksApp
    Public Class BooksController
        Inherits ApiController

        ' LIST OBJECT WILL HOLD AND RETURN A LIST OF BOOKS.
        Dim MyBooks As New List(Of Books)()

        ' RETURN A LIST OF BOOKS MATCHING WITH THE REQUESTED ALPHANUMERIC VALUE(S).
        Public Function [Get](sLookUpString As String) As IEnumerable(Of Books)
            GetTheBooks(sLookUpString)
            Return MyBooks
        End Function

        Public Sub GetTheBooks(sFind As String)

            Dim sConnString As String = "Data Source=DNA;Persist Security Info=False;" & _
                "Initial Catalog=DNA_Classified;User Id=sa;Password=;Connect Timeout=30;"

            Dim myConn As New SqlConnection(sConnString)

            ' SEARCH DATABASE TABLE MATCHING BOOKS WITH THE "LOOKUP" STRING.
            Dim objComm As New SqlCommand("SELECT *FROM dbo.Books " & _
                "WHERE BookName LIKE '%'+@LookUP+'%' ORDER BY BookName", myConn)

            myConn.Open()

            objComm.Parameters.AddWithValue("@LookUP", sFind)
            Dim reader As SqlDataReader = objComm.ExecuteReader()

            ' ADD EACH BOOKNAME ALONG WITH ITS ID IN THE LIST.
            While reader.Read()
                MyBooks.Add(New Books() With { _
                    .BookId = CInt(reader("BookID")), _
                    .BookName = reader("BookName").ToString() _
                 })
            End While

            myConn.Close()
        End Sub

    End Class
End Namespace

Make Changes to “WebApiConfig” File

Before proceeding further, we need to make slight changes in the WebApiConfig.cs file (WebApiConfig.vb for Visual Basic). In “Solution Explorer” window, find WebApiConfig.cs under App_Start folder and double click the file to open.

The WebApiConfig file defines a default routing table that the Web API will use to determine the “actions” it will take when it receives a request. The framework will route the requests through the “routing table” defined in this file. However, we have the liberty to alter and define our own routing tables as per our requirement.

The Default Route Table

config.Routes.MapHttpRoute(
    name: "DefaultApi",
    routeTemplate: "api/{controller}/{id}",
    defaults: new { id = RouteParameter.Optional }
);

Redefined Route Table

config.Routes.MapHttpRoute(
    name: "DefaultApi",
    routeTemplate: "api/{controller}/{sLookUpString}",
    defaults: new { sLookUpString = RouteParameter.Optional }
);

I have highlighted the changes that I have made in the Route table. I have altered the parameter “{id}” with “{sLookUpString}”. If you now open the “BooksController.cs” file, you will notice that I have defined the string parameter in IEnumerable<Books> type.

public IEnumerable<Books> Get(string sLookUpString)

Well, we now have a Web API ready to serve our clients. Let’s now create the client app.

AutoComplete Textbox using jQuery and Ajax

Add an HTML file in the project and name it default.htm. In the markup section, all we need to add is a TextBox (input box) control inside the <body> tag.

The Markup
<!DOCTYPE html>
<html>
<head>
    <title>AutoComplete Example using Asp.Net Web API and jQuery Ajax</title>

    <link href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script>
</head>
<body style="font-size:80%">
    <div>
        Type a letter:  <input type="text" value="" id="myBooks" />
    </div>
</body>
The Script
<script>
    $(document).ready(function () {
        BindControls();
    });

    function BindControls() {
        $("#myBooks").autocomplete({
            source: function (request, response) {

                var val = request.term;

                $.ajax({
                    url: "/api/books/" + val,
                    type: "GET",
                    success: function (data) {
                        response($.map(data, function (item) {
                            return { value: item.BookName }
                        }))
                    },
                    error: function (XMLHttpRequest, textStatus, errorThrown) {
                        alert(textStatus);
                    }
                });
            },
            minLength: 1   // MINIMUM 1 CHARACTER TO START WITH.
        });
    }
</script>

I have added a jQuery AutoComplete UI CDN in the <head> section. Later in the <script>, I have attached the input box with the autocomlete() method. Check the “uri” inside the “ajax()” method.

url: “/api/books/” + val

I am passing a single value as parameter (for sLookUpString) to the Web API, which then returns a list of books matching the letter. You may increase the number of values passed to the lookup string. For that, you will have to reset the value for the minLength property.

minLength: 2

🙂

Next →