如何在"SqlCommand"句子中包含参数并在设置其值后？

Question

我有下一个SQL Server命令工作：(它将数据插入表中(

try
{
// Open conn
conexion.Open();
// New trans
SqlTransaction transaccion = conexion.BeginTransaction();
// Command
SqlCommand comandoAEjecutar = new SqlCommand
{
Connection = conexion,
Transaction = transaccion,
CommandText = @"
INSERT INTO [dbo].[table_battery]
([capacity], [description], [image], [price]) 
VALUES 
(@capacity, @description, @fileContent, @price)
"
};
int capacity = 50;
string descr = "Funciona2";
float price = 70;
string path = @"C:UsersJuanDesktopIngeniería Informática2 año2º CuatrimestreProgramación Visual AvanzadaProyectoFinalAJMobileAJMobilesrcimagesBatterybaterry_4000.png";
byte[] fileContent = File.ReadAllBytes(path);
comandoAEjecutar.Parameters.Add("@capacity", SqlDbType.Int).Value = capacity;
comandoAEjecutar.Parameters.Add("@description", SqlDbType.VarChar).Value = descr;
comandoAEjecutar.Parameters.Add("@fileContent", SqlDbType.VarBinary).Value = fileContent;
comandoAEjecutar.Parameters.Add("@price", SqlDbType.Float).Value = price;
int numeroFilasAfectadas = comandoAEjecutar.ExecuteNonQuery();
}

如您所见，我在语句中添加了参数及其值SqlCommand。我想在此语句中包含参数，并在设置其值后，例如：

try
{
// Open conn
conexion.Open();
// New trans
SqlTransaction transaccion = conexion.BeginTransaction();
// Command
SqlCommand comandoAEjecutar = new SqlCommand
{
Connection = conexion,
Transaction = transaccion,
CommandText = @"
INSERT INTO [dbo].[table_battery]
([capacity], [description], [image], [price]) 
VALUES 
(@capacity, @description, @fileContent, @price)
",
Parameters =
{
// I wanna set their types too
"@capacity" as SqlDbType.Int,
"@description" as SqlDbType.VarChar,
"@fileContent" as SqlDbType.VarBinary,
"@price" as SqlDbType.Float
}
};

int capacity = 50;
string descr = "Funciona2";
float price = 70;
string path = @"C:UsersJuanDesktopIngeniería Informática2 año2º CuatrimestreProgramación Visual AvanzadaProyectoFinalAJMobileAJMobilesrcimagesBatterybaterry_4000.png";
byte[] fileContent = File.ReadAllBytes(path);
// Add values to parameters
comandoAEjecutar.Parameters["@capacity"].Value = capacity;
comandoAEjecutar.Parameters["@description"].Value = descr;
comandoAEjecutar.Parameters["@fileContent"].Value = fileContent;
comandoAEjecutar.Parameters["@price"].Value = price;
int numeroFilasAfectadas = comandoAEjecutar.ExecuteNonQuery();
}

无论如何都要这样做吗？我已经尝试了很多方法，但我无法实现。

谢谢。

Answer 1

您正在尝试将对象添加到集合中，因此您需要适当的集合初始化器模式。

Parameters =
{
new SqlParameter {ParameterName = "@capacity", DbType = DbType.Int32, Value = capacity},
new SqlParameter {ParameterName = "@description", DbType = DbType.String, Value = descr},
new SqlParameter {ParameterName = "@fileContent", DbType = DbType.Binary, Value = fileContent},
new SqlParameter {ParameterName = "@price", DbType = DbType.Decimal, Value = price}
}

这将确保 Command 对象按照您稍后的预期添加了参数