Get Stored Procedure used by a Smart Object using the C# API?


Badge +1

I'm writing a view which gets back information about SmartObjects. In the SmartObject Tester you can get back information about the Stored Procedure being used by a Smart Object and I'm wondering whether I can get this information programatically using the C# APIs?

 

I can successfully get information about a SMO using the SmartMethodDefinition and SmartObjectInfo C# classes. Is there a way I can get the associated Stored Procedures from these classes or do I need to get this information using some other process?


4 replies

Userlevel 1
Badge +8

Hi Ben

 

I'm a little confused by what you are asking for. Can you explain what you mean by "In the SmartObject Tester you can get back information about the Stored Procedure being used by a Smart Object"? What information are you referring to, and where do you see this in the SMO Tester?

 

I assume you referring to a SmartObject you have created using the SQL Server Service Type? Are you referring to actual stored procedures found on the SQL Server database, or to the SQL statements K2 executes when calling a method?

Badge +1
Hi Andrew,
With regards to:
"In the SmartObject Tester you can get back information about the Stored Procedure being used by a Smart Object"? What information are you referring to, and where do you see this in the SMO Tester?
If I open the SmartObject Service Tester, I can browse to my SMO by drilling down from the "SmartObject Explorer" folder. When I get to my SMO I then expand the "Methods" folder. In there I can see my SMO method. If I then drill down into that I can see the SQL Server Service Type (you were right in your assumption) my SMO refers to along with the name of the specific Stored Procedure it is associated with.
To re-iterate I am refering to specific stored procedures which reside within SQL Server. Hope what I've written above is clear.
If the SMO Service tester can get back the stored procedure info I'd hope that the C# API can do the same?

 

 

 

 

 

 

Userlevel 1
Badge +8

Hi Ben, what you are after are the "Service Objects" related to each SmartObject method. When you look in the ServiceObject Explorer SmartObject tester under the SQL Server instance you will see all of the tables, views and stored procedures listed as Service Objects. If you use one of these objects (e.g. a stored procdure) in a SmartObject method then you will see that object when you look at the SmartObject in the SmartObject Explorer (as you have discovered).

 

I have adapted some code I use to document SmartObjects to list just the methods and their related Service Objects.

 

 

        public bool GetServiceObjects(string smartObjectGuid)
{
try
{
SmartObjectClientServer socServer = GetSmtClientServer();
SmartObjectManagementServer somServer = GetSmoManagementServer();

using (socServer.Connection)
{

SmartObject smo = socServer.GetSmartObject(new Guid(smartObjectGuid));

using (somServer.Connection)
{
SmartObjectDefinition smartObjectDef = SmartObjectDefinition.Create(somServer.GetSmartObjectDefinition(true, smo.Guid));

foreach (SmartMethodDefinition methdef in smartObjectDef.Methods)
{

foreach (ExecutionBlock block in methdef.ExecutionBlocks)
{
Console.WriteLine(string.Format("Method Name:{0} | Service Instance Name:{1} | Guid:{2}", methdef.DisplayName, block.ServiceInstance.DisplayName, block.ServiceInstance.Guid));

foreach (ServiceObject svcobj in block.ServiceInstance.ServiceObjects)
{
Console.WriteLine("Service Object: {0}", svcobj.Metadata.DisplayName);
}
}
}
}
}
return true;
}
catch (Exception ex)
{
return false;
}
}

private SmartObjectClientServer GetSmtClientServer()
{
SourceCode.SmartObjects.Client.SmartObjectClientServer serverName = new SmartObjectClientServer();

serverName.CreateConnection();
serverName.Connection.Open(K2ConnectionString);

return serverName;
}

private SmartObjectManagementServer GetSmoManagementServer()
{
// open a K2 connection
SmartObjectManagementServer smoServer = new SmartObjectManagementServer();
smoServer.CreateConnection();
smoServer.Connection.Open(K2ConnectionString);

return smoServer;
}

 

 

Badge +1

Yet again a great answer :)

 

I had already written most of the code you had here but the key information I was missing is the fact that the stored procedure information is stored deep(ish) within the ExecutionBlock object.

 

Thanks again and keep up the good work.I expect you'll be the most Kudo'd contributor on here soon!

Reply