Hello Everyone,
Wish you all a Happy New Year!! Hope you are doing well!
Recently I was assigned with a task to generate a custom report/Application Form Softcopy from K2 application, there is something which our team have already developed and being used for this purpose. We have a word document as template which has replaceable fields mentioned in a specific format. There are around 300 – 400 fields. All the data is residing in Database tables, and then there is a .Net Assembly which takes 400 optional string parameters and template file path as input and reads through template document and replaces the fields in document if parameters has values and saves the file in a specific location on the server and returns the generated file path. So, to use this dll we have created a smart object.
Now the process that we are currently doing is
Step 1: Get all the required fields for report generation from Database using a Smartobject.
Step 2: Call the Smartobject for ReportGeneration DLL using all the fields received from step 1.
This method works absolutely fine but what I felt was that why we need to make 2 calls (one to get data and second to pass the data to dll and generate report) why not just make one call with reference number that we have to database and then let database store procedure take care of gathering data & report generation
So that’s when I started to look at options to call a .Net DLL from stored procedure 😊 and found out that there are ways to achieve this. let’s learn this together with below sample I tried.
We have 3 Steps to execute this sample
Step 1: Create a Sample Assembly with a method.
Step 2: Add Assembly to SQL Database and Create StoredProcedure on the Assembly Method.
Step 3: Create K2 Smart object on SQL Procedure and execute.
Step 1: Create a Sample Assembly with a method.
I have created a Sample Assembly which has 2 methods, they take a string value as input and returns character count. Below is the C# code for the Assembly & class with sample methods.
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//Custom Workspaces
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
namespace VijayTestAssembly
{
public class VijayTestClass
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void VijaySQLTestMethod(string inputString)
{
SqlDataRecord sqlDR = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData(“StringCount”, SqlDbType.Int) });
sqlDR.SetInt32(0, inputString.Length);
SqlContext.Pipe.Send(sqlDR);
}
[SqlFunction(IsDeterministic = true,IsPrecise =true)]
public static int VijaySQLTestFunction(string inputString2)
{
return(inputString2.Length);
}
}
}
Let’s dig a little deep into the code above.
- Class needs to be “public” & methods needs to be “public static”.
- [Microsoft.SqlServer.Server.SqlProcedure] – This is to say that this method will be called from SQL Stored Procedure. There are other Types which are supported, details can be found at below link.
Also return type of the method we mentioned as “void” but return will be sent using “SqlContext.Pipe.Send”
SqlDataRecord sqlDR = new SqlDataRecord(new SqlMetaData[] { new (“StringCount”, SqlDbType.Int) });
sqlDR.SetInt32(0, inputString.Length);
SqlContext.Pipe.Send(sqlDR);
There are different types Data SqlDataRecord that we return, check the Microsoft documentation at the link specified above.
- This code is for a SQL function.
[SqlFunction(IsDeterministic = true,IsPrecise =true)]
public static int VijaySQLTestFunction(string inputString2)
{
return(inputString2.Length);
}
Now the Building Sample Assembly part is done. Just Build it and check for any errors and once done copy the DLL path.
Step 2: Add Assembly to SQL Database and Create StoredProcedure on the Assembly Method.
Now we need to execute below SQL Queries in your database in sequence as I’m mentioning for registering the DLL
- Enable CLR (by Default it will OFF)
sp_configure ‘clr enabled’, 1
GO
RECONFIGURE
GO
- Register Assembly (Give the dll path and name)
CREATE ASSEMBLY VijayTestAssembly
FROM ‘C:\Users\k2admin\Desktop\Vijay\Assemblies\VijayTestAssembly\VijayTestAssembly\bin\Release\VijayTestAssembly.dll’
WITH PERMISSION_SET = SAFE
Check if the assembly has been created.
You can also create from the window by using right clicking on the Assemblies Folder and select New Assembly and fill the details
- Create Stored Procedure with created assembly as external reference
CREATE PROCEDURE spStringLengthFromAssembly (@inputString nvarchar(max))
AS
EXTERNAL NAME VijayTestAssembly.[VijayTestAssembly.VijayTestClass].VijaySQLTestMethod
GO
EXEC spStringLengthFromAssembly ‘This is a TEST String’
GO
You see the from the DLL if you remember we mentioned a SQL data record with name “StringCount” so you can see same column name here in result 😊
SqlDataRecord sqlDR = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData(“StringCount”, SqlDbType.Int) });
- Let’s use the other Assembly Method to create a Function, its almost same process as we created StoredProcedure but with some minor change as mentioned in below query
CREATE FUNCTION fnStringLengthFromAssembly(@inputString NVARCHAR(MAX))
Returns INT
AS
EXTERNAL NAME VijayTestAssembly.[VijayTestAssembly.VijayTestClass].VijaySQLTestFunction
- Once above query executed successfully Let’s test the function.
SELECT dbo.fnStringLengthFromAssembly(‘This is a TEST String’) AS ‘StringLength’
GO
Step 3: Create K2 Smart object on SQL Procedure and execute.
Now You might be thinking that its just easy thing to create a smartobject on this stored procedure and use it from K2 but there a twist here 😉. After you create a Service Instance on this Data Base, K2 is unable to read the output parameters of stored procedure that we created (In Our Case the output parameter should be “StringCount”) and see below screen shot from K2 SmartObjects Tester Utility, we are able to see input parameter “InputString” but no return Parameter.
* Let me know if I can do something to fix this. But it’s strange, right? 🙂
So as an alternative approach I have shown you how to create a function on top of .Net assembly and now we will creating one more Stored Procedure in SQL which will call the function inside and return the result for us.
- Execute below SQL
CREATE PROCEDURE spWrapperStringLengthFromFunction(@inputString NVARCHAR(MAX))
AS
BEGIN
–Call the Function
SELECT dbo.fnStringLengthFromAssembly(@inputString) AS ‘StringLength’
END
GO
- TEST the Procedure & Check result
EXEC spWrapperStringLengthFromFunction ‘THIS is a test string2’
GO
Once the result is Ok, just refresh your service instance and Check the for this new Stored Procedure.
Now the final step is to create a smartObject and check the result from SMO,
Success!! We can see the result from the SMO.
Now that we have seen this basic sample, we can use this approach and extend it for serving our development purposes.
Below mentioned are few things to be kept in mind while using this approach.
- When there is any change in the DLL, we have to update the Assembly reference using below query
ALTER ASSEMBLY VijayTestAssembly FROM ‘C:\Users\k2admin\Desktop\Vijay\Assemblies\VijayTestAssembly\VijayTestAssembly\bin\Release\VijayTestAssembly.dll’
- Also, we need to consider that if we have referenced any assemblies in our DLL, then we need to copy those Referenced assemblies while creating assembly in SQL.
- Also, we can explore other return types of SqlContext.Pipe.Send().
Just to iterate please find all the queries that we have executed below
–STEP 1 ENABLE CLR
sp_configure ‘clr enabled’, 1
GO
RECONFIGURE
GO
–***************************************************************************
— STEP 2 CREATE ASSEMBLY REFERENCE
CREATE ASSEMBLY VijayTestAssembly
FROM ‘C:\Users\k2admin\Desktop\Vijay\Assemblies\VijayTestAssembly\VijayTestAssembly\bin\Release\VijayTestAssembly.dll’
WITH PERMISSION_SET = SAFE
GO
–***************************************************************************
— STEP 3 CREATE STORED PROCEDURE
CREATE PROCEDURE spStringLengthFromAssembly (@inputString NVARCHAR(MAX))
AS
EXTERNAL NAME VijayTestAssembly.[VijayTestAssembly.VijayTestClass].VijaySQLTestMethod
GO
–***************************************************************************
— STEP 4 TEST STORED PROCEDURE
EXEC spStringLengthFromAssembly ‘This is a TEST String’
GO
–***************************************************************************
–STEP 5 CREATE SQL FUNCTION
CREATE FUNCTION fnStringLengthFromAssembly(@inputString NVARCHAR(MAX))
Returns INT
AS
EXTERNAL NAME VijayTestAssembly.[VijayTestAssembly.VijayTestClass].VijaySQLTestFunction
GO
–***************************************************************************
–STEP 6 TEST SQL FUNCTION
SELECT dbo.fnStringLengthFromAssembly(‘This is a TEST String’) AS ‘StringLength’
GO
–***************************************************************************
–STEP 7 CREATE WRAPPER STORED PROCEDURE ON SQL FUNCTION
CREATE PROCEDURE spWrapperStringLengthFromFunction(@inputString NVARCHAR(MAX))
AS
BEGIN
–Call the Function
SELECT dbo.fnStringLengthFromAssembly(@inputString) AS ‘StringLength’
END
GO
–***************************************************************************
–STEP 8 TEST NEW WRAPPER STORED PROCEDURE
EXEC spWrapperStringLengthFromFunction ‘THIS is a test string2’
GO
–***************************************************************************
–STEP 9 REFRESHING ASSEMBLY
ALTER ASSEMBLY VijayTestAssembly
FROM ‘C:\Users\k2admin\Desktop\Vijay\Assemblies\VijayTestAssembly\VijayTestAssembly\bin\Release\VijayTestAssembly.dll’
GO
That’s it for this Post, Thanks for Reading!!
Uncode K2 