Reputation: 4261
Normally I use SQL parameters to avoid injection SQL by the hacker. But how can I use SQL parameters in my loop :
try
{
using (var connectionWrapper = new Connexion())
{
var connectedConnection = connectionWrapper.GetConnected();
string sSql = "";
foreach (var oItem in LeListVoit_End)
{
//insert into Lettrvoit
if (sSql != "") sSql += " UNION ALL ";
sSql += "SELECT '" + oItem.IdLettre + "', '" + oItem.Date_Cloture + "', '" + oItem.CodeDest + "', '" + oItem.ModalMode + "', '" + oItem.LibPort + "', '" + oItem.LibExpr + "', '" + oItem.LibUnite + "', '" + oItem.EnlvUnite + "', '" + oItem.NbrColis + "', '" + oItem.Poids.ToString().Replace(',', '.') + "', '" + oItem.LeCR.ToString().Replace(',', '.') + "', '" + oItem.LeVD.ToString().Replace(',', '.') + "', '" + oItem.CodeClient + "', '"
+ oItem.RsNom_Exp + "', '" + oItem.Addr_Exp + "', '" + oItem.CP_Exp + "', '" + oItem.Ville_Exp + "', '" + oItem.Tel_Exp + "', '" + oItem.Fax_Exp + "', '"
+ oItem.RsNom_Dest + "', '" + oItem.Addr_Dest + "', '" + oItem.CP_Dest + "', '" + oItem.CP_Dest + "', '" + oItem.Tel_Dest + "', '" + oItem.Fax_Dest + "', '" + oItem.InseeDest + "', '"
+ Is_Print + "', '" + CHAUFFEUR + "'";
}
string sqlComm_Insert = "INSERT INTO LETTRE_VOIT_FINAL ([NOID], [DATE_CLOTURE], [CODE_DEST] ,[MODAL_MODE], [LIBELLE_PORT] ,[LIBELLE_EXPR], [LIBELLE_UNITE],ENLEV_UNITE, [NBR_COLIS], [POID], [ENLEV_CREMB], [ENLEV_DECL], CODE_CLIENT, [RS_NOM_EXP] ,[ADDR_EXP] ,[CP_EXP] ,[VILLE_EXP] ,[TEL_EXP] ,[FAX_EXP],[RS_NOM_DEST] ,[ADDR_DEST] ,[CP_DEST] ,[VILLE_DEST] ,[TEL_DEST] ,[FAX_DEST],INSEE_DEST, IS_PRINT, CHAUFFEUR) " + sSql;
SqlCommand comm_Insert = new SqlCommand(sqlComm_Insert, connectionWrapper.conn);
comm_Insert.ExecuteScalar();
}
}
catch (Exception excThrown)
{
throw new Exception("Err", excThrown);
}
as you can see above the SQL parameters is outside my loop.
Upvotes: 0
Views: 1937
Reputation: 6152
You can create the connection, command and parameters outside of the loop and then just set the parameter values and execute the command within the loop:
comm_Insert.Parameters[""].Value = "";
comm_Insert.ExecuteScalar();
Upvotes: 0
Reputation: 161012
Since SQL parameter names have to be unique you could use a for loop instead and append the index of the loop variable in each iteration to the SQL parameter name, i.e.:
for(int i=0; i< LeListVoit_End.Length; i++)
{
string sql = string.Format("select foo from bar where baz = @FOO{0}" i);
command.Parameters.Add(string.Format("@FOO{0}",i), SqlDbType.VarChar, 80).Value = "someValue";
}
Upvotes: 2
Reputation: 13335
Can you write a stored procedure instead of building a statement string? Either way I would execute a command per item in the list, and execute all commands in a single transaction.
Add parameter to the command using the Parameters collection:
command.Parameters.Add({several overloads})
Upvotes: 0