No i do not leave the readers open.
The code is like this:
//the connectionstring is always the same
private IDataReader dataBaseReader;
function:
using (TransactionScope ts = new TransactionScope())
{
//READ
if (dataBaseReader != null && !dataBaseReader.IsClosed)
dataBaseReader.Close();
if (dataBaseConnection.State != ConnectionState.Open)
{
dataBaseConnection.ConnectionString = connectionString;
dataBaseConnection.Open();
}
command.Connection = dataBaseConnection; //Goes to remote DBServer
dataBaseReader = command.ExecuteReader(
CommandBehavior.CloseConnection);
dt.Table[0].Load(dataBaseReader, LoadOption.Upsert);
dataBaseReader.Close();
//INSERT
if (dataBaseReader != null && !dataBaseReader.IsClosed)
dataBaseReader.Close();
if (dataBaseConnection.State != ConnectionState.Open)
{
dataBaseConnection.ConnectionString = connectionString;
dataBaseConnection.Open();
}
command.Connection = dataBaseConnection;
int rowsAffected = command.ExecuteNonQuery();
if (dataBaseReader != null && !dataBaseReader.IsClosed)
dataBaseReader.Close();
if (dataBaseConnection.State != ConnectionState.Open)
{
dataBaseConnection.ConnectionString = connectionString;
dataBaseConnection.Open();
}
command.Connection = dataBaseConnection;
object result = command.ExecuteScalar();
//READ
if (dataBaseReader != null && !dataBaseReader.IsClosed)
dataBaseReader.Close();
if (dataBaseConnection.State != ConnectionState.Open)
{
dataBaseConnection.ConnectionString = connectionString;
///EXCEPTION!!!
dataBaseConnection.Open();
///EXCEPTION!!!
}
command.Connection = dataBaseConnection; //Goes to remote DBServer
dataBaseReader = command.ExecuteReader(
CommandBehavior.CloseConnection);
dt.Table[0].Load(dataBaseReader, LoadOption.Upsert);
dataBaseReader.Close();
}
Thank for everything you have been very helpful.
"Marc Gravell" wrote:
Are you perhaps leaving your readers open? It is unfortunately quite hard to
tell without some code... I'll try and run a few tests later to see if I can
figure anything out...
Marc