Data Replication 2
Results 1 to 1 of 1

Thread: Invoking Adapter Interactions with the ADO.NET Provider

  1. #1
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10

    Invoking Adapter Interactions with the ADO.NET Provider

    Starting with version 5.1, the Attunity adapter framework is accessible by means of the Attunity ADO.NET provider.

    To work with an adapter, rather than with a database driver, set the 'Adapter' option on the connection string and avoid setting the 'Database' option.

    Adapter interactions are invoked just like stored procedures. The code below shows several ways in which one can invoke an adapter interaction using the Attunity.Data.AisClient ADO.NET provider.

    All of the examples use the OrderSys sample adapter with the findOrder interaction that gets an XML input like:

    PHP Code:
    <findOrder ORDER_ID='1' /> 
    and returns a response like:

    PHP Code:
    <findOrderResponse>
        <
    ORDER ORDER_ID="1" ORDERED_BY="Jim Bo" N_LINES="2">
            <
    ADDRESS ADDRESSEE="Jim Bo & Sons, LLC." STREET="Fits Road" CITY="Oukalaka"
                                
    ZIP="33212" STATE="NH" COUNTRY="USA"/>
            <
    LINES LINE_NO="1" ITEM_NAME="Knife" QUANTITY="5" ITEM_PRICE="1.2">
            <
    LINES LINE_NO="2" ITEM_NAME="Fork" QUANTITY="5" ITEM_PRICE="0.9">
        </
    ORDER>
    </
    findOrderResponse
    The following code samples show various ways to invoke the OrderSys findOrder interaction.



    PHP Code:
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.IO;
    using System.Data;
    using Attunity.Data.AisClient;

    namespace 
    AcxSchmeaAdoNet
    {
        class 
    Program
        
    {
            static 
    void Main(string[] args)
            {
                
    AisConnectionStringBuilder csb = new AisConnectionStringBuilder();

                
    csb.Adapter "orders";
                
    csb.DataSource "localhost"// Alas, by ADO.NET stanadard used in place of 'Server'
                
    csb.Port 2551;
                
    csb.Workspace "navigator";

                
    AisConnection con = new AisConnection(csb.ToString());

                
    con.Open();

                
    // Invocation method 1: Using CommandType 'Text' with the text being the interaction
                // XML input. Note that in this example, 'findOrder' is both the interaction name and
                // the name of the input element. In this method, the command text must specify the 
                // interaction name, not the input element name.
                //
                // Use this method when you already have the interaction input as an XML document or
                // when the interaction input is complex (e.g., with child elements).
                
                
    AisCommand cmd1 con.CreateCommand();
                
    cmd1.CommandType CommandType.Text// That's the default command type
                
    cmd1.CommandText = @"<findOrder ORDER_ID='1'/>";

                
    string result1 cmd1.ExecuteScalar() as string;
                
    Console.WriteLine(String.Format("Result of method 1:\n{0}\n"result1));

                
    // Invocation method 2: Using CommandType 'StoredProcedure' with the command text 
                // being the interaction name and the ORDER_ID is specified as a regular parameter.
                //
                // Use this method when the interaction input is a simple element with attributes, 
                // and when you want to maintain the ability to switch to using a database stored
                // procedure without code changes.

                
    AisCommand cmd2 con.CreateCommand();
                
    cmd2.CommandType CommandType.StoredProcedure;
                
    cmd2.CommandText "findOrder";
                
    cmd2.Parameters.AddWithValue("ORDER_ID"1);

                
    string result2 cmd2.ExecuteScalar() as string;
                
    Console.WriteLine(String.Format("Result of method 2:\n{0}\n"result2));

                
    // Invocation method 3: Using CommandType 'StoredProcedure' with the command text 
                // being the interaction name and the input document specified with the special 
                // 'xml' parameter.
                //
                // Use this method when you already have the interaction input as an XML document 
                // with a name different than the interaction name or when the interaction input 
                // is complex (like in method 1).

                
    AisCommand cmd3 con.CreateCommand();
                
    cmd3.CommandType CommandType.StoredProcedure;
                
    cmd3.CommandText "findOrder";
                
    cmd3.Parameters.Add("xml", @"<findOrder ORDER_ID='1'/>");

                
    string result3 cmd3.ExecuteScalar() as string;
                
    Console.WriteLine(String.Format("Result of method 3:\n{0}\n"result3));

                
    // Invocation method 4: Using CommandType 'Text' for batch invocation of multiple
                // interaction in a single call. Here the command text groups the input to the 
                // multiple interactions within a special <batch> element.
                // 
                // Note that when using this method, interaction execution errors are reported in 
                // the result document and are not thrown as exceptions (see the result of the 
                // third interaction). This makes it possible to easily detect which interaction 
                // completed successfully and which failed; request processing stopped once an 
                // interaction returned an error.
                //
                // Use this method when you want to perform multiple interactions in a single call. 
                // For example, when performing multiple INSERT operations. Batching reduces the 
                // number of roundtrips over the network and increases performance.

                
    AisCommand cmd4 con.CreateCommand();
                
    cmd4.CommandType CommandType.Text;
                
    cmd4.CommandText 
    @
    "&lt;batch&gt;
        &lt;findOrder ORDER_ID='1'/&gt;
        &lt;findOrder ORDER_ID='2'/&gt;
        &lt;findOrder ORDER_ID='x'/&gt;
    &lt;/batch&gt;"
    ;  // ^-- remove the previous line to remove the error in the result

                
    string result4 cmd4.ExecuteScalar() as string;
                
    Console.WriteLine(String.Format("Result of method 4:\n{0}\n"result4));
            }
        }

    Running this program yields the following output (pretty-printed for clarity):

    PHP Code:
    Result of method 1:
    <
    findOrderResponse>
        <
    ORDER ORDER_ID="1" ORDERED_BY="Jim Bo" N_LINES="2">
            <
    ADDRESS ADDRESSEE="Jim Bo &amp; Sons, LLC." STREET="Fits Road" CITY="Oukalaka" ZIP="33212" STATE="NH" COUNTRY="USA" />
            <
    LINES LINE_NO="1" ITEM_NAME="Knife" QUANTITY="5" ITEM_PRICE="1.2" />
            <
    LINES LINE_NO="2" ITEM_NAME="Fork" QUANTITY="5" ITEM_PRICE="0.9" />
        </
    ORDER>
    </
    findOrderResponse>

    Result of method 2:
    <
    findOrderResponse>
        <
    ORDER ORDER_ID="1" ORDERED_BY="Jim Bo" N_LINES="2">
            <
    ADDRESS ADDRESSEE="Jim Bo &amp; Sons, LLC." STREET="Fits Road" CITY="Oukalaka" ZIP="33212" STATE="NH" COUNTRY="USA" />
            <
    LINES LINE_NO="1" ITEM_NAME="Knife" QUANTITY="5" ITEM_PRICE="1.2" />
            <
    LINES LINE_NO="2" ITEM_NAME="Fork" QUANTITY="5" ITEM_PRICE="0.9" />
        </
    ORDER>
    </
    findOrderResponse>

    Result of method 3:
    <
    findOrderResponse>
        <
    ORDER ORDER_ID="1" ORDERED_BY="Jim Bo" N_LINES="2">
            <
    ADDRESS ADDRESSEE="Jim Bo &amp; Sons, LLC." STREET="Fits Road" CITY="Oukalaka" ZIP="33212" STATE="NH" COUNTRY="USA" />
            <
    LINES LINE_NO="1" ITEM_NAME="Knife" QUANTITY="5" ITEM_PRICE="1.2" />
            <
    LINES LINE_NO="2" ITEM_NAME="Fork" QUANTITY="5" ITEM_PRICE="0.9" />
        </
    ORDER>
    </
    findOrderResponse>

    Result of method 4:
    <
    batchResponse>
        <
    findOrderResponse>
            <
    ORDER ORDER_ID="1" ORDERED_BY="Jim Bo" N_LINES="2">
                <
    ADDRESS ADDRESSEE="Jim Bo &amp; Sons, LLC." STREET="Fits Road" CITY="Oukalaka" ZIP="33212" STATE="NH" COUNTRY="USA" />
                <
    LINES LINE_NO="1" ITEM_NAME="Knife" QUANTITY="5" ITEM_PRICE="1.2" />
                <
    LINES LINE_NO="2" ITEM_NAME="Fork" QUANTITY="5" ITEM_PRICE="0.9" />
            </
    ORDER>
        </
    findOrderResponse>
        <
    findOrderResponse>
            <
    ORDER ORDER_ID="2" ORDERED_BY="Bim Ho" N_LINES="4">
                <
    ADDRESS ADDRESSEE="Bim &amp; Bam Tick" STREET="Zifer Av." CITY="Beterly" ZIP="45212" STATE="OR" COUNTRY="USA" />
                <
    LINES LINE_NO="1" ITEM_NAME="Left shoe #5" QUANTITY="1" ITEM_PRICE="31.2" />
                <
    LINES LINE_NO="2" ITEM_NAME="Right shoe #6" QUANTITY="1" ITEM_PRICE="31.2" />
                <
    LINES LINE_NO="3" ITEM_NAME="Flexi-sock" QUANTITY="3" ITEM_PRICE="5.2" />
                <
    LINES LINE_NO="4" ITEM_NAME="Green laces" QUANTITY="3" ITEM_PRICE="0.9" />
            </
    ORDER>
        </
    findOrderResponse>
        <
    exception origin="acx.execute" name="client.requestError">
            <
    info>[X02EFailed in converting interaction input from XML</info>
            <
    info>[X014XML document errorcannot convert value 'x' of field 'ORDER_ID'</info>
        </
    exception>
    </
    batchResponse
    There are cases where the interaction input is complex and in that case, one has to use method 3 as was shown above. An example of that is the CICS adapter where an interaction input can be something like:
    <pre>
    &lt;dfhcommarea CA_EYE_CATCH='ATCLIENT'&gt;
    &lt;ca_acct_in ca_act_id_in='111111111'/&gt;
    &lt;/dfhcommarea&gt;
    </pre>
    As the parameters method is not applicable here, a workable alternative that can be use is shown in the following snippet:

    Code:
        string findOrderReq = 
    @"&lt;findAccount CA_EYE_CATCH='{0}'&gt;
        &lt;ca_acct_in ca_act_id_in='{1}'/&gt;
    &lt;/findAccount&gt;";
    
        AisCommand cmd3a = con.CreateCommand();
        cmd3a.CommandType = CommandType.StoredProcedure;
        cmd3a.CommandText = "findAccount";
        cmd3a.Parameters.AddWithValue("xml", String.Format(findOrderReq, "ATCLIENT", "111111111"));
        string result3a = cmd3a.ExecuteScalar() as string;
    Last edited by DrorHarari; 12-08-2012 at 01:34 PM.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •