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 lt  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

User · Answer

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

User · Answer

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

User · Answer

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 lt  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

User · Answer

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

User · Answer

for  int i   0  i  lt  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

User · Answer

Try this 100  Working Code string SQL    quot  quot   tableName    quot tableName quot   for  int i   0  i  lt  dataGridView1 Rows Count  i          SQL     quot INSERT INTO  quot    tableName    quot  VALUES   quot       for  int col   0  col  lt  dataGridView1 ColumnCount  col                  string data    quot  quot           if  dataGridView1 Rows i  Cells col  Value    null                        data   dataGridView1 Rows i  Cells col  Value ToString                      SQL     quot   quot    data Trim      quot   quot           if  col  lt  dataGridView1 ColumnCount - 1                        SQL     quot   quot                       SQL     quot   quot       string finalSQL   SQL        INSERT to DB the finalSQL    Your Data is ready now Insert the finalSQL in your database with your connection

User · Answer

You can do the same thing with the connection opened just once  Something like this   for int i 0  i lt  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

[c#] Insert all data of a datagridview to database at once

Examples related to c#

Examples related to sql-server

Examples related to winforms