ExecuteReader with Oracle

Topics: Data Access Application Block
Feb 7, 2007 at 2:03 PM
Edited Feb 7, 2007 at 2:03 PM
Hello,

ExecuteReader does not work with Oracle ...

I create the select, and call ExecuteReader but any data return, if I use ExecuteDataSet no problem.

1-Fail
-------

' cria a script sql
_SqlScript.Remove(0, _SqlScript.Length)
SqlScript.Append("select c.NOMRAZAO_SOC")
SqlScript.Append(" from odonto.atosclin a, odonto.orcam o, odonto.vodo_creden c")
_SqlScript.Append(" where a.orcamento = o.orcamento")
SqlScript.Append(" and a.codcrm = c.cod_crm")
SqlScript.Append(" and c.codcrm = ")
_SqlScript.Append(pLiberacao.ToString())

' executa comando
_rs = db.ExecuteReader(CommandType.Text, _SqlScript.ToString)


2- Works fine

' cria a script sql
_SqlScript.Remove(0, _SqlScript.Length)
SqlScript.Append("select c.NOMRAZAO_SOC")
SqlScript.Append(" from odonto.atosclin a, odonto.orcam o, odonto.vodo_creden c")
_SqlScript.Append(" where a.orcamento = o.orcamento")
SqlScript.Append(" and a.codcrm = c.cod_crm")
SqlScript.Append(" and c.codcrm = ")
_SqlScript.Append(pLiberacao.ToString())

' define comando
sqlExecCmd = db.GetSqlStringCommand(SqlScript.ToString)

' executa comando
rs = db.ExecuteDataSet(sqlExecCmd)


Why :::

Paulo Moreno
Feb 12, 2007 at 3:42 PM
Edited Feb 12, 2007 at 3:43 PM
It is difficult to debug what the problem might be from what you have provided above.

With ExecuteReader you need to either bind the IDataReader Object or loop through the records to get the data

while (_rs.Read())
{
	// Do Something...
}

You may want to try a different approach and use the wonderful DataTable.Load Method that will load a DataTable from an IDataReader (ADO.NET 2.0 Feature)

DataTable.Load(IDataReader datareader)


DataTable - Load from IDataReader - CreateDataReader - ReadXml WriteXml


Hence you might do:

DataTable dt = new DataTable();
 
using (IDataReader _rs = db.ExecuteReader(CommandType.Text, _SqlScript.ToString))
{
    dt.Load(_rs);
}

and then see if you get Data in the DataTable using the DataSet Debugger Visualizer in Visual Studio.

Dave

_____________________

David Hayden
Microsoft MVP C#