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;
/ |