Returning multiple values with a stored procedure

for starters you can't user the ExecuteNonQuery() function, if you're using sql you can use the sqldatareader

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx

The SqlDataReader is a class that is used to create a forward only result set that requires an open data-connection the whole time it's running. You can only iterate over the rows once, while you read through them and do not have the ability to move the cursor back. You have one chance to look at each record and then move on. Remember that you must close your data reader before you close your connection otherwise you'll end up with an orphaned data reader which will hurt performance over time.

using System;
using System.Data.SqlClient;

namespace pc.sqldatareaderExample
{
    class Program
    {
        static string cs =
          @"Data Source=BEAST\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";

        static void Main(string[] args)
        {
            string GetPersons = "SELECT * from Person;";

            //using statement to ensure dispose of
            using (var conn = new SqlConnection(cs))
                try
                {
                    var cmnd = new SqlCommand(GetPersons, conn);

                    conn.Open();
                    using (SqlDataReader dr = cmnd.ExecuteReader())
                        try
                        {
                            if (dr.HasRows)
                                while (dr.Read())
                                    Console.WriteLine($"{dr[0]}) {dr[1]} {dr[2]}");
                        }
                        finally
                        {
                            dr.Close();
                        }
                   
                }
                catch (SqlException sqlEx)
                {
                    //display sql exception
                    Console.WriteLine(sqlEx.Message);
                }
                catch (Exception Ex)
                {
                    //display all other exceptions
                    Console.WriteLine(Ex.Message);
                }
                finally
                {
                    //ensure that the connection is closed everytime
                    conn.Close();
                }
        }
    }
}

Select allOpen in new window

Interesting, I have been using ExecuteNonQuery()  for years but rarely to read one record. Normally I am grabbing many rows and display the data. Let me review sample.

strange, as far as i recall ExecuteNonQuery only returns a count of how many rows your query affected, but it has been a couple years since i've worked with database queries.

actually i think i may have misunderstood your question, are you trying to retrieve data from a stored procedure?

ok so i was a bit off base turns out you where right and you can get results from a stored procedure, it's actually pretty cool

             SqlCommand cmd = new SqlCommand("rbs_PerformARCreditCheck", DataConnection);


            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@I_CUSTNMBR", CUSTNMBR));

            SqlParameter r1 = GetSqlParameter("@O_CURRBLNC", CURRBLNC);
            SqlParameter r2 = GetSqlParameter("@O_CRLMTTYP", CRLMTTYP);
            SqlParameter r3 = GetSqlParameter("@O_CRLMTAMT", CRLMTTYP);

            cmd.Parameters.Add(r1);
            cmd.Parameters.Add(r2);
            cmd.Parameters.Add(r3);

            cmd.ExecuteNonQuery();

            // cast values to their appropriate types
            var resutl1 = r1.Value;
            var resutl2 = r2.Value;
            var resutl3 = r3.Value;

Select allOpen in new window

    public SqlParameter GetSqlParameter(string paramterName, object value)
        {
            return new SqlParameter { ParameterName = paramterName, Value = value, Direction = ParameterDirection.Output};
        }

Select allOpen in new window

I haven't tested this code, but i think this is more or less the idea; give it a try

https://msdn.microsoft.com/en-us/library/system.data.parameterdirection(v=vs.110).aspx

I think the direction enum has to be output when it's being set in the stored procedure and return if that stored procedure returns a value.

I will be able to test this in about 30 minutes.  The trick is in VS C# and the trick is the setting up of the output variables. I have found example with all kinds of settings but I was not to find what all the settings mean.

Here is the stored procedure. Right now when called the result1, result2 and result3 values are NULL.

USE [MYCOMP]
GO

/****** Object:  StoredProcedure [dbo].[rbs_PerformARCreditCheck]    Script Date: 5/8/2017 9:38:37 AM ******/
DROP PROCEDURE [dbo].[rbs_PerformARCreditCheck]
GO

/****** Object:  StoredProcedure [dbo].[rbs_PerformARCreditCheck]    Script Date: 5/8/2017 9:38:37 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:            Richard E. Wheeler
-- Create date: 05/08/2017
-- Description:      Get the AR open balances amounts plus the open SOP orders and invoices totals for a customer and return this as one lump sum number
-- =============================================

CREATE PROCEDURE [dbo].[rbs_PerformARCreditCheck]
@I_CUSTNMBR CHAR(15),@O_CURRBLNC decimal(19,5) OUTPUT,@O_CRLMTTYP INT OUTPUT,@O_CRLMTAMT decimal(19,5) OUTPUT
      
AS
BEGIN

DECLARE @I_ARBALNCE decimal(19,5)
DECLARE @I_SOPBLNCE decimal(19,5)
DECLARE @O_iErrorState INT

SET NOCOUNT ON;

SET @I_ARBALNCE = 0.00
SET @I_SOPBLNCE = 0.00
SET @O_CURRBLNC = 0.00
SET @O_CRLMTTYP = 0.00
SET @O_CRLMTAMT = 0.00

BEGIN TRY
      -- Get the current AR balance
      SELECT @I_ARBALNCE = agperamt_1 + agperamt_2 + agperamt_3 + agperamt_4 + agperamt_5 + agperamt_6 + agperamt_7
      FROM POWMT..RM00103
      WHERE CUSTNMBR = @I_CUSTNMBR

      -- Get the current balance of any unposted orders and invoices
      SELECT @I_SOPBLNCE = SUM(QTYREMAI*UNITPRCE)
      FROM POWMT..SOP10200 S12
      INNER JOIN POWMT..SOP10100 S11 ON S12.SOPTYPE=S11.SOPTYPE AND S12.SOPNUMBE=S11.SOPNUMBE
      WHERE CUSTNMBR = @I_CUSTNMBR

      SET @O_CURRBLNC = @I_ARBALNCE + @I_SOPBLNCE

      -- Get the customers credit limit type
      SELECT @O_CRLMTTYP = CRLMTTYP FROM RM00101
      WHERE CUSTNMBR = @I_CUSTNMBR

      -- Get the customers credit limit amount
      SELECT @O_CRLMTAMT = CRLMTAMT FROM RM00101
      WHERE CUSTNMBR = @I_CUSTNMBR

      RETURN(0)
END TRY

BEGIN CATCH
    RETURN(1);
END CATCH;

END

This is what I got to work.

        private void DisplayARCreditSummary()
        {
            try
            {
                SqlCommand cmd = new SqlCommand("rbs_PerformARCreditCheck", DataConnection);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@I_CUSTNMBR", CUSTNMBR));

                cmd.Parameters.Add(new SqlParameter("@O_CURRBLNC", SqlDbType.Float)).Direction = ParameterDirection.Output;
                cmd.Parameters.Add(new SqlParameter("@O_CRLMTTYP", SqlDbType.Int)).Direction = ParameterDirection.Output;
                cmd.Parameters.Add(new SqlParameter("@O_CRLMTAMT", SqlDbType.Float)).Direction = ParameterDirection.Output;

                cmd.Connection = DataConnection;

                cmd.ExecuteNonQuery();

                CURRBLNC = Convert.ToDecimal(cmd.Parameters["@O_CURRBLNC"].Value);
                CRLMTTYP = Convert.ToInt32(cmd.Parameters["@O_CRLMTTYP"].Value);
                CRLMTAMT = Convert.ToDecimal(cmd.Parameters["@O_CRLMTAMT"].Value);

            }
            catch (Exception ex)
            {
                string eMsg = "003: ERROR: " + ex.Message;
                if (stackTraceWanted) eMsg += "\n" + ex.StackTrace;
                if (stackTraceWanted) MessageBox.Show(eMsg);
            }
        }

        public SqlParameter GetSqlParameter(string paramterName, object value)
        {
            return new SqlParameter { ParameterName = paramterName, Value = value, Direction = ParameterDirection.Output };
        }

Thank you so much for your help. As you can tell C# is not my forte so any help is always appreciated.