private void ExecuteAdoQuery()
{
var adoQuery = GetStringParameter(Constants.Parameters.AdoQuery);
SoDefinition soDefinition =
SoDefCollection.GetSoDefinitionByName(ServiceBroker.Service.ServiceObjects[0].Name);
ServiceBroker.Service.ServiceObjects[0].Properties.InitResultTable();
DataTable results = ServiceBroker.ServicePackage.ResultTable;
DataTable dataTable = new DataTable();
using (SOConnection soConnection = new SOConnection(ServiceBroker.K2Connection.GetSOConnectionString()))
using (SOCommand command = new SOCommand(adoQuery, soConnection))
using (SODataAdapter adapter = new SODataAdapter(command))
{
soConnection.Open();
adapter.Fill(dataTable);
}
foreach (DataRow dr in dataTable.Rows)
{
DataRow dRow = results.NewRow();
foreach (var prop in soDefinition.Properties)
{
dRow[prop.Name] = dr[prop.Name];
}
results.Rows.Add(dRow);
}
}
private void ADOQuery2Excel()
{
ServiceObject serviceObject = ServiceBroker.Service.ServiceObjects[0];
serviceObject.Properties.InitResultTable();
DataTable results = ServiceBroker.ServicePackage.ResultTable;
string fileName = GetStringProperty(Constants.SOProperties.ExportToExcel.FileName, true);
string query = GetStringProperty(Constants.SOProperties.ExportToExcel.ADOQuery, true);
DataTable SOQueryResult = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
using (SOCommand command = new SOCommand(query, connection))
using (SODataAdapter adapter = new SODataAdapter(command))
{
connection.DirectExecution = true;
adapter.Fill(SOQueryResult);
}
DataRow dr = results.NewRow();
//Calling the helper method with dataresult and expecting a File in return.
CreateExcel excel = new CreateExcel();
dr[Constants.SOProperties.ExportToExcel.ExcelFile] = excel.ConvertDataTable2Excelfile(SOQueryResult, fileName).ToString();
results.Rows.Add(dr);
}
private DataTable GetSchema(string query, Dictionary <string, string> props)
{
DataTable results = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (KeyValuePair <string, string> prop in props)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Key, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
adapter.FillSchema(results, SchemaType.Source);
}
}
connection.Close();
}
return(results);
}
private void Join()
{
string query = GetStringProperty(Constants.SOProperties.ADOHelper.ADOQuery, true);
string delimiter = GetStringProperty(Constants.SOProperties.ADOHelper.Delimiter, true);
ServiceObject serviceObject = base.ServiceBroker.Service.ServiceObjects[0];
serviceObject.Properties.InitResultTable();
DataTable results = base.ServiceBroker.ServicePackage.ResultTable;
DataTable adoResults = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
using (SOCommand command = new SOCommand(query, connection))
using (SODataAdapter adapter = new SODataAdapter(command))
{
connection.DirectExecution = true;
connection.Open();
adapter.Fill(adoResults);
}
string result = "";
foreach (DataRow dRow in adoResults.Rows)
{
result += dRow[0].ToString() + delimiter;
}
result = result.Remove(result.LastIndexOf(delimiter));
DataRow resultsRow = results.NewRow();
resultsRow[Constants.SOProperties.ADOHelper.Result] = result;
results.Rows.Add(resultsRow);
}
private void ListQuery()
{
ServiceObject serviceObject = base.ServiceBroker.Service.ServiceObjects[0];
serviceObject.Properties.InitResultTable();
DataTable results = base.ServiceBroker.ServicePackage.ResultTable;
string query = serviceObject.Methods[0].MetaData.GetServiceElement <string>("Query");
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (Property prop in serviceObject.Properties)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Name, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
adapter.Fill(results);
}
}
connection.Close();
}
}
//sample: joining two List methods with a JOIN operator, filtering and returnign TOP N items
static void ExecuteSelectStatementJoin()
{
Console.WriteLine("**Executing SmartObject list method with the ADO Provider SELECT statement");
Console.WriteLine("(return top 20 active workflow instances, join to Activity instances)**");
using (SOConnection soServerConnection = EstablishK2Connection())
{
soServerConnection.Open();
//build up the SQL-92 Query. Notice the user of System Names, not display names
//you can filter with normal WHERE clauses
string query = "SELECT TOP 20 * FROM Process_Instance JOIN Activity_Instance ON Process_Instance.ProcessInstanceID = Activity_Instance.ProcessInstanceID WHERE Process_Instance.Status = 'Active'";
using (SOCommand command = new SOCommand(query, soServerConnection))
{
//in this sample we'll use a Data Adpater so we can fill a datatable
using (SODataAdapter adapter = new SODataAdapter(command))
{
soServerConnection.DirectExecution = true; //direct execution will show performance improvements for SQL-based smartobjects
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("Folio: " + dr["Folio"].ToString()
+ " | ProcInstID: " + dr["ProcessInstanceID"].ToString()
+ " | ActivityName: " + dr["ActivityName"].ToString());
}
}
}
}
Console.WriteLine("**Completed SmartObject list method JOIN with the ADO.NET provider**");
Console.ReadLine();
}
private DataTableReader GetData(string query, Properties props)
{
DataTable results = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (Property prop in props)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Name, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
adapter.Fill(results);
}
}
connection.Close();
}
return(results.CreateDataReader());
}
public static DataTable GetSchema(string connStr, string query, Dictionary<string,string> props)
{
DataTable results = new DataTable();
using (SOConnection connection = new SOConnection(connStr))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (KeyValuePair<string,string> prop in props)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Key, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
adapter.FillSchema(results, SchemaType.Source);
}
}
connection.Close();
}
return results;
}
public static DataTableReader GetData(string connStr, string query, Properties props)
{
DataTable results = new DataTable();
using (SOConnection connection = new SOConnection(connStr))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (Property prop in props)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Name, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
adapter.Fill(results);
}
}
connection.Close();
}
return results.CreateDataReader();
}
private void FilesToZipMethod()
{
ServiceObject serviceObject = ServiceBroker.Service.ServiceObjects[0];
serviceObject.Properties.InitResultTable();
DataTable results = ServiceBroker.ServicePackage.ResultTable;
string fileName = GetStringProperty(Constants.SOProperties.FilesToZip.FileName, true);
string query = GetStringProperty(Constants.SOProperties.FilesToZip.ADOSMOQuery, true);
DataTable SOQueryResult = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
using (SOCommand command = new SOCommand(query, connection))
using (SODataAdapter adapter = new SODataAdapter(command))
{
connection.DirectExecution = true;
adapter.Fill(SOQueryResult);
}
string xmlZipFile = string.Empty;
if (SOQueryResult.Rows.Count > 0)
{
using (var memoryStream = new MemoryStream())
{
using (var archive = new ZipArchive(memoryStream, ZipArchiveMode.Create, true))
{
foreach (DataRow row in SOQueryResult.Rows)
{
XElement fileXml = XElement.Parse(row[0].ToString());
var demoFile = archive.CreateEntry(fileXml.Element("name").Value);
using (var entryStream = demoFile.Open())
using (var b = new BinaryWriter(entryStream))
{
b.Write(Convert.FromBase64String(fileXml.Element("content").Value));
}
}
}
string content = Convert.ToBase64String(memoryStream.ToArray());
xmlZipFile = string.Format("<file><name>{0}.zip</name><content>{1}</content></file>", fileName, content);
}
}
DataRow dr = results.NewRow();
//Calling the helper method with dataresult and expecting a File in return.
CreateExcel excel = new CreateExcel();
dr[Constants.SOProperties.FilesToZip.ZipFile] = xmlZipFile;
results.Rows.Add(dr);
}
/// <summary>
/// Smartobject ADO query
/// </summary>
/// <param name="sql">SQL statment to execute </param>
/// <param name="parameters">query paramters, if used then command type of StoredProcedure is used</param>
/// <param name="directExecution">Will this query use direct execution</param>
/// <returns>A Datatable of results </returns>
public DataTable SmartObjectADOQuery(string sql, Dictionary<string, object> parameters, bool directExecution)
{
SOCommand command = null;
DataTable dataTable = null;
try
{
command = new SOCommand();
command.Connection = new SOConnection(this.SMOServer.Connection.Host, int.Parse(this.SMOServer.Connection.Port.ToString()));
command.Connection.DirectExecution = directExecution;
command.Connection.Port = 5555;
command.CommandText = sql;
if (parameters.Count > 0)
{
command.CommandType = CommandType.StoredProcedure;
foreach (KeyValuePair<string, object> item in parameters)
{
command.Parameters.Add(new SOParameter(item.Key, item.Value.ToString()));
}
}
SODataAdapter adapter = new SODataAdapter(command);
dataTable = new DataTable();
adapter.Fill(dataTable);
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
}
return dataTable;
}
private DataTable GetData(string query, Properties props, bool schemaOnly)
{
DataTable results = new DataTable();
using (SOConnection connection = new SOConnection(base.BaseAPIConnectionString))
{
using (SOCommand command = new SOCommand(query, connection))
{
using (SODataAdapter adapter = new SODataAdapter(command))
{
foreach (Property prop in props)
{
if (prop.Value != null)
{
command.Parameters.AddWithValue(prop.Name, prop.Value);
}
}
connection.DirectExecution = true;
connection.Open();
if (schemaOnly)
{
adapter.FillSchema(results, SchemaType.Source);
}
else
{
adapter.Fill(results);
}
}
}
connection.Close();
}
return results;
}
