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?
This question is related to
c#
sql-server
winforms
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!");
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
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
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
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
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 + "')";
Source: Stackoverflow.com