Export (0) Print
Expand All

Performing an Insert Operation by Using the WCF Channel Model

This section shows how to insert a record into an Oracle database by using a channel. You must specify both a message body and a message action when you send a message.

The Insert Message

The following XML shows a message body for an Insert operation on the HR.EMPLOYEES table. The record set consists of a single employee record. For more information about the schema of an Insert message, see Message Schemas for the Basic Insert, Update, Delete, and Select Operations on Tables and Views. This is the contents of the Employee_Insert.xml file used in the example.

<!-- New namespace: http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES -->
<Insert xmlns="http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES">
    <RECORDSET xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
        <EMPLOYEESRECORDINSERT>
            <EMPLOYEE_ID>0</EMPLOYEE_ID>
            <FIRST_NAME>Anton</FIRST_NAME>
            <LAST_NAME>Kirilov</LAST_NAME>
            <EMAIL></EMAIL>
            <PHONE_NUMBER>555-0198</PHONE_NUMBER>
            <HIRE_DATE>2007-03-01T00:00:00.0000000</HIRE_DATE>
            <JOB_ID>FI_ACCOUNT</JOB_ID>
            <SALARY>5000</SALARY>
            <COMMISSION_PCT>0.15</COMMISSION_PCT>
            <MANAGER_ID>108</MANAGER_ID>
            <DEPARTMENT_ID>100</DEPARTMENT_ID>
       </EMPLOYEESRECORDINSERT>
    </RECORDSET>
</Insert>

Specifying the Message Action

You must specify a message action when you send a SOAP message to the Oracle Database adapter. You can specify the message action when you create the message as in the following example.

Message messageIn2 = Message.CreateMessage(MessageVersion.Default, "http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES/Insert", readerIn2);

The message action in this example, "/HR/Table/EMPLOYEES/Insert", specifies that an Insert operation on the HR.EMPLOYEES table is to be performed

Sending the Insert Message

This example shows how to perform an Insert operation on an Oracle table over a channel. The code uses the SQLEXECUTE operation exposed by the Oracle Database adapter to return the next value of an Oracle SEQUENCE. This value is then written to the EMPLOYEE_ID field in the Insert record. This pattern enables you to insert rows into databases that have an auto-generated primary key value. For more information about invoking the SQLEXECUTE operation over a channel, see Performing a SQLEXECUTE Operation by Using the WCF Channel Model.

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.IO;
 
using System.ServiceModel;
using System.ServiceModel.Channels;
 
using Microsoft.ServiceModel.Adapters;
using Microsoft.Adapters.OracleDB;
 
namespace OracleDMLChannel
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create Endpoint
            EndpointAddress address = new EndpointAddress("oracledb://ADAPTER");
 
            // Create Binding
            OracleDBBinding binding = new OracleDBBinding();
 
            // Create Channel Factory
            ChannelFactory<IRequestChannel> factory = new ChannelFactory<IRequestChannel>(binding, address);
            factory.Credentials.UserName.UserName = "HR";
            factory.Credentials.UserName.Password = "TIGER";
            factory.Open();
 
            // Create Request Channel
            IRequestChannel channel = factory.CreateChannel();
            channel.Open();
 
            // Send Request
            System.Xml.XmlReader readerIn = System.Xml.XmlReader.Create("SQLExecute.xml");
 
            Message messageIn = Message.CreateMessage(MessageVersion.Default, "http://Microsoft.LobServices.OracleDB/2007/03/SQLEXECUTE", readerIn);
            Message messageOut = channel.Request(messageIn);
                        
            // Get Response XML
            XmlReader readerOut = messageOut.GetReaderAtBodyContents();
 
            // Get Employee ID
            string id = null;
            XmlDocument doc = new XmlDocument();
            doc.Load(readerOut);
            XmlNodeList list = doc.GetElementsByTagName("ColumnValue");
            if (list.Count > 0) id = list[0].InnerXml;
 
            // Compose Insert XML
            XmlDocument insertDoc = new XmlDocument();
            insertDoc.Load("Employee_Insert.xml");
 
            // Change Employee ID
            XmlNodeList empidList = insertDoc.GetElementsByTagName("EMPLOYEE_ID");
            XmlNode empidNode = empidList[0];
            empidNode.InnerXml = id;
 
            // Change email
            XmlNodeList emailList = insertDoc.GetElementsByTagName("EMAIL");
            XmlNode emailNode = emailList[0];
            emailNode.InnerXml = "scotty" + id + "@microsoft.com";
 
            // Change date
            XmlNodeList dateList = insertDoc.GetElementsByTagName("HIRE_DATE");
            XmlNode dateNode = dateList[0];
            dateNode.InnerXml = "2007-03-01T00:00:00.0000000";
 
            StringReader strReader = new StringReader(insertDoc.InnerXml);
            XmlReader readerIn2 = XmlReader.Create(strReader);
 
            // Send XML
            Message messageIn2 = Message.CreateMessage(MessageVersion.Default, "http://Microsoft.LobServices.OracleDB/2007/03/HR/Table/EMPLOYEES/Insert ", readerIn2);
            Message messageOut2 = channel.Request(messageIn2);

            // Close the messages
            messageOut.Close();
            messageOut2.Close();
 
            channel.Close();
        }
    }
}

See Also

© 2014 Microsoft Corporation. All rights reserved.
Show:
© 2014 Microsoft