How can I insert data from SQLite to my DataGridView? WindowsForms

Question

How can I insert data from SQLite to my DataGridView? WindowsForms

Navigation

#1 by (1 votes)

1

I have the next code that is executed each time the ADD ITEM button is clicked and that creates a new row with the data entered in the textBox above in the DataGridView below.

    // AGREGA UN PRODUCTO
    private void btnAgregarItem_Click(object sender, EventArgs e)
    {
            Pagos.Rows.Add(txtID.Text, txtNombre.Text, txtPrecio.Text, txtCantidad.Text, etiquetaSubTotalNum.Text);

            string mesa = this.numMesa;
            int id_producto = Convert.ToInt32(txtID.Text.ToString());
            string nombre = txtNombre.Text.ToString();
            double precio = Convert.ToDouble(txtPrecio.Text.ToString());
            double cantidad = Convert.ToDouble(txtCantidad.Text.ToString());
            double subtotal = Convert.ToDouble(etiquetaSubTotalNum.Text.ToString());

            String consulta = "INSERT INTO Productos (mesa, id_producto, nombre, precio, cantidad, subtotal) VALUES (@mesa, @id_producto, @nombre, @precio, @cantidad, @subtotal)";
            SQLiteCommand cmd = new SQLiteCommand(consulta, conexion);

            cmd.Parameters.Clear();
            cmd.Parameters.Add(new SQLiteParameter("@mesa", mesa));
            cmd.Parameters.Add(new SQLiteParameter("@id_producto", id_producto));
            cmd.Parameters.Add(new SQLiteParameter("@nombre", nombre));
            cmd.Parameters.Add(new SQLiteParameter("@precio", precio));
            cmd.Parameters.Add(new SQLiteParameter("@cantidad", cantidad));
            cmd.Parameters.Add(new SQLiteParameter("@subtotal", subtotal));
            cmd.ExecuteNonQuery();

    }

The function load () I need to run when a table is opened, so every time you click on the button of a table I bring the data from that table from my table Products in my database and I Insert that data from the table into the columns of my dataGridView:

  public void cargar()
    {
        string mesa = this.numMesa;
        string consulta = "select * from Productos where mesa = @mesa";
        SQLiteCommand cmd = new SQLiteCommand(consulta, conexion);
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new SQLiteParameter("@mesa", mesa));
        using (SQLiteDataReader read = cmd.ExecuteReader())
        {
            while (read.Read())
            {
                Pagos.Rows.Add(new object[] {
    read.GetValue(0),
    //read.GetValue(read.GetOrdinal("mesa")),
    read.GetValue(read.GetOrdinal("id_producto")),
    read.GetValue(read.GetOrdinal("nombre")),
    read.GetValue(read.GetOrdinal("precio")),
    read.GetValue(read.GetOrdinal("cantidad")),
    read.GetValue(read.GetOrdinal("subtotal"))
    });
            }
        }

    }

My product table is composed of the following columns:

mesa, id_producto, nombre, precio, cantidad, subtotal

My dataGridView has the columns:

id_productos, nombre, precio, cantidad, subtotal.

c# windows-forms sqlite gui

asked by Nacho Zve De La Torre 18.06.2018 в 00:10

source

1 answer

ReactJS - Hide other Popovers when a Popover opens How to use TGpsStatus.SatelliteCount?

score 1 · Accepted Answer

EDITED

Option 1

A way to read row by row would be like this example:

public void cargar()
{
    string mesa = this.numMesa;
    string consulta = "select * from Productos where mesa = @mesa";
    SQLiteCommand cmd = new SQLiteCommand(consulta, conexion);
    cmd.Parameters.Clear();
    cmd.Parameters.Add(new SQLiteParameter("@mesa", mesa));
    using (SQLiteDataReader read = cmd.ExecuteReader())
    {
      while (read.Read())
      {
        dataGridView1.Rows.Add(new object[] { 
        read.GetValue(0),  
        read.GetValue(read.GetOrdinal("mesa")),  
        read.GetValue(read.GetOrdinal("id_producto")),
        read.GetValue(read.GetOrdinal("nombre")),
        read.GetValue(read.GetOrdinal("precio")),
        read.GetValue(read.GetOrdinal("cantidad")),
        read.GetValue(read.GetOrdinal("subtotal"))  
        });
      }
   }

}

If you want to put some condition you could control it in the constructor.

Option 2

On the other hand, the most logical thing is to add to the DataSource of datagridview all the contents of the table:

public void cargar()
{
    string mesa = this.numMesa;
    string consulta = "select * from Productos where mesa = @mesa";
    SQLiteCommand cmd = new SQLiteCommand(consulta, conexion);
    cmd.Parameters.Clear();
    cmd.Parameters.Add(new SQLiteParameter("@mesa", mesa));
    var sqlda = new SQLiteDataAdapter(cmd);
    using (dt = new DataTable())
    {
        sqlda.Fill(dt);
        dataGridView1.DataSource = dt;
    }

}