How to execute stored procedure in C#?-CodePudding

I have a stored procedure with these parameters:

ALTER PROCEDURE [dbo].[RegistrTankInPack]
    @inCode NVARCHAR(50),
    @inNameTemplate NVARCHAR(50),
    @inLine_id uniqueidentifier,
    @inOperator_id uniqueidentifier,
    @inPackage_id uniqueidentifier,
    @inQuantity int -- 0 - default pack size

And return:

SELECT
    @product_id AS product_id,
    @DASIP_TANK AS DASIP_TANK,
    @DASIP_PIPE AS DASIP_PIPE,
    @SequenceNumber AS SequenceNumber,
    @FDM AS FDM,
    @model_id AS model_id,
    @Model AS Model, 
    @package_id AS package_id,
    @PackageName AS PackageName,
    @QuantityInPack AS QuantityInPack,
    @ResultStr AS ResultStr,
    @Status AS Status,
    @ErrCode AS ErrorCode,
    @RetCode AS Code,
    @LeadTime AS LeadTime

It's not a row of any table, just some values. Because of that I can't use FromSqlRaw.

I tried this code:

connection.Open();
var command = new SqlCommand($"EXEC RegistrTankInPack "  
     $"@inCode = '{productCode}', "  
     $"@inNameTemplate = 'TOG16RL123456', "  
     $"@inLine_id = '2326c2f4-ab2a-40e3-8bac-186617b10fdd', "  
     $"@inOperator_id = '{operatorID}', "  
     $"@inPackage_id = '00000000-0000-0000-0000-000000000000', "  
     $"@inQuantity = 0", connection);

command.Parameters.Add(paramCode);
command.Parameters.Add(paramTemplate);
command.Parameters.Add(paramLine);
command.Parameters.Add(paramOperator);
command.Parameters.Add(paramPackage);
command.Parameters.Add(paramQuantity);

var result = command.ExecuteReader();

for (byte i = 0; i < result.FieldCount; i  )
{
     packageHeader.Add(result.GetName(i));
     packageData.Add(result[i].ToString());
}

But I get this error (https://i.stack.imgur.com/yeuJ9.png)

Unhandled exception rendering component: Invalid attempt to read when no data is present.

CodePudding user response：

You call stored procedure like as below code:

using(SqlConnection conn = new SqlConnection("Data Source=*******;Initial Catalog=YourDatabaseName;User ID=YourUsername;Password=****"))
using (SqlCommand cmd = new SqlCommand("RegistrTankInPack", conn))
{
    SqlDataAdapter adapt = new SqlDataAdapter(cmd);
    adapt.SelectCommand.CommandType = CommandType.StoredProcedure;

    adapt.SelectCommand.Parameters.Add(new SqlParameter("@inCode", SqlDbType.NVarChar, 50));
    adapt.SelectCommand.Parameters["@inCode "].Value = inCode;

    adapt.SelectCommand.Parameters.Add(new SqlParameter("@inNameTemplate ", SqlDbType.NVarChar, 50));
    adapt.SelectCommand.Parameters["@inNameTemplate "].Value = inNameTemplate;

    adapt.SelectCommand.Parameters.Add(new SqlParameter("@inLine_id", SqlDbType.UniqueIdentifier));
    adapt.SelectCommand.Parameters["@inLine_id"].Value = inLine_id;

    adapt.SelectCommand.Parameters.Add(new SqlParameter("@inOperator_id", SqlDbType.UniqueIdentifier));
    adapt.SelectCommand.Parameters["@inOperator_id"].Value = inOperator_id;

    adapt.SelectCommand.Parameters.Add(new SqlParameter("@inPackage_id", SqlDbType.UniqueIdentifier));
    adapt.SelectCommand.Parameters["@inPackage_id"].Value = inPackage_id;

    adapt.SelectCommand.Parameters.Add(new SqlParameter("@inQuantity", SqlDbType.Int));
    adapt.SelectCommand.Parameters["@inQuantity"].Value = inQuantity;

    DataTable dt = new DataTable();
    adapt.Fill(dt);

    if (dt.Rows.Count > 0)
    {
        MessageBox.Show("Connection Succedded");
    }
    else
    {
        MessageBox.Show("Connection Fails");
    }
}

important

adapt.SelectCommand.CommandType = CommandType.StoredProcedure;