.Net with Oracle : Working with Multiple Result Sets

Working with Multiple Result Sets

Oracle does not support batch queries, so you cannot return multiple result sets from a command. With a stored procedure, returning multiple result sets is similar to returning a single result set; you have to use REF CURSOR output parameters. To return multiple result sets, use multiple REF CURSOR output parameters.

The package spec that returns two result sets—all EMPLOYEES and JOBS records—follows:

CREATE OR REPLACE PACKAGE SELECT_EMPLOYEES_JOBS AS

TYPE T_CURSOR IS REF CURSOR;

PROCEDURE GetEmployeesAndJobs (

    cur_Employees OUT T_CURSOR,   

    cur_Jobs OUT T_CURSOR

);

END SELECT_EMPLOYEES_JOBS;

The package body follows:

CREATE OR REPLACE PACKAGE BODY SELECT_EMPLOYEES_JOBS AS

PROCEDURE GetEmployeesAndJobs

(

    cur_Employees OUT T_CURSOR,

    cur_Jobs OUT T_CURSOR

)

IS

BEGIN

    -- return all EMPLOYEES records

    OPEN cur_Employees FOR

    SELECT * FROM Employees;

    -- return all JOBS records

    OPEN cur_Jobs FOR

    SELECT * FROM Jobs;

END GetEmployeesAndJobs;

END SELECT_EMPLOYEES_JOBS;

The following code shows how to fill two related tables in a DataSet using the two result sets returned from the above package:

// create the connection

OracleConnection conn = new OracleConnection("Data Source=oracledb;

    User Id=UserID;Password=Password;");

// define the command for the stored procedure

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

cmd.CommandText = "SELECT_EMPLOYEES_JOBS.GetEmployeesAndJobs";

// add the parameters including the two REF CURSOR types to retrieve

// the two result sets

cmd.Parameters.Add("cur_Employees", OracleType.Cursor).Direction =

    ParameterDirection.Output;

cmd.Parameters.Add("cur_Jobs", OracleType.Cursor).Direction =

    ParameterDirection.Output;

cmd.CommandType = CommandType.StoredProcedure;

// create the DataAdapter and map tables

OracleDataAdapter da = new OracleDataAdapter(cmd);

da.TableMappings.Add("Table", "EMPLOYEES");

da.TableMappings.Add("Table1", "JOBS");

// create and fill the DataSet

DataSet ds = new DataSet();

da.Fill(ds);

// create a relation

ds.Relations.Add("EMPLOYEES_JOBS_RELATION",

   ds.Tables["JOBS"].Columns["JOB_ID"],

   ds.Tables["EMPLOYEES"].Columns["JOB_ID"]);

// output the second employee (zero-based array) and job title

// based on the relation

Console.WriteLine("Employee ID: " +

    ds.Tables["EMPLOYEES"].Rows[1]["EMPLOYEE_ID"] +

    "; Job Title: " +

    ds.Tables["EMPLOYEES"].Rows[1].GetParentRow(

    "EMPLOYEES_JOBS_RELATION")["JOB_TITLE"]);

The console output shows the job title for the second employee:

Employee ID: 101; Job Title: Administration Vice President

XPATH Injection .net

Just like Sql Injection. Xpath injection is a type of attack where hacker can overtake the authentication of the site and try to inject melisious

XPath which may reveal the whole XML and hence display all the required values without proper authentication. Possible solution of these kind of attacks. 1. Validating the Input. 2. Modifying the XPath quite similar to the Sql Parameterized queries so, that changes of hacking it with wrong input will be minimised.

.NET has everything for doing XPath selections that way. But unfortunately the XsltContext API isn't really intuitive one and is poorly documented. Happily there are XML MVPs around :). So Daniel Cazzulino has created handy DynamicContext class, which you can find in recently released Mvp.Xml v1.0 library, particulary in theMvp.Xml.Common.XPath namespace. Read excellent Daniel's explanation for more info. I only want to show you couple of lines that leverage that class. Instead of crappy"//customer[@name='" + txtUser.Text + "' and @password='" + txtPassword.Text + "']" you can have shiny clear "//customer[@name=$name and @password=$password]", precompiled and bulletproof!

//Can be done at initialization time 
string xpath = "//customer[@name=$name and @password=$password]";
XPathExpression expr = DynamicContext.Compile(xpath);

//Run-time
DynamicContext ctx = new DynamicContext();
ctx.AddVariable("name", txtUser.Text);
ctx.AddVariable("password",txtPasowrd.Text);
expr.SetContext(ctx);
XPathNodeIterator custData = customers.Select(expr);

And you don't even have to validate user input here - it's all done for free.

Go download Mvp.Xml and start to play with its classes, there are some gems there that can save you hours of coding and make your code faster and safer. And be aware of XPath injection attack and ways to mitigate it in .NET.

Update from Daniel Cazzulino:

Better yet, they can directly use the XPathCache class (1 line of code!!!):

XPathNodeIterator custData = XPathCache.Select(
    "//customer[@name=$name and @password=$password]",
    customersDocument,
    new XPathVariable("name", txtName.Text), 
    new XPathVariable("password", txtPassword.Text));

And all will be equally precompiled, cached and secure :) . There is an overload for each need, and you can do pretty anything with a single line.

YII Framework of PHP with MySql a gr8 alternative to Asp.net MVC

Hi Guys,

I was fond of asp.net mvc as I worked on many applications of asp.net MVC. but later i realised that customers are moving more on PHP as its not that costly for them to afford hosting packages of Linux Servers.

Inbetween I was also fond of Asp.net MVC which have saperation of consern in form of Model views and controller. But later i found out that there is a framework for PHP which is Also in MVC pattern. This framework is called YII framework and its quite similar.

As, all people working in Asp.net MVC must have realised that client side scripting and html can be share between PHP and asp.net projects and hence asp.net MVC developers can easily switch to PHP as PHP is not difficult languge to learn.

you can find the link for YII framework below http://www.yiiframework.com/

Also, try YII with APC extension for best performance of the application. try XAMPP tool to install PHP,APACHE and MySql server on your machine and try out these framework by yourself.

Thanks & Regards, Sam