Stored Procedure Event Template

I believe the request was for a modified k2 studio SQL event template that could call stored procedures rather than the standard update/insert SQL table function found in the built in template. Our DBA doesn't allow direct interaction with production SQL tables in our environment, enforcing interaction through SQL stored procedures.

We basically had to modify the K2 supplied SQL template to run a stored proc. It wasn't that hard, and here is some example code below:

Private SqlConn As New System.Data.SqlClient.SqlConnection()
Private sqlComm As New System.Data.SqlClient.SqlCommand()
Private strVar As String = "" 

'------ IS dept ----------------------------------------------
Private sqlConnIS As New System.Data.SqlClient.SqlConnection()
Private sqlCommIS As New System.Data.SqlClient.SqlCommand()
'------ IS dept ----------------------------------------------

Sub Main(ByVal K2 As ServerEventContext)


 Try
'------ IS dept ----------------------------------------------

    Dim sProcStr As String

    sqlConnIS.ConnectionString = "Server=SQLserverHistory;Integrated Security=SSPI;DataBase=DB1;"

    sqlCommIS.CommandType = System.Data.CommandType.Text

    sqlCommIS.Connection = sqlConnIS




    sProcStr = " EXEC dbo.prcITStockCertificateInsert "

    sProcStr += K2.ProcessInstance.DataFields("ProcessID").Value.ToString + ", "

    sProcStr += "'07', "



    ' ACCT_NUMBER

    strVar = ""

    strVar += SourceCode.K2Utilities.XMLFieldMod.GetXMLValue(K2.ActivityInstanceDestination.XmlFields("K2InfoPathSchema").Value, "my:myFields/my:Certificate_Information/my:AccountNumber")

    sProcStr += "'" + strVar.ToString().Replace("'", "''") + "'" + ", "


    strVar = ""

    strVar += SourceCode.K2Utilities.XMLFieldMod.GetXMLValue(K2.ActivityInstanceDestination.XmlFields("K2InfoPathSchema").Value, "my:myFields/my:Certificate_Information/my:cusip")

    sProcStr += "'" + strVar.ToString().Replace("'", "''") + "'" + ", "



    strVar = ""

    strVar += SourceCode.K2Utilities.XMLFieldMod.GetXMLValue(K2.ActivityInstanceDestination.XmlFields("K2InfoPathSchema").Value, "my:myFields/my:Certificate_Information/my:CertNumber")

    sProcStr += "'" + strVar.ToString().Replace("'", "''") + "'" + ", "



    strVar = ""

    strVar += SourceCode.K2Utilities.XMLFieldMod.GetXMLValue(K2.ActivityInstanceDestination.XmlFields("K2InfoPathSchema").Value, "my:myFields/my:Certificate_Information/my:Type")

    sProcStr += "'" + strVar.ToString().Replace("'", "''") + "'" + ", "




    strVar = ""

    strVar += SourceCode.K2Utilities.XMLFieldMod.GetXMLValue(K2.ActivityInstanceDestination.XmlFields("K2InfoPathSchema").Value, "my:myFields/my:Certificate_Information/my:P3Location")

    sProcStr += "'" + strVar.ToString().Replace("'", "''") + "'" + " "


    sqlCommIS.CommandText = sProcStr
'Console.WriteLine("CommandText assigned" ) 

    OpenConnection(sqlConnIS)
'Console.WriteLine("Opened Connection" ) 

    sqlCommIS.ExecuteNonQuery()
'Console.WriteLine("Executed Query.") 

   sqlConnIS.Close()

'Console.WriteLine("Closed Connection to IS") 




 Catch ex As System.Exception
  Throw New System.Exception(ex.Message)
 End Try

' SqlConn.Close()
 K2.Synchronous = True

End Sub

Private Sub OpenConnection(ByRef SqlConn As System.Data.SqlClient.SqlConnection)

 If SqlConn.State = System.Data.ConnectionState.Closed Then
  SqlConn.Open()
 End If

End Sub