Shared db update between VB.NET and VB6

I am converting a project from VB6 to VB.NET which has hundreds of plug-in
DLLs that perform various tasks on the application database. In order to
allow us to use the .NET version of the application without first having to
convert all of the plug-ins, I am planning to allow the application to call
into the existing COM plug-in DLLs.

The DLLs expect an ADO (classic) connection to be provided to them before
their main method is called so that they can read and write the database.
When calling, the main application begins a transaction, calls into the DLL,
then makes some database updates itself and commits the transaction. If
anything goes wrong the transaction can be rolled back and everything is
undone.

The VB.NET version of the application is using ADO.NET to access the
database. I'm therefore unable to pass the application's connection to the
COM plug-ins as they won't recognise it. I am planning to open a second
connection using ADO classic and pass this to the plug-ins.

However, this means that I cannot put a single transaction around the
updates performed by both the application and the plug-in (as each is a
separate connection). If one should fail after the other has been committed,
I have lots the integrity of my data.

Can anyone suggest any approaches to dealing with this situation to minimise
(or hopefully eliminate) this problem?

Many thanks,

--

(O) e n o n e

re: Shared db update between VB.NET and VB6

Hi,

Create a webservice to update the database that you use with vb6 or
vb.net

http://www.dotnetspider.com/technology/kbpages/898.aspx

Ken
----------------------
"Oenone" <oenone@nowhere.com> wrote in message
news:miI%d.930$eO2.65@newsfe2-gui.ntli.net...
I am converting a project from VB6 to VB.NET which has hundreds of plug-in
DLLs that perform various tasks on the application database. In order to
allow us to use the .NET version of the application without first having to
convert all of the plug-ins, I am planning to allow the application to call
into the existing COM plug-in DLLs.

The DLLs expect an ADO (classic) connection to be provided to them before
their main method is called so that they can read and write the database.
When calling, the main application begins a transaction, calls into the DLL,
then makes some database updates itself and commits the transaction. If
anything goes wrong the transaction can be rolled back and everything is
undone.

The VB.NET version of the application is using ADO.NET to access the
database. I'm therefore unable to pass the application's connection to the
COM plug-ins as they won't recognise it. I am planning to open a second
connection using ADO classic and pass this to the plug-ins.

However, this means that I cannot put a single transaction around the
updates performed by both the application and the plug-in (as each is a
separate connection). If one should fail after the other has been committed,
I have lots the integrity of my data.

Can anyone suggest any approaches to dealing with this situation to minimise
(or hopefully eliminate) this problem?

Many thanks,

--

(O) e n o n e

re: Shared db update between VB.NET and VB6

Ken Tucker [MVP] wrote:[color=blue]
> Create a webservice to update the database that you use with
> vb6 or vb.net[/color]

Hi Ken,

My application is entirely WinForms based, so a webservice isn't relevant to
me. I'm also not sure how it would help with reducing transaction problems
between the two connections..?

Any other suggestions?

--

(O)enone

re: Shared db update between VB.NET and VB6

Oenone,
[color=blue]
>
> My application is entirely WinForms based, so a webservice isn't relevant
> to me. I'm also not sure how it would help with reducing transaction
> problems between the two connections..?
>[/color]
A webservice is for a winform application.

See this great walkthrough
http://msdn.microsoft.com/library/de...alkthrough.asp

I hope this helps,

Cor

re: Shared db update between VB.NET and VB6

On Mon, 21 Mar 2005 22:56:18 GMT, "Oenone" <oenone@nowhere.com> wrote:

¤ I am converting a project from VB6 to VB.NET which has hundreds of plug-in
¤ DLLs that perform various tasks on the application database. In order to
¤ allow us to use the .NET version of the application without first having to
¤ convert all of the plug-ins, I am planning to allow the application to call
¤ into the existing COM plug-in DLLs.
¤
¤ The DLLs expect an ADO (classic) connection to be provided to them before
¤ their main method is called so that they can read and write the database.
¤ When calling, the main application begins a transaction, calls into the DLL,
¤ then makes some database updates itself and commits the transaction. If
¤ anything goes wrong the transaction can be rolled back and everything is
¤ undone.
¤
¤ The VB.NET version of the application is using ADO.NET to access the
¤ database. I'm therefore unable to pass the application's connection to the
¤ COM plug-ins as they won't recognise it. I am planning to open a second
¤ connection using ADO classic and pass this to the plug-ins.
¤
¤ However, this means that I cannot put a single transaction around the
¤ updates performed by both the application and the plug-in (as each is a
¤ separate connection). If one should fail after the other has been committed,
¤ I have lots the integrity of my data.
¤
¤ Can anyone suggest any approaches to dealing with this situation to minimise
¤ (or hopefully eliminate) this problem?

I don't see how you can share the transaction space between the managed and unmanaged code for you
application unless you perform the transactions at the server level.

Another option might be to use COM+. I don't know the full details of your implementation but you
may be able to enable COM+ to handle the transaction for both your VB 6.0 and VB.NET components.


Paul
~~~~
Microsoft MVP (Visual Basic)

re: Shared db update between VB.NET and VB6

Oenone,
In addition to Ken's suggestion.

I would consider using the Adapter Pattern to define a set of proxy classes
that implement the ADO interfaces, where the proxy classes use ADO.NET
internally to implement the ADO methods used by the COM plug-ins.

Then pass these Adapter objects to the COM plug-ins.

The "problem" with this method is that most people (me included) create
ADODB.Recordset & other ADODB objects directly, rather then indirectly via
Connection.Execute.

My other concern is how much effort this would be. However with hundreds of
COM plug-ins it may be worth it...

Hope this helps
Jay


"Oenone" <oenone@nowhere.com> wrote in message
news:miI%d.930$eO2.65@newsfe2-gui.ntli.net...[color=blue]
>I am converting a project from VB6 to VB.NET which has hundreds of plug-in
> DLLs that perform various tasks on the application database. In order to
> allow us to use the .NET version of the application without first having
> to
> convert all of the plug-ins, I am planning to allow the application to
> call
> into the existing COM plug-in DLLs.
>
> The DLLs expect an ADO (classic) connection to be provided to them before
> their main method is called so that they can read and write the database.
> When calling, the main application begins a transaction, calls into the
> DLL,
> then makes some database updates itself and commits the transaction. If
> anything goes wrong the transaction can be rolled back and everything is
> undone.
>
> The VB.NET version of the application is using ADO.NET to access the
> database. I'm therefore unable to pass the application's connection to the
> COM plug-ins as they won't recognise it. I am planning to open a second
> connection using ADO classic and pass this to the plug-ins.
>
> However, this means that I cannot put a single transaction around the
> updates performed by both the application and the plug-in (as each is a
> separate connection). If one should fail after the other has been
> committed,
> I have lots the integrity of my data.
>
> Can anyone suggest any approaches to dealing with this situation to
> minimise
> (or hopefully eliminate) this problem?
>
> Many thanks,
>
> --
>
> (O) e n o n e
>
>[/color]

re: Shared db update between VB.NET and VB6

It's not guaranteed data-integrity, but if it's possible/feasible from
ADO.NET use a two-stage commit and if you DB supports it:
ADO.NET => prepare to commit (stage 1)
ADO => Commit

If success(ADO Commit)
ADO.NET => Commit (stage 2)
else
ADO.NET => Rollback

re: Shared db update between VB.NET and VB6

Oenone wrote:[color=blue]
> However, this means that I cannot put a single transaction around the
> updates performed by both the application and the plug-in (as each is
> a separate connection). If one should fail after the other has been
> committed, I have lots the integrity of my data.[/color]

I actually found the answer I was looking for in the end, but it turned out
to be a SQL Server problem rather than ADO.NET.

The solution is to use bound connections (sp_bindsession). This allows two
separate connections (one in ADO.NET and the other in ADO) to share the same
transaction. They will therefore not block one another, and all updates can
be committed in a single atomic operation.

--

(O) e n o n e