Get column values using stored procedure output parameters in C#

Question

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 code:

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:06:50.700'

The execution of the stored proc returns, correctly, 5 values.

I want to get those 5 values in C#. What I've tried:

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");            
        }

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?

Do Stored Procedures Protect Against SQL Injection? - The wrong way — Reza Aghaei
– Reza Aghaei, Commented Feb 6, 2020 at 9:17
why are you making dynamic sql query? And what is your exact requirement? Do you even need output parameter? — Atk
– Atk, Commented Feb 6, 2020 at 9:22
How could make it using an ordinary sql query? I mean, how could I pass the parameter when I exec the stored proc? I have tried something like this: SELECT [ID] ,[Tag1] ,[DateTime] FROM [TRTF_TagLogging].[dbo].[tbl_TagLogging] WHERE DateTime between DataStart and DataStop AND Tag1=Col; but I don't know how to exec using Col parameter. — Ionut
– Ionut, Commented Feb 6, 2020 at 9:33
This is why I wrote that I'm not sure if I have used correctly the Col parameter as Output. — Ionut
– Ionut, Commented Feb 6, 2020 at 9:35

Ionut · Accepted Answer · 2020-02-07 07:29:33Z

If someone has the same problem as I did, it's been solved like this:

My stored proc:

ALTER PROCEDURE [dbo].[PS_TagLogging]
 -- Add the parameters for the stored procedure here
 @DataStart datetime=null,
 @DataStop  datetime=null

AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;    

    -- Insert statements for procedure here
SELECT [ID]
      ,[Tag1]      
      ,[DateTime]
  FROM [TRTF_TagLogging].[dbo].[tbl_TagLogging]
  WHERE DateTime between @DataStart and @DataStop 
END

And in C#, on button click action:

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("@DataStart", SqlDbType.DateTime).Value = new DateTime(2020, 02, 05, 13, 06, 30, 697);
                    cmd.Parameters.Add("@DataStop", SqlDbType.DateTime).Value = new DateTime(2020, 02, 05, 13, 12, 25, 703);//2020, 02, 05, 13, 12, 25, 703   //2020, 02, 05, 13, 06, 50, 700

                    var values = new List<int>();
                    var valData = new List<DateTime>();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        values.Add(reader.GetInt64(0));//reads the value of my first column (ID) from DB
                        values.Add(reader.GetInt32(1));//reads the value of my second column (Tag1) from DB
                        valData.Add(reader.GetDateTime(2));//reads the date from my third column of DB
                    }

                    strCol = String.Join(" ", values);
                    strDate = String.Join(" ", valData);


                    connection.Close();
                }
            }
            //MessageBox.Show("Proc: " + strCol + " values");
            MessageBox.Show("Date: "+ strDate);            
        }

Collectives™ on Stack Overflow

Get column values using stored procedure output parameters in C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related