Link to home
Start Free TrialLog in
Avatar of Bran-Damage
Bran-DamageFlag for United States of America

asked on

Read output parameters from a stored procedure using c#

I am trying to read the output parameters from the stored procedure.  There are two of them.

The stored procedure executes correctly.  But, it does not have rows.  

Now, if i drill into the sqlDR and go to
    Non Public Memebers
    Command
    Parameters
    Non Public Members
    Inner List
    Then open up both of my output parameters, they both have values, one is a code, and the other is a description.  How can I get those values?
   
InterfaceExportShipDetails objInterfaceExportShipDetails = (InterfaceExportShipDetails) objInterfaceExport;
            odbcCon.ConnectionString = InterlinkOneDBConnection();

            SqlConnection ObjSQLConn = new SqlConnection();
            ObjSQLConn = InterlinkOneSQLCon(ObjSQLConn);
            SqlDataReader sqlDR;
            for (int i = 0; i <= objInterfaceExportShipDetails.aryStrHandlingID.Length; i++)
            {
                SqlCommand sqlCmd = new SqlCommand("spShipmentImport", ObjSQLConn);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.AddWithValue("@Cost", objInterfaceExportShipDetails.dblPkgCost);
                sqlCmd.Parameters.AddWithValue("@ShipDate", objInterfaceExportShipDetails.dtShipDate);
                sqlCmd.Parameters.AddWithValue("@TrackingNumber", objInterfaceExportShipDetails.aryStrTrackingNumber[i]);
                sqlCmd.Parameters.AddWithValue("@SystemOrderNumber", objInterfaceExportShipDetails.strOrderNumber);
                sqlCmd.Parameters.AddWithValue("@PackageType", objInterfaceExportShipDetails.strPackageType);
                sqlCmd.Parameters.AddWithValue("@PackageID", (i + 1));
                //BWL - 04.23.2010 - status of 0 means to add the shipment detail to the order.
                sqlCmd.Parameters.AddWithValue("@Status", 0);
                sqlCmd.Parameters.AddWithValue("@ShipServiceCode", objInterfaceExportShipDetails.strShipViaCode);
                sqlCmd.Parameters.AddWithValue("@Weight", objInterfaceExportShipDetails.aryStrPkgWeight[i]);
                sqlCmd.Parameters.AddWithValue("@TotalNumberOfPackages", objInterfaceExportShipDetails.aryStrHandlingID.Length);
                //sqlCmd.Parameters.AddWithValue("@ProductCode", DBNull.Value);
                //sqlCmd.Parameters.AddWithValue("@Quantity", DBNull.Value);
                //BWL - 04.23.2010 - adding 999 as the default id, this means remote process.
                sqlCmd.Parameters.AddWithValue("@UpdateRepID", 999);
                sqlCmd.Parameters.AddWithValue("@ShipMethod", "Processed");
                SqlParameter parmReturnCode = sqlCmd.Parameters.Add("@ReturnCode", SqlDbType.Int);
                parmReturnCode.Direction = ParameterDirection.Output;
                SqlParameter parmReturnDesc = sqlCmd.Parameters.Add("@ReturnDesc", SqlDbType.NVarChar, 255);
                parmReturnDesc.Direction = ParameterDirection.Output;
                
                try
                {
                    objLogging.intSeverity = 1;
                    objLogging.strInterface = "InterlinkOne";
                    objLogging.strEvent = "InterlinkOnePutback";
                    objLogging.strUserID = objInterfaceExportShipDetails.strPSUserID;
                    objLogging.strdeliveryDocNum = objInterfaceExportShipDetails.strOrderNumber;
                    objLogging.strDescription = sqlCmd.CommandText.ToString() + "   " + sqlCmd.Parameters.ToString();
                    objLogging.InsertQuery();
                }
                catch (Exception ex)
                { }
                try
                {
                    sqlDR = sqlCmd.ExecuteReader();
                    
                    while (sqlDR.Read())
                    {
                        string strErrorCode = (sqlDR.GetString(0));
                        string strErrorDescription = (sqlDR.GetString(1));
                    }
                }

Open in new window

Avatar of DaveJellison
DaveJellison
Flag of United States of America image

Just read your parameters as fields of your command after your command has been executed.



returnCode = (int)sqlCmd.Parameters["@ReturnCode"].Value;
returnDescription = (int)sqlCmd.Parameters["@ReturnDesc"].Value;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of DaveJellison
DaveJellison
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bran-Damage

ASKER

I did it a little more like this, but you got me almost there.

thanks!

                    string strErrorCode = parmReturnCode.Value.ToString();
                    string strErrorDescription = parmReturnDesc.Value.ToString();
Ah very cool. I wasn't sure if that would work. Learned something myself so thank you!