Jamari_L_Powers

C# Error Using ISqlWorkspace for Arcmap 10.0

Discussion created by Jamari_L_Powers on May 7, 2014
I'm trying to run a query using the ArcObjects Interface ISqlWorkspace for Arcmap 10.0, but I keep receiving two different errors (depending on which line of code is commented out). My code is below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ESRI.ArcGIS.Carto;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.DataSourcesGDB;
using ESRI.ArcGIS.esriSystem;

namespace OracleQueryPrototype
{
    public class cmdRunQuery : ESRI.ArcGIS.Desktop.AddIns.Button
    {
        public string strQuery = @"select emergency_equipment_type,
sum(AVAILABLE_COUNT) as available_count,
sum(AVAILABLE_COUNT_LENGTH) as available_count_length
FROM MY_TABLE
GROUP BY emergency_equipment_id, emergency_equipment_type";

        public cmdRunQuery()
        {
        }

        protected override void OnClick()
        {
            try
            {
                ArcMap.Application.CurrentTool = null;
                IWorkspace myWorkspace = GetProductionEGISWorkspace();
                if (myWorkspace is SqlWorkspace)
                {
                    MessageBox.Show("Is a SQL Workspace");

                    // LOOK HERE
                    //ISqlWorkspace pSQL_WS = (ISqlWorkspace)myWorkspace;
                    ISqlWorkspace pSQL_WS = myWorkspace as ISqlWorkspace;

                    ICursor pCursor = pSQL_WS.OpenQueryCursor(strQuery);
                    string cursorResults = "";
                    IRow pRow;
                    while ((pRow = pCursor.NextRow()) != null)
                    {
                        int fc = pRow.Fields.FieldCount;
                        for (int x = 0; x < fc; x++)
                        {
                            cursorResults += pRow.get_Value(x).ToString() + "     ";
                        }
                        cursorResults += Environment.NewLine;
                    }
                    MessageBox.Show(cursorResults, "Query result");
                }
                else
                {
                    MessageBox.Show("Is not a SQL Workspace");
                }
            }
            catch (Exception pException)
            {
                MessageBox.Show("Oops, that wasn't supposed to happen. "
                    + Environment.NewLine + Environment.NewLine
                    + pException.ToString()
                    + Environment.NewLine + Environment.NewLine
                    + "Press Ctrl+C to copy this error message.",
                    "An unexpected error has occurred in JambWow.");
            }
        }
        protected override void OnUpdate()
        {
            Enabled = ArcMap.Application != null;
        }

        //Create a workspace for the production EGIS  database
        private static IWorkspace GetProductionEGISWorkspace()
        {
     
        string strProdEGIS_SDE_Path = @"C:\Users\Me\Desktop\DynamicText\FolderA\Folder_A.2\config\MySDE.sde";


            IWorkspaceFactory pWorkspaceFactory = new ESRI.ArcGIS.DataSourcesGDB.SdeWorkspaceFactoryClass();
            IWorkspace pProdEGISWorkspace = pWorkspaceFactory.OpenFromFile(strProdEGIS_SDE_Path, 0);
            return pProdEGISWorkspace;
        }
    }

}


Notice in the code my comment that says "// LOOK HERE". The two lines below are essentially the same line I believe. One is voided out and one isn't. When I run this code as is, first I see a message box that says "Is a SQL Workspace", as it should. After that, I get an error that says:

System.NullReferenceException: Object reference not set to an instance of an object.

The error is in relation to the line of code UNDER the line that isn't voided out: ICursor pCursor = pSQL_WS.OpenQueryCursor(strQuery);

I am not sure what the issue here is or what is going on. Does anyone have a clue as to what this is and can help me with this???


[SCENARIO 2]
Now in the code where it says "LOOK HERE", line 1 is voided out and line 2 is active. If I were to swap those out (Make line 1 active and make line 2 voided out), then I can also run my code. I get the first message that says, "Is a SQL workspace" but I get another error:

System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to interface type 'ESRI.ArcGIS.Geodatabase.IsqlWorkspace'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{EED692F3-04E5-42D8-A232-4AAD0085E471}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). This error is in referrence to line 1 (ISqlWorkspace pSQL_WS = (ISqlWorkspace)myWorkspace), the line that isn't voided out.

I've been googling these errors and trying to find a solution but nothing seems to be working. I'm wondering if there may be a better way to write my code or if I'm missing something. I'm just not sure. Any help on this topic would be great and you would have my praise. Thanks in advance.

Outcomes