OleDbException System Resources Exceeded

Question

The following code executes a simple insert command. If it is called 2,000 times consecutively (to insert 2,000 rows) an OleDbException with message = "System Resources Exceeded" is thrown. Is there something else I should be doing to free up resources?

using (OleDbConnection conn = new OleDbConnection(connectionString))
using (OleDbCommand cmd = new OleDbCommand(commandText, conn))
{
    conn.Open();
    cmd.ExecuteNonQuery();
}

Answer 1

The system resources exceeded error is not coming from the managed code, its coming from you killing your database (JET?)

You are opening way too many connections, way too fast...

Some tips:

• Avoid round trips by not opening a new connection for every single command, and perform the inserts using a single connection.
• Ensure that database connection pooling is working. (Not sure if that works with OLEDB connections.)
• Consider using a more optimized way to insert the data.

Have you tried this?

using (OleDBConnection conn = new OleDBConnection(connstr))
{
    while (IHaveData)
    {
        using (OldDBCommand cmd = new OldDBCommand())
        {
            cmd.Connection = conn;
            cmd.ExecuteScalar();
        }
    }
}

Answer 2

I tested this code out with an Access 2007 database with no exceptions (I went as high as 13000 inserts).

However, what I noticed is that it is terribly slow as you are creating a connection every time. If you put the "using(connection)" outside the loop, it goes much faster.