This article demonstrates how to use multi-threading to return the name of two customers who have the closest birthdate. Also, it includes how to create API to add/retrieve customer details from SQL database using tasks and how to use MVC to call an API to add a new customer.
The proposed algorithm
- For each customer, find the difference between the selected customer and the rest of the customers.
- Do the same for all other customers. Note that you don’t have to look for the differences if it was already calculated. See the diagram below.
The complexity for calculating the diff between n customers will be equal to
n2-n-((n2-n)/2)
To find the closest birthday between 10 customers, you need to calculate 45 operations.
See below
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
0 | | 0,1 | 0,2 | 0,3 | 0,4 | 0,5 | 0,6 | 0,7 | 0,8 | 0,9 |
1 | | | 1,2 | 1,3 | 1,4 | 1,5 | 1,6 | 1,7 | 1,8 | 1,9 |
2 | | | | 2,3 | 2,4 | 2,5 | 2,6 | 2,7 | 2,8 | 2,9 |
3 | | | | | 3,4 | 3,5 | 3,6 | 3,7 | 3,8 | 3,9 |
4 | | | | | | 4,5 | 4,6 | 4,7 | 4,8 | 4,9 |
5 | | | | | | | 5,6 | 5,7 | 5,8 | 5,9 |
6 | | | | | | | | 6,7 | 6,8 | 6,9 |
7 | | | | | | | | | 7,8 | 7,9 |
8 | | | | | | | | | | 8,9 |
9 | | | | | | | | | | |
The main idea is to find the difference between n customers using multi-threading where each task will return the minimum difference between one customer and the rest of the customers. The tasks will be executed asynchronously on a separate processor. Each task will return an object that will include the customer index in the array, the index of the customer with minimum difference, and the difference value.
The last step is to find the objects that have the minimum difference from all the returned task objects. The resulted object will include the index of the first customer and the index of the second customer with closest birthdates.
The project is created using Visual Studio 2015 and MVC5
Create an SQL database with one table using the following script.
- CREATE DATABASE [CustomersDB]
- GO
-
- SET ANSI_NULLS ON
- GO
-
- SET QUOTED_IDENTIFIER ON
- GO
-
- CREATE TABLE [dbo].[PersonalInformation](
- [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PersonalInformation_ID]
- DEFAULT (newid()),
- [FirstName] [nvarchar](50) NULL,
- [LastName] [nvarchar](50) NULL,
- [BirthDate] [datetime] NULL
- ) ON [PRIMARY]
-
- GO
![C#]()
Create a new MVC5 project using Visual Studio 2015.
![C#]()
Under Controller folder, add a new API called CustomerApiController, allow the user to add a new customer to the database, retrieve all customer IDs, and retrieve each customer's details.
![C#]()
- The API controller code is as below.
- public class CustomerApiController : ApiController
- {
- [HttpPost]
- public IHttpActionResult AddClient(Customer customer)
- {
- Repository.AddCustomers(customer);
- return Ok(customer);
- }
-
-
- [HttpGet]
- [ActionName("GetCustomer")]
- [ResponseType(typeof(Customer))]
- public IHttpActionResult GetCustomer(Guid id)
- {
- return Ok(Repository.CustomerInfo(id));
- }
-
-
- [HttpGet]
- [ActionName("CustomersOId")]
- public IEnumerable<Guid> CustomersOId()
- {
- return Repository.CustomersOID();
- }
- }
2- Need to create the model part to enable connecting to the SQL. Under the Models folder, create the Repository.cs files.
![C#]()
... which include retrieving and adding data to the customer database tables.
Note
You can use any ORM Model but this is not part of this article.
- The Repository code is,
- public static class Repository
- {
-
-
-
-
-
- public static void AddCustomers(Customer customer)
- {
- string sql = "INSERT INTO PersonalInformation(FirstName,LastName,BirthDate)
- VALUES(@FirstName,@LastName,@BirthDate)";
- using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerConnection"].ToString()))
- {
- connection.Open();
- using (SqlTransaction transaction = connection.BeginTransaction())
- {
- using (SqlCommand command = new SqlCommand(sql, connection, transaction))
- {
- command.CommandType = CommandType.Text;
- command.Parameters.AddWithValue("@FirstName", customer.FirstName);
- command.Parameters.AddWithValue("@LastName", customer.LastName);
- command.Parameters.AddWithValue("@BirthDate", customer.BirthDate);
- command.ExecuteNonQuery();
- transaction.Commit();
- }
- }
- }
- }
-
-
-
- public static List<Guid> CustomersOID()
- {
- string sql = "SELECT ID from PersonalInformation";
-
- List<Guid> customerOIds = new List<Guid>();
- using (SqlConnection connection =
- new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerConnection"].ToString()))
- {
- connection.Open();
- using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
- {
- using (SqlDataReader reader = sqlCommand.ExecuteReader())
- {
- while (reader.Read())
- {
- Guid guid = reader.GetGuid(0);
- customerOIds.Add(guid);
- }
-
- }
- }
- }
- return customerOIds;
- }
-
-
-
- public static Customer CustomerInfo(Guid guid)
- {
- string sql = "SELECT FirstName,LastName,BirthDate from PersonalInformation where ID =@ID";
- Customer C1 = new Customer();
- using (SqlConnection connection =new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerConnection"].ToString()))
- {
- connection.Open();
- using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
- {
- sqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.UniqueIdentifier)).Value = guid;
- using (SqlDataReader reader = sqlCommand.ExecuteReader())
- {
-
- while (reader.Read())
- {
- C1.FirstName = reader.GetString(0);
- C1.LastName= reader.GetString(1);
- C1.BirthDate = reader.GetDateTime(2);
- }
-
- }
- }
- }
- return C1;
- }
- }
- Create the Customer.cs file that defines the customer object ( you can create the file under Model folder).
- public class Customer
- {
- public Guid ID { get; set; }
- public string FirstName { get; set; }
- public string LastName { get; set; }
- public DateTime BirthDate { get; set; }
- }
Build the algorithm of finding the closest birthdates using multi-threading. You can create a new folder to add the classes needed to find the closest birthdates or for simplicity, I have added the 2 required classes for the algorithm DiffClass and Resultstruct under Models folder.
![C#]()
- Create the cs. This is a static class used to define 2 static functions,
- The first function, FindMinDiff, will accept 2 parameters - customer and index (n). You need to find the diff with the other customers in a list of all retrieved customers (CustomerDate). It will return ResultStruct object which includes the customer number, the index of the nearest customer birthday and the difference between the 2 birthdays.
- The second function, ClosestBirthDate, will create a list of tasks and assign each customer to a separate task and wait for all the tasks to complete. Then, find the minimum difference between all the returned results and return the nearest birthdays of the 2 customers.
- See the code below.
- public static class DiffClass
- {
-
-
-
-
-
-
-
- public static ResultStruct FindMinDiff(int no, List<Customer> customersDate,int arraysize)
- {
- double[] diffs = new double[arraysize];
- int index = -1;
- DateTime pivot = customersDate[no].BirthDate;
-
- for (int i = 0; i < customersDate.Count; i++)
- if (no < i) diffs[i] = Math.Abs((pivot - customersDate[i].BirthDate).TotalDays);
- else diffs[i] = -1;
-
- index = Enumerable.Range(0, diffs.Length)
- .Aggregate((a, b) => (diffs[a] < diffs[b] && diffs[a] != -1 && diffs[b] != -1) ? a : b);
- return new ResultStruct() {PivotNumber = no, IndexResult = index, DiffValue = diffs[index]};
- }
-
-
-
-
-
-
-
-
-
-
-
- public static List<Customer> ClosestBirthDate(List<Customer> Customers)
- {
- Task<ResultStruct>[] tasks = new Task<ResultStruct>[Customers.Count-1];
-
- for (int ctr = 0; ctr < Customers.Count-1; ctr++)
- {
- int factor = ctr;
- tasks[ctr] = Task.Run(() => FindMinDiff(factor, Customers, Customers.Count));
- }
- Task.WaitAll(tasks);
- var min = tasks.Min(t => t.Result.DiffValue);
- var item = tasks.First(t => t.Result.DiffValue == min);
- List<Customer> rsesutedCustomers = new List<Customer>();
- rsesutedCustomers.Add(Customers[item.Result.PivotNumber]);
- rsesutedCustomers.Add(Customers[item.Result.IndexResult]); ;
-
- return rsesutedCustomers;
- }
- }
- cs is the class definition where the result is returned from FindMinDiff function
-
- public class ResultStruct
- {
- public int PivotNumber;
- public int IndexResult;
- public double DiffValue;
- }
Create the CustomerControllers.cs under Controllers folder.
![C#]()
This control has 3 main Controllers - Index, Create, and GetClosestBirthday; where the Index and Create controllers are used to help the user add new customer to the DB using the Index.cshtml View.
The GetClosestBirthday() controller is used to call the 2 customer APIs through 2 functions, GetCustomerIDs() and CallCustomer(Guid oid) where the first asyn task function is used to get all the id of the customers and the second async function is used to get all the customer details and finally call the DiffClass.ClosestBirthDate() static function to get the closet birthdates and display the result in a View.
The following is the customerController code.
Finally, you also need to create 2 views under the Views folder. Create a folder “Customers” and add 2 Views, index.cshtml and GetClosestBirthday. The first one adds a new customer information to the DB while the second one displays the closest birthdates of two customers.
![C#]()
Following is the code of the 2 Views.
Index.cshtml
- @model CustomersApi.Models.Customer
-
- @{
- ViewBag.Title = "Index";
- }
-
- <h2>Index</h2>
-
- @using (Html.BeginForm("Create","customer"))
- {
- @Html.AntiForgeryToken()
-
- <div class="form-horizontal">
- <h4>Customer</h4>
- <hr />
- @Html.ValidationSummary(true, "", new { @class = "text-danger" })
- <div class="form-group">
- @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
- <div class="col-md-10">
- @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new {@class = "form-control",autofocus = "" } })
- @Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
- </div>
- </div>
-
- <div class="form-group">
- @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
- <div class="col-md-10">
- @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
- @Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
- </div>
- </div>
-
- <div class="form-group">
- @Html.LabelFor(model => model.BirthDate, htmlAttributes: new { @class = "control-label col-md-2" })
- <div class="col-md-10">
- @Html.EditorFor(model => model.BirthDate, new { htmlAttributes = new { @class = "form-control" } })
- @Html.ValidationMessageFor(model => model.BirthDate, "", new { @class = "text-danger" })
- </div>
- </div>
-
- <div class="form-group">
- <div class="col-md-offset-2 col-md-10">
- <input type="submit" value="Create" class="btn btn-default" />
- </div>
- </div>
- </div>
- }
-
- <div>
- @Html.ActionLink("Back to List", "Index")
- </div>
GetClosestBirthday.cshtml
- @model IEnumerable<CustomersApi.Models.Customer>
-
- @{
- ViewBag.Title = "GetSmallestBirthday";
- }
-
- <h1> Customers with the nearest Birthday</h1>
-
- @using (Html.BeginForm("GetSmallestBirthday", "customer"))
- {<table class="table table-striped ">
- <thead>
- <tr>
- <th >
- @Html.DisplayNameFor(model => model.FirstName)
- </th>
- <th >
- @Html.DisplayNameFor(model => model.LastName)
- </th>
- <th >
- @Html.DisplayNameFor(model => model.BirthDate)
- </th>
- <th >
-
- </th>
-
- </tr>
- </thead>
- <tbody>
- @if (Model != null)
- {
- foreach (var item in Model)
- {
- <tr style="vertical-align: middle">
- <td >
- @Html.DisplayFor(modelItem => item.FirstName)
- </td>
- <td >
- @Html.DisplayFor(modelItem => item.LastName)
- </td>
- <td >
- @Html.DisplayFor(modelItem => item.BirthDate)
- </td>
-
- </tr>
- }
- }
- </tbody>
- </table>
- }
Run the application.
To add a new customer, you should use the following URL http://localhost:56237/Customer.
To get the closest birthdates, use the following URL http://localhost:56237/Customer/GetClosestBirthday
The result is given below.
![C#]()
![C#]()
The application is built using Visual Studio 2015. Download the application, compile, and run it, and make sure to change the CustomerConnection to point to your SQL server.