How to convert IWorkspace to SqlClient.SqlConnection??

Question

Here's the deal. I can't have user input (for reasons I can not explain here). I will have a specific Layers in the TOC that I can obtain a FeatureClass and therefore its Workspace. I need to convert that somehow to SqlClient.SqlConnection so I can run stored procedures from the databases that the FeatureClass is apart of. The following code is the closest I've come to converting the IWorkspace, but it is to an ADODB.Connection and I need to stick with SqlCient.SqlConnection due to conformity requirements in my programming department. Anyone know a way??

' _featureClass is obtained from a specific layer found in the TOC

Dim _workspace As ESRI.ArcGIS.Geodatabase.IWorkspace
_workspace = _featureClass.FeatureDataset.Workspace

Dim _fdoToadoConnection As ESRI.ArcGIS.DataSourcesOleDB.IFDOToADOConnection = New ESRI.ArcGIS.DataSourcesOleDB.FdoAdoConnection

Dim _adoConnection As ADODB.Connection
_adoConnection = CType(_fdoToadoConnection.CreateADOConnection(_workspace), ADODB.Connection)

JamesCrandall · Answer

First, thanks for your response. That was the way I was leaning too, but i didn't want to have hardcode a connection string. Since the MXD requires certain layers in the TOC I have access to the database, and planned on using the layers connection to gain access to the rest of the database.

Hmmm -- there is probably a way to get the name of the server and database from a layer loaded in the TOC.  I'd have to dig a bit further into it.  You could basically just get those required parameters and still utilize Windows Authentication in the GetConnectionString Function:
Private Function GetConnectionString(ByVal paramServerName, ByVal paramDataBaseName) As String

Return "Data Source=" & paramServerName & ";" _
        & "Initial Catalog=" & "paramDataBaseName & "; Integrated Security=True;"

End Function

I want to apologize for this next stupid question up front, but are you able to use the same connection string for an ADODB.Connection as you are for a SqlClient.SqlConnection or a IWorkspace connection? I was under the impression that they were all in different formats and were not interchangable.

Don't have a good / direct answer for you here.  The SqlClient.SqlConnection is an ADO.NET thing, so my best GUESS would be, no, they're different.  But I'd have to dig a bit further to find out for sure.

ChrisAnderson · Answer

First, thanks for your response. That was the way I was leaning too, but i didn't want to have hardcode a connection string. Since the MXD requires certain layers in the TOC I have access to the database, and planned on using the layers connection to gain access to the rest of the database.

I want to apologize for this next stupid question up front, but are you able to use the same connection string for an ADODB.Connection as you are for a SqlClient.SqlConnection or a IWorkspace connection? I was under the impression that they were all in different formats and were not interchangable.

JamesCrandall · Answer

I have implemented my own DataAccess Layer and classes for the Data-Centric GIS applications I build/maintain.  While these are typically N-Tier applications that are also "GIS", I tend to consolodate my db connections (across servers sometimes) into their own classes.  Here is an example of a class you could implement that you'd add to your project/assembly (or into another assembly you could reference -- it doesn't really matter).

Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class vrConn
    Implements IDisposable
    Private disposedValue As Boolean = False
    Private _LastException As SqlException
    Private sqlCn As New SqlConnection()
    Public Sub New()
        MyBase.New()
        Me.openConnection(GetConnectionString)

End Sub
    Public Property sqlConn() As SqlConnection
        Get
            Return sqlCn
        End Get
        Set(ByVal value As SqlConnection)
            sqlCn = value
        End Set
    End Property
    Public Overrides Function ToString() As String
        If Me.sqlCn Is Nothing Then
            Return ""
        Else
            Return Me.sqlCn.ToString
        End If
    End Function
    Public Sub openConnection(ByVal cnnString As String)
        If sqlCn.State <> ConnectionState.Open Then
            Try
                If Me.sqlCn Is Nothing Then
                    Me.sqlCn = New SqlConnection
                End If
                sqlCn.ConnectionString = cnnString
                sqlCn.Open()
            Catch sqlEx As SqlException
                Me._LastException = sqlEx
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

End If
    End Sub
    Public Sub CloseConnection()
        If sqlCn.State = ConnectionState.Open Then
            sqlCn.Close()
        End If
        Me.Dispose()
    End Sub
    Private Function GetConnectionString() As String

Return "Data Source=ServerName;" _
        & "Initial Catalog=DataBaseName; Integrated Security=True;"

End Function
    Public ReadOnly Property LastException() As SqlException
        Get
            Return _LastException
        End Get
    End Property
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                Me.sqlCn = Nothing
                GC.Collect()
            End If
        End If
        Me.disposedValue = True
    End Sub
#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region
End Class

Ok, so once you have this class in your project, you can just reference this and open the connection from whatever other class/form/whatever you are trying to run the StoredProcedure from.  This happens to be a "DALC" class (DataAccessLayerClass) I use in one of my assemblies:

Imports System.Data.Sql
Imports System.Data.SqlClient

Private sqlCn As New SqlConnection()
Private connSvc As vrConn

Public Sub New()
        MyBase.New()
        connSvc = New vrConn
        sqlCn = connSvc.sqlConn
End Sub

'Here is a funciton that fills a DataTable of "Species" via a Stored Procedure

Public Function Hydrate_speciesList() As DataTable

Dim spCmdSpec As SqlCommand = New SqlCommand()
        spCmdSpec.CommandText = "dbo.SpeciesSelect"
        spCmdSpec.CommandType = CommandType.StoredProcedure

Dim ds As New DataSet()
        Dim da As New SqlDataAdapter

Try

Me.OpenConnection()

Using spCmdSpec
                spCmdSpec.Connection = Me.sqlCn
                da.SelectCommand = spCmdSpec
                da.Fill(ds, "species")
            End Using

Me.CloseConnection()

Dim tb As DataTable = ds.Tables("species")
            tb.Rows.RemoveAt(0)

Return tb

Catch ex As Exception
            MsgBox(ex.ToString)
            Me.CloseConnection()
            Return Nothing
        End Try

End Function

'database connectivity
    Private Sub CloseConnection()
        If sqlCn.State = ConnectionState.Open Then
            sqlCn.Close()
        End If
    End Sub
    Private Sub OpenConnection()
        If sqlCn.State = ConnectionState.Closed Then
            sqlCn.Open()
        End If
    End Sub