Home / Questions / 173763
Accepted
Nacho Zve De La Torre
Asked: 2020-06-18 14:10:18 +0800 CST 2020-06-18 14:10:18 +0800 CST

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

  • 772

Form1 Form2I have the following code that is executed every 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();

    }

I need the load() function to be executed when a table is opened, that is, every time the button of a table is clicked, it brings me the data of that table from my Products table in my database and inserts that data from the table inside 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 products table is made up of the following columns:

mesa, id_producto, nombre, precio, cantidad, subtotal

My dataGridViewhas the columns:

id_productos, nombre, precio, cantidad, subtotal.
c#

1 Answers

  1. Best Answer

    EDITED

    Option 1

    A way to read row by row would be as in 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 the content of the table to DataSourcethe whole: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));
    var sqlda = new SQLiteDataAdapter(cmd);
    using (dt = new DataTable())
    {
        sqlda.Fill(dt);
        dataGridView1.DataSource = dt;
    }

}
    • 1