1. I want to write a wep api without adding a database and using a procedure. To do this, I first wrote a procedure in the database
Create Procedure[dbo].[UsersPerdays]
@IdentityNo varchar(11)
AS
BEGIN
SET NOCOUNT ON;
SELECT
UserInfID,
FullName,
PersonnelNo,
IdentityNo,
MobileNo,
EmailAdd,
DepartmentID,
RegDate
FROM Users
END
2. Then I created a project and created a model in the model section and wrote the following code in the startup section
services.AddDbContext<ApplicationDbContext>(options =options.UseSqlServer(
Configuration.GetConnectionString("DefaultConnection")));
3. Then in the controller section, I created a web API controller and wrote the following code, but when I checked it with my post software, I encounter the following error
[Route("api/[controller]")]
[ApiController]
[Produces("application/json")]
public class UsersApiController : Controller
{
private readonly ApplicationDbContext context;
public UsersApiController(ApplicationDbContext _context)
{
context = _context;
}
public async Task<IActionResult> Index(string identityNo)
{
var model = await GetUserPerDay(identityNo);
return Ok(model);
}
[HttpGet("{identityNo}")]
public async Task<IEnumerable<UserViewModels>> GetUserPerDay(string identityNo)
{
SqlConnection connection = null;
SqlCommand command = null;
var model = new List<UserViewModels>();
try
{
connection = context.Database.GetDbConnection() as SqlConnection;
command = new SqlCommand("dbo.UsersPerdays")
{
CommandType = CommandType.StoredProcedure,
Connection = connection
};
command.Parameters.Add(new SqlParameter("@IdentityNo", identityNo));
if (connection.State != ConnectionState.Open)
await connection.OpenAsync();
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
model.Add(new UserViewModels
{
UserInfId = reader.GetInt32(reader.GetOrdinal("UserInfId")),
FullName = reader.GetString(reader.GetOrdinal("FullName")),
PersonnelNo = reader.GetString(reader.GetOrdinal("PersonnelNo")),
IdentityNo = reader.GetString(reader.GetOrdinal("IdentityNo")),
MobileNo = reader.GetString(reader.GetOrdinal("MobileNo")),
EmailAdd = reader.GetString(reader.GetOrdinal("EmailAdd")),
DepartmentId = reader.GetInt32(reader.GetOrdinal("DepartmentId")),
RegDate = reader.GetDateTime(reader.GetOrdinal("RegDate")),
UserName = reader.GetString(reader.GetOrdinal("UserName")),
PassWord = reader.GetString(reader.GetOrdinal("PassWord"))
});
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (command != null)
{
command.Dispose();
}
if (connection.State != ConnectionState.Closed)
{
connection.Close();
connection.Dispose();
}
}
return model;
}
}
}
What I have tried:
Error in postman :
Could not send request
Error: connect ECONNREFUSED 127.0.0.1:44303