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