how to insert only selected rows data from datagridview c# to sql-CodePudding

i have problem with my code where i try to insert selected rows data from a datagridview to sql database, but it only works for one row, even im using a loop for every selected row, i get the violation of primary key of one inserted row of those multiple rows and then it stops and cancel the rest, even that these rows has not the same primary key

its like the loop goes multiple times on the same row i'm not really sure whats happening im really stuck!

what i want is when i select multiple rows in datagridview and clicking a bouton these line are saved(i hope i get only a small edit and not the whole code changes)

foreach (DataGridViewRow row in dataGridView1.SelectedRows)
        {
                //i tried all these
                //int index = dataGridView1.CurrentRow.Index
                //int index = datagridview.CurrentCell.RowIndex
                int index = dataGridView1.SelectedRows[0].Index;
                d.cmd.CommandText = "insert into projection values('"  
                            d.dt.Rows[index][0].ToString()  
                    "','"   d.dt.Rows[index][1].ToString()  
                    "','"   d.dt.Rows[index][2].ToString()  
                    "','"   d.dt.Rows[index][3].ToString()  
                    "','"   d.dt.Rows[index][4].ToString()  
                    "','"   d.dt.Rows[index][5].ToString()  
                    "','"   d.dt.Rows[index][6].ToString()  
                    "')";
                d.cmd.Connection = d.con;
                d.cmd.ExecuteNonQuery();
        }

CodePudding user response：

For starters, you need to use parameters. Second, you are doing a foreach on each selected row already. Access the data in each cell with: row.Cells[columnNumber].Value

CodePudding user response：

First off, setup the DataGridView with a DataSource, like a DataTable. Then perhaps in a Button Click event get the selected rows.

private void InsertButton_Click(object sender, EventArgs e)
{
    if (dataGridView1.SelectedRows.Count == 0) return;

    List<DataRow> selectedRows = dataGridView1
        .SelectedRows
        .Cast<DataGridViewRow>()
        .Select(dgvr => ((DataRowView)dgvr.DataBoundItem).Row)
        .ToList();

    var (success, exception) = Operations.Insert(selectedRows);
    if (success)
    {
        // rows inserted
    }
    else
    {
        // exception has info for failure
    }

}

Place your data operations in a class. Here two columns are used. You would add a column for each column in the DataGridView. And you need to change the data provider if not using SQL-Server.

public class Operations
{

    private static string _connectionString = "TODO";

    public static (bool success, Exception exception) Insert(List<DataRow> rows)
    {
        using (var cn = new SqlConnection() { ConnectionString = _connectionString })
        {
            using (var cmd = new SqlCommand() { Connection = cn })
            {
                cmd.CommandText = "INSERT INTO Customers VALUES (@CompanyName, @ContactId)";
                cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar);
                cmd.Parameters.Add("@ContactId", SqlDbType.Int);

                try
                {
                    cn.Open();

                    foreach (var row in rows)
                    {
                        cmd.Parameters["@CompanyName"].Value = row.Field<string>("CompanyName");
                        cmd.Parameters["@ContactId"].Value = row.Field<int>("ContactId");
                        cmd.ExecuteNonQuery();
                    }

                    return (true, null);
                }
                catch (Exception localException)
                {
                    return (false, localException);
                }
            }
        }
    }
}