Return Identity value from Stored Procedure in Web API in ASP.Net

Last Reply 6 months ago By dharmendr

Posted 6 months ago

After inserting data into database, I want to return a value named poid using identity in sql and also I wanted to pass the identity return value to another web api in angularjs.

[HttpPost]
[ActionName("addPoDetails")]
public int addPoDetails(poDetails pd)
{
    pd = new poDetails();
    SqlConnection myConnection = new SqlConnection();
    myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ERPConnectionString"].ConnectionString;
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.CommandText = "INSERT_PURCHASEORDER_N";
    sqlCmd.Connection = myConnection;
    sqlCmd.Parameters.AddWithValue("@PURCHASEORDERNO", pd.PURCHASEORDERNO);
    sqlCmd.Parameters.AddWithValue("@SUPPLIERID", pd.SUPPLIERID);
    sqlCmd.Parameters.AddWithValue("@CreatedDate", pd.CREATEDDATE);
    sqlCmd.Parameters.AddWithValue("@SUBTOTAL", pd.SUBTOTAL);
    sqlCmd.Parameters.AddWithValue("@GRANDTOTAL", pd.GRANDTOTAL);
    sqlCmd.Parameters.AddWithValue("@CreatedDate", pd.CREATEDDATE);
    myConnection.Open();
    sqlCmd.Parameters.AddWithValue("@RETURNVALUE", 8).Direction = ParameterDirection.ReturnValue;
    sqlCmd.ExecuteNonQuery();
    myConnection.Close();
    int poid = int.Parse(sqlCmd.Parameters["@RETURNVALUE"].Value.ToString());
    return poid;
}

 

ALTER PROCEDURE INSERT_PURCHASEORDER_N
	@PURCHASEORDERNO VARCHAR(50) = NULL,
	@SUPPLIERID INT = NULL,
	@CREATEDDATE DATETIME = NULL,
	@SUBTOTAL DECIMAL(18,2) = NULL,
	@GRANDTOTAL DECIMAL(18,2) = NULL
AS
BEGIN
	DECLARE @POID INT

	INSERT INTO PURCHASEORDER
	(
		PURCHASEORDERNO,
		SUPPLIERID,
		CREATEDDATE,
		SUBTOTAL,
		GRANDTOTAL
	)
	VALUES
	(
		@PURCHASEORDERNO,
		@SUPPLIERID,
		GETDATE(),
		@SUBTOTAL,
		@GRANDTOTAL
	)

	SET @POID = @@IDENTITY
	RETURN @POID
END
GO
Posted 6 months ago

Hi skp,

For returning output parameter you have to define the OUTPUT keyword.

Refer below article.

How to return Output parameter from Stored Procedure in ASP.Net using C# and VB.Net

If you want to return identity refer below article.

Return Identity value from Stored Procedure in SQL Server