Update sql database from datagridview in c#

Question

I'm new here but I need some help. I need to update a SQL Server database from C# with Windows Forms, but I'm having problems. I looked it up but still can't find the right answer. I need to do  insert and update by pressing a button for changing or filling the database from the datagridview. I've created a separate function for both I am using this code;
private void InsertPositionen()
{
    string qry = "";

SqlCommand insert = new SqlCommand(qry, con);

try
    {
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            qry = "INSERT INTO BelegePositionen (BelID, BelPosId, Artikelnummer, Menge, Preis) VALUES( " + dataGridView1.Rows[i].Cells["BelID"] + ", " 
                   + dataGridView1.Rows[i].Cells["BelPosId"] + ", " 
                   + dataGridView1.Rows[i].Cells["Artikelnummer"] + ", " 
                   + dataGridView1.Rows[i].Cells["Menge"] + ", " 
                   + dataGridView1.Rows[i].Cells["Preis"];
        }

insert.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

private void UpdatePositionen()
{
        string updt = "";

SqlCommand update = new SqlCommand(updt, con);

try
        {
            for (int i = 0; i < dataGridView1.Rows.Count -1; i++)
            {
                updt = "UPDATE BelegePositionen SET BelID =  "
                    + dataGridView1.Rows[i].Cells["BelID"] +
                    ", BelPosID = "
                    + dataGridView1.Rows[i].Cells["BelPosID"] +
                    ", Atrikelnummer = "
                    + dataGridView1.Rows[i].Cells["Artikelnummer"] +
                    ", Menge = "
                    + dataGridView1.Rows[i].Cells["Menge"] +
                    ", Preis = " 
                    + dataGridView1.Rows[i].Cells["Preis"];
            }

update.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Done!");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
}

CobyC · Answer

Your Question is quite vague as you state you are having problems, but not quite sure what problems you are having. It will help if you can describe what problems you are having.
In addition to what @marc_c said about sql injection, I can't see how you manage your connection to the database.
From the code it looks like you could run into a situation where you are leaving connection strings open, or not opening them at all.
using the using(...) {  } will close the connections when you are done with it.
private void InsertPositionen()
{
    //using the using statement you will insure that the connection is closed and resources released
    using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.db))
    {
        string cmd = "INSERT INTO BelegePositionen (BelID, BelPosId, Artikelnummer, Menge, Preis) " +
         "VALUES(@BelId, @BelPosId, @ArtNr, @Menge, @Preis);";

//using the using statement will ensure any reasources are released when exiting the code block
        using (SqlCommand insert = new SqlCommand(cmd, connection))
        {
            // define the parameters
            insert.Parameters.Add("@BelId", SqlDbType.Int);
            insert.Parameters.Add("@BelPosId", SqlDbType.Int);
            insert.Parameters.Add("@ArtNr", SqlDbType.Int);  // maybe this is a string? 
            insert.Parameters.Add("@Menge", SqlDbType.Int);
            insert.Parameters.Add("@Preis", SqlDbType.Decimal, 20, "4");

try
            {
                //open the connection
                insert.Connection.Open();

// in the loop, only *set* the parameter's values
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    insert.Parameters["@BelId"].Value = dataGridView1.Rows[i].Cells["BelID"];
                    insert.Parameters["@BelPosId"].Value = dataGridView1.Rows[i].Cells["BelPosId"];
                    insert.Parameters["@ArtNr"].Value = dataGridView1.Rows[i].Cells["Artikelnummer"];
                    insert.Parameters["@Menge"].Value = dataGridView1.Rows[i].Cells["Menge"];
                    insert.Parameters["@Preis"].Value = dataGridView1.Rows[i].Cells["Preis"];

insert.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                MessageBox.Show("Done!");
            }
        }
    }
}

marc_s · Answer

You should really NOT do your SQL stuff like this!! This leaves your code wide open for SQL injection vulnerabilities! Stop that - right now!
Instead - use parametrized queries - like this:
private void InsertPositionen()
{
    string qry = "INSERT INTO BelegePositionen (BelID, BelPosId, Artikelnummer, Menge, Preis) " +  
                 "VALUES(@BelId, @BelPosId, @ArtNr, @Menge, @Preis);";
 
    SqlCommand insert = new SqlCommand(qry, con);
    
    // define the parameters
    insert.Parameters.Add("@BelId", SqlDbType.Int);
    insert.Parameters.Add("@BelPosId", SqlDbType.Int);
    insert.Parameters.Add("@ArtNr", SqlDbType.Int);  // maybe this is a string? 
    insert.Parameters.Add("@Menge", SqlDbType.Int);
    insert.Parameters.Add("@Preis", SqlDbType.Decimal, 20, 4);

try
    {
        // in the loop, only *set* the parameter's values
        
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            insert.Parameters["@BelId"].Value = 1;
            insert.Parameters["@BelPosId"].Value = 2;
            insert.Parameters["@ArtNr"].Value = 3;
            insert.Parameters["@Menge"].Value = 4;
            insert.Parameters["@Preis"].Value = 99.95;

insert.ExecuteNonQuery();
        }   
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Update sql database from datagridview in c#

2 Answers

Add your own answers!

Ask a Question