AutoComplete feature with ASP.NET and SQL Server

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT
(NIV) Hebrews 10:35 So do not throw away your confidence; it will be richly rewarded.
Published:
Updated:
Edited by: Andrew Leniart
One of my friends recently asked me how to do an AutoComplete feature. I thought it was easy and there are already a lot of examples online. But I wish to provide him with a working solution instead of providing links. And in this article, I will explain the steps that I used to build that solution.
First of all, I know the AutoComplete feature should be done via an AJAX call to some web service/ web API, and then get and populate the response to the correct places or fields we wanted.

Things to consider:

1) What technologies to be used
2) How to start
3) Where to get the data
4) How to populate the data
5) Any other concerns



1) What technologies to be used

My friend didn't explain much on what program he is working on, but I do know he is using ASP.NET and SQL Server to construct his solution.

Since I need to use AJAX in order to pull the data, so I have done a search on Google and it returned with some examples and links. Eventually, I decided to use jqueryui's Autocomplete feature to build the solution for my friend.

2) How to start

First of all, we need to create a .NET project, with C# as the preferred programming language.

Generally, you could create a ASP.NET Core or Web Application with .NET Framework project, and you could choose between a MVC or WebForm model as well.

3) Where to get the data

In a common use case, we will probably grab the data from a Web service or Web API, which linked to a backend database, and the data returned will probably be in JSON or XML format.

For my friend's case, he doesn't have a ready-to-call interface available. Having said that, he has the full control over this web interface, database and able to decide what data format to be returned.

So, he needs to build the web service/ web API and develop some backend database scripting.

For database end, we need to know what table we are referring to, and to make the data retrieval easier, we probably can create a stored procedure, so at later stage, we can call it from the C# codes.

For demo purposes, we can create the following table and stored procedure:

CREATE TABLE [dbo].[tblBorrowers](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](80) NULL,
    [address] [nvarchar](80) NULL,
    [phone] [nvarchar](30) NULL,
    [contact] [nvarchar](30) NULL,
    [email] [nvarchar](40) NULL,
    [website] [nvarchar](255) NULL,
    [tic_borrower] [nvarchar](20) NULL,
 CONSTRAINT [PK_tblBorrowers] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE PROCEDURE [dbo].[Sp_GetBorrowsers]
(
    @term NVARCHAR(30)
)
AS
BEGIN
    SET NOCOUNT ON;
   
    SELECT name label, name value, *
    FROM tblBorrowers
    WHERE [name] LIKE '%' + @term + '%'
    ORDER BY [name];
END
GO

We could insert some dummy data into the table:

SET IDENTITY_INSERT [dbo].[tblBorrowers] ON
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (1, N'Name 1', N'Address 1', N'Phone 1', N'Contact 1', N'Email 1', N'Website 1', N'A')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (2, N'Name 2', N'Address 2', N'Phone 2', N'Contact 2', N'Email 2', N'Website 2', N'B')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (3, N'Name 3', N'Address 3', N'Phone 3', N'Contact 3', N'Email 3', N'Website 3', N'C')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (4, N'Tan', N'dummy'' addr'' xx', N'phone 4', N'91234567', N'test@here.com', N'web 4', N'D')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (5, N'Andy', N'du<b>mmy " \ > addr DGs dd', N'phone 5', N'61234567', N'gtst@h.com', N'web 5', N'A')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (6, N'David', N'dummy addr dgo', N'phone 6', N'11111111', N'em@here.com', N'web 6', N'C')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (7, N'Dan', N'dummy addr Hds', N'Phone 7', N'22222222', N'sgd@h.com', N'web 7', N'B')
GO
INSERT [dbo].[tblBorrowers] ([id], [name], [address], [phone], [contact], [email], [website], [tic_borrower]) VALUES (8, N'Ant', N'dummy addr sdds', N'Phone 8', N'33333333', N'dgd@GS', N'web 8', N'C')
GO
SET IDENTITY_INSERT [dbo].[tblBorrowers] OFF
GO

For the web service/ web API, we could create an ASP.NET Core Web API or WCF project for this purpose, but due to the fact that I need to show this sample project to my friend pretty quickly, so I have created a simple Generic Handler for this purpose.

We name the file as posting.ashx

public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/json";


            string term = context.Request.Form["term"];
            try
            {
                if (term == null)
                {
                    context.Response.Write("Invalid Parameter(s)");
                }
                else
                {
                    //Trim what to search
                    term = term.Trim();


                    //Read connection string
                    String connstring = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;


                    //Establish connection and grab data
                    using (SqlConnection conn = new SqlConnection(connstring))
                    {
                        conn.Open();
                        using (SqlCommand cmd = new SqlCommand("Sp_GetBorrowsers", conn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandTimeout = 30;


                            cmd.Parameters.Add("@term", System.Data.SqlDbType.NVarChar).Value = term;
                           
                            using (SqlDataReader r = cmd.ExecuteReader())
                            {
                                //Convert data reader to json
                                var obj = Serialize(r);
                                string json = JsonConvert.SerializeObject(obj, Formatting.Indented);
                                context.Response.Write(json);
                            }
                        }


                    }
                }
            }
            catch (Exception)
            {
                context.Response.Write("Error Processing Request");
            }
        }


public IEnumerable<Dictionary<string, object>> Serialize(SqlDataReader reader)
        {
            var results = new List<Dictionary<string, object>>();
            var cols = new List<string>();
            for (var i = 0; i < reader.FieldCount; i++)
                cols.Add(reader.GetName(i));


            while (reader.Read())
                results.Add(SerializeRow(cols, reader));


            return results;
        }
        private Dictionary<string, object> SerializeRow(IEnumerable<string> cols,
                                                        SqlDataReader reader)
        {
            var result = new Dictionary<string, object>();
            foreach (var col in cols)
                result.Add(col, reader[col]);
            return result;
        }

What interest thing part from C# codes above is to serialize SqlDataReader object to JSON.

4) How to populate the data

Now, let's try construct a simple WebForm.

What important for us is to create the form elements, such as:

<table>
                <tr>
                    <th colspan="3" class="auto-style4">Borrower Detail</th>
                </tr>
                <tr>
                    <td class="auto-style3">
                        <label for="name">Name</label></td>
                    <td class="auto-style2">:</td>
                    <td class="auto-style1">
                        <input id="name"></td>
                </tr>
                <tr>
                    <td class="auto-style3">
                        <label for="address">Address</label></td>
                    <td class="auto-style2">:</td>
                    <td class="auto-style1">
                        <input id="address"></td>
                </tr>
                <tr>
                    <td class="auto-style3">
                        <label for="phone">Phone</label></td>
                    <td class="auto-style2">:</td>
                    <td class="auto-style1">
                        <input id="phone"></td>
                </tr>
                <tr>
                    <td class="auto-style3">
                        <label for="contact">Contact</label></td>
                    <td class="auto-style2">:</td>
                    <td class="auto-style1">
                        <input id="contact"></td>
                </tr>
                <tr>
                    <td class="auto-style3">
                        <label for="email">Email</label></td>
                    <td class="auto-style2">:</td>
                    <td class="auto-style1">
                        <input id="email"></td>
                </tr>
                <tr>
                    <td class="auto-style3">
                        <label for="website">Website</label></td>
                    <td class="auto-style2">:</td>
                    <td class="auto-style1">
                        <input id="website"></td>
                </tr>
                <tr>
                    <td class="auto-style3">
                        <label for="tic_borrower">TIC Borrower</label></td>
                    <td class="auto-style2">:</td>
                    <td class="auto-style1">
                        <select id="tic_borrower">
                            <option value=""></option>
                            <option value="A">A</option>
                            <option value="B">B</option>
                            <option value="C">C</option>
                            <option value="D">D</option>
                        </select></td>
                </tr>
                <tr>
                    <td class="auto-style3">&nbsp;</td>
                    <td class="auto-style2">&nbsp;</td>
                    <td class="auto-style1">
                        <input id="reset" type="reset" value="reset" /></td>
                </tr>
            </table>
and then we need to include some style sheets and JavaScript links in the head section:

<head runat="server">
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Test</title>
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">


    <style>
        .ui-autocomplete-loading {
            background: url(http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.2/themes/smoothness/images/ui-anim_basic_16x16.gif) no-repeat right center
        }


        table, th, td {
            border: 1px solid black;
            width: 500px;
            height: 50px;
        }


        .auto-style1 {
            width: 100px;
        }


        .auto-style2 {
            width: 16px;
        }


        .auto-style3 {
            width: 136px;
        }


        .auto-style4 {
            text-align: left;
        }
    </style>
    <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <script>
        $(function () {
            function populate(item) {
                $('#address').val(item.address);
                $('#phone').val(item.phone);
                $('#contact').val(item.contact);
                $('#email').val(item.email);
                $('#website').val(item.website);
                $('#tic_borrower').val(item.tic_borrower);
            }


            $("#name").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: "posting.ashx",
                        method: "POST",
                        dataType: "json",
                        data: {
                            term: request.term
                        },
                        success: function (data) {
                            response(data);
                        }
                    });
                },
                minLength: 2,
                select: function (event, ui) {
                    populate(ui.item);
                }
            });
        });


        $(document).ready(function () {
            $("#name").focus();


            $("#reset").click(function () {
                $("#name").focus();
            });
        });
    </script>
</head>

Let me try to explain some of the important part from codes above.

a) style .ui-autocomplete-loading

.ui-autocomplete-loading {
            background: url(http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.2/themes/smoothness/images/ui-anim_basic_16x16.gif) no-repeat right center
        }
We use it so that it will generate a small loading icon while the data is loading.

b) Call the web service/ web API

$("#name").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: "posting.ashx",
                        method: "POST",
                        dataType: "json",
                        data: {
                            term: request.term
                        },
                        success: function (data) {
                            response(data);
                        }
                    });
                },
                minLength: 2,
                select: function (event, ui) {
                    populate(ui.item);
                }
            });

The codes above will tell us:
  • What URL to post the request
  • What method to be used (POST, GET, etc)
  • What data type to be returned (JSON, XML, etc)
  • What action to be done once we got a success response (in this case, we are calling the function: "populate" to generate the output)
  • What is the minimum length of search characters (in this case, at least 2 chars need to be entered to trigger the autocomplete posting)

5) Any other concerns

It is always good and maintain a best practice to "ask questions", and not to simply copy and paste some solutions from elsewhere and apply it directly into our own project without thinking the risk and impact of it.

a) Data encoding?

Just want to make sure the data populating is in good form and didn't break up the form.

We can test this by doing a search for "Tan" and "Andy".



As a result, it seems that the code is handling the encoding well.

Hence, it also mean a valid JSON should be returned from the respective web service/ web API.

b) The necessity of Multiple Posting?

Someone may ask, do we need to have the separate posting since the first posting is grabbing all matched data back from the response?

For example, if we do a search on "na", the following result will be returned:

[
  {
    "label": "Name 1",
    "value": "Name 1",
    "id": 1,
    "name": "Name 1",
    "address": "Address 1",
    "phone": "Phone 1",
    "contact": "Contact 1",
    "email": "Email 1",
    "website": "Website 1",
    "tic_borrower": "A"
  },
  {
    "label": "Name 2",
    "value": "Name 2",
    "id": 2,
    "name": "Name 2",
    "address": "Address 2",
    "phone": "Phone 2",
    "contact": "Contact 2",
    "email": "Email 2",
    "website": "Website 2",
    "tic_borrower": "B"
  },
  {
    "label": "Name 3",
    "value": "Name 3",
    "id": 3,
    "name": "Name 3",
    "address": "Address 3",
    "phone": "Phone 3",
    "contact": "Contact 3",
    "email": "Email 3",
    "website": "Website 3",
    "tic_borrower": "C"
  }
]

Is there a scenario that we wish to "protect" the data above from being exposed to end users? and only provide the necessary details, such as "Label", "Value" and "Id" to be shown on the result drop down list?

Since now we only sent one request to the web service/ web API, other details such as "Name", "Address", "Phone", etc are being returned as well. Shall we create the second request call based on "Id" and grab that details so that such data are not being exposed at the first place?

As far as what I consider, I would think a multiple posting architecture is not necessary since we are doing a wildcard searching. Those data should be classified as "not sensitive" despite the fact it may contain Personally identifiable information (PII).

If there is some data which is classified to be (highly) "sensitive", we should discourage it and avoid the implementation with autocomplete feature.

We could refer to Open Web Application Security Project (OWASP) for such considerations.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT
(NIV) Hebrews 10:35 So do not throw away your confidence; it will be richly rewarded.
1
246 Views

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
Continue Growing Your Skills and Your Career
  • Interact with leading experts on your specific technology problems.
  • Receive the guidance of experienced professionals.
  • Learn from troubleshooting others have experienced.
  • Gain knowledge from a library of courses, all included.