CODEWITHSUNDEEP
c#databaseoracle-database
Karanveer Plaha
Karanveer Plaha

Reputation: 65

How to check whether query gets a result or not

I'm trying to grab some data from a database using a simple 'select' query but I'm not getting any data back. When I run the same query letter by letter in the Toad editor for oracle, I get the data just fine.

In C#

OdbcDataReader reader = cmd.ExecuteReader(); //cmd contains the query
DataTable dtHRSinfo = new DataTable();

while (reader.Read()) // no lines to read
{
    dtHRSinfo.Rows.Add(reader); // never comes to this statement!
}

EDIT: I also have this alternate method that works for my other queries but not for this one. This method also doesn't give any values to the data table.

dtHRSinfo.Load(cmd.ExecuteReader());

The query

select unitid,lasttimeon,lasttimeoff,currentlyon,currentcallontime, currentcallofftime

from opr.mktunit

I don't get any errors or exceptions. The db reader never gets the values and they are never assigned to the datatable. I don't know what I am doing wrong.

The DB connection 

public bool OpenDBConnections(string user, string pass)
    {
        try
        {
            this.Log("Connection to Database");
            cnOprPrd = new OdbcConnection();
            cnOprPrd.ConnectionString = @"Driver={Microsoft ODBC for Oracle};Server=OPRPRD;Uid=" + user + ";Pwd=" + pass;
            cnOprPrd.Open();
            //INPUT FOLDER
            sqlFolder = AC2_SQL_FOLDER;

            return true;
        }
        catch (Exception e)
        {
            Log(e.Message);
            return false;
        }
    }

UPDATE:

I fixed the problem by typing in my command explicitly into a string variable rather than using a Streamreader to read the string from a file.

The issue may have arisen from some unrecognizable character sequences from the streamreader. Thanks for the answers, guys.

Upvotes: 0

Views: 1451

Answers (3)

Aaron
Aaron

Reputation: 686

from What I suggest if you can connect to your Database this should be simple try this 

DataSet ds = new DataSet();
OracleConnection connection;
OracleDataAdapter OracleAdapter;
connection = new OracleConnection(ConfigurationManager.AppSettings["ConnectionString"]);
        connection.Open();
        OracleAdapter = new OracleDataAdapter(ConfigurationManager.AppSettings["your select statement"], ConfigurationManager.AppSettings["ConnectionString"]);
        OracleAdapter.Fill(ds, "your table name");

using the fill method will populate all data from database table into you dataset.

to use configurationManager.Appsetting refrence to 

using System.Configuration;

add a configuration file and externalize your select query use the key value tags in 

<appSettings>
<addKey="your select statement" value 
"select unitid,lasttimeon,lasttimeoff,currentlyon,currentcallontime, currentcallofftime
from opr.mktunit"/>

see if this works for you let me know. Cheers

Upvotes: 1

D Stanley
D Stanley

Reputation: 152556

This block:

while (reader.Read()) // no lines to read
{
    dtHRSinfo.Rows.Add(reader); // never comes to this statement!
}

looks odd. I've never seen an extension method that adds rows to a data table one row at a time directly from a data reader.

I would expect to see the Load method used instead:

OdbcDataReader reader = cmd.ExecuteReader(); //cmd contains the query
DataTable dtHRSinfo = new DataTable();

dtHRSinfo.Load(reader);

My guess is that it's using an overload of Add that is implicitly adding the value of reader.ToString() to the first column.

Upvotes: 1

Haney
Haney

Reputation: 34802

It's likely your connection string. Debug the cmd object or show us the code where you set the connection string. We also need to talk about Disposing unmanaged resource classes... Use the using syntax to ensure that your connections are properly cleaned up:

using (OdbcDataReader reader = cmd.ExecuteReader())  //cmd contains the query
{
    DataTable dtHRSinfo = new DataTable();

    while (reader.Read()) // no lines to read
    {
        dtHRSinfo.Rows.Add(reader); // never comes to this statement!
    }
}

More about the using statement here

Upvotes: 1

Related Questions