Return multiple record sets from stored procedure using Web API in AngularJS

skp

SOLVED CLOSED ACTIVE ANSWERED
User: skp
Posted: on Jun 11, 2019 10:52 PM
Forum: ASP.Net MVC
Answer: 1
Views: 2943

How to retrieve two output table data from a single stored procedure using web api and angularjs.

Sp :

USE [ERPT]
GO
/****** Object:  StoredProcedure [dbo].[GET_WH_RECEIPT_AUTO]    Script Date: 6/12/2019 10:13:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC GET_WH_RECEIPT_AUTO 
ALTER PROCEDURE [dbo].[GET_WH_RECEIPT_AUTO]
AS
BEGIN

declare @invoiceno varchar(50)

SELECT @INVOICENO = invoiceno FROM DISPATCH WHERE CUSTOMERID = 22 AND STATUS = 2 ORDER BY CREATEDDATE desc

DECLARE @STARTDATE DATETIME,@ENDDATE DATETIME

SELECT @STARTDATE = DATEADD(MONTH,-7,STARTDATE),@ENDDATE = ENDDATE FROM FINANCIALYEAR where getdate() between startdate and enddate

--SELECT @INVOICENO 'InvNo'

SELECT
    DD.DISPATCHID, 
	DD.PARTID,
	DD.CUSTOMERID,
	--INVOICENO,
    PART.PARTNAME,
--	PART.CASTINGNUMBER,
	REPLACE(REPLACE(PART.PARTNO,'-',''),'.','') PARTNO,
	CONVERT(VARCHAR(10), DD.DISPATCHTIME, 103) 'InvoiceDate',
	DD.NUMBERS,
	DC.CURRENCYRATE,
	DC.CURRENCYAMOUNT,
	DC.INRRATE 'ExchangeRate',
	DC.INRAMOUNT,
	CAST((Dc.INRAMOUNT/DD.NUMBERS) AS DECIMAL(18,2)) 'UNITAMT',
	isnull(PALLETQTY, 0)'PalletCapacity',
	isnull(DD.NUMBERS/NULLIF(PALLETQTY,0),0) 'NoofPallets',
	DISPATCH.INVOICEID,
	WAREHOUSE.ISSUEDQTY,
	WAREHOUSE.STOCK,
	WAREHOUSE.STATUS
INTO #TEMPWH
 FROM DISPATCH_DAILY DD
 INNER JOIN DISPATCH_CURRENCY DC ON DC.DISPATCHID = DD.DISPATCHID
 INNER JOIN PART ON PART.PARTID = DD.PARTID 
 LEFT  JOIN part_pallet_master PPM ON PPM.PARTID = DD.PARTID AND PPM.CUSTOMERID = DD.CUSTOMERID
 LEFT  JOIN DISPATCH ON DISPATCH.INVOICEno = DD.INVOICEno AND DISPATCH.CREATEDDATE >= @STARTDATE
 LEFT JOIN WAREHOUSE ON WAREHOUSE.DISPATCHID = DD.DISPATCHID 
 WHERE DD.INVOICENO = @INVOICENO AND DISPATCHTIME >= @STARTDATE AND DISPATCHTIME <= @ENDDATE
 ORDER BY WAREHOUSE.STOCK DESC

 SELECT * FROM #TEMPWH

'Accepted',sum(ISSUEDQTY) 'Issued',sum(STOCK) 'Stock' FROM #TEMPWH

SELECT DISTINCT @INVOICENO invoiceno, invoiceid FROM #TEMPWH

drop table #TEMPWH
 
 end

skp

0 3

Reply
ANSWER
User: skp
Replied: on Jun 12, 2019 03:42 AM
Report

Finally, found the solution. Using dataset or datatable is always recommended, because it gives control to the code. Because, unspecified row data will not displayed. Dataset and datatable will eliminate those unspecified row data and display only the specified data.

Controller.cs (First ouput table data):

        [HttpGet]
        [ActionName("GetPendingDetails")]
        public string GetInvoiceDetails(DataSet ds)
        {
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ERPConnectionString"].ConnectionString;
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "GET_WH_RECEIPT_AUTO";
            sqlCmd.Connection = myConnection;
            SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
            ds = new DataSet();
            string jsonString = string.Empty;
            myConnection.Open();
            sda.Fill(ds);
            myConnection.Close();
            jsonString = JsonConvert.SerializeObject(ds.Tables[0]);
            return jsonString;           
        }

Controller.cs (Second ouput table data):

        [HttpGet]
        [ActionName("GetInvoiceProperties")]
        public string GetInvoiceProperties(DataSet ds)
        {           
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ERPConnectionString"].ConnectionString;
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "GET_WH_RECEIPT_AUTO";
            sqlCmd.Connection = myConnection;          
            SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
            ds = new DataSet();
            string jsonString = string.Empty;           
            myConnection.Open();
            sda.Fill(ds);            
            myConnection.Close();
            jsonString = JsonConvert.SerializeObject(ds.Tables[1]);
            return jsonString;           
        }

Return multiple record sets from stored procedure using Web API in AngularJS

Company

Explore

Follow us

Contact Us