Member Avatar for naveen_arc

getting value of return value of stored procedure to code behined,with MicroSoft Patterns and Practices.

Hi all,

I am using Microsoft Patterns and Practices to make a database connection to my application. I am able to get the value of the OUT parameter of the stored procedure to code behind. But not able to find the way how to get the return value of a stored procedure to code behind.

Can anyone plz let me know the way....! The example code snippet is really appriciable.....!

Thanks & Regards,
(Naveen Kumar M)

  • 2 Contributors
  • 3 Replies
  • 197 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Jugortha

Dani AI

Generated

Short answer for : add an explicit return-value parameter to the command (ParameterDirection.ReturnValue) before executing the procedure, then read it back after execution (Enterprise Library provides GetParameterValue; plain ADO.NET uses the SqlParameter.Value). Note: SQL Server’s RETURN is an integer status code — use OUTPUT parameters for non-integer data. (documentation.help)

Example with the Microsoft Patterns & Practices Data Access block (Enterprise Library):

Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("dbo.MyProc");

// add return-value (name is arbitrary; avoid provider prefixes for portability)
db.AddParameter(cmd, "ReturnValue", DbType.Int32, ParameterDirection.ReturnValue, string.Empty, DataRowVersion.Default, null);

// add other params as needed
db.AddInParameter(cmd, "SomeInput", DbType.Int32, 123);

db.ExecuteNonQuery(cmd);

// read the return value
int rv = Convert.ToInt32(db.GetParameterValue(cmd, "ReturnValue"));

The same idea in plain ADO.NET:

using (var cn = new SqlConnection(connString))
using (var cmd = new SqlCommand("dbo.MyProc", cn) { CommandType = CommandType.StoredProcedure })
{
    var ret = new SqlParameter("@ReturnValue", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue };
    cmd.Parameters.Add(ret);

    cmd.Parameters.AddWithValue("@SomeInput", 123);

    cn.Open();
    cmd.ExecuteNonQuery();

    int rv = (int)ret.Value;
}

If you use ExecuteReader, remember output/return parameters aren’t available until the reader is closed. If you get “parameter not supplied” errors, check you added the return param (not an OUTPUT one) and that names/prefixes match your provider usage. As suggested, if you control the proc it’s usually cleaner to use OUTPUT (or a UDF) for returning non-status values. (learn.microsoft.com)

Member Avatar for Jugortha

Why you don' use an UDF user defined function instead of a stored procedure

Member Avatar for naveen_arc

Why you don' use an UDF user defined function instead of a stored procedure

Hi,

Thanx for the reply and actually that is the clients requirement. if not i could have gone for OUT parameter.

As this is the client's requirement i could not change. So, please let me know the way.

Thanks & Regards,
(Naveen Kumar M)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.