Sql Server连接C＃：最大插入查询数

I've a problem with SqlConnection in C#. I do a large number of INSERT NonQuery, but in any case SqlConnection save in the database always the first 573 rows. This is the method I use for queries. In this method there is a lock because I use different thread to save the data.

我在C#中遇到SqlConnection问题。我做了大量的INSERT NonQuery,但无论如何SqlConnection在数据库中保存的第一行总共是573行。这是我用于查询的方法。在这种方法中有一个锁,因为我使用不同的线程来保存数据。

   public void InsertElement(string link, string titolo, string text)
    {
        string conString = "*****************";
        using (SqlConnection connection = new SqlConnection(conString))
        {
            connection.Open();

            text = text.Replace("\"", "");
            DateTime localDate = DateTime.Now;

            lock (thisLock)
            {
                string query = "IF (NOT EXISTS(SELECT * FROM Result " +
                " WHERE Link = '" + link + "')) " +
                " BEGIN " +
                " INSERT INTO Result ([Titolo],[Link],[Descrizione],[DataRicerca],[FKDatiRicercheID]) " +
                " VALUES('" + titolo + "', '" + link + "', '" + text + "', '" + localDate + "', 1) " +
                " END";

                if (connection != null)
                {
                    SqlCommand cmd = new SqlCommand(query, connection);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }

This is the code of the loop that call the method InsertElement()

这是调用方法InsertElement()的循环代码

public void Save()
{
    string[] DatiLetti;
    string url = "";

    while (result.Count > 0)
    {
        try
        {
            url = result.Last();
            result.RemoveAt(result.Count - 1);

            DatiLetti = ex.DirectExtractText(url);

            if (DatiLetti[0].Length > 2)
            {
                ssc.InsertGare(url, DatiLetti[0], DatiLetti[1]);
            }
        }
        catch (Exception exc)
        {
            logger.Error("Exception SpiderSave> " + exc);
        }
    }
}

Result is a volatile array that is progressively filled from other thread. I'm sure that the array contains more than 573 items.

结果是一个从其他线程逐步填充的易失性数组。我确定该数组包含超过573项。

I try to search one solution, but all the answers say that the number of database connections for SQLServer is over 32K at a time and I've already checked this number in my database. Is there anyone who can help me understand the problem?

我尝试搜索一个解决方案,但所有答案都说SQLServer的数据库连接数一次超过32K,我已经在我的数据库中检查了这个数字。有没有人可以帮我理解这个问题?

2 个解决方案

#1

Don't open a connection for every insert. Use one connection, then pass that connection through to your insert, like this :

不要为每个插件打开连接。使用一个连接,然后将该连接传递到您的插入,如下所示:

public void InsertElement(string link, string titolo, string text, SqlConnection conn)
{
    text = text.Replace("\"", "");
    DateTime localDate = DateTime.Now;

    lock (thisLock)
    {
        string query = "IF (NOT EXISTS(SELECT * FROM Result " +
                        " WHERE Link = '" + link + "')) " +
                        " BEGIN " +
                        " INSERT INTO Result ([Titolo],[Link],[Descrizione],[DataRicerca],[FKDatiRicercheID]) " +
                            " VALUES('" + titolo + "', '" + link + "', '" + text + "', '" + localDate + "', 1) " +
                            " END";

        if (connection != null)
        {
            SqlCommand cmd = new SqlCommand(query, connection);
            cmd.ExecuteNonQuery();
        }
    }
}

I recommend also looking at paramatizing your query, as well as using bulk inserts, and not individual inserts

我建议您还要查看对查询的参数化,以及使用批量插入,而不是使用单个插入

#2

If you are executing InsertElement() once for each rows of data to insert, then the execution will be too slow for large no. of rows. (Also, you are creating SqlConnection for each query execution.) Try adding many rows at once using a single INSERT query:

如果要为要插入的每行数据执行一次InsertElement(),那么执行对于大型no来说执行速度太慢。的行。 (此外,您正在为每个查询执行创建SqlConnection。)尝试使用单个INSERT查询一次添加许多行:

INSERT INTO tablename
(c1,c2,c3)
VALUES
(v1,v2,v3),
(v4,v5,v6)
...

#1