I have a program that reads a set of parent objects using a datareader and inside this reading loop, it reads some child data using other data readers (lazy load is not an option for me in ths case).
The problem is that after the first inner reader is disposed, DateTime fields cannot be read from the outer reader and an InvalidCastException is raised saying "cannot convert from 'CoreLab.Common.DbConnectionClosed' to 'CoreLab.PostgreSql.v'".
The inner data readers are not using (not so sure about that) the same connection as the outer data reader, so I believe this shouldn't happen.
Debugging the program I found out that the PgSqlDataReader object holds a reference to a PgSqlConnection and its fields seems to hold a reference to other PgSqlConnection just before the error happens. The connection held by the data reader is "Open" and the one held by the reader's fields is "Closed". The closed connection seems to be the problem when trying to read the DateTime value.
Below are the steps to reproduce this scenario :
CREATE AND POPULATE TABLES :
Code: Select all
create table parentTable
(
parentId integer not null,
parentName text,
parentCreationDate timestamp
);
create table childTable
(
childId integer not null,
parentId integer not null,
childName text,
childCreationDate timestamp
);
insert into parentTable (parentId,parentName,parentCreationDate)
values(1,'parent 1',current_timestamp);
insert into parentTable (parentId,parentName,parentCreationDate)
values(2,'parent 2',current_timestamp);
insert into parentTable (parentId,parentName,parentCreationDate)
values(3,'parent 3',current_timestamp);
insert into childTable(childId, parentId,childName,childCreationDate)
values(1,2,'child 1',current_timestamp);
insert into childTable(childId, parentId,childName,childCreationDate)
values(2,2,'child 2',current_timestamp);CREATE THE PROGRAM :
Code: Select all
class Program
{
static void Main(string[] args)
{
TestMethod();
}
static void TestMethod()
{
// open reader with parent rows
using (IDataReader parent = GetDataReader("select * from parentTable order by parentId"))
{
while (parent.Read())
{
// get parent data
int parentId = (int)parent["parentId"];
string parentName = (string)parent["parentName"];
DateTime parentCreationDateTime = (DateTime)parent["parentCreationDate"];
// open reader with child data
using (IDataReader child = GetDataReader("select * from childTable where parentId = " + parentId.ToString()))
{
while (child.Read())
{
// get child data
int childId = (int)parent["childId"];
int parentId2 = (int)parent["parentId"];
string childName = (string)parent["childName"];
DateTime childCreationDateTime = (DateTime)parent["childCreationDate"];
}
}
}
}
}
static PgSqlConnection Connect()
{
PgSqlConnection c = new PgSqlConnection(
"user id=testCoreLab;host=localhost;database=testdb;persist security info=True;password=testCoreLab");
c.Open();
return c;
}
static IDataReader GetDataReader(
string sql)
{
PgSqlConnection conn = Connect();
try
{
using (PgSqlCommand cmd = new PgSqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch
{
conn.Close();
conn.Dispose();
throw;
}
}
}
Let me know if you need further information.
Thanks,
Thiago