asked on

Visual Studio 2008 connecting to Oracle database

I have this attached code (ASP.net C# code) that I use to connect to a Oracle database to get some table's total record count. When I run in debug it shows that the value of "count" variable below is 1.
It should have been 215.

How do I troubleshot to see if it is really connecting to the db or not?

Also attached the partial content of the DL.cs file:

namespace WebApplication1
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DL bDL = new DL();
            string sqlBranch = "select count(*) from mytab1";
            DataSet dsBranch = bDL.getDatasetFromJira(sqlBranch);
            string count;
            //count = dsBranch.Tables[0].Rows.Count();
            if (dsBranch.Tables[0].Rows.Count > 0)
            {
                count = dsBranch.Tables[0].Rows.Count.ToString();
            }

        }
    }
}

Open in new window

public DataSet getDatasetFromJira(string sql)
        {
            DataSet ds = new DataSet();
            OracleConnection sconn = new OracleConnection();
            sconn.ConnectionString = this.DSN_JIRA;
            sconn.Open();

            OracleCommand scmd = new OracleCommand();
            scmd.Connection = sconn;
            scmd.CommandText = sql;
            scmd.CommandType = CommandType.Text;


            OracleDataAdapter sda = new OracleDataAdapter();
            sda.SelectCommand = scmd;
            try
            {
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
            finally
            {
                if (sconn != null)
                    sconn.Dispose();

                if (scmd != null)
                    scmd.Dispose();

                if (sda != null)
                    sda.Dispose();
            }
            return ds;
        }

Open in new window

public string DSN_JIRA
        {
            get
            {
                return ConfigurationSettings.AppSettings["JIRADMConnectString"].ToString();
            }
        }

In web.config:
	<appSettings>
		<add key="JIRADMConnectString" value="Data Source=JIRA-DM-TESTDB;Persist Security Info=True;User ID=myact;Password=mypwd;"/>

Open in new window

kaufmed

You query does a COUNT(*), which is a scalar value and hence only 1 record. You are not selecting multiple rows (nor should you) when you do a COUNT query. I can't say I would have used the approach you have here, but to inspect the value of the COUNT, do this:

namespace WebApplication1
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DL bDL = new DL();
            string sqlBranch = "select count(*) from mytab1";
            DataSet dsBranch = bDL.getDatasetFromJira(sqlBranch);
            string count;

            if (dsBranch.Tables[0].Rows.Count > 0)
            {
                count = dsBranch.Tables[0].Rows[0][0].ToString();
            }

        }
    }
}

Open in new window

kaufmed

You are not selecting multiple rows (nor should you) when you do a COUNT query.

To expand on this, I should say that when you do a COUNT query, you are asking the DB to return you one single value: the number of records that satisfy the WHERE clause of the query, or the total number of records in the table if there is no WHERE clause.

For instance, if I had 3 records in my table:

ID  FNAME   LNAME
1   John    Doe
2   Jane    Doe
3   Jimmy   Smiles

Open in new window

and I did a COUNT query on this talbe as:

SELECT COUNT(1) FROM myTable

Open in new window

The result I would get back would be:

Open in new window

toooki

ASKER

Thanks a lot for reply and explanation.
I wanted the count(*) in the query only to see if the program is really connecting to the database.
Actually I will need the dataset to have the content of the entire table.
When I use the query:
string sqlBranch = "select * from mytab1";
And run in debug mode I expected to see the "count" under the right click menu to show the number 215.
Otherwise in debug mode how do I check if the dataset is empty or got some returned values?
I attached the screnshot when debugging.

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

toooki

ASKER

Many thanks.
I did not see Tables->Non-Public members->List->Rows->Count
Under Tables->Non-Public members-> There is no "List".
However, Immediate window ( Ctrl + Alt + i ) worked. And it let me get what I wanted.
Thanks.

kaufmed

There is no "List".

Interesting. My screenshot was from VS 2008. Admittedly, I did botch the list of elements to expand: I forgot to include the first table inside of List (but you can see such in the screenshot).

In any event, glad you got what you needed = )