Notes

program.cs
using System.Data.Common;
using Microsoft.Data.SqlClient;
using TMS_webAPI.Repos;
#pragma warning disable
using TMS_webAPI.ScopedService;
var builder = WebApplication.CreateBuilder(args);

builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddControllers();
builder.Services.AddControllers().AddNewtonsoftJson();
builder.Services.AddScopedServices();
DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient",SqlClientFactory.Instance);
DBHelper.connectionString = builder.Configuration.GetValue<string>("connString");
var app = builder. Build();
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.MapControllers();
app.Run();

iservices.cs
using System.Data;
using TMS_webAPI.Models;
namespace TMS_webAPI.Interfaces
{
public interface ITMSServiceInterface
{
Task<DataTable> RegisterUser(UserRegisterModel model);
Task<DataTable> LoginUser(UserLoginModel model);

Task<DataTable> UpsertTask(TaskModel model);
Task<DataSet> GetTasks(string? Status);
}
}

services.cs
using System.Data;
using TMS_webAPI.Custom;
using TMS_webAPI.Interfaces;
using TMS_webAPI.Models;
using TMS_webAPI.Repos;

namespace TMS_webAPI.Services
{
public class TMSService : ITMSServiceInterface
{
IDBConnection _DBConnection;
public TMSService(IDBConnection dbConnection)
{
_DBConnection = dbConnection;
}
public async Task<DataTable> RegisterUser(UserRegisterModel model)
{
return await _DBConnection.FillDataTable(DBCustom.registerSP, model);
}
public async Task<DataTable> LoginUser(UserLoginModel model)
{
return await _DBConnection.FillDataTable(DBCustom.loginSP, model);
}
public async Task<DataTable> UpsertTask(TaskModel model)
{
return await _DBConnection.FillDataTable(DBCustom.upsertSP, model);
}
public async Task<DataSet> GetTasks(string? Status)
{
StatusTaskModel? model;
if (Status.Equals("null"))
{
model = null;
}
else
{
model = new StatusTaskModel();
model.Status = Status;
}
return await _DBConnection.FillDataSet(DBCustom.taskByStatus, model);
}
}
}


controller.cs
using Microsoft.AspNetCore.Mvc;
using TMS_webAPI.CustomRoute;
using TMS_webAPI.Interfaces;
using TMS_webAPI.Models;
namespace TMS_webAPI.Controllers
{
[ApiController]
[CustomRoute]
public class TMSController : Controller
{
ITMSServiceInterface _TMSServiceInterface;
public TMSController(ITMSServiceInterface tMSServiceInterface)
{
_TMSServiceInterface = tMSServiceInterface;
}
[HttpPost("auth/Register")]
public async Task<IActionResult> RegisterUser(UserRegisterModel model)
{
var result = await _TMSServiceInterface.RegisterUser(model);
if (result.Rows.Count > 0)
{
return Ok(new { error_status = false, Message = "User registered successfully.", UserId = result.Rows[0][0] });
}
else
{
return BadRequest(new { error_status = true, Message = "Registration Failed ." });
}
}
[HttpPost("auth/Login")]
public async Task<IActionResult> LoginUser(UserLoginModel model)
{
var result = await _TMSServiceInterface.LoginUser(model);
if (result.Rows.Count > 0)
{
return Ok(new { error_status = false, Message = "User Logged in successfully.", Token = result.Rows[0][0], role = result.Rows[0][1] });
}
else
{
return BadRequest(new { error_status = true, Message = "Invalid Credential." });
}
}
[HttpPost("[action]")]
public async Task<IActionResult> UpsertTask(TaskModel model)
{
var result = await _TMSServiceInterface.UpsertTask(model);
if (result.Rows.Count > 0)
{
return Ok(new { error_status = false, Message = "Task Created or modified successfully.", TaskId = result.Rows[0][0] });
}
else
{
return BadRequest(new { error_status = true, Message = "Failed to create or Modify Task." });
}
}
[HttpGet("[action]/{Status}")]
public async Task<IActionResult> GetTasks(string? Status = null)
{
var result = await _TMSServiceInterface.GetTasks(Status);
if (result.Tables.Count > 1)
{
return Ok(new
{
error_status = false,
Message = "Record List successfully.",
data = new
{
Pending = new
{ TaskId = result.Tables[0].Rows[0][0], Title = result.Tables[0].Rows[0][1], AssignedTo = result.Tables[0].Rows[0][2] },
InProgress = new
{ TaskId = result.Tables[1].Rows[1][0], Title = result.Tables[1].Rows[1][1], AssignedTo = result.Tables[1].Rows[1][2] },
Completed = new
{ TaskId = result.Tables[2].Rows[2][0], Title = result.Tables[2].Rows[2][1], AssignedTo = result.Tables[2].Rows[2][2] }
}
});
}
else
{
return Ok(new
{
error_status = false,
Message = "Record List successfully.",
data = new
{ TaskId = result.Tables[0].Rows[0][0], Title = result.Tables[0].Rows[0][1], AssignedTo = result.Tables[0].Rows[0][2] }
});
}
}
}
}



connection.cs
using System.Data;
using System.Data.Common;
using Microsoft.Data.SqlClient;
#pragma warning disable

namespace TMS_webAPI.Repos
{
public class DBConnection : IDBConnection
{
public async Task<DataTable> FillDataTable<T>(string pStrSPName, T? pSQLParams)
{
DbProviderFactory factory = DbProviderFactories.GetFactory("Microsoft.Data.SqlClient");
using (DbConnection con = factory.CreateConnection())
{
con.ConnectionString = DBHelper.connectionString;
using (DbCommand cmd = factory.CreateCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = pStrSPName;

if (pSQLParams != null)
{
foreach (var item in typeof(T).GetProperties())
{
DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = item.Name;
parameter.Value = item.GetValue(pSQLParams);
cmd.Parameters.Add(parameter);
}
}

con.Open();
var result = await cmd.ExecuteReaderAsync();
DataTable op = new DataTable();
op.Load(result);

return op;
}
}
}

public async Task<DataSet> FillDataSet<T>(string pStrSPName, T? pSQLParams)
{
// DbProviderFactory factory = DbProviderFactories.GetFactory("Microsoft.Data.SqlClient");
// using (DbConnection con = factory.CreateConnection())
// {
// con.ConnectionString = DBHelper.connectionString;
// using (DbCommand cmd = factory.CreateCommand())
// {
// cmd.Connection = con;
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.CommandText = pStrSPName;

// if (pSQLParams != null)
// {
// foreach (var item in typeof(T).GetProperties())
// {
// DbParameter parameter = cmd.CreateParameter();
// parameter.ParameterName = item.Name;
// parameter.Value = item.GetValue(pSQLParams);
// cmd.Parameters.Add(parameter);
// }
// }

// con.Open();
// var result = await cmd.ExecuteReaderAsync();
// DataSet dataSet = new DataSet();
// SqlDataAdapter adapter = new SqlDataAdapter(cmd);
// adapter.Fill(dataSet);

// return dataSet;
// }
// }

using (SqlConnection con = new SqlConnection(DBHelper.connectionString))
{
using(SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = pStrSPName;

if(pSQLParams != null)
{
foreach (var item in typeof(T).GetProperties())
{
cmd.Parameters.Add(new SqlParameter(item.Name,item.GetValue(pSQLParams)));
}
}
con.Open();

SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet op = new DataSet();
dataAdapter.Fill(op);

return op;
}
}
}
}
}