Calling SP with multiple output parameters from C# using LINQ

Hi

I have a SQL SP:

CREATE PROCEDURE [dbo].[usp_MySp]
	  @ResultCode INT OUTPUT
	 ,@Error_Number INT OUTPUT
	 ,@Error_Procedure VARCHAR(255) OUTPUT
	 ,@Error_Line INT OUTPUT
	 ,@Error_Message VARCHAR(MAX) OUTPUT
AS
	  SET @ResultCode = 1
		  
	  BEGIN TRANSACTION
	  BEGIN TRY
			SET NOCOUNT ON

--Do lots of "StufF"

END TRY
	  BEGIN CATCH
			IF @@TRANCOUNT > 0
			   SET @Error_Number = ERROR_NUMBER()
			SET @Error_Procedure = ERROR_PROCEDURE()
			SET @Error_Line = ERROR_LINE()
			SET @Error_Message = ERROR_MESSAGE() 
			
			ROLLBACK TRANSACTION;
	  END CATCH

	  IF @@TRANCOUNT > 0
		 BEGIN
			   COMMIT TRANSACTION;

			   SET @ResultCode = 0; -- OK
		 END

Open in new window


I need to call it in C#


        public ResponseBO CreateCashingOutRecords(EnquiryBO enquiry)
        {
            ResponseBO toReturn = new ResponseBO();
           

            try
            {
                using (var db = GetDataContext())
                {
                    toReturn = db.usp_MySp(ref toReturn.ResultCode, ref toReturn.Error_Number, ref toReturn.Error_Procedure, ref toReturn.Error_Line, ref toReturn.Error_Message);

                    toReturn.ResultCode = enquiry.ResultCode;
                }
            }
            catch (Exception e)
            {

                throw e;
            }

            return toReturn;
        }
    }

Open in new window


This bit of my code does not work:

                 
 toReturn = db.usp_MySp(ref toReturn.ResultCode, ref toReturn.Error_Number, ref toReturn.Error_Procedure, ref toReturn.Error_Line, ref toReturn.Error_Message);

Open in new window


I have also tried "enquiry."

         
 toReturn = db.usp_MySp(ref enquiry.ResultCode, ref enquiry.Error_Number, ref enquiry.Error_Procedure, ref enquiry.Error_Line, ref enquiry.Error_Message);

Open in new window

How do I call the SP to return the multiple output parameter to my "toReturn" object.

Thanks

nutnut
nutnutAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
Hi,

try this

 public ResponseBO CreateCashingOutRecords(EnquiryBO enquiry)
        {
            ResponseBO toReturn = new ResponseBO();
            try
            {
                using (var db = GetDataContext())
                {
                    toReturn = db.usp_MySp();
                    toReturn.ResultCode = enquiry.ResultCode;
                    toReturn.Error_Number = enquiry.Error_Number;

                }
            }
            catch (Exception e)
            {

                throw e;
            }

            return toReturn;
        }

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nutnutAuthor Commented:
Hi Thanks but does not work it returns

Error      3      No overload for method 'usp_MySp' takes 0 arguments
Lokesh B RDeveloperCommented:
Hi,

public ResponseBO CreateCashingOutRecords(EnquiryBO enquiry)
        {
            ResponseBO toReturn = new ResponseBO();

            int ResultCode;
            int Error_Number;
            string Error_Procedure;
            int Error_Line;
            string Error_Message;

            try
            {
                using (var db = GetDataContext())
                {
                    db.usp_MySp(ref ResultCode, ref Error_Number, ref Error_Procedure, ref Error_Line, ref Error_Message);

                    toReturn.ResultCode = ResultCode;
                }
            }
            catch (Exception e)
            {

                throw e;
            }

            return toReturn;
        }

Open in new window


http://weblogs.asp.net/scottgu/linq-to-sql-part-6-retrieving-data-using-stored-procedures
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

nutnutAuthor Commented:
Hi thanks but still getting

Error      3      Cannot implicitly convert type 'int' to BO.ResponseBO

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace BO
{
    public class ResponseBO
    {
        public int ResultCode  { get; set; }
        public int Error_Number { get; set; }
        public string Error_Procedure { get; set; }
        public int Error_Line { get; set; }
        public string Error_Message { get; set; }
    }
}	

Open in new window

Lokesh B RDeveloperCommented:
Hi,

IN which line you are getting that error?
nutnutAuthor Commented:
db.usp_MySp(ref ResultCode, ref Error_Number, ref Error_Procedure, ref Error_Line, ref Error_Message);

Open in new window


this one.  Thank you for your help its appreciated and so frustrating!!
Lokesh B RDeveloperCommented:
Hi,

try this

db.usp_MySp(ref toReturn);

Open in new window

nutnutAuthor Commented:
Still not working....Get this

Error      3      No overload for method 'usp_MySp' takes 1 arguments
Lokesh B RDeveloperCommented:
Hi,

Why don't you check what types the stored procedure takes?

i mean parameters?
nutnutAuthor Commented:
Thanks so much for help...I have solved like this:

CREATE PROCEDURE [co].[usp_MySp]
AS
	  DECLARE @ResultCode INT
	  SET @ResultCode = 1
		  
	  BEGIN TRANSACTION
	  BEGIN TRY
			SET NOCOUNT ON

	--do stuff

	  END TRY
	  BEGIN CATCH
			IF @@TRANCOUNT > 0
			   SELECT	@ResultCode AS ResultCode
					   ,ERROR_NUMBER() AS ErrorNumber
					   ,ERROR_PROCEDURE() AS ErrorProcedure
					   ,ERROR_LINE() AS ErrorLine
					   ,ERROR_MESSAGE() AS ErrorMessage;

			ROLLBACK TRANSACTION;
	  END CATCH

	  IF @@TRANCOUNT > 0
		 BEGIN
			  
			  SELECT @ResultCode = 0; -- OK 
			  COMMIT TRANSACTION;

			   
		 END

Open in new window


then
 try
            {
                using (var db = GetDataContext())
                {
                    
                 var output = db.usp_MySp();

                 foreach (var row in output) 
                 {
                     toReturn.ResultCode = row.ResultCode;

                     toReturn.Error_Number = row.ErrorNumber;
                     toReturn.Error_Procedure = row.ErrorProcedure;
                     toReturn.Error_Line = row.ErrorLine;
                     toReturn.Error_Message = row.ErrorMessage;

                 }
                       

                   
                 
                }
            }
            catch (Exception e)
            {

                throw e;
            }

            return toReturn

Open in new window

;

Thanks you very much again for all your help!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.