How do I get column values using stored procedure output parameters in C#

Question

0.00/5 (No votes)

See more:

I have a SQL table with 3 columns: ID (bigint), Tag1 (int), DateTime (datetime). I've made a stored procedure which selects from my table the values of Tag1 from DataStart to DataStop. It works fine. The execution of the stored proc returns, correctly, 5 values.
I want to get those 5 values in C#.
I get no error when I press the button, but no value is displayed in MessageBox. I'm not sure if I should have used the @Col parameter as Output. What should I do in order to get those values?

What I have tried:

My stored procedure:

ALTER PROCEDURE [dbo].[PS_TagLogging]
 -- Add the parameters for the stored procedure here
 @Col varchar(30) Output, 
 @DataStart varchar(50) ,
 @DataStop  varchar(50) 

AS
BEGIN

 SET NOCOUNT ON;
 DECLARE @sql nvarchar(1000)

  SET @sql = N'SELECT ' + @Col + ', DateTime ' + 'FROM [TRTF_TagLogging].[dbo].[tbl_TagLogging] WHERE (DateTime BETWEEN ' +''''+ @DataStart +'''' + ' AND '  +''''+ @DataStop+'''' +')'
   exec (@sql)
   PRINT @sql
END

execute [PS_TagLogging] '[Tag1]', '2020-02-05 13:06:30.697','2020-02-05 13:12:25.703'

My C# code:

private void DB_ProcStoc_Click(object sender, EventArgs e)
        {
            using (SqlConnection connection = new SqlConnection(@"Data Source=DESKTOP-JQSJAF8\SQLEXPRESS;Initial Catalog=TRTF_TagLogging;Integrated Security=True"))
            {
                using (SqlCommand cmd = new SqlCommand("PS_TagLogging", connection))
                {
                    connection.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Col", SqlDbType.VarChar, 30).Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@DataStart", SqlDbType.VarChar, 30).Value = "2020-02-05 13:06:30.697";
                    cmd.Parameters.Add("@DataStop", SqlDbType.VarChar, 30).Value = "2020-02-05 13:06:50.700";

                    cmd.ExecuteNonQuery();

                    strCol = Convert.ToString(cmd.Parameters["@Col"].Value); //strCol is a string globally declared
                    connection.Close();
                }
            }
            MessageBox.Show("Proc: " + strCol + " values");            
        }

Posted 5-Feb-20 22:23pm

DrgIonuţ

Updated 6-Feb-20 3:03am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-02-05T22:31:00

You are SELECTing the values - so don't use ExecuteNonQuery, this is a Query!
Use a DataReader or DataAdapter instead with SqlCommand.ExecuteReader or SqlDataAdapter.Fill and it will work (except @Col is always going to be NULL because it's an OUTPUT parameter).

But ... that is a very dangerous thing to do, unless you have absolutely rigid control over what gets passed to your SP: if the user can type what he likes in @Col then your code is vulnerable to SQL injection ...

phil.o · Answer 2 · 2020-02-05T22:38:00

Solution 2

As a complement to solution 1, you do not assign the @col parameter any value. Instead, you are using it as a column name in your query.
Using a stored procedure with output parameters - SQL Server | Microsoft Docs[^]
At some point in your procedure, the @col parameter should be in the left part of an assignment operation:

SQL

SET @col = SELECT ...

Posted 5-Feb-20 22:38pm

phil.o

Comments

DrgIonuţ 6-Feb-20 4:52am

I have put in SP: SET @Col = N'SELECT Tag1 FROM [TRTF_TagLogging].[dbo].[tbl_TagLogging]', but I get the error: "Must declare the scalar variable "@Col"."
If I declare it, it would be a conflict whit my parameter.

phil.o 6-Feb-20 5:13am

You could try SELECT @Col = Tag1 FROM ....
Here are a few examples: Execute stored procedure with an Output parameter?[^]

Richard Deeming · Answer 3 · 2020-02-06T03:03:00

Remove the dynamic SQL, which is making your code vulnerable to SQL Injection[^].
Remove the output parameter, which isn't used.
Change the two date parameters to use the correct data type.

SQL

ALTER PROCEDURE [dbo].[PS_TagLogging]
(
    @DataStart datetime,
    @DataStop  datetime
)
AS
BEGIN
    SELECT
        [Tag1]
    FROM
        [TRTF_TagLogging].[dbo].[tbl_TagLogging]
    WHERE
        [DateTime] BETWEEN @DataStart And @DataStop
    ;
END
GO

Change your C# code to use ExecuteReader and read all of the values returned from the stored procedure.

C#

private void DB_ProcStoc_Click(object sender, EventArgs e)
{
    using (SqlConnection connection = new SqlConnection(@"Data Source=DESKTOP-JQSJAF8\SQLEXPRESS;Initial Catalog=TRTF_TagLogging;Integrated Security=True"))
    using (SqlCommand cmd = new SqlCommand("PS_TagLogging", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@DataStart", SqlDbType.DateTime).Value = new DateTime(2020, 2, 5, 13, 6, 30, 697);
        cmd.Parameters.Add("@DataStop", SqlDbType.DateTime).Value = new DateTime(2020, 2, 5, 13, 6, 50, 700);

        connection.Open();
        
        List<int> tags = new List<int>();
        using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (reader.Read())
            {
                tags.Add(reader.GetInt32(0));
            }
        }

        strCol = string.Join(", ", tags);
    }
    
    MessageBox.Show("Proc: " + strCol + " values");            
}

If you really want to pass in the column name to select, then you need to validate it extremely carefully. You will need to use sp_executesql[^] to pass the parameters to the dynamic SQL query:

SQL

ALTER PROCEDURE [dbo].[PS_TagLogging]
(
    @Col       varchar(30),
    @DataStart datetime,
    @DataStop  datetime
)
AS
BEGIN
DECLARE @RealColumnName sysname;
DECLARE @sql nvarchar(max), @params nvarchar(max);
    
    SET NOCOUNT ON;
    
    -- Validate the column name:
    SELECT
        @RealColumnName = QUOTENAME(C.name)
    FROM
        [TRTF_TagLogging].sys.columns As C
        INNER JOIN [TRTF_TagLogging].sys.objects As T ON T.object_id = C.object_id
        INNER JOIN [TRTF_TagLogging].sys.schemas As S ON S.schema_id = T.schema_id
    WHERE
        S.name = 'dbo'
    And
        T.name = 'tbl_TagLogging'
    And
        (C.name = @Col Or QUOTENAME(C.name) = @Col)
    ;
    
    If @RealColumnName Is Null THROW 51000, 'The specified column does not exist.', 1; 
    
    SET @sql = N'SELECT ' + @RealColumnName + N' FROM [TRTF_TagLogging].[dbo].[tbl_TagLogging] WHERE [DateTime] BETWEEN @DataStart And @DataStop';
    
    SET @params = N'@DataStart datetime, @DataStop datetime';
    
    EXEC sp_executesql @sql, @params, @DataStart = @DataStart, @DataEnd = @DataEnd;
END
GO

You'll then need to pass the parameter from your C# code:

C#

cmd.Parameters.Add("@Col", SqlDbType.VarChar, 30).Value = "Tag1";

How do I get column values using stored procedure output parameters in C#

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0