C# Query From GeoDatabase to Return Single Value (Arc Objects)

[JamariPowers]

Using C#, arcobjects and IQueryDef, I've been learning how to write queries to return rowsets and multiple results. I would also use ICursor and Irow to loope through each record from the cursor. This is great for multiple results but I am having trouble returning one result.

I want to run a query to return a single integer from a query. Would I need to have ICursor or IRow still? I've been looking online for examples but everything incorporates a ICursor / IRow.

I was using something along these lines:

private static List<object> GenericQuery(string sQueryColumn, string sFrom, string sWhere)
{

// connect to workspace

List<object> myObjects = new List<object>();

IQueryDef pQD = pFeatureWorkspace.CreateQueryDef();
pQD.Tables = sFrom;
pQD.SubFields = sQueryColumn;
pQD.WhereClause = sWhere;

ICursor pCursor = pQD.Evaluate();

//Loop through each of the records in the cursor                        
            IRow pRow = pCursor.NextRow();
            while (pRow != null)
            {
                object myObject = myObject = pRow.get_Value(lIndex);
                myObjects.Add(myObject);
                //Increment the cursor
                pRow = pCursor.NextRow();
            }

Of course, there would be an actual table name, subfields, and a where clause there. I would use ICursor to evaluate my query def but I just want one result. So if I created a function that returned an integer with a query that just returned an integer, would I still need ICursor / IRow?

Not sure how to go about this. Not too many examples online. Any feedback on this would be greatly appreciated. Thanks in advance.

[ModyBuchbinder]

Hi Jamari

Yes, you open a cursor the same way and do NextRow only once.
You do not need to loop if you are sure there is only one result.

Have fun
Mody

[AhmedEl-Sisi]

You can get your first record using your cursor, just get your next row for one time and then get your integer value.

    

   private static int GenericQuery(string sQueryColumn, string sFrom, string sWhere)
        {
            ICursor pCursor = null;
            try
            {
                IQueryDef pQD = pFeatureWorkspace.CreateQueryDef();
                pQD.Tables = sFrom;
                pQD.SubFields = sQueryColumn;
                pQD.WhereClause = sWhere;

                pCursor = pQD.Evaluate();

                //Get the first Row
                IRow pRow = pCursor.NextRow();
                //Get your integer value
                if (pRow != null)
                {
                    object myObject = pRow.get_Value(lIndex);
                    if (myObject != null)
                    {

                        return Convert.ToInt32(myObject);
                    }
                }
                return -1;//Default value
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                if (pCursor!=null)
                {
                    Marshal.FinalReleaseComObject(pCursor);
                }
            }
          
        }

[JamariPowers]

Thanks for your input Mody and cc4ever.

THe logic and the code snippet does makes sense and it does make more sense to just do NextRow once (and not, per say, loop through it) if you're trying t odisplay one value from a query. Just wanted some kind of confirmation on which direction to go and if there was another method besides ICursor and IRow to get a single value.

THanks alot. I'll give this a shot and try a few examples and see what I come up with.

[JamariPowers]

So I've put together the following code:

public static int FirstQuery()
         {
            int _val = 0;
            ICursor _pCursor = null;
            try
            {

                IFeatureWorkspace _pfeatureWorkspace = Utilities.connect();
                /*
                * Query below will iterate through several rows of returned data from query
                * I need a query to return a single value
     */
                IQueryDef _pdq = _pfeatureWorkspace.CreateQueryDef();
                _pdq.Tables = PersonsTable.TableName;
                _pdq.SubFields = PersonsTable.PERSON_ID;
                _pdq.WhereClause = PersonsTable.FIRST_NAME = "JAMARI";

                _pCursor = _pdq.Evaluate();
                int lIndex = _pCursor.FindField(_pdq.SubFields);

                IRow _pRow = _pCursor.NextRow();
                if (_pRow != null)
                {
                    object _myObject = _pRow.get_Value(lIndex);
                    if (_myObject != null)
                    {
                        _val = Convert.ToInt32(_myObject);
                        return _val;
                    }
                }

                return _val;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                if (_pCursor != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(_pCursor);
                }
            }
}

This is a function that runs a query to output the id associated to my name (We have a PERSONS table with names and Id's in our database). I am passing this value to another function that outputs the value in a simple MessageBox. That function is below:

 protected override void OnClick()
        {
            int _queryVal = 0;
            MessageBox.Show("Jamari was here");
            _queryVal = BasicQuery.FirstQuery();

            MessageBox.Show("Query result is: " + _queryVal);
        }

However, everytime I run it, it outputs the value -1. Am I missing something? I should get a value other than -1 because I know the ID associated with my name. I've been trying and changing and testing different things but nothing is quite working. Could it be the way I have my query set up; particularly in my lIndex variable? I'm almost there, but I have one minor road block that I'm just not seeing yet.

[AhmedEl-Sisi]

you should debug your code to catch the exception, but at first glance you can check this line
 

 _pdq.WhereClause = PersonsTable.FIRST_NAME = "JAMARI"

it should be something like the following:

   _pdq.WhereClause = PersonsTable.FIRST_NAME + " = 'JAMARI'";

[JamariPowers]

I've made the following change to the syntax of the query. But now I have another issue. Because I have a variable (int _val) and assigned it to 0, thats the only value thats being returned, 0. Whatever I assign _val to, whether 0 or 100, it outputs that value. It is not being used properly in my code. I'll have to trace through some more.

In the same sense, if I don't assign it to a value at the top, of course I get an error when I use it at the bottom right before the curly beacket in the "Try" function. (Error: Use of unassigned local variable '_val'

So, yes, I'll have to trace through to see what the issue is.

Thanks for your help though.

------------------

I actually just realized my own mistake in why I wasn't getting the wrong value. My query wasn't returning anything because there was no data in it. A database change, (that I wasn't aware of) had occurred where we are migrating data to and from different tables. Database 101, make sure the table you are querying has data in it. lol

But again, thanks for your assistance on everything.