CODEWITHSUNDEEP
sqlasp.net-mvc-4stored-procedures
Thushara
Thushara

Reputation: 246

Deleting Record using stored procedure and get a return result to C#

Here My C# Code

public DailyLossElements DeleteDailyLossElements(DailyLossElements _DOmodel)
    {
        decimal res = 0;
        try
        {
            var DOC = new SqlParameter("@DOC", _DOmodel.Daily_Opt_Code);
            var LossCategory = new SqlParameter("@LossCategory", _DOmodel.LossCategory);
            var Loss_Sub_Category = new SqlParameter("@Loss_Sub_Category", _DOmodel.Loss_Sub_Category);
            var Loss_Element = new SqlParameter("@Loss_Element", _DOmodel.Loss_Element);
            //var Result = new SqlParameter("@Result", _DOmodel.Loss_Element);

            OEEDBEntities oeedbEntities = new OEEDBEntities();
            res = oeedbEntities.Database.SqlQuery<decimal>("DeleteDailyLossElements @DOC,@LossCategory,@Loss_Sub_Category,@Loss_Element", DOC, LossCategory, Loss_Sub_Category, Loss_Element).FirstOrDefault();
            if (res != -999)
            {
                _DOmodel.IsActive = true;
                DeductFromDailyInput(_DOmodel.Daily_Opt_Code,res, _DOmodel.LossCategory, _DOmodel.Loss_Sub_Category);
            }

        }
        catch (Exception ex)
        {
            var excep = ex.Message;
        }
        return _DOmodel;

    }

Here My Stored Procedure

ALTER PROCEDURE [dbo].[DeleteDailyLossElements]
(@DOC int,@LossCategory nvarchar(50),@Loss_Sub_Category nvarchar(50),@Loss_Element nvarchar(50),@Result Decimal = -999 Output)

AS
Begin
DECLARE @LossElement_value as decimal 

if exists(SELECT LossElement_value from DailyLossElement where Daily_Opt_Code = @DOC AND LossCategory = @LossCategory AND Loss_Sub_Category = @Loss_Sub_Category AND Loss_Element = @Loss_Element )
Begin
SELECT @LossElement_value = LossElement_value from DailyLossElement where Daily_Opt_Code = @DOC AND LossCategory = @LossCategory AND Loss_Sub_Category = @Loss_Sub_Category AND Loss_Element = @Loss_Element 

Delete from DailyLossElement where Daily_Opt_Code = @DOC AND LossCategory = @LossCategory AND Loss_Sub_Category = @Loss_Sub_Category AND Loss_Element = @Loss_Element 

Set @Result= @LossElement_value;

End
else
Begin 
Set @Result=-999;
end
SELECT @Result
End

When I Try to Retrieve Return Result from stored procedure And Check it in c# Code It doesn't Work.and also i want to Returned value to send another function named DeductFromDailyInput() as a parameter.

Upvotes: 2

Views: 890

Answers (2)

Huske
Huske

Reputation: 9296

Change your code to the following:

public DailyLossElements DeleteDailyLossElements(DailyLossElements _DOmodel)
{
    decimal res = 0;
    try
    {
        var DOC = new SqlParameter("@DOC", _DOmodel.Daily_Opt_Code);
        var LossCategory = new SqlParameter("@LossCategory", _DOmodel.LossCategory);
        var Loss_Sub_Category = new SqlParameter("@Loss_Sub_Category", _DOmodel.Loss_Sub_Category);
        var Loss_Element = new SqlParameter("@Loss_Element", _DOmodel.Loss_Element);
        var Result = new SqlParameter("@Result", _DOmodel.Loss_Element) { Direction = ParameterDirection.Output };

        OEEDBEntities oeedbEntities = new OEEDBEntities();
        oeedbEntities.Database.SqlQuery<decimal>("DeleteDailyLossElements @DOC,@LossCategory,@Loss_Sub_Category,@Loss_Element", DOC, LossCategory, Loss_Sub_Category, Loss_Element, Result).FirstOrDefault();

        res = decimal.Parse(Result.Value.ToString());

        if (res != -999)
        {
            _DOmodel.IsActive = true;
            DeductFromDailyInput(_DOmodel.Daily_Opt_Code,res, _DOmodel.LossCategory, _DOmodel.Loss_Sub_Category);
        }

    }
    catch (Exception ex)
    {
        var excep = ex.Message;
    }
    return _DOmodel;

}

So, I've updated your code from the top of my mind. Probably I missed something but it should give you are pretty good idea where to go from here.

Upvotes: 1

Adersh M
Adersh M

Reputation: 586

In your procedure you are setting the variable @Result as output parameter. So in code,you must have sql parameter @Result and used as ParameterDirection.Output

Upvotes: 1

Related Questions