C# 一次将一个datagridview的所有数据插入到数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10556556/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert all data of a datagridview to database at once
提问by Manish Gautam
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:
我有一个由各种操作和用户对数据的操作创建的 datagridview。我想一次将gridview的所有数据插入到数据库中，我知道我可以尝试类似这样的代码：
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?
但是每次插入记录时都创建一个新连接是否公平？数据网格可能包含多行......有没有办法一次将所有数据取出到服务器并在sql中循环插入所有数据？
采纳答案by CM Kanode
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):
如果移动 for 循环，则不必进行多个连接。只需对您的代码块进行快速编辑（绝不是完全正确的）：
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
关于一次执行多条SQL命令，请看这个链接： 单个SqlCommand中的多条语句
回答by InspiredBy
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
此外，根据您的特定场景，您可能需要考虑将网格绑定到数据库。这将大大减少手动工作量：http: //www.switchonthecode.com/tutorials/csharp-tutorial-binding-a-datagridview-to-a-database
回答by siddhi
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!");
回答by Suresh
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 + "')";
回答by WhySoSerious
I think the best way is by using TableAdaptersrather 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.
我认为最好的方法是使用TableAdapters而不是使用 Commands 对象，它的 Update 方法将数据集或数据表中的所有更改（更新、插入和删除）直接发送到数据库。通常在使用 DataGridView 时，您绑定到 BindingSource，它允许您与数据源（例如数据表或数据集）进行交互。
If you work like this, then on your bounded DataGridView you can just do:
如果你这样工作，那么在你的有界 DataGridView 上你可以这样做：
this.customersBindingSource.EndEdit();
this.myTableAdapter.Update(this.myDataSet.Customers);
The 'customersBindingSource' is the DataSource of the DataGridView.
'customersBindingSource' 是 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.
适配器的 Update 方法将更新单个数据表并根据表中每个数据行的 RowState 执行正确的命令（INSERT、UPDATE 或 DELETE）。
From: https://msdn.microsoft.com/en-us/library/ms171933.aspx
来自：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.
因此，在使用 Update 方法时，在 DatagridView 中所做的任何更改都将反映在数据库上。
More about TableAdapters: https://msdn.microsoft.com/en-us/library/bz9tthwx.aspx
有关 TableAdapter 的更多信息：https: //msdn.microsoft.com/en-us/library/bz9tthwx.aspx
回答by P. nganga
Please see if below can help you
请看看以下是否可以帮助您
Class Post_Sales
类 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
结束类
