Sending XML string as an input parameter to Oracle Stored Procedure from VB.NET

Sending XML string as an input parameter to Oracle Stored Procedure from VB.NET Overview: This document will guide you to insert or update multiple rows into the oracle stored database using VB.NET code. This is common requirement across any project when you have to insert or update multiple rows in one shot from the code. This is especially true when you are working on web application where you have a restriction not to hit database multiple time. Using this document you can create VB/C#.NET program to achieve this. This document will also gives sample Oracle Store procedure. Introduction: Passing XML string as an input parameter to Oracle Stored procedure is always need some research. This way we have to spend lots of time to really understand the architecture and flow. To save research and other POC development time, I am trying to give very simple way to describe how we can pass XML string as an input parameter to oracle stored procedure using VB.NET code. You can convert this code in C#.NET also. Assume you have a requirement; to insert 10 rows into the database; how you do? Normally you execute stored procedure 10 times to insert 10 rows into the database table from the code. While doing this your application will hit the performance and slow down the process especially in case of web applications. Here I am giving the best way to pass multiple rows into the database table from VB.NET; you have to call oracle stored procedure only one time. This method you can use for insert/update multiple rows/data into the database table. Microsoft Application Block use "System.Data.OracleClient"for oracle stored procedure execution and this namespace not allow to use "OracleDbType.XmlType" datatype, that's requires for sending XML string as a input parameter into the oracle stored procedure. You require "Oracle.DataAccess.Client" namespace to use "OracleDbType.XmlType" data type and pass XML value into the Oracle Stored procedure. Using this document you can modify application block using following lines of code. VB.NET 2.0 Source Code Name Space Imports Oracle.DataAccess.Client Imports System.Text VB.NET Code Dim oraCmd As New OracleCommand Dim oraCon As New OracleConnection Dim recordEffected As Integer Dim XMLString As String try { 'Generate XML String, here I am taking 2 rows in the xml 'This XML can be taken from XML file or can be generated using .NET 'Serialization XMLString.Append("<?xml version='1.0' encoding='utf-8' ?>") XMLString.Append("<EmployeeData>") XMLString.Append("<Root>") XMLString.Append("<EmpName>Ritesh</EmpName>") XMLString.Append("<EmpSal>10000</EmpSal>") XMLString.Append("<DeptNo>1010</DeptNo>") XMLString.Append("<JoinDate>03-Apr-2006</JoinDate>") XMLString.Append("</Root>") XMLString.Append("<Root>") XMLString.Append("<EmpName>Rajiv</EmpName>") XMLString.Append("<EmpSal>20000</EmpSal>") XMLString.Append("<DeptNo>2020</DeptNo>") XMLString.Append("<JoinDate>05-May-2007</JoinDate>") XMLString.Append("</Root>") XMLString.Append("</EmployeeData>") 'Create the connection String oraCon.ConnectionString = "Data Source=DNSName;UserID=UID;Password=PWD;" oraCon.Open() oraCmd.CommandType = CommandType.StoredProcedure oraCmd.CommandText = "INSERT_EMPLOYEE_DATA" ' Oracle SP Name oraCmd.Connection = oraCon oraCmd.Parameters.Add("ip_emp_details", OracleDbType.XmlType, XMLString, ParameterDirection.Input) ' Oracle SP Input Parameter name 'Execute the Stored Procedure recordEffected = oraCmd.ExecuteNonQuery() catch { throw; } } Finally { oraCon.Close() oraCon.Dispose() } Oracle Stored Procedure Oracle stored procedure will be different for different XML format,but the logic for facthing the XML node value will be the same.According to above XML format used the oracle stored procedure will be as following Oracle Database Table CREATE TABLE employeeDetails ( EmpName VARCHAR2(50), EmpSal INTEGER, DeptNo INTEGER, JoinDate DATE ) Oracle Stored Procedure for Insert data CREATE OR REPLACE PROCEDURE "INSERT_EMPLOYEE_DATA" ( ip_emp_details IN XMLTYPE ) IS BEGIN FOR i IN ( SELECT XMLTYPE.EXTRACT (VALUE (a), '/Root/EmpName/text()').getstringval() AS ipEmpName, XMLTYPE.EXTRACT (VALUE (a), '/Root/EmpSal/text()').getstringval() AS ipEmpSal, XMLTYPE.EXTRACT (VALUE (a), '/Root/DeptNo/text()').getstringval () AS ipDeptNo, XMLTYPE.EXTRACT (VALUE (a), '/Root/JoinDate/text()').getstringval () AS ipJoinDate FROM TABLE (XMLSEQUENCE (ip_emp_details.EXTRACT ('/EmployeeData/Root') ) ) a ) LOOP INSERT INTO employeeDetails (EmpName, EmpSal, DeptNo,JoinDate) VALUES (i.ipEmpName, i.ipEmpSal, i.ipDeptNo,i.ipJoinDate); END LOOP; END INSERT_EMPLOYEE_DATA; / Oracle Stored Procedure for Update data CREATE OR REPLACE PROCEDURE "UPDATE_EMPLOYEE_DATA" ( ip_emp_details IN XMLTYPE ) IS BEGIN FOR i IN ( SELECT XMLTYPE.EXTRACT (VALUE (a), '/Root/EmpName/text()').getstringval() AS ipEmpName, XMLTYPE.EXTRACT (VALUE (a), '/Root/EmpSal/text()').getstringval() AS ipEmpSal, XMLTYPE.EXTRACT (VALUE (a), '/Root/DeptNo/text()').getstringval () AS ipDeptNo, XMLTYPE.EXTRACT (VALUE (a), '/Root/JoinDate/text()').getstringval () AS ipJoinDate FROM TABLE (XMLSEQUENCE (ip_emp_details.EXTRACT ('/EmployeeData/Root') ) ) a ) LOOP UPDATE employeeDetails SET EmpName = i.ipEmpName, EmpSal= i.ipEmpSal, JoinDate = i.ipJoinDate WHERE DeptNo = i.ipDeptNo; END LOOP; END UPDATE_EMPLOYEE_DATA; /

SQL Server Database
If you are working with SQL Server as a Database and want to do same operation as above then see the following link
http://www.riteshkk2000.blogspot.com/2005/06/sent-multiple-data-to-storeprocedure.html