To help illustrate my problem, I'll use a toy example. Imagine the flatfile has these columns: author_id|author_name|book_id|book_name. Also assume that my database starts out blank. I have two tables, an author table and a book table. The author table has an author_id column (primary key) and an author_name column. The book table has book_id (primary key), author_id and book_name columns.
There are a few problems here.
1) I'd like to be able to insert all data in a single transaction.
2) The file is too large to fit into memory so I'd like to be able to process the file line by line
3) I insert new authors as I see them, but one author may be scattered in the file. In other words, lines 8 and 9 may be books by Author1, lines 10 and 11 by Author2 and then line 12 will be another book by Author1. This leads to the need to query the author table by author_id to ensure that I don't try and insert the author twice.
So I have a question and an error. First the question. I am trying to do this all in a single transaction. Does the devart DataContext cache the change set in memory until the transaction has been committed? In other words, am I going to hit a memory problem even though my code processes the file line by line?
Second, I am getting an exception that says Too many connections. I am only using a single DataContext in a single transaction. How could I have used up too many connections? I understand that I am executing a lot of queries, but I don't see why that should be a problem.
Here is the stack trace:
Code: Select all
Test.API.RelationalStoreException : Error occurred during write operation.
----> Devart.Data.Linq.LinqCommandExecutionException : Error on opening DbConnection.
----> Devart.Data.MySql.MySqlException : Too many connections
at Test.Storage.Relational.MySqlRelationalStorage.c__DisplayClass8a.b__89() in MySqlRelationalStorage.cs: line 1022
at Test.Storage.Relational.MySqlRelationalStorage.WrapTx(Action dbCall) in MySqlRelationalStorage.cs: line 1057
at Test.Storage.Relational.MySqlRelationalStorage.WriteTx(Action dbCall) in MySqlRelationalStorage.cs: line 1007
at Test.Storage.Relational.MySqlRelationalStorage.BootstrapData(RelationalBootstrapDataArg arg) in MySqlRelationalStorage.cs: line 712
at Test.Storage.Relational.Tests.MySqlRelationalStorageTests.TestBootstrapData() in MySqlRelationalStorageTests.cs: line 244
--LinqCommandExecutionException
at Devart.Data.Linq.LinqCommandExecutionException.a(String A_0, Exception A_1)
at Devart.Data.Linq.Provider.n.g()
at Devart.Data.Linq.Provider.n.b(IConnectionUser A_0)
at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(CompiledQuery compiledQuery, Object[] parentArgs, Object[] userArgs, Object lastResult)
at Devart.Data.Linq.Provider.DataProvider.ExecuteAllQueries(CompiledQuery compiledQuery, Object[] userArguments)
at Devart.Data.Linq.Provider.DataProvider.CompiledQuery.Devart.Data.Linq.Provider.ICompiledQuery.Execute(IProvider provider, Object[] userArgs)
at Devart.Data.Linq.CompiledQuery.a(DataContext A_0, Object[] A_1)
at Devart.Data.Linq.CompiledQuery.Invoke(a A_0, b A_1)
at Test.Storage.Relational.MySqlRelationalStorage.ProcessBootstrapData(StreamReader bsymSR, DateTime bsymTime, StreamReader cusipSR, DateTime cusipTime) in MySqlRelationalStorage.cs: line 806
at Test.Storage.Relational.MySqlRelationalStorage.c__DisplayClass68.b__61() in MySqlRelationalStorage.cs: line 712
at Test.Storage.Relational.MySqlRelationalStorage.c__DisplayClass8a.b__89() in MySqlRelationalStorage.cs: line 1011
--MySqlException
at Devart.Data.MySql.bk.s()
at Devart.Data.MySql.bk.d()
at Devart.Data.MySql.u.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, SshOptions A_7, SslOptions A_8, ProxyOptions A_9, MySqlHttpOptions A_10, HttpOptions A_11)
at Devart.Data.MySql.MySqlInternalConnection.Connect(MySqlConnection owner, String userId, String password, String host, String database, Int32 port, Int32 connectionTimeout, MySqlProtocol protocol, Boolean compress, Boolean clientInteractive)
at Devart.Data.MySql.MySqlInternalConnection..ctor(o connectionOptions, MySqlConnection owner)
at Devart.Data.MySql.ar.a(w A_0, Object A_1, DbConnectionBase A_2)
at Devart.Common.DbConnectionFactory.a(DbConnectionPool A_0, w A_1, DbConnectionBase A_2)
at Devart.Common.DbConnectionPoolGroup.a(DbConnectionPool A_0, DbConnectionBase A_1)
at Devart.Common.DbConnectionPool.a(DbConnectionBase A_0)
at Devart.Common.DbConnectionPool.GetObject(DbConnectionBase owningConnection)
at Devart.Common.DbConnectionFactory.a(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.MySql.MySqlConnection.Open()
at Devart.Data.Linq.Provider.n.g()