Paste all datagridview data into the database at once

Question

I have a datagridview that is created by various actions and user manipulation of data. I want to insert all gridview data into the database right away, I know I can try 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? A Datagrid can contain many rows ... Is there a way to delete all the data on the server right away and loop inside in sql to insert all the data?

Answer 1

If you move the for loop, you won’t have to make multiple connections. Just quickly edit 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(); } } } } 

Regarding the execution of several SQL commands at once, follow this link: Several statements in one SqlCommand

Answer 2

I think the best way is to use TableAdapters instead of using Commands objects, its Update method sends all changes mades (Updates, Inserts and Deletes) inside Dataset or DataTable directly to the database. Typically, when using a DataGridView, you bind to a BindingSource, which allows you to interact with a data source such as Datatables or Datasets.

If you work like this, then on a limited DataGridView you can simply do:

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

"CustomerBindingSource" is a DataSource DataGridView.

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

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

Thus, any changes made inside the DatagridView will be reflected in the database using the Update method.

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

Answer 3

See if below helps 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 

Final class

Answer 4

You can do the same with a connection opened only once. Something like that.

 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(); } 

In addition, depending on your specific scenario, you may want to bind the grid to the database. This would significantly reduce the amount of manual work: http://www.switchonthecode.com/tutorials/csharp-tutorial-binding-a-datagridview-to-a-database

Answer 5

You have a syntax error. Try the following syntax 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 + "')"; 

Answer 6

 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!");