DrgIonuţ Ответов: 3

Как получить значения столбцов с помощью выходных параметров хранимой процедуры в C#

У меня есть SQL-таблица с 3 столбцами: ID (bigint), Tag1 (int), DateTime (datetime). Я создал хранимую процедуру, которая выбирает из моей таблицы значения Tag1 от DataStart до DataStop. Это прекрасно работает. Выполнение сохраненного proc возвращает, правильно, 5 значений.
Я хочу получить эти 5 значений в C#.
Я не получаю никакой ошибки, когда нажимаю кнопку, но никакое значение не отображается в MessageBox. Я не уверен, что мне следовало использовать параметр @Col в качестве вывода. Что я должен сделать, чтобы получить эти ценности?

Что я уже пробовал:

Моя хранимая процедура:
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'

Мой код 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))
                {
                    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");            
        }

3 Ответов

Рейтинг:
2

phil.o

В качестве дополнения к решению 1 вы не назначаете @col параметр любое значение. Вместо этого вы используете его в качестве имени столбца в своем запросе.
Использование хранимой процедуры с выходными параметрами - SQL Server | Microsoft Docs[^]
В какой-то момент вашей процедуры @col параметр должен находиться в левой части операции присваивания:

SET @col = SELECT ...

DrgIonuţ

Я вставил SP: SET @Col = N'Select Tag1 из [TRTF_TagLogging].[dbo].[tbl_TagLogging]', но я получаю ошибку: "необходимо объявить скалярную переменную "@Col"."
Если я объявлю его, это будет конфликт с моим параметром.

phil.o

Ты можешь попробовать SELECT @Col = Tag1 FROM ....
Вот несколько примеров: Выполнить хранимую процедуру с выходным параметром?[^]

Рейтинг:
1

OriginalGriff

Вы выбираете значения - так что не используйте ExecuteNonQuery, это запрос!
Использовать объект DataReader и dataadapter, так вместо того, чтобы с sqlcommand объект.ExecuteReader или SqlDataAdapter.Заполните, и он будет работать (за исключением того, что @Col всегда будет нулевым, потому что это выходной параметр).

Но... это очень опасная вещь, если у вас нет абсолютно жесткого контроля над тем, что передается вашему SP: если пользователь может ввести то, что ему нравится в @Col, то ваш код уязвим для SQL-инъекции ...

DrgIonuţ

Спасибо за ваше предложение, OriginalGriff! У меня есть, чтобы увидеть, как использовать объект DataReader и dataadapter, так. Пользователь не может ввести то, что ему нравится в @Col. Я хочу просто отобразить эти значения в своей WinForm.

Рейтинг:
1

Richard Deeming

  • Удалите динамический SQL, который делает ваш код уязвимым для 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
Измените свой код C# для использования ExecuteReader и считайте все значения, возвращенные из хранимой процедуры. 
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");            
}


Если ты действительно если вы хотите передать имя столбца для выбора, то вам нужно проверить его чрезвычайно тщательно. Вам нужно будет использовать процедуры sp_executesql[^] для передачи параметров в динамический 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
Затем вам нужно будет передать этот параметр из вашего кода C# : 
cmd.Parameters.Add("@Col", SqlDbType.VarChar, 30).Value = "Tag1";