Sqlcommand在foreach循环中运行时间不超过60次 [英] Sqlcommand not run more than 60 times in a foreach loop

问题描述

hi
i在我们的表单中有datagridview有360行，

i希望将这些行插入数据库但是Sqlcommand在foreach循环中运行不超过60次



我的代码：

hi i have datagridview in our form that have 360 rows,
i want insert this rows to database but Sqlcommand not run more than 60 times in a foreach loop

this my code:

try
            {

                con.Open();
                foreach (DataGridViewRow row in dataGridViewX1.Rows)
                {
                    SqlCommand com = new SqlCommand();
                    com.Connection = con;
                    com.CommandType = CommandType.StoredProcedure;
                    com.CommandText = "add_data_to_calander";
                    com.Parameters.AddWithValue("@date", row.Cells[2].Value.ToString());
                    com.Parameters.AddWithValue("@dayinweek", row.Cells[1].Value.ToString());
                    com.Parameters.AddWithValue("@dayinyear", row.Cells[0].Value.ToString());
                    var _value = row.Cells[3].Value;
                    if (_value != null && (bool)_value)
                        com.Parameters.AddWithValue("@isdisable", "1");
                    else
                        com.Parameters.AddWithValue("@isdisable", "0");
                    counter++;
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    com.ExecuteNonQuery();
                    com.Parameters.Clear();
                }
            }
            catch (Exception)
            {
            }
            finally { con.Close(); }
            MessageBox.Show(counter.ToString());

最终计数器值为60.

finally counter value is 60.

推荐答案

尝试处理您的SqlCommand对象：

Try disposing of your SqlCommand objects:

using (SqlCommand com = new SqlCommand())
    {
    com.Connection = con;
    com.CommandType = CommandType.StoredProcedure;
    com.CommandText = "add_data_to_calander";
    com.Parameters.AddWithValue("@date", row.Cells[2].Value.ToString());
    com.Parameters.AddWithValue("@dayinweek", row.Cells[1].Value.ToString());
    com.Parameters.AddWithValue("@dayinyear", row.Cells[0].Value.ToString());
    var _value = row.Cells[3].Value;
    if (_value != null && (bool)_value)
        com.Parameters.AddWithValue("@isdisable", "1");
    else
        com.Parameters.AddWithValue("@isdisable", "0");
    counter++;
    if (con.State == ConnectionState.Closed)
        con.Open();
    com.ExecuteNonQuery();
    com.Parameters.Clear();
    }

SqlCommands是稀缺资源，很可能你在SQL服务器上用完了。

SqlCommands are scarce resources, and the chances are you are running out in SQL server.

这篇关于Sqlcommand在foreach循环中运行时间不超过60次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！