dbDataReader

Mr. X.

Hello.
When I use in my program the dbDataReader,
I can open only one datareader, and if not, computer shouts an error that
there is already an opened datareader.

I have VS 2008.
Use mySql.
Windows 7.

Is that problem a specific problem for mysql?

How can I overcome that problem (open more then one datareader)?

Thanks

Arne Vajhøj

On 21-09-2010 20:29, Mr. X. wrote:
> When I use in my program the dbDataReader,
> I can open only one datareader, and if not, computer shouts an error
> that there is already an opened datareader.
>
> I have VS 2008.
> Use mySql.
> Windows 7.
>
> Is that problem a specific problem for mysql?

No.

> How can I overcome that problem (open more then one datareader)?

Use two connections !

Arne

Mr. X.

I get several sqls externally.
I need to run all the sqls in the same transaction,
so open other connection for each sql is not a good solution.

I don't know if datareader is the best.
Is there any other solution then using datareader (or can I use datareader
by some parameters that I can open more then one datareader).

Thanks

"Arne Vajhøj" <arne@vajhoej.dk> wrote in message
news:4c995417$0$50446$14726298@news.sunsite.dk...
> On 21-09-2010 20:29, Mr. X. wrote:
>> When I use in my program the dbDataReader,
>> I can open only one datareader, and if not, computer shouts an error
>> that there is already an opened datareader.
>>
>> I have VS 2008.
>> Use mySql.
>> Windows 7.
>>
>> Is that problem a specific problem for mysql?
>
> No.
>
>> How can I overcome that problem (open more then one datareader)?
>
> Use two connections !
>
> Arne

Arne Vajhøj

On 21-09-2010 21:09, Mr. X. wrote:
> "Arne Vajhøj" <arne@vajhoej.dk> wrote in message
> news:4c995417$0$50446$14726298@news.sunsite.dk...
>> On 21-09-2010 20:29, Mr. X. wrote:
>>> When I use in my program the dbDataReader,
>>> I can open only one datareader, and if not, computer shouts an error
>>> that there is already an opened datareader.
>>>
>>> I have VS 2008.
>>> Use mySql.
>>> Windows 7.
>>>
>>> Is that problem a specific problem for mysql?
>>
>> No.
>>
>>> How can I overcome that problem (open more then one datareader)?
>>
>> Use two connections !
> I get several sqls externally.
> I need to run all the sqls in the same transaction,
> so open other connection for each sql is not a good solution.
>
> I don't know if datareader is the best.
> Is there any other solution then using datareader (or can I use
> datareader by some parameters that I can open more then one datareader).

For MS SQLServer you can specify:
MultipleActiveResultSets=true
in the connection string.

But not in MySQL. Or most other databases.

There you have two options:

1) read everything from the first query and close the
reader before you make the next query

2) use two connections and TransactionScope to put them
in a single transaction

Or maybe reconsider your logic. It is rather unusual to have
multiple queries that return many rows in a transaction.

Arne

Mr. X.

Well.
It seems I should be very careful when using several datasource.
I would like an example of TransactionScope use, please.

Thanks

"Arne Vajhøj" <arne@vajhoej.dk> wrote in message
news:4c995a7e$0$50448$14726298@news.sunsite.dk...
> On 21-09-2010 21:09, Mr. X. wrote:
>> "Arne Vajhøj" <arne@vajhoej.dk> wrote in message
>> news:4c995417$0$50446$14726298@news.sunsite.dk...
>>> On 21-09-2010 20:29, Mr. X. wrote:
>>>> When I use in my program the dbDataReader,
>>>> I can open only one datareader, and if not, computer shouts an error
>>>> that there is already an opened datareader.
>>>>
>>>> I have VS 2008.
>>>> Use mySql.
>>>> Windows 7.
>>>>
>>>> Is that problem a specific problem for mysql?
>>>
>>> No.
>>>
>>>> How can I overcome that problem (open more then one datareader)?
>>>
>>> Use two connections !
> > I get several sqls externally.
> > I need to run all the sqls in the same transaction,
> > so open other connection for each sql is not a good solution.
> >
> > I don't know if datareader is the best.
> > Is there any other solution then using datareader (or can I use
> > datareader by some parameters that I can open more then one datareader).
>
> For MS SQLServer you can specify:
> MultipleActiveResultSets=true
> in the connection string.
>
> But not in MySQL. Or most other databases.
>
> There you have two options:
>
> 1) read everything from the first query and close the
> reader before you make the next query
>
> 2) use two connections and TransactionScope to put them
> in a single transaction
>
> Or maybe reconsider your logic. It is rather unusual to have
> multiple queries that return many rows in a transaction.
>
> Arne

Arne Vajhøj

On 24-09-2010 18:58, Mr. X. wrote:
> "Arne Vajhøj" <arne@vajhoej.dk> wrote in message
> news:4c995a7e$0$50448$14726298@news.sunsite.dk...
>> On 21-09-2010 21:09, Mr. X. wrote:
>>> "Arne Vajhøj" <arne@vajhoej.dk> wrote in message
>>> news:4c995417$0$50446$14726298@news.sunsite.dk...
>>>> On 21-09-2010 20:29, Mr. X. wrote:
>>>>> When I use in my program the dbDataReader,
>>>>> I can open only one datareader, and if not, computer shouts an error
>>>>> that there is already an opened datareader.
>>>>>
>>>>> I have VS 2008.
>>>>> Use mySql.
>>>>> Windows 7.
>>>>>
>>>>> Is that problem a specific problem for mysql?
>>>>
>>>> No.
>>>>
>>>>> How can I overcome that problem (open more then one datareader)?
>>>>
>>>> Use two connections !
>> > I get several sqls externally.
>> > I need to run all the sqls in the same transaction,
>> > so open other connection for each sql is not a good solution.
>> >
>> > I don't know if datareader is the best.
>> > Is there any other solution then using datareader (or can I use
>> > datareader by some parameters that I can open more then one
>> datareader).
>>
>> For MS SQLServer you can specify:
>> MultipleActiveResultSets=true
>> in the connection string.
>>
>> But not in MySQL. Or most other databases.
>>
>> There you have two options:
>>
>> 1) read everything from the first query and close the
>> reader before you make the next query
>>
>> 2) use two connections and TransactionScope to put them
>> in a single transaction
>>
>> Or maybe reconsider your logic. It is rather unusual to have
>> multiple queries that return many rows in a transaction.
> It seems I should be very careful when using several datasource.

It is not that bad.

> I would like an example of TransactionScope use, please.

MS has an example in the docs:

http://msdn.microsoft.com/en-us/libr...tionscope.aspx

Arne