一次将datagridview的所有数据插入数据库 [英] Insert all data of a datagridview to database at once

问题描述

我有一个datagridview，它是由各种操作和用户对数据的操作所创建的。
我想一次将gridview的所有数据插入数据库，我知道我可以尝试类似的代码：

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

每次插入记录创建一个新的连接？ datagrid可能包含许多行...是否有任何方法可以一次将所有数据取出到服务器并在sql中循环插入所有数据？

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?

推荐答案

如果移动for循环，则无需建立多个连接。只需对您的代码块进行快速编辑（绝对不完全正确）：

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

要一次执行多个SQL命令，请查看以下链接：
单个语句中有多个语句SqlCommand

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

这篇关于一次将datagridview的所有数据插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！