Creating a WEB API POST Method to Retrieve Data from SQL Database using ASP.NET MVC Application

Article

Introduction

In this article, I have explained how to create a WEBAPI POST method to retrieve data from an SQL database using ASP.NET MVC Application.

Open SQL Management Studio then create a new table and write the stored procedure for the table.

Column name and datatype of table based on your requirement.

data table

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 right side core references section.

create New application

Now the project has been created successfully.

project success

Once the project has been created then Right click on the Models folder, choose to add, and click New item.

Add the class file to the Models Folder.

AddClassFile

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.

classDefinition

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.

mvc

select Controller

Then give the controller name based on your requirement.

AddController

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));
            }

        }

    }
}

Once we ran the application, we got the output like the below screenshot.

output

Sharing is Caring!!..