C# List<> - Insert into database

Question

I have an app that takes information from a TextBox, inserts into a list List<> and then shows in a CheckBoxList.
Example:

List<string> quantidade = new List<string>();
for (int i = 0; i < CheckBoxListLinhas.Items.Count; i++)
{
   quantidade.Add(TextBoxQuantidade.Text.ToString());
}
List<string> artigo = new List<string>();
for (int i = 0; i < CheckBoxListLinhas.Items.Count; i++)
{
   artigo.Add(TextBoxArtigo.Text.ToString());
}
List<string> valor = new List<string>();
for (int i = 0; i < CheckBoxListLinhas.Items.Count; i++)
{
   valor.Add(TextBoxValor.Text.ToString());
}
/*List<string> artigo = new List<string>();
  for (int i = 0; i < CheckBoxListLinhas.Items.Count; i++)
  {
    artigo.Add(TextBoxArtigo.Text.ToString());
  }*/

CheckBoxListLinhas.Items.Add(new ListItem("Quantidade: " + TextBoxQuantidade.Text + " Artigo: " + TextBoxArtigo.Text + " Valor: " + TextBoxValor.Text) + " Anexo: " + FileUpload1.FileName.ToString());

And I want to add to the database all the rows inserted in List<> and I already have this example:

SqlCommand sqlInsertList = new SqlCommand("Insert into linhas (quantidade,descricao,valor) VALUES(@quantidade,@descricao,@valor)", sqlConn);
sqlInsertList.Parameters.AddWithValue("@quantidade", );
sqlInsertList.Parameters.AddWithValue("@descricao", TextBoxArtigo.Text);
sqlInsertList.Parameters.AddWithValue("@valor", float.Parse(TextBoxValor.Text, CultureInfo.InvariantCulture.NumberFormat));

sqlConn.Open();                         
sqlTran = sqlConn.BeginTransaction();
sqlInsert.Transaction = sqlTran;        
sqlInsert.ExecuteNonQuery();             
sqlTran.Commit();

What is the easiest way to do this and adapt the SQLInsert?

Answer 1

From what I see, the problem here is that three lists are being created to operate, instead of using only one, facilitating abstraction of the idea of Row. First you would have to have a class that represents this kind of data.

public class Linha
{
    public int Quantidade { get; set; }
    public string Descricao { get; set; }
    public float Valor { get; set }
}

Using a concrete class you no longer need to perform three loopings, one for each entity property Row.

IList<Linha> linhas = new List<Linha>();

for (int i = 0; i < CheckBoxListLinhas.Items.Count; i++)
{
    linhas.Add(new Linha
    {
        Quantidade = int.Parse(TextBoxQuantidade.Text.ToString(), CultureInfo.InvariantCulture.NumberFormat),
        Descricao = TextBoxArtigo.Text.ToString(),
        Valor = float.Parse(TextBoxValor.Text, CultureInfo.InvariantCulture.NumberFormat)
    });
}

So to insert all the lines the process becomes much simpler, with only one foreach.

foreach (Linha linha in linhas)
    this.InserirLinha(linha);
}

Consider that Little is your current method that serves to treat only one unit. Modify your code in this method so that it works as follows:

SqlCommand sqlInsert = new SqlCommand("Insert into linhas (quantidade,descricao,valor) VALUES(@quantidade,@descricao,@valor)", sqlConn);

sqlInsert.Parameters.AddWithValue("@quantidade", linha.Quantidade);
sqlInsert.Parameters.AddWithValue("@descricao", linha.Descricao);
sqlInsert.Parameters.AddWithValue("@valor", linha.Valor);

sqlConn.Open();                         
sqlTran = sqlConn.BeginTransaction();
sqlInsert.Transaction = sqlTran;        
sqlInsert.ExecuteNonQuery();             
sqlTran.Commit();

REMARKS:

• Despite being looped, you’re always accessing the same three fields. I don’t know if that’s the behavior you really wanted. If there are multiple lines or you want to duplicate this information.
• I used the type float to the property Valor, but if it is to deal with a monetary value the most appropriate type for such operations is the decimal.
• I don’t know how your whole method is in inserting a Row, but in the example that was passed the sqlConn is not being closed. Always close connections to bank-related operations (IO, stream, etc.) when you complete your action. If you don’t want to use the method Close-up, usually these classes implement the interface Idisposable, allowing you in this way to Instruction Using, who already does this job for you.