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