How to write sql query in web api

some1some1 Member ✭✭✭
edited July 4 in Xamarin.Forms

I have created Web api.I want to find last data in a column of table. How can i write sql query in controller.

I want to establish a query just like this in controller
select MAX(ORDERNO) from ORDERBILL

Best Answer

Answers

  • Amar_BaitAmar_Bait DZMember ✭✭✭✭✭

    What database are you using? are you using any ORM (Entity Framework or other) ?

  • some1some1 Member ✭✭✭

    @Amar_Bait yes ,Im using Entity Framework.

  • some1some1 Member ✭✭✭

    @Amar_Bait An error message is showing
    An Object reference is required for the non static field ,method,or property "BillingEntities2.TH_ESTIMATE_ORDER_BILL"

  • Amar_BaitAmar_Bait DZMember ✭✭✭✭✭

    Show us your code

  • some1some1 Member ✭✭✭

    @Amar_Bait

    public string GetOrderNo()
    {
    var max= BillingEntities2.TH_ESTIMATE_ORDER_BILL.Max(x => x.TH_ORDER_NO);
    return max;
    }

  • Amar_BaitAmar_Bait DZMember ✭✭✭✭✭

    Show us the DbContext and the entities. Does the database exist? Can you connect successfully to the database and retrieve some records? Are you using Code First or Database First approach?

  • some1some1 Member ✭✭✭

    @Amar_Bait Thank you . I got answer for that.

    Could please help me to write something like this in controller.

    SELECT TOP 1 TH_DOC_NO=CONVERT(INT,(SUBSTRING(TH_DOC_NO,3,10))) FROM TH_ESTIMATE_ORDER_BILL ORDER BY TH_DOC_NO DESC

  • Amar_BaitAmar_Bait DZMember ✭✭✭✭✭

    What are you trying to get from that query?

  • some1some1 Member ✭✭✭

    @Amar_Bait I have a string like 'DD00023' i have to increment this to 'DD00024'. How can i do that in controller

  • Amar_BaitAmar_Bait DZMember ✭✭✭✭✭
    edited July 4
    var result = dbContext.TH_ESTIMATE_ORDER_BILL.OrderByDesc(x => x.TH_DOC_NO).Take(1).Select(x => new
    {
        TH_DOC_NO_INT = int.Parse(x.TH_DOC_NO.Substring(2))
    });
    
    var nextNumber = result.TH_DOC_NO_INT + 1;
    var nextThDocNoString = $"DD{nextNumber:00000}";
    

    I strongly suggest you to start learning LINQ before continuing your project, as it is not sustainable that you block and ask for help for every query your project will have.

  • some1some1 Member ✭✭✭

    @Amar_Bait Ok. Thank You

  • Amar_BaitAmar_Bait DZMember ✭✭✭✭✭
    edited July 4

    Welcome. If my answer helped you mark it as answer for future reference. Good luck with your project!

Sign In or Register to comment.