Simple.Data Oracle issue (closes connection after insert executes)

Question

0.00/5 (No votes)

See more:

Hello guys,

Recently I encountered an issue with Simple.Data, that's hard to grasp so wonder if anyone has a better idea of the way out.

I got this (simplified version):

C#

var db = Simple.Data.OpenNamedConnection(myConn);
using (var transaction = db.BeginTransaction())
{
   foreach(DataRow row in Table.Rows)
   {
      transaction[Table.TableName].Insert(row);
      // after the first iteration of this transaction.ConnectionState becomes "Closed"
   }
}

So this is a simple loop through each row and doing insert (don't ask why - this is simplified version). The problem here is that after the first row goes fine and after

C#

transaction[Table.TableName].Insert(row);

is executed the connection state becomes closed and exception occurs during the next iteration.
By the way - this is Oracle issue and this code works fine when I am using MS SQL database.

Any thoughts what I might be missing?

Thanks!
Modestas

Posted 1-Jan-15 21:53pm

MK-Gii

Add a Solution

Comments

[no name] 2-Jan-15 4:27am

I guess there must be connection closure in oracle. Can you try using the transaction inside the for loop and test whether its working or not.

MK-Gii 2-Jan-15 4:41am

Hm.... not sure how would that help, but yes - it does the same. Loops don't have connection closure logic. So it just closes on transaction.insert (this is a thing from Simple.Data)

Tushar sangani 2-Jan-15 4:37am

You Check connection is open or not

MK-Gii 2-Jan-15 4:43am

I am inserting a lot or rows and targeting to commit the transaction with all the inserts in one go. Reopening the connection each time would become bloody slow. This could not be applied in production environment unfortunately.

Tushar sangani 2-Jan-15 4:51am

You can set timeout of connection

MK-Gii 2-Jan-15 5:19am

This is not the reason. It gets closed after Insert transaction is made. This is only Oracle related and works fine with MS SQL and the timeouts are the same. Setting the timeout to 1 hours did not help to maintain connection state.

2 solutions

Solution 1

Hi,

There is a concept called transaction scope. If your transaction will be inside the transaction scope then your connection will not get disconnected. Refer the following article (specifically 14th point).

TranactionScope Default Properties

This will help you.

Thanks
Sisir Patro

Posted 1-Jan-15 23:29pm

Sisir Patro

Comments

MK-Gii 2-Jan-15 6:10am

It's in a scope - if it would not be it would fail with all database types - including MS SQL. Now it works just fine with MS SQL and fails with Oracle. And also please note that I am using Simple.Data ORM as stated in the question :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

VytautasG · Accepted Answer · 2015-01-02T02:21:00

check property ProviderSupportsCompoundStatements - it's a read-only property

C#

using (SimpleTransaction transaction = db.BeginTransaction())
{
    AdoAdapter adoAdapter = transaction.GetAdapter() as AdoAdapter;
    if (adoAdapter != null)
    {
        if(!adoAdapter.ConnectionProvider.SupportsCompoundStatements)
        {
            //transaction.GetAdapter().UpsertMany
        }
    }
}

OracleConnectionProvider doesn't support compound statements, SqlConnectionProvider does .

https://github.com/flq/Simple.Data.Oracle/blob/master/Simple.Data.Oracle/OracleConnectionProvider.cs
https://github.com/markrendle/Simple.Data/blob/master/Simple.Data.SqlServer/SqlConnectionProvider.cs

Maybe 'transaction.GetAdapter()' and UpsertMany, InsertMany and UpdateMany will help instead of using foreach loop with 'transaction' object Insert and Update