Re: SQL Adapter or call SQL from C# Helper class

Please let me know how you get on with using C# for your DB access as I will
be very interested to hear.

BTW. Where will you be storing the database parameters e.g. name, server,
username, password etc.? Does BizTalk automatically pass its transaction
context on to your C# program and therefore your database connection?

--
McGeeky
http://mcgeeky.blogspot.com


"Leonid Ganeline" <leo_gan_57@xxxxxxxxxxx> wrote in message
news:Ob$1rpQeGHA.4932@xxxxxxxxxxxxxxxxxxxxxxx
First of all, thank you very much for this discussion!

Disclaimer: Here I mention only Send Port functionality.
All things you listed can be done from C# easily within Expression shape.
And ADO.NET client of SQL is more flexible, more manageable then "BizTalk"
client.
Disadvantages of the "BizTalk" client if we use SP(Stored Procedure (not
updategram):
* If we send a complicated Xml document (not only 1 record of one table or
some values) we have to convert a message to String before pass it to the
Send Port . Inside SP we have to convert it from String to OPENXML. It is
a big performance degradation.
* C# code is much more suitable to handle SQL errors with different
severity. The SQL Send Port + Orchestration are clumsy.
* C# code within Expression shape is much more straightforward way to send
data to SQL, all in one.

I agree that SQL Receive Location is useful:
* it manages the "Pooling" process.
* usually we send to SP in SQL Receive Location only few parameters and
can escape of OPENXML completely

Comment are below, in your text.

--
Regards,

Leonid Ganeline
BizTalk Solution Developer
===================================
BizTalk Blog -- http://geekswithblogs.net/leonidganeline

"McGeeky" <anon@xxxxxxxx> wrote in message
news:uDWUcjHeGHA.1320@xxxxxxxxxxxxxxxxxxxxxxx
1. Automatic retries by BizTalk on deadlocks or failures. Retries and
time
between retries is already built in to the send port configuration
+++ it's automatic. We can only set up the counter and duration of
retries. Then we get an exception and have to clumsy process it into
Orchestration. All these task we can work out in C#, the retry logic - in
Orchestration

2. Database configuration settings separated out in to the send port
+++ I've never seen the BTS solution without Helper classes. And they
always use the config information, It's pretty easy to save config
information in Xml files then we've got the same level of manageability as
with "Port configurations"

3. Visibility of the data travelling back and forth between the BizTalk
orchestration and SQL Server. E.g. if anything goes wrong you can
retreive
the message
+++ Maybe. But the same logic I can implement with Delay... shapes or with
exceptions from C# (Expression shape)

4. No need for any C# coding. All database activity can be encapsulated
within an xml schema, an orchestration, and a stored procedure - all very
visible and accessible

Don't let me discourage you from using C# if its a good fit though.

+++ The point is: It fit ALL cases the "SQL Send port --> Stored
Procedure"


--
McGeeky
http://mcgeeky.blogspot.com


"Leonid Ganeline" <leo_gan_57@xxxxxxxxxxx> wrote in message
news:OAOYDZDeGHA.1456@xxxxxxxxxxxxxxxxxxxxxxx
Are you sure there are "lots of advantages"?
Could you, please, give some examples of them?
Regards,

Leonid Ganeline
BizTalk Solution Developer
===================================
BizTalk Blog -- http://geekswithblogs.net/leonidganeline

"McGeeky" <anon@xxxxxxxx> wrote in message
news:OXreDszdGHA.5116@xxxxxxxxxxxxxxxxxxxxxxx
If I want to return codes from a stored procedure to a BizTalk
orchestration
I use XML. E.g. <CreateInvoiceStoredProc
Code="InvoiceAlreadyExists"/>.
The
orchestration can then check the code in a decision shape and do
something
about it.

Keeping database access within the send ports may seem a bit of a
headache
at first but it does give lots of advantages. However, if you find in
your
particularly situation C# database access is better then I would go
ahead
with that approach.

--
McGeeky
http://mcgeeky.blogspot.com


"Leonid Ganeline" <leo_gan_57@xxxxxxxxxxx> wrote in message
news:eyHDaVtdGHA.4148@xxxxxxxxxxxxxxxxxxxxxxx
Yes, it retrying the message but...
then the retrying is under control of Send port (N repetition then
buummmz..
exception). If there is another kind of error in SQL SP then it can
be
different branch: return @@errror into SP and then processing the
Response
message in the Orchestration.
I want to get the standard way to handle errors.
If the client of SP is my C# code it is under my control.
If the client of SP is the Send Port I have to use very narrow set of
control: setting in the Send Port and the Orchestration shapes.

I think using the helper classes for saving data to DB is more
flexible
and
powerful tool then Send Ports.
Of course if I need to process data "right now". If I need to
response
to
errors with some suspended processing (for example, sending message
to
InfoPath for hand-remediation etc.)

--

Regards,

Leonid Ganeline
BizTalk Developer, MCSD
http://geekswithblogs.net/leonidganeline/

"McGeeky" <anon@xxxxxxxx> wrote in message
news:OBU225mdGHA.4900@xxxxxxxxxxxxxxxxxxxxxxx
In the case of a deadlock BizTalk will handle this for you
automatically
by
retrying the message.

--
McGeeky
http://mcgeeky.blogspot.com


"Leonid Ganeline" <leo_gan_57@xxxxxxxxxxx> wrote in message
news:uNxYzEidGHA.380@xxxxxxxxxxxxxxxxxxxxxxx
I wander If there is any disadvantage of call SQL Stored Procedure
from
C#
method?
It's easy to send/receive the data from SQL from C# code:
convert the message to the string and call Method(MyString).
I see one but huge advantage of this variant. Now I can handle the
SQL
errors much precisely. With SQL adapter I can not for example
handle
the
deadlock cases.

--
Regards,

Leonid Ganeline
BizTalk Solution Developer
===================================
BizTalk Blog -- http://geekswithblogs.net/leonidganeline
















.

Relevant Pages

  • Re: Error while Processing the input file instance....
    ... It looks as if your orchestration is not bound to your send port properly. ... "A BizTalk Enthusiast" ... > Health and Activity Tracking tool for more detailed information on this ...
    (microsoft.public.biztalk.general)
  • Re: General Biztalk advice
    ... You might want to consider using a combination of BizTalk and Sql Service ... Broker, or just BizTalk. ... You can start an orchestration from your web app by submitting to BizTalk ...
    (microsoft.public.biztalk.general)
  • RE: Batching
    ... Currently batching is only supported with some accelerators like HL7. ... receives individual messages (from orchestration) and batches them ... In Visual Studio .Net 2003, BizTalk Explorer, you should find ... Receive Port named "BatchControlPort". ...
    (microsoft.public.biztalk.general)
  • Re: Persistance points performance
    ... BizTalk, I can't see it being a hardware problem. ... I suspect it has something to do with creating a connection to the SQL ... Server, but I can't see how that takes three seconds. ... > assembly from the orchestration. ...
    (microsoft.public.biztalk.general)
  • Re: Correlation
    ... I am developing an orchestration that is kicked off by a record being ... port and passed to an activating receive in my orchestration. ... set of SQL Server tables. ... field because, according to the generated schema, it can occur more than ...
    (microsoft.public.biztalk.general)
Loading