In this post, I'll show you how to use an ASP.NET MVC application to establish a WEBAPI POST function to extract data from a SQL database.

Open SQL Management Studio, then create a new table and write the table's stored procedure.
Table column names and datatypes are determined by your needs.

Create proc [dbo].[UserInfo]
(
@UserName nvarchar(100)
)
as
(
select UserId,UserName,Address,IsActive,UserTypeId from WebAPI
where
UserName=@UserName
)

Once you have successfully created the SQL store procedure then Create a new MVC WebAPI Application using visual studio.
provide the project name and project location.

Choose Empty project, then select the MVC and WEB API options from the essential references column on the right.

Now the project has been created successfully.

After you've created the project, right-click on the Models folder, select Add, and then New item. In the Models Folder, place the class file.

From the C# node then choose the class definition and provide the class file name UserEntity.cs. Once we added the class file to our project solution.

Use the below code in UsersEntity.cs file.
using Microsoft.SqlServer.Server;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http.Headers;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web;
using System.Web.UI.WebControls;
using Microsoft.Office.SharePoint.Tools;
using static System.Net.Mime.MediaTypeNames;
using System.Data;

namespace SampleWebAPI.Models
{
    public class UsersEntity
    {

        public string UserId { get; set; }

        public string UserName { get; set; }

        public string Address { get; set; }

        public string UserTypeId { get; set; }

        public string IsActive { get; set; }

        public UsersEntity()
        {

        }
     }

}

Likewise create a new logger.cs and ErrorDetails.cs class file to capture the logs and error details in the Models folder.

Use the below code in Logger.cs file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace SampleWebAPI.Models
{
    public class Logger
    {
         // To capture the log details
        #region "-- Class Level Variable / Object Declaration --"
        private static readonly log4net.ILog logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        #endregion
        #region "-- Public Functions --"
        public static void LogError(string msg, Exception ex)
        {
            logger.Error(msg, ex); //This for error msg
        }
        public static void LogDebug(string msg, Exception ex)
        {
            logger.Debug(msg, ex);
        }
        public static void LogFatal(string msg, Exception ex)
        {
            logger.Fatal(msg, ex);
        }
        public static void LogInfo(string msg)
        {
            logger.Info(msg);
        }
        public static void LogWarn(string msg, Exception ex)
        {
            logger.Warn(msg, ex);
        }
        public static void LogInfo(string msg, Exception ex)
        {
            logger.Info(msg, ex);
        }
    }
    #endregion
}

Use the below code in ErrorDetails.cs file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace SampleWebAPI.Models
{
//This Class used for error msg
    public class ErrorDetails
    {
        public List<string> schemas { get; set; }
        public string details { get; set; }
        public int status { get; set; }
    }
}

Then add the controller to the Controller folder.
Right Click on the Controller folder, choose to add, and click Controller.
Click the MVC empty read and write controller file.

Then give the controller name based on your requirement.

Once the Controller has been created successfully, we can write the Controller methods in the controller.
Use the below code in the controller.
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using SampleWebAPI.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Http;
using System.Web.Mvc;
using System.Web.UI.WebControls;
using Microsoft.Office.SharePoint.Tools;
using static System.Net.Mime.MediaTypeNames;

namespace SampleWebAPI.Controllers
{
    public class UserController : ApiController // To Initialize the Web API
    {
        [System.Web.Http.AcceptVerbs("GET", "POST")] // To check the result in browser
        [System.Web.Http.HttpPost] //To check the Post method
        [System.Web.Http.Route("users/info")] //This the route url
        public JObject info(string UserName)
        {
            ErrorDetails objErrorDetail = new ErrorDetails();
            string jsonResponse = string.Empty;
            JObject jsonObject = null;


            try
            {
                Logger.LogInfo("info: starting to get user list info");
                DataSet ds = new DataSet();
                UsersEntity objUserDetail = new UsersEntity();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString ="Data Source=Test;" +"Initial Catalog=Test;" +"User id=Test;" + "Password=Test;";

                {
                  //Sql connection from the database
                    using (SqlCommand cmd = new SqlCommand("UserInfo", conn))
                    {
                        conn.Open();
                        Logger.LogInfo("info: Sql Connection established.");
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@UserName",UserName);
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(ds);
                        conn.Close();
                        Logger.LogInfo("info: Sql Connection closed.");

                    }
                }

                if (ds.Tables[0].Rows.Count >= 1)
                {
                    Logger.LogInfo("info: starting to format the json.");
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        // Display the column in the output page using object dataset.
                        List<string> lst = new List<string>();
                        objUserDetail.UserName = dr["UserName"].ToString();
                        objUserDetail.UserId = dr["UserId"].ToString();
                        objUserDetail.Address = dr["Address"].ToString();
                        objUserDetail.UserTypeId = dr["UserTypeId"].ToString();
                        objUserDetail.IsActive = dr["IsActive"].ToString();
                        jsonResponse = JsonConvert.SerializeObject(objUserDetail);
                        // Deserialize the JSON API response into a JObject
                        jsonObject = JsonConvert.DeserializeObject<JObject>(jsonResponse);
                        // Serialize the updated object back to JSON
                        string updatedJsonResponse = jsonObject.ToString();

                    }
                    Logger.LogInfo("info: data formatted in json successfuly.");
                    return jsonObject;
                }
                else
                {
                    Logger.LogInfo("info: User not found returned null data.");
                    List<string> lst = new List<string>();
                    lst.Add("urn:ietf:params:scim:api:messages:2.0:Error");
                    objErrorDetail.schemas = lst;
                    objErrorDetail.status = (int)HttpStatusCode.NotFound;
                    objErrorDetail.details = "User Not Found";
                    return JsonConvert.DeserializeObject<JObject>(JsonConvert.SerializeObject(objErrorDetail));
                }
            }
            catch (Exception ex)
            {
                Logger.LogInfo("info: Error occured.");
                Logger.LogError("info: Error - " + ex.Message, ex);
                jsonObject = null;
                List<string> lst = new List<string>();
                lst.Add("urn:ietf:params:scim:api:messages:2.0:Error");
                objErrorDetail.schemas = lst;
                objErrorDetail.status = (int)HttpStatusCode.InternalServerError;
                objErrorDetail.details = ex.Message.ToString();
                return JsonConvert.DeserializeObject<JObject>(JsonConvert.SerializeObject(objErrorDetail));
            }

        }

    }
}

Introduction: In this article, we will explore how to implement dynamic dropdown lists in an ASP.NET MVC application. We'll use jQuery AJAX to fetch data from the server and populate dependent dropdown lists based on user selections. The provided code includes examples of how to create cascading dropdowns for countries, states, and cities, allowing users to select specific locations for student data.

Prerequisites: To follow this tutorial, you should have a basic understanding of ASP.NET MVC, C#, jQuery, and Entity Framework.

Building the Dropdown Form
Building the Dropdown Form: In the "Index.chtml" file, we have designed a form to collect student data, including cascading dropdowns for countries, states, and cities.
<!-- Index.chtml -->
<!-- ... (existing code) ... -->
<div class="text-light text-center my-3 d-flex justify-content-center gap-3">
    <div>
        @if (ViewBag.Country != null)
        {
            @Html.DropDownListFor(x => x.Country, ViewBag.Country as SelectList, "Select Country", new { @class = "form-control" })
        }
    </div>
    <div class="stateSection">
        <span id="states-loading-progress" style="display: none;">Please wait for State..</span>
        @Html.DropDownListFor(x => x.State, ViewBag.State as SelectList, "Select State", new { @class = "form-control" })
        @Html.ValidationMessageFor(x => x.State)
    </div>
    <div class="citySection">
        <span id="states-loading-progress" style="display: none;">Please wait for City..</span>
        @Html.DropDownListFor(x => x.City, ViewBag.City as SelectList, "Select City", new { @class = "form-control" })
        @Html.ValidationMessageFor(x => x.City)
    </div>
</div>
<!-- ... (existing code) ... -->

Implementing AJAX for Dropdowns
Implementing AJAX for Dropdowns: In the JavaScript section, we've added AJAX functions to handle the country and state dropdowns dynamically.
/*------ Country Function -------*/
$("#Country").change(function () {
    var stateID = $(this).val();
    $('#State').empty();
    $('#City').addClass('d-none');

    console.log(stateID);

    var statesProgress = $("#states-loading-progress");
    statesProgress.show();

    if (stateID > 0) {
        $(".stateSection").addClass('active');

        $.ajax({
            type: "GET",
            url: "/Home/GetStateById/" + stateID,
            success: function (data) {
                $("#State").html('');

                var defaultOption = "<option value>Select State</option>";
                $("#State").append(defaultOption);

                $.each(data, function (id, option) {
                    var optionHtml = "<option value=" + option.StateID + ">" + option.StateName + "</option>";
                    $("#State").append(optionHtml);
                });

                statesProgress.hide();
            },
            error: function (xhr, ajaxOptions, thrownError) {
                alert('State function failed');
                statesProgress.hide();
            }
        });
    } else {
        $(".stateSection").removeClass('active');
        $(".citySection").removeClass('active');
        $.notify("Please select a Country", "error");
    }
});


/*------ State Function -------*/
$("#State").change(function () {
    $('#City').removeClass('d-none');
    var cityID = $(this).val();
    var statesProgress = $("#states-loading-progress");
    statesProgress.show();

    if (cityID > 0) {
        $(".citySection").addClass('active');

        $.ajax({
            type: "GET",
            url: "/Home/GetCityById/" + cityID,
            success: function (data) {
                $("#City").html('');

                var defaultOption = "<option value>Select City</option>";
                $("#City").append(defaultOption);

                $.each(data, function (id, option) {
                    var optionHtml = "<option value=" + option.Id + ">" + option.CItyName + "</option>";
                    $("#City").append(optionHtml);
                });

                statesProgress.hide();
            },
            error: function (xhr, ajaxOptions, thrownError) {
                alert('City function failed');
                statesProgress.hide();
            }
        });
    } else {
        $(".citySection").removeClass('active');
        $.notify("Please select a State", "error");
    }
});

Handling Dropdown Requests in the Controller
Handling Dropdown Requests in the Controller: In the "HomeController," we've added action methods to retrieve country, state, and city data from the database and return them as JSON results for the AJAX requests.
// Home Controller Method
public class HomeController : Controller
{
    public ActionResult Index()
        {
            List<CountryTable> CountryList = db.CountryTable.ToList();
            ViewBag.Country = new SelectList(CountryList, "Id", "countryName");

            List<StateTable> StateList = db.StateTable.ToList();
            ViewBag.State = new SelectList(StateList, "StateID", "StateName");

            List<CityTable> CityList = db.CityTable.ToList();
            ViewBag.City = new SelectList(CityList, "Id", "CItyName");

            return View();
        }

        public JsonResult GetStateById(int ID)
        {
            db.Configuration.ProxyCreationEnabled = false;
            var stateList = db.StateTable.Where(x => x.CountryID == ID).ToList();
            return Json(stateList, JsonRequestBehavior.AllowGet);
        }

        public JsonResult GetCityById(int ID)
        {
            db.Configuration.ProxyCreationEnabled = false;
            var CityList = db.CityTable.Where(x => x.StateID == ID).ToList();
            return Json(CityList, JsonRequestBehavior.AllowGet);
        }

        public ActionResult EditStudent(int ID)
        {
            var result = repo.EditFunction(ID);
            // Set selected values for the dropdown lists
            List <CountryTable> CountryList = db.CountryTable.ToList();
            ViewBag.CountryList = new SelectList(CountryList, "Id", "countryName", result.Country);

            List<StateTable> StateList = db.StateTable.ToList();
            ViewBag.StateList = new SelectList(StateList, "StateID", "StateName", result.State);

            List<CityTable> CityList = db.CityTable.ToList();
            ViewBag.CityList = new SelectList(CityList, "Id", "CItyName", result.City);

            return View(result);
        }
    }
}

Conclusion: In this article, we have learned how to create dynamic dropdown lists with cascading behavior in an ASP.NET MVC application using AJAX. The JavaScript AJAX functions communicate with the server to fetch data based on user selections, and the ASP.NET MVC controller methods handle the requests and return JSON results. By following this guide, you can enhance your web application with more interactive features and improve the user experience with dynamic dropdowns. Happy coding!

Routing can be a very tricky issue within ASP.NET MVC and Web API applications. This can be especially true if you have a variety of different routes with varying parameters, defined in such a way that a single request could satisfy multiple routes.

This blog post will cover some of the precedence rules that are applied to routing in ASP.NET MVC and Web API and how to leverage these to ensure that the route you want to use gets used.

What are Routes? How do they work?
A Route in ASP.NET simply consists of a pattern that is going to be mapped to a handler. The handlers themselves can be a variety of different mechanisms (e.g. a physical file, a Web Forms page, or an MVC Controller class). In simplest terms, Routes define how requests are handled within your application.

Routes consist of the following three components,

    A name to identify the Route itself.
    A pattern to match URLs to match a request with its appropriate handler.
    A handler to tell requests that match the pattern where to go.

You can see an example of a route declaration in ASP.NET MVC below, which contains all three of these criteria.
    routes.MapRoute(   
    "Default", // Name  
    "{controller}/{action}/{id}", // Pattern  
    new { controller = "Home", action = "Index", id = "" } // Handler  
    );  

Routes can also be defined by using the [Route] attribute and decorating a particular Controller Action with it.
    [Route("widgets/{brand}", Order = 1)]  
    public IEnumerable<Widget> GetWidgetsByBrand(string brand) { ... }   

Since the focus of this post isn't really about routing itself and more of routing precedence, I won't go into too many more details about declaring them.

ASP.NET will store all of the defined Routes within a Routes Table and when a request comes to it, it will look through these Routes to determine the one best suited in order to serve the request. To know how this works, we need to know how Routes are prioritized and that's why this blog post exists at all.

Routes match requests using a pattern and tell them where to go.

Routing Precedence
Routing can be a blessing and a curse within an application. It can provide you with the freedom to define all sorts of very creative and easily understandable approaches to accessing the data, but when overused, things can get hairy. The primary reason that routing can make you want to pull your hair out is that a single request can match multiple routes.

The routing order can be broken down into the following steps.
    Check the Order property (if available).
    Order all Routes without an explicit Order attribute, as follows.

        Literal segments.
        Route parameters with constraints.
        Route parameters without constraints.
        Wildcard parameter segments with constraints.
        Wildcard parameter segments without constraints.

    As a tie-breaker, order the Routes via a case-insensitive string comparison.

Confused yet? That's okay
Let's talk about defining Route Order and the different types of routes for a moment, to clear things up.

Route Order

You can use the [Route] attribute to explicitly set when a particular route is going to be checked against the order property. By default, all defined routes have an order value of 0 and routes are processed from lowest to highest. This is the primary reason that it is so important for establishing the route precedence, as it's a single attribute that will govern the order in which routes are processed.

Let's look at the following three routes.
    [Route("Alpha", Order = 1)]  
    public void Fizz() { ... }   
    [Route("Beta")]  
    public void Buzz() { ... }   
    [Route("Gamma")]  
    public void FizzBuzz() { ... }   

Since the Alpha Route has an order property of 1, it will always be evaluated last. The only scenario where that would not be true would be if another route had a higher order value or an equal one (and it received priority via the other criteria).

Literal Segments
A Literal Segment Route can be thought of as a "hard-coded" route. It contains no types of parameters and no constraints. A few examples of these types of routes might look like.
    /widgets/broken  
    /employees  

Route Parameters
Route Parameters are going to have a bit more information that literals, but not by much. The only major difference is that they will have a "placeholder" that can be used to define parameters, similar to the String.Format() method.
    /widgets/{widgetId}  
    /reports/{year}/{month}  

Route Parameters with Constraints
Route Parameters can also be constrained to a specific type. This is important as they will be evaluated prior to unconstrained ones.
    /widgets/{widgetId:int}  
    /reports/{year:int}/{month:int}  

Wildcard Parameters
Wildcard Parameters are the last type of parameters to review, and you generally won't see these as much as the aforementioned types. These function as "catch-all" parameters and may contain more than a single segment.

Consider the following route,
    /query/widgets/{*queryvalues}  

Notice the {*queryvalues} segment. The leading asterisk indicates that this is a wildcard parameter and thus, it can accept all sorts of additional segments in it.
    /query/widgets/broken  
    /query/widgets/byyear/2015  

Wildcard Parameters with Constraints
Wildcard parameters can also feature type constraints just like normal route parameters as well if you are expecting a certain type,
    /query/widgets/{*byyear:int}  

Applying Precedence in Action
Now, to break this down, let's define several actions that meet the criteria of at least one of each of these routes. We will look at each phase as we order the routes.
    [RoutePrefix("widgets")]  
    public class WidgetsController: ApiController {  
            [Route("{widgetId:int}")] // Constrained Route Parameter  
            public HttpResponseMessage Get(int widgetId) {...  
                }  
                [Route("new")] // Literal Segment  
            public HttpResponseMessage GetNew() {...  
                }  
                [Route("{*features})] // Unconstrained Wildcard Parameter  
                        public HttpResponseMessage GetByFeatures(string features) {...  
                        }  
                        [Route("broken", RouteOrder = 1)] // Literal Segment (with Route Order)  
                        public HttpResponseMessage GetBroken() {...  
                        }  
                        [Route("{brand}")] // Unconstrained Route Parameter  
                        public HttpResponseMessage GetByBrand(string brand) {...  
                        }  
                        [Route("{*date:datetime}")] // Constrained Wildcard Parameter  
                        public HttpResponseMessage GetByManufacturedDate(DateTime date) {...  
                        }  
                    }  

So, with all of these routes defined, let's output all of them as they appear in the Controller and update them as we apply each rule.
    Get(int widgetId) { ... } // widgets/{widgetId:int}   
    GetNew() // widgets/new   
    GetByFeatures(string features) // widgets/{*features}   
    GetBroken() // widgets/broken   
    GetByBrand(string brand) // widgets/{brand}   
    GetByManufacturedDate(DateTime date) // widgets/{*date:datetime}   

Firstly, check the routes for the Order attribute. Now, since the GetBroken()action has an Order of 1, we know that this will be the last route to be evaluated (since the default is 0 and routes are processed in ascending order).
    Get(int widgetId) { ... } // widgets/{widgetId:int}   
    GetNew() // widgets/new   
    GetByFeatures(string features) // widgets/{*features}   
    GetByBrand(string brand) // widgets/{brand}   
    GetByManufacturedDate(DateTime date) // widgets/{*date:datetime}   
    GetBroken() // widgets/broken   

Next up, we will check for any literal routes and ensure that they will be the first to be processed. In this case, the GetNew() action meets that requirement, so move it to the beginning,
    GetNew() // widgets/new   
    Get(int widgetId) { ... } // widgets/{widgetId:int}   
    GetByFeatures(string features) // widgets/{*features}   
    GetByBrand(string brand) // widgets/{brand}   
    GetByManufacturedDate(DateTime date) // widgets/{*date:datetime}   
    GetBroken() // widgets/broken   

After literals, the next routes to be processed are constrained route parameters. The Get(int) action meets this requirement as it accepts an integer for its widgetId parameter, so it will fall next in line,
    GetNew() // widgets/new   
    Get(int widgetId) { ... } // widgets/{widgetId:int}   
    GetByFeatures(string features) // widgets/{*features}   
    GetByBrand(string brand) // widgets/{brand}   
    GetByManufacturedDate(DateTime date) // widgets/{*date:datetime}   
    GetBroken() // widgets/broken   

Next, unconstrained route parameters are processed. The GetByBrand(string)action meets this requirement as it contains a parameter, but it doesn't specify a type for it,
    GetNew() // widgets/new   
    Get(int widgetId) { ... } // widgets/{widgetId:int}   
    GetByBrand(string brand) // widgets/{brand}   
    GetByFeatures(string features) // widgets/{*features}   
    GetByManufacturedDate(DateTime date) // widgets/{*date:datetime}   
    GetBroken() // widgets/broken   

After all of the literals and route parameters have been routed, the next to be evaluated are the wildcard parameters. More specifically, the constrained wildcard parameters, which the GetbyManufacturedDate(DateTime) route matches,
    GetNew() // widgets/new   
    Get(int widgetId) { ... } // widgets/{widgetId:int}   
    GetByBrand(string brand) // widgets/{brand}   
    GetByManufacturedDate(DateTime date) // widgets/{*date:datetime}   
    GetByFeatures(string features) // widgets/{*features}   
    GetBroken() // widgets/broken   

And finally, the last route type to evaluate is unconstrained wildcard parameters (or "catch-all" routes). The GetByFeatures(string) route fits under that category so it will be placed prior to the GetBroken() route, which had a higher Order property,
    GetNew() // widgets/new   
    Get(int widgetId) { ... } // widgets/{widgetId:int}   
    GetByBrand(string brand) // widgets/{brand}   
    GetByManufacturedDate(DateTime date) // widgets/{*date:datetime}   
    GetByFeatures(string features) // widgets/{*features}   
    GetBroken() // widgets/broken   

And that's it as we don't have any ties to break. But, if a tie between two routes did exist, then a case-insensitive comparison of the route template names would resolve it.

Any requests that come through will be evaluated in that order (i.e. the first pattern to match against the URL will be action that handles the request).

Your Mileage May Vary
One important thing to point out in a post like this is that generally, you shouldn't have to deal with routing precedence too often. If you are responsible for building and designing your API, then try to keep your API as simple as possible.

Don't try to create this sprawling, verbose API of endless routes unless you absolutely have to. Complicated APIs can lead to complicated problems, and in most cases, if your API is too difficult to interact with, others won't likely want to use it (especially, if you are building a public-facing API).