How to execute Store Procedure in Net 6 Core.?

Question

I want to use store Procedure to Display data into View using label.

Below is my table.

CREATE TABLE [dbo].[SaleInvoiceMaster]( 	[invID] [int] IDENTITY(1,1) NOT NULL, 	[invDate] [date] NOT NULL, 	[customerName] [varchar](100) NOT NULL ) ON [PRIMARY] 

Store Procedure.

Create PROC [dbo].SP_GETInvoiceID 

 AS BEGIN 

  ;with cte as (select 1 as InvID

UNION ALL
select top (1) InvID + 1 as InvID

from SaleInvoiceMaster t 
order by t.InvID  DESC)

select top 1 InvID

from cte ORDER BY InvID DESC

END
 
 

C#

I want to call procedure ,when Create View page load ,i need just idea for calling store Procedure.

public IActionResult Create()
		{

			SaleInvoiceViewModel viewModel = new SaleInvoiceViewModel();
			viewModel.saleInvoiceDetails = new List<SaleInvoiceDetail>();
			SaleInvoiceDetail row1= new SaleInvoiceDetail();
			SaleInvoiceDetail row2 = new SaleInvoiceDetail();
			SaleInvoiceDetail row3 = new SaleInvoiceDetail();
			viewModel.InvDate= DateTime.Now;
			viewModel.saleInvoiceDetails.Add(row1);
			viewModel.saleInvoiceDetails.Add(row2);	
			viewModel.saleInvoiceDetails.Add(row3);


			return View(viewModel);
		}

Answer 1

Hi @akhter hussain,

UPDATE

This another story why the asp-for not working for label tag. I change your code like below and it shows InvId. If you still want to know why, you may need to create a new post. Thanks for your understanding.

        public IActionResult Create()
        {
            SaleInvoiceViewModel viewModel = new SaleInvoiceViewModel();
            viewModel.saleInvoiceDetails = new List<SaleInvoiceDetail>();

            SaleInvoiceDetail row1 = new SaleInvoiceDetail();
            SaleInvoiceDetail row2 = new SaleInvoiceDetail();
            SaleInvoiceDetail row3 = new SaleInvoiceDetail();

            viewModel.InvDate = DateTime.Now;
            viewModel.saleInvoiceDetails.Add(row1);
            viewModel.saleInvoiceDetails.Add(row2);
            viewModel.saleInvoiceDetails.Add(row3);


            int invID = _DBContext.Database.SqlQuery<int>($"EXEC [dbo].SP_GETInvoiceID").ToList<int>()[0];
            viewModel.InvId = invID;
            viewModel.CustomerName = "test";

            return View(viewModel);
        }

            <div class="form-group">
                <label asp-for="InvId" class="control-label"></label>
                <label asp-for="InvId" class="control-label">@Model.InvId</label>
                <span asp-validation-for="InvId" class="text-danger"></span>
            </div>

=================================================================

In your scenario, you can try the following code.

 _DBContext.Database.SqlQuery<int>($"EXEC [dbo].SP_GETInvoiceID").ToList<int>()[0];

Test Result

        private readonly ILogger<TestController> _logger;
        private readonly SqlDataContext _DBContext;

        public TestController(ILogger<TestController> logger, SqlDataContext dBContext)
        {
            _logger = logger;
            _DBContext = dBContext;
        }
        public async Task<JsonResult> testsql()
        {
            int invID = _DBContext.Database.SqlQuery<int>($"EXEC [dbo].SP_GETInvoiceID").ToList<int>()[0];

            return Json(invID);

        }

=================================================================

I see that you have set the invID field to autoincrement, and I don't see the need to create the page and then execute the stored procedure to get the value of invID+1 for the new page.

Suppose a scenario, if 10 people open the page at the same time, or if one person opens the page but does not save it, the next 9 people open the invID and the first person. At this time, the invID does not make any sense.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Jason Pan