Can Data connection accessible in Object Library?

Best Answers

• Options

  khooker Posts: 4 ✭✭

  June 2019 Answer ✓

  Here's a small custom Activity where a data connection is used.  The activity allows you to select a data connection, then allows you to type in the name of a stored procedure that will execute when the activity runs.  

  ExecuteStoredProcedure.zip 226.8K

  6
• Options

  strotelli Posts: 42 admin

  June 2019 edited August 2019 Answer ✓

  It depends on what you mean by better. There are advantages to using the System.Data.SqlClient.SqlConnection, but you're connection string will be stored in plaintext in your code. 
  
  But if you would like to reference an existing Grooper Data Connection here is an example of my colleague's script. 
  
  
  

  ‘This makes the property appear in the UI.

  <DataMember> Public DataConnectionId As Guid = Guid.Empty

      ''' <summary>Specify the database table that will store the audit results.</summary>

      <DisplayName("Database Table"), Category("Connections"), Viewable, UI(GetType(NodeReferenceEditor)), Required>

      Public Property DatabaseTable As DatabaseTable

          Get

              If (DataConnectionId = Guid.Empty) Then Return Nothing

              Return Root.GetNode(DataConnectionId)

          End Get

          Set(value As DatabaseTable)

              If (value Is Nothing) Then

                  DataConnectionId = Guid.Empty

              Else

                  DataConnectionId = value.Id

              End If

          End Set

      End Property

   

  ‘This custom function executes the query, saves to a Data.DataTable, and closes the SQL connection. I pass everything to a function like this as much as possible to keep the sql connection closed efficiently. The command parameter is statement you’d write in SQL.(Select, Update, Insert Into, Delete, Etc.)

   

      Private Function Query(command As String) As Data.DataTable

           Dim db As Database = DatabaseTable.DataConnection.CreateInstance

           Dim dt = db.ExecuteDataTable(command)

           db.Disconnect() : db = Nothing

           Return dt

       End Function

  
  

  6