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