C#: Retrieve output parameter of non-parametrized stored procedure query?

Question

I have this stored procedure

CREATE PROCEDURE [dbo].[TestProcedure] 
     @param1 int = 0
    ,@param2 int = 0
    ,@total_sales int = 5 OUTPUT 
AS
BEGIN
    SET NOCOUNT ON;

    SET @total_sales = @total_sales * 5
    SELECT * FROM SomeTable
END

And this string in C#

string strSQL = @"
                DECLARE @RC int
                DECLARE @param1 int
                DECLARE @param2 int
                DECLARE @total_sales int

                -- TODO: Set parameter values here.
                SET @param1 = 1
                SET @param2 = 2

                EXECUTE @RC = [TestDB].[dbo].[TestProcedure] 
                   @param1
                  ,@param2
                  ,@total_sales OUTPUT";

And now I want to retrieve the output value, but without parametrizing the input query !

I tried this:

using (System.Data.SqlClient.SqlCommand cmd = (System.Data.SqlClient.SqlCommand)idbConn.CreateCommand())
{
   cmd.CommandText = strSQL;
   cmd.Transaction = (System.Data.SqlClient.SqlTransaction)idbtTrans;

   iAffected = cmd.ExecuteNonQuery();
   idbtTrans.Commit();

   string strOutputParameter = cmd.Parameters["@total_sales"].Value.ToString();
   Console.WriteLine(strOutputParameter);
} // End Using IDbCommand

And this throws an exception (the parameter @total_sales is not in the parameter list).

How can I retrieve an output parameter in a non-parametrized stored-procedure call WITHOUT parametrizing the query ?

antisanity: I have my own methods for database access, and the SQL string gets created automatically. I don't want to start to take it apart again, just to add some parameters. — Stefan Steiger, Sep 22 '11 at 13:14
@Quandary: You don't want to take it apart in order to allow your class to take advantage of functionality already provided by SqlClient? — canon, Sep 22 '11 at 13:16
How do you expect to read a **parameter** without *parameterizing* the query. Isn't the clue in the (self imposed) restriction? — Jamiec, Sep 22 '11 at 13:26
@antisanity: Because the built-in way is about the most work-insentive and unintuitive way to do queries that I've ever seen. — Stefan Steiger, Sep 22 '11 at 13:36
@Quandry - which is why almost nobody uses ADO.NET directly and instead opts for things like EntityFramework. — Jamiec, Sep 22 '11 at 13:47
@Jamiec: EntityFramwork is not implemented on Linux. If at all, I'd use nHibernate. But this is not the issue here. Actually, my class is multi-database. But I can't create the cmd (with parameters) in code, and then pass it to the class, which would be required for parametrization. And that destroys my whole class design. — Stefan Steiger, Sep 22 '11 at 13:53
@Quandary not to be too argumentative, but this is a huge security problem. Embedding literal queries in code is also poor practice. If your class design requires this, it's time to revisit the design (as evidenced by how you've painted yourself into a corner here). Also, ADO.NET, EF, Enterprise Library, etc *all* work with different databases (MySQL, Oracle, of course SQL Server). — 3Dave, Sep 22 '11 at 14:04
@David Lively: At some point, every abstraction framework must produce a string of SQL code. And just because 3rd party code escapes apostrophes instead of yours, doesn't mean it's any less or more secure. In fact, using 3rd party code is probably less secure. Using my own framework doesn't mean it's insecure, if properly used. I do escape strings, and I use templates. — Stefan Steiger, Sep 22 '11 at 14:42
"Using 3rd party code is less secure?" What's your basis for that assumption? — 3Dave, Sep 22 '11 at 14:53
@David Lively: The fact that you don't know exactly what it is doing, how it is doing it, and what it is not doing. — Stefan Steiger, Sep 23 '11 at 10:30
Cite your sources. If you really want to know what it's doing, just fire up SQL profiler and see what's going across the wire, or read the docs. "Not Invented Here" syndrome has been the death of many a project. — 3Dave, Sep 23 '11 at 17:55
@David, Quandary, this is a fantastic discussion, but you're kinda going off on a tangent. David, if you want to write up an answer explaining why *not-parameterizing* is a bad idea and perhaps demonstrating how even if this was working it would fail, that would be awesome; otherwise, consider taking it to chat or just letting it drop. — Shog9, Sep 23 '11 at 22:18
Solved using System.Data.IDbCommand and IDbDataParameter. One needs to cast a parameter in IDbCommand to IDbDataParameter before one can access its value ((System.Data.IDbDataParameter)idbc.Parameters[strParameterName]).Value — Stefan Steiger, Sep 26 '11 at 09:22

score 6 · Accepted Answer · answered Sep 22 '11 at 13:26

6

Short answer: You can't.

Long Answer: You need to use ADO.NET in a more standard way to enable you to leverage things such as output parameters.

answered Sep 22 '11 at 13:26

Jamiec

133,658
13
134
193

score 1 · Answer 2 · answered Sep 24 '11 at 01:22

Well, not sure if this will help, but the following is technically possible :

In your TSQL scripts, use RAISERROR to raise informational messages. You could use this to return information about the name and value of variables. e.g.

DECLARE @Msg NVARCHAR(200)
SET @Msg = 'totalsales=5' -- construct string of form : VariableName=ValueAsString
RAISERROR(@Msg, 10, 1)

In C#, use the SqlConnection.InfoMessage event to catch these messages. Parse the returned strings to extract the names and values. It would be a roundabout way to return parameter values, but it would work.

C#: Retrieve output parameter of non-parametrized stored procedure query?

2 Answers2