Insert datagridview data into database

Question

I have a form with a blank datagridview. What's the most effective way to get the mulptiple lines of data from the datagridview saved into the database? I've searched for tutorials and have been recommended SqlBulkCopy and a few other solutions. The aim is to use it to create invoices, so the names of the tables would need to be unique. I'm using SqlCe and C# 2010.

Answer 1

I refer to each cell specifically (using index), paramaterize that value and use it in an INSERT statement.

See here:

SqlCommand cmd = new SqlCommand();
 //Basic SQL INSERT statement
cmd.CommandText += " INSERT INTO ELECT_SALARY_DFRL (ELECT_XREF_SEQ_ID, EMPL_SALARY_DFRL_AMT, EMPL_SALARY_DFRL_FREQ_CD, ";
cmd.CommandText += "  FUND_SEQ_ID, ELECT_SALARY_DFRL_FUND_PCT_RT, ELECT_SALARY_DFRL_ELECT_DT, BEG_DT, END_DT)";
cmd.CommandText += " VALUES (@SeqID, @DefAmt, @Freq, @FundID, @FundPct, @Today, @begDt, @EndDT); ";

//Parameters for sql insert, from DGV.
cmd.Parameters.Add("@DefAmt", SqlDbType.VarChar);
cmd.Parameters.Add("@Freq", SqlDbType.VarChar);
cmd.Parameters.Add("@FundID", SqlDbType.Int);
cmd.Parameters.Add("@SeqID", SqlDbType.Int);
cmd.Parameters.Add("@FundPct", SqlDbType.VarChar);
cmd.Parameters.AddWithValue("@Today", System.DateTime.Now());
cmd.Parameters.AddWithValue("@begDt", "19000101");
cmd.Parameters.AddWithValue("@EndDT", "24000101");

conn.Open();
cmd.Connection = conn;


try {
         //Loop through entire DataGridView
    for (int i = 0; i <= SalaryDGV.Rows.Count - 1; i++) {
        //If row is empty, exit For Next block.
        if (BonusDGV.Rows(i).Cells(0).Value == null) {
            break;
        }
        //Adds value to parameters from data grid view.
        cmd.Parameters("@DefAmt").Value = SalaryDGV.Rows(i).Cells(0).Value;
        cmd.Parameters("@Freq").Value = SalaryDGV.Rows(i).Cells(1).Value;
        cmd.Parameters("@FundID").Value = Convert.ToInt32(SalaryDGV.Rows(i).Cells(2).Value);
        cmd.Parameters("@SeqID").Value = SalaryDGV.Rows(i).Cells(3).Value;
        cmd.Parameters("@FundPct").Value = SalaryDGV.Rows(i).Cells(4).Value;
        cmd.ExecuteNonQuery();
    }
} catch (Exception ex) {
    MessageBox.Show("Error adding the Salary Deferral " + ex.Message);
}

Note: I used a converter so the code may not have translated the best.

Also note, I do not use this to insert 100's or thousands of rows of data, only 3-4 at a time. If there is a more efficient approach, I am not sure of it, however you asked me to post how I would do it.