Retriving records from Oracle table without using Views  
Author Message
krisvenki





PostPosted: .NET Framework Data Access and Storage, Retriving records from Oracle table without using Views Top

Hi,

In our application we need to retrieve first 50 records from oracle table for the specified filter criteria.
before retrieving the records I need to sort the records, then apply filter. for this scenario we can use view which contains all the records sorted by the fields or we can use sub querries.
but sub querries are not suppported by .NET. instead of using views is there any way to achieve this.

Regards,

Venki.




.NET Development31  
 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Retriving records from Oracle table without using Views Top


You can certainly use sub queries in PL/SQL, either in a stored procedure or when executing a SQL statement, so I'm not sure what you mean when you say that .NET doesn't support them.

 
 
krisvenki





PostPosted: .NET Framework Data Access and Storage, Retriving records from Oracle table without using Views Top

Below is the code snippet in which i tried to use nested query with FillSchema. if I run this, it throws exception. If I remove the nested query, it works fine.

OracleConnection conn = new OracleConnection("Data Source=DB;User Id=abc;password=abc;");

//string query = "select emp_id, emp_name_1 from employee where emp_name_1 like 'ABB%' and rownum != 10 order by emp_name_1";

string query = "select emp_id, emp_name_1 from (select * from employee where emp_name_1 like 'ABB%' order by emp_name_1) a where rownum != 10";

OracleDataAdapter da = new OracleDataAdapter(query, conn);

DataSet ds = new DataSet();

da.FillSchema(ds, SchemaType.Mapped, "table");

da.Fill(ds);

dataGrid1.DataSource = ds.Tables[0];

Regards,

Venki



 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Retriving records from Oracle table without using Views Top


Did you check the exception to see if it was an Oracle error Maybe you could post the exception messages.

 
 
Matt Neerincx





PostPosted: .NET Framework Data Access and Storage, Retriving records from Oracle table without using Views Top

Oracle supports sub-queries I am pretty sure, look at this for example:

http://www.onlamp.com/pub/a/onlamp/2001/07/26/aboutSQL.html

.NET does not restrict what SQL you send to Oracle.