Insert all data of a datagridview to database at once

Question

I have a datagridview which is created by various action and user's manipulation of data. I want to insert all the data of the gridview to the database at once, I know I could try a code similar to this:

for(int i=0; i< dataGridView1.Rows.Count;i++)
    {
        string StrQuery= @"INSERT INTO tableName VALUES (" + dataGridView1.Rows[i].Cells["ColumnName"].Value +", " + dataGridView1.Rows[i].Cells["ColumnName"].Value +");";

      try
      {
        using (SqlConnection conn = new SqlConnection(ConnString))
        {
            using (SqlCommand comm = new SqlCommand(StrQuery, conn))
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
        }
      }

But will it be fair to create a new connection every time a record is inserted? The datagrid may contain many rows... Is there any way to take off all the data to the server at once and loop inside in sql to insert all the data?

score 20 · Accepted Answer · edited May 23 '17 at 12:02

If you move your for loop, you won't have to make multiple connections. Just a quick edit to your code block (by no means completely correct):

string StrQuery;
try
{
    using (SqlConnection conn = new SqlConnection(ConnString))
    {
        using (SqlCommand comm = new SqlCommand())
        {
            comm.Connection = conn;
            conn.Open();
            for(int i=0; i< dataGridView1.Rows.Count;i++)
            {
                StrQuery= @"INSERT INTO tableName VALUES (" 
                    + dataGridView1.Rows[i].Cells["ColumnName"].Text+", " 
                    + dataGridView1.Rows[i].Cells["ColumnName"].Text+");";
                comm.CommandText = StrQuery;
                comm.ExecuteNonQuery();
            }
        }
    }
}

As to executing multiple SQL commands at once, please look at this link: Multiple statements in single SqlCommand

what if i have Datagrid instead of DataGridView ".Cells is not avaialbe" — Shubham Sahu, Aug 16 '18 at 12:03

score 4 · Answer 2 · answered Aug 01 '15 at 17:09

I think the best way is by using TableAdapters rather than using Commands objects, its Update method sends all changes mades (Updates,Inserts and Deletes) inside a Dataset or DataTable straight TO the database. Usually when using a DataGridView you bind to a BindingSource which lets you interact with a DataSource such as Datatables or Datasets.

If you work like this, then on your bounded DataGridView you can just do:

this.customersBindingSource.EndEdit();
this.myTableAdapter.Update(this.myDataSet.Customers);

The 'customersBindingSource' is the DataSource of the DataGridView.

The adapter's Update method will update a single data table and execute the correct command (INSERT, UPDATE, or DELETE) based on the RowState of each data row in the table.

From: https://msdn.microsoft.com/en-us/library/ms171933.aspx

So any changes made inside the DatagridView will be reflected on the Database when using the Update method.

More about TableAdapters: https://msdn.microsoft.com/en-us/library/bz9tthwx.aspx

This is the better answer because it leverages the native features of table adapters — VA systems engineer, Jun 22 '19 at 10:02
What if the user search for a specific ID, so it displays only 1 row of record in the datagridview. Then he made changes to that record and update using this.myTableAdapter.Update(). Will it update the specific record in the database or replace the database to only store that 1 row of record? — lance2k, Jun 01 '21 at 18:59

score 1 · Answer 3 · answered Nov 03 '16 at 06:01

Please see if below can help you

Class Post_Sales

Public Shared Sub Post_sales()

    Dim ITM_ID As Integer

    Dim SLS_QTY As Integer

    Dim SLS_PRC As Double

    Dim SLS_AMT As Double

    Dim DSPL_RCT As String

    Dim TAX_CODE As Integer


    'Format the current  date and send it to a textbox
    Form1.TextBox6.Text = System.DateTime.Now.ToString((" yyyy-MM-dd"))

    'Open Connection

    Dim con As New SqlConnection("Initial Catalog=Your Database here;Data source=.;Network Library=DBMSSOCN;User ID=sa;Password=")

    con.Open()

    'Insert Records into the database


    For Each rw As DataGridViewRow In Form1.DataGridView1.Rows

        ITM_ID = rw.Cells("Column1").Value
        DSPL_RCT = rw.Cells("Column2").Value
        SLS_QTY = rw.Cells("Column3").Value
        SLS_PRC = rw.Cells("Column4").Value
        SLS_AMT = rw.Cells("Column5").Value
        TAX_CODE = rw.Cells("Column6").Value

        Dim cmd As New SqlCommand("INSERT INTO DAY_PLUSALES (DT,ITM_ID,DSPL_RCT,SLS_QTY,SLS_PRC,SLS_AMT,TAX_CODE) values ('" & Form1.TextBox6.Text & "','" & ITM_ID & "','" & DSPL_RCT & "','" & SLS_QTY & "','" & SLS_PRC & "','" & SLS_AMT & "','" & TAX_CODE & "')", con)

        cmd.ExecuteNonQuery()

    Next

    con.Close()

    MessageBox.Show("Records Added to the SQL Database successfully!", "Records Updated ")

End Sub

End Class

However the code above is inserting a blank row in the SQL database table please assist — P. nganga, Nov 03 '16 at 07:01

score 1 · Answer 4 · answered Oct 21 '20 at 11:16

Try this 100% Working Code

string SQL = "", tableName = "tableName";
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    SQL = @"INSERT INTO " + tableName + " VALUES (";
    for (int col = 0; col < dataGridView1.ColumnCount; col++)
    {
        string data = "";
        if (dataGridView1.Rows[i].Cells[col].Value != null)
        {
            data = dataGridView1.Rows[i].Cells[col].Value.ToString();
        }
        SQL += "'" + data.Trim() + "'";
        if (col < dataGridView1.ColumnCount - 1)
        {
            SQL += ",";
        }
    }
    SQL += ")";
    string finalSQL = SQL;
    //INSERT to DB the finalSQL
}

Your Data is ready now Insert the finalSQL in your database with your connection

InspiredBy · Answer 5 · 2012-05-11T18:26:23.507

You can do the same thing with the connection opened just once. Something like this.

for(int i=0; i< dataGridView1.Rows.Count;i++)
  {
    string StrQuery= @"INSERT INTO tableName VALUES (" + dataGridView1.Rows[i].Cells["ColumnName"].Value +", " + dataGridView1.Rows[i].Cells["ColumnName"].Value +");";

  try
  {
      SqlConnection conn = new SqlConnection();
      conn.Open();

          using (SqlCommand comm = new SqlCommand(StrQuery, conn))
          {
              comm.ExecuteNonQuery();
          }
      conn.Close();

  }

Also, depending on your specific scenario you may want to look into binding the grid to the database. That would reduce the amount of manual work greatly: http://www.switchonthecode.com/tutorials/csharp-tutorial-binding-a-datagridview-to-a-database

score 0 · Answer 6 · edited Apr 24 '13 at 07:58

0

You have a syntax error Please try the following syntax as given below:

string StrQuery="INSERT INTO tableName VALUES ('" + dataGridView1.Rows[i].Cells[0].Value + "',' " + dataGridView1.Rows[i].Cells[1].Value + "', '" + dataGridView1.Rows[i].Cells[2].Value + "', '" + dataGridView1.Rows[i].Cells[3].Value + "',' " + dataGridView1.Rows[i].Cells[4].Value + "')";

edited Apr 24 '13 at 07:58

Ankur

5,086
19
37
62

answered Apr 24 '13 at 07:37

Suresh

23
4

score 0 · Answer 7 · edited Nov 03 '21 at 09:06

try
{
    string barcode = "", name = "";
    int qty = 0, unitprice = 0;
    for (int i = 0; i < dataGridView1.Rows.Count; i++)
    {
       barcode= dataGridView1.Rows[i].Cells["barcode"].Value.ToString();
        name = dataGridView1.Rows[i].Cells["name"].Value.ToString();
        qty = Int32.TryParse(dataGridView1.Rows[i].Cells["stkqty"].Value.ToString(),out qty)?qty:0;
        unitprice = Int32.TryParse(dataGridView1.Rows[i].Cells["unitprice"].Value.ToString(),out unitprice)?unitprice:0;
        SqlConnection conn = new SqlConnection(urls);
        conn.Open();
        String insertquery = "INSERT INTO Stock_Sale_Record(invoiceno,barcode,saleqty,proname,saleprice,duedate) VALUES (@invoiceno,@barcode,@saleqty,@proname,@saleprice,@duedate)";
        SqlCommand insertcommand = new SqlCommand(insertquery, conn);
        insertcommand.Parameters.AddWithValue("@invoiceno", invoicenolabel.Text.ToString());
        insertcommand.Parameters.AddWithValue("@barcode", barcode);
        insertcommand.Parameters.AddWithValue("@saleqty", qty);
        insertcommand.Parameters.AddWithValue("@proname", name);
        insertcommand.Parameters.AddWithValue("@saleprice", unitprice);
        insertcommand.Parameters.AddWithValue("@duedate", duedatetxt.Value.Date);
        insertcommand.ExecuteNonQuery();
        conn.Close();
    }
}
catch (Exception ex)
{
    MessageBox.Show(""+ex.Message);
}

Welcome to StackOverflow. I would suggest to move `conn.Close()` into a `finally` block to make sure that the connection is closed even if there were some exception. — Peter Csala, Nov 03 '21 at 09:07

score -2 · Answer 8 · edited Dec 27 '12 at 11:47

for (int i = 0; i < dataGridView2.Rows.Count; i++)
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=ID_Proof;Integrated Security=True");
                        SqlCommand cmd = new SqlCommand("INSERT INTO Restaurant (Customer_Name,Quantity,Price,Category,Subcategory,Item,Room_No,Tax,Service_Charge,Service_Tax,Order_Time) values (@customer,@quantity,@price,@category,@subcategory,@item,@roomno,@tax,@servicecharge,@sertax,@ordertime)", con);
                        cmd.Parameters.AddWithValue("@customer",dataGridView2.Rows[i].Cells[0].Value);
                        cmd.Parameters.AddWithValue("@quantity",dataGridView2.Rows[i].Cells[1].Value);
                        cmd.Parameters.AddWithValue("@price",dataGridView2.Rows[i].Cells[2].Value);
                        cmd.Parameters.AddWithValue("@category",dataGridView2.Rows[i].Cells[3].Value);
                        cmd.Parameters.AddWithValue("@subcategory",dataGridView2.Rows[i].Cells[4].Value);
                        cmd.Parameters.AddWithValue("@item",dataGridView2.Rows[i].Cells[5].Value);
                        cmd.Parameters.AddWithValue("@roomno",dataGridView2.Rows[i].Cells[6].Value);
                        cmd.Parameters.AddWithValue("@tax",dataGridView2.Rows[i].Cells[7].Value);
                        cmd.Parameters.AddWithValue("@servicecharge",dataGridView2.Rows[i].Cells[8].Value);
                        cmd.Parameters.AddWithValue("@sertax",dataGridView2.Rows[i].Cells[9].Value);
                        cmd.Parameters.AddWithValue("@ordertime",dataGridView2.Rows[i].Cells[10].Value);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                        MessageBox.Show("Added successfully!");

This code still creates a new database connection for each insert. — alroc, Dec 27 '12 at 11:59

Insert all data of a datagridview to database at once

8 Answers8

Linked