Click here to Skip to main content
14,449,906 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 6-Feb-20 4:03am
Rate this:
Please Sign up or sign in to vote.

Solution 1

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 ...
   
v2
Comments
DrgIonuţ 6-Feb-20 4:39am
   
Thanks for your suggestion, OriginalGriff! I have to see how to use DataReader or DataAdapter. The user cannot type what he likes in @Col. I want just to display those values on my WinForm.
Rate this:
Please Sign up or sign in to vote.

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:
SET @col = SELECT ...
   
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?[^]
Rate this:
Please Sign up or sign in to vote.

Solution 3

  • 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.
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.
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:
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:
cmd.Parameters.Add("@Col", SqlDbType.VarChar, 30).Value = "Tag1";
   
v2

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100