I want to create an autoincrement order number. Where the numbers will start from 00001. Current year will be attached. When the new year comes, the number will start again from 00001. For example - 00001/2023 00002/2023 New Year it will be 00001/2024.
An expert helped me by writing the code below. But the problem is when I want to get it as json data via dotnet core FromSqlRaw it is not working. It would be helpful if any experienced person could help me. thanks in advance
//expert sql Code
create table NextOrderNumber ( Year int not null, Number int not null ) go declare @year int = 2023; declare @number int = 1; merge NextOrderNumber as n using (select @year as year) as y on n.Year = y.Year when matched then update set Number = Number + 1, @number = Number + 1 when not matched then insert (Year, Number) values (y.year, @number); select cast(@year as varchar(4)) + right('0000' + cast(@number as varchar(5)), 4);
//my code Controller
public JsonResult GetOrderNO()
{ string query = $"declare @year int = 2025; declare @number int = 1; merge NextOrderNumber as n using (select @year as year) as y on n.Year = y.Year when matched then update set Number = Number + 1, @number = Number + 1 when not matched then insert (Year, Number) values (y.year, @number); select cast(@year as varchar(4)) + right('0000' + cast(@number as varchar(5)), 4); ";
var List = _context.NextOrderNumber.FromSqlRaw(query);
return Json(List); }